appending to cursor in oracle
Posted
by Omnipresent
on Stack Overflow
See other posts from Stack Overflow
or by Omnipresent
Published on 2010-03-23T18:43:55Z
Indexed on
2010/03/23
19:03 UTC
Read the original article
Hit count: 374
I asked a question yesterday which got answers but didnt answer the main point. I wanted to reduce amount of time it took to do a MINUS
operation.
Now, I'm thinking about doing MINUS
operation in blocks of 5000, appending each iterations results to the cursor and finally returning the cursor.
I have following:
V_CNT NUMBER :=0;
V_INTERVAL NUMBER := 5000;
begin
select count(1) into v_cnt from TABLE_1
while (v_cnt > 0)
loop
open cv_1 for
SELECT A.HEAD,A.EFFECTIVE_DATE,
FROM TABLE_1 A
WHERE A.TYPE_OF_ACTION='6' AND A.EFFECTIVE_DATE >= ADD_MONTHS(SYSDATE,-15)
AND A.ROWNUM <= V_INTERVAL
MINUS
SELECT B.head,B.EFFECTIVE_DATE,
FROM TABLE_2 B
AND B.ROWNUM <= V_INTERVAL
V_CNT := V_CNT - V_INTERVAL;
END LOOP;
end;
However, as you see...in each iteration the cursor is overwritten. How can I change the code so that in each iteration it appends to cv_1
cursor rather than overwriting?
© Stack Overflow or respective owner