資訊請求問題

下面的代碼按預期作業。完成檢查邏輯錯誤，一開始的語法錯誤，它只是無濟于事（或者我錯過了一些？）

問題是外部游標正在作業并且會顯示其中的每條記錄，但內部游標就像不可見并且不顯示任何內容或錯誤訊息（顯示錯誤？沒有錯誤）。

但是一旦將內部顯式游標替換為FOREACH LOOP，程式結果就完美了。

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;
/

現在似乎不允許LOOP中的OPEN陳述句，這是原因嗎？如果不是，請復活一個腦死亡的人。

uj5u.com熱心網友回復：

這是錯誤的：

OPEN orderDetail_cursor;
IF orderDetail_cursor%FOUND THEN

這從來都不是真的。為什么？因為您必須先獲取以查看是否（或未）找到某些東西。

我沒有你的表，所以我將在 Scott 的示例模式中演示它。這模擬了您的代碼：

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  /

結果是：

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>

但是，如果您獲取（打開游標后）（參見第 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  /

結果：

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>

差別很大，不是嗎？

標籤：甲骨文 循环 plsql 拿来 数据库游标

上一篇：R回圈線性回歸

下一篇：AlpineJs：回圈中的回圈-如何在第二個回圈中使用第一個變數？