Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql cursors
pl/sql cursors [message #36819] Sun, 23 December 2001 19:38 Go to next message
hnetscape
Messages: 30
Registered: December 2001
Location: India
Member

Hi,

I have a following query. Is that procedure is correct or any easier way to get the result.

1 declare
2 CURSOR c2 IS SELECT ao1.compcode,ao1.branchcd,ao1.seriescd,
3 ao1.aono,ao1.slno,ao1.orderqty,nvl(ao1.deliveryqty,0) deliveryqty
4 from saao1 ao1
5 where aono = '20010247'
6 and compcode='02'
7 ORDER BY ao1.compcode,ao1.branchcd,ao1.seriescd,
8 ao1.aono,ao1.slno,ao1.orderqty,ao1.deliveryqty;
9 CURSOR c3 IS SELECT ao2.compcode,ao2.branchcd,ao2.seriescd,
10 ao2.aono,ao2.slno,ao2.deliveryqty,
11 ao2.deliverydt from saao2 ao2
12 where aono = '20010247'
13 and compcode='02'
14 ORDER BY ao2.compcode,ao2.branchcd,ao2.seriescd,
15 ao2.aono,ao2.slno,ao2.deliverydt;
16 v2cocd saao1.compcode%type;
17 v2brcd saao1.branchcd%type;
18 v2scd saao1.seriescd%type;
19 v2aono saao1.aono%type;
20 v2slno saao1.slno%type;
21 v2ordqty saao1.orderqty%type;
22 v2delqty saao1.deliveryqty%type;
23 v3cocd saao2.compcode%type;
24 v3brcd saao2.branchcd%type;
25 v3scd saao2.seriescd%type;
26 v3aono saao2.aono%type;
27 v3slno saao2.slno%type;
28 v3delqty saao2.deliveryqty%type;
29 v3deldt saao2.deliverydt%type;
30 v3bal saao2.deliveryqty%type :=0;
31 v3dspd saao2.deliveryqty%type :=0;
32 cnt saao2.deliveryqty%type;
33 begin
34 dbms_output.enable(1000000);
35 OPEN c2 ;
36 LOOP
37 FETCH c2 INTO v2cocd,v2brcd,v2scd,v2aono,v2slno,v2ordqty,v2delqty;
38 EXIT WHEN c2%NOTFOUND;
39 DBMS_OUTPUT.PUT_LINE('saao1 '||v2slno||' '||v2ordqty||' '||v2delqty);
40 DBMS_OUTPUT.PUT_LINE('sno slno ordqty balqty shdqty dspqty ');
41 open c3;
42 v3bal := v2delqty;
43 cnt := 1;
44 loop
45 FETCH c3 INTO v3cocd,v3brcd,v3scd,v3aono,v3slno,v3delqty,v3deldt;
46 EXIT WHEN c3%NOTFOUND;
47 if (v2cocd=v3cocd and v3brcd=v2brcd and v2scd=v3scd
48 and v2aono=v3aono and v2slno=v3slno) then
49 if cnt=1 then
50 v3bal := v2delqty;
51 v3bal := v2delqty - v3delqty;
52 v3dspd := v3delqty;
53 elsif sign(v3bal - v3delqty)=-1 then
54 v3bal := v3delqty - v3bal;
55 v3dspd := v3delqty - v3bal;
56 v3bal := 0;
57 else
58 v3bal := v3bal - v3delqty;
59 v3dspd := v3delqty;
60 end if;
61 DBMS_OUTPUT.PUT_LINE(' ' || cnt || ' '
62 ||v3slno||' ' || v2ordqty ||' '
63 ||v2delqty||' '
64 ||v3delqty||' '||v3dspd);
65 cnt := cnt +1;
66 end if;
67 v2delqty := v3bal;
68 end loop;
69 close c3;
70 END LOOP;
71 CLOSE c2;
72* end;

How can I make is it run fast and get the result. Is there any way of running nested loops with cursors. When Iam running the query for one particular record it is gettting very fast but when Iam running for whole table it is not running. Both the tables have more than 40,000 records each.

Thanks & Regards,
Balraj.

----------------------------------------------------------------------
Re: pl/sql cursors [message #36824 is a reply to message #36819] Mon, 24 December 2001 03:32 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
declare
cursor c2 is select ao1.compcode,ao1.branchcd,ao1.seriescd,..
from saao1 ao1
ORDER BY ao1.compcode,ao1.branchcd,ao1.seriescd,
ao1.aono,ao1.slno,ao1.orderqty,ao1.deliveryqty;

cursor c2 (cao1 varchar2,cc_code varchar2) IS
select ao2.compcode,ao2.branchcd,ao2.seriescd,..
from saao2 ao2
where aono = '20010247'
and compcode='02'
ORDER BY ao2.compcode,ao2.branchcd,ao2.seriescd,
ao2.aono,ao2.slno,ao2.deliverydt;

-- other declarations

begin

FOR crec in c2 loop
DBMS_OUTPUT.PUT_LINE('saao1 '||crec.slno||' '||crec.ordqty||' '||crec.deliveryqty);
40 DBMS_OUTPUT.PUT_LINE('sno slno ordqty balqty shdqty dspqty ');

FOR crec1 IN C3(crec.aono,crec.comcode) LOOP

-- process here

END LOOP;
END LOOP;
END;

----------------------------------------------------------------------
Previous Topic: plsql
Next Topic: Stored procedure to return rows
Goto Forum:
  


Current Time: Thu Mar 28 06:10:05 CDT 2024