Rewrite into SQL
- From: amonte <ax.mount@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 9 Mar 2009 22:50:17 +0100
Hi all
I have a simple piece of pl/sql code which can be written into SQL, I have
rewritten but was wondering if anyone knows a more efficient way to do it?
*Original code:*
BEGIN
FOR i IN ( SELECT cntr_no,
max(update_time) arrive_date,
max(trace_seq) mysequence
FROM trace_inv
WHERE data_field= 'DELETE'
AND update_time <= sysdate - 60
GROUP BY cntr_no;
LOOP
INSERT INTO BACKUP_TRACE
SELECT *
FROM trace_inv
WHERE cntr_no = i.cntr_no
AND trace_seq <= mysequence;
END LOOP;
END;
/
*Rewritten:*
INSERT INTO backup_trace
SELECT *
FROM trace_inv y
WHERE trace_seq <= (SELECT max(trace_seq)
FROM trace_inv x
where x.cntr_no = y.cntr_no
AND x.update_time <= SYSDATE - 60
AND x.data_field= 'DELETE');
Thanks all
Alex
Other related posts:
