The code below is working as expected. Done checking logic error, syntax error at the beginning, it just wont help (or I'd missed some?)
The problem is the outer cursor is working and would display every record in it, but the inner cursor is just act like invisible and display nothing nor error message (show errors? No error).
But once the inner explicit cursor replaced by the FOREACH LOOP, the procedure result is perfect.
CREATE OR REPLACE PROCEDURE prc_order_details AS
--variable declaration
v_orderNo ORDERS.orderNumber%TYPE;
v_orderDate ORDERS.orderDate%TYPE;
v_requiredDate ORDERS.requiredDate%TYPE;
v_shippedDate ORDERS.shippedDate%TYPE;
v_custNo ORDERS.customerNumber%TYPE;
v_productCode ORDERDETAILS.productCode%TYPE;
v_qtyOrd ORDERDETAILS.quantityOrdered%TYPE;
v_priceEach ORDERDETAILS.priceEach%TYPE;
--cursor declaration
cursor order_cursor is
select customerNumber, orderNumber, orderDate, requiredDate, shippedDate
from ORDERS;
cursor orderDetail_cursor is
select productCode, quantityOrdered, priceEach
from ORDERDETAILS
where orderNumber = v_orderNo;
--processing
BEGIN
OPEN order_cursor;
LOOP
FETCH order_cursor
INTO v_custNo, v_orderNo, v_orderDate, v_requiredDate, v_shippedDate;
DBMS_OUTPUT.PUT_LINE('Customer No : '||v_custNo);
DBMS_OUTPUT.PUT_LINE('Order No : '||v_orderNo);
DBMS_OUTPUT.PUT_LINE('Order Date : '||v_orderDate);
DBMS_OUTPUT.PUT_LINE('Shipped : '||v_shippedDate);
DBMS_OUTPUT.PUT_LINE('Required Date : '||v_requiredDate);
dbms_output.put_line(chr(10));
/*--- these code did not work as expected ----
OPEN orderDetail_cursor;
IF orderDetail_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE('got something to fetch');
ELSIF orderDetail_cursor%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('got nothing to fetch');
END IF;
WHILE orderDetail_cursor%FOUND
LOOP
FETCH orderDetail_cursor
INTO v_productCode, v_qtyOrd, v_priceEach;
DBMS_OUTPUT.PUT_LINE(v_productCode||'***'||v_qtyOrd||'***'||v_priceEach);
END LOOP;
CLOSE orderDetail_cursor;
*/
FOR detail IN orderDetail_cursor
LOOP
dbms_output.put_line(detail.productCode||'***'||detail.quantityOrdered||'***'||detail.priceEach);
END LOOP;
DBMS_OUTPUT.PUT_LINE('End of Customer '||v_custNo||'************************');
dbms_output.put_line(chr(10));
EXIT WHEN order_cursor%NOTFOUND;
END LOOP;
CLOSE order_cursor;
END;
/
Now it seems like the OPEN statement in a LOOP is not allowed, is it the reason? If not please revive a brain dead man.
This is wrong:
OPEN orderDetail_cursor;
IF orderDetail_cursor%FOUND THEN
It is never true. Why? Because you have to fetch first to see whether something was (or was not) found.
I don't have your tables so I'll demonstrate it on Scott's sample schema. This simulates your code:
SQL> declare
2 v_deptno number;
3 v_ename varchar2(10);
4 v_job varchar2(20);
5 cursor cout is select deptno from dept order by deptno;
6 cursor cin is
7 select ename, job from emp
8 where deptno = v_deptno;
9 begin
10 open cout;
11 loop
12 fetch cout into v_deptno;
13 dbms_output.put_line('Deptno = ' || v_deptno);
14
15 open cin;
16 if cin%found then
17 dbms_output.put_line('got something');
18 else
19 dbms_output.put_line('got nothing');
20 end if;
21
22 while cin%found loop
23 fetch cin into v_ename, v_job;
24 dbms_output.put_line(v_ename ||': '|| v_job);
25 end loop;
26
27 close cin;
28 exit when cout%notfound;
29 end loop;
30 close cout;
31 end;
32 /
Result is:
Deptno = 10
got nothing
Deptno = 20
got nothing
Deptno = 30
got nothing
Deptno = 40
got nothing
Deptno = 40
got nothing
PL/SQL procedure successfully completed.
SQL>
But, if you fetch (after opening the cursor) (see line #16):
SQL> declare
2 v_deptno number;
3 v_ename varchar2(10);
4 v_job varchar2(20);
5 cursor cout is select deptno from dept order by deptno;
6 cursor cin is
7 select ename, job from emp
8 where deptno = v_deptno;
9 begin
10 open cout;
11 loop
12 fetch cout into v_deptno;
13 dbms_output.put_line('Deptno = ' || v_deptno);
14
15 open cin;
16 fetch cin into v_ename, v_job; --> here
17 if cin%found then
18 dbms_output.put_line('got something');
19 else
20 dbms_output.put_line('got nothing');
21 end if;
22
23 while cin%found loop
24 fetch cin into v_ename, v_job;
25 dbms_output.put_line(v_ename ||': '|| v_job);
26 end loop;
27
28 close cin;
29 exit when cout%notfound;
30 end loop;
31 close cout;
32 end;
33 /
Result:
Deptno = 10
got something
KING: PRESIDENT
MILLER: CLERK
MILLER: CLERK
Deptno = 20
got something
JONES: MANAGER
SCOTT: ANALYST
ADAMS: CLERK
FORD: ANALYST
FORD: ANALYST
Deptno = 30
got something
WARD: SALESMAN
MARTIN: SALESMAN
BLAKE: MANAGER
TURNER: SALESMAN
JAMES: CLERK
JAMES: CLERK
Deptno = 40
got nothing
Deptno = 40
got nothing
PL/SQL procedure successfully completed.
SQL>
Quite a difference, is it not?