Information Requesting Question

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.

CodePudding user response：

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?