2011-10-19 對REF CURSOR 的理解

http://www.itpub.net/thread-1499223-7-1.html

64樓

我創建了這張表并填入了數據：

CREATE TABLE plch_employees
(
   employee_id   INTEGER
,  last_name     VARCHAR2 (100)
,  salary        NUMBER
)
/
 
BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Ellison', 1000000);
   INSERT INTO plch_employees
        VALUES (200, 'Gates', 1000000);
   INSERT INTO plch_employees
        VALUES (300, 'Zuckerberg', 1000000);
   COMMIT;
END;
/

然后我寫了這個塊：

DECLARE
   c1     SYS_REFCURSOR;
   c2     SYS_REFCURSOR;
   l_id   plch_employees.employee_id%TYPE;
BEGIN
   OPEN c1 FOR
        SELECT employee_id FROM plch_employees
      ORDER BY last_name;
   /*FINISH*/
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR');
END;
/

下列的選項中哪些可用來代替上文的 /*FINISH*/, 從而使得這個塊執行之后會顯示下列三行：
100
200
300

(A)

FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
c2 := c1;
FETCH c2 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
CLOSE c1;
FETCH c2 INTO l_id;
DBMS_OUTPUT.put_line (l_id);

SQL> DECLARE
  2    c1   SYS_REFCURSOR;
  3    c2   SYS_REFCURSOR;
  4    l_id plch_employees.employee_id%TYPE;
  5  BEGIN
  6    OPEN c1 FOR
  7      SELECT employee_id FROM plch_employees ORDER BY last_name;
  8  
  9    FETCH c1
 10      INTO l_id;
 11    DBMS_OUTPUT.put_line(l_id);
 12  
 13    c2 := c1;
 14  
 15    FETCH c2
 16      INTO l_id;
 17    DBMS_OUTPUT.put_line(l_id);
 18  
 19    CLOSE c1;
 20  
 21    FETCH c2
 22      INTO l_id;
 23    DBMS_OUTPUT.put_line(l_id);
 24  
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      DBMS_OUTPUT.put_line('ERROR');
 28  END;
 29  /
100
200
ERROR
PL/SQL procedure successfully completed
SQL>

(B)

FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
c2 := c1;
FETCH c2 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
CLOSE c2;
FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);

SQL> DECLARE
  2    c1   SYS_REFCURSOR;
  3    c2   SYS_REFCURSOR;
  4    l_id plch_employees.employee_id%TYPE;
  5  BEGIN
  6    OPEN c1 FOR
  7      SELECT employee_id FROM plch_employees ORDER BY last_name;
  8  
  9    FETCH c1
 10      INTO l_id;
 11    DBMS_OUTPUT.put_line(l_id);
 12  
 13    c2 := c1;
 14  
 15    FETCH c2
 16      INTO l_id;
 17    DBMS_OUTPUT.put_line(l_id);
 18  
 19    CLOSE c2;
 20  
 21    FETCH c1
 22      INTO l_id;
 23    DBMS_OUTPUT.put_line(l_id);
 24  
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      DBMS_OUTPUT.put_line('ERROR');
 28  END;
 29  /
100
200
ERROR
PL/SQL procedure successfully completed
SQL>

(C)

FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
c2 := c1;
FETCH c2 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
CLOSE c1;
CLOSE c2;

SQL> DECLARE
  2    c1   SYS_REFCURSOR;
  3    c2   SYS_REFCURSOR;
  4    l_id plch_employees.employee_id%TYPE;
  5  BEGIN
  6    OPEN c1 FOR
  7      SELECT employee_id FROM plch_employees ORDER BY last_name;
  8  
  9    FETCH c1
 10      INTO l_id;
 11    DBMS_OUTPUT.put_line(l_id);
 12  
 13    c2 := c1;
 14  
 15    FETCH c2
 16      INTO l_id;
 17    DBMS_OUTPUT.put_line(l_id);
 18  
 19    FETCH c1
 20      INTO l_id;
 21    DBMS_OUTPUT.put_line(l_id);
 22  
 23    CLOSE c1;
 24    CLOSE c2;
 25  
 26  EXCEPTION
 27    WHEN OTHERS THEN
 28      DBMS_OUTPUT.put_line('ERROR');
 29  END;
 30  /
100
200
300
ERROR
PL/SQL procedure successfully completed
SQL>

(D)

FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
c2 := c1;
FETCH c2 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
FETCH c1 INTO l_id;
DBMS_OUTPUT.put_line (l_id);
CLOSE c1;

SQL> DECLARE
  2    c1   SYS_REFCURSOR;
  3    c2   SYS_REFCURSOR;
  4    l_id plch_employees.employee_id%TYPE;
  5  BEGIN
  6    OPEN c1 FOR
  7      SELECT employee_id FROM plch_employees ORDER BY last_name;
  8  
  9    FETCH c1
 10      INTO l_id;
 11    DBMS_OUTPUT.put_line(l_id);
 12  
 13    c2 := c1;
 14  
 15    FETCH c2
 16      INTO l_id;
 17    DBMS_OUTPUT.put_line(l_id);
 18  
 19    FETCH c1
 20      INTO l_id;
 21    DBMS_OUTPUT.put_line(l_id);
 22  
 23    CLOSE c1;
 24  
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      DBMS_OUTPUT.put_line('ERROR');
 28  END;
 29  /
100
200
300
PL/SQL procedure successfully completed
SQL>

答案D

答案說明65樓

2011-10-19 答案：D
(A)
這個選項的結果是：
100
200
ERROR
這是因為我關閉C1之后，C2也會被關閉，所以第三個FETCH會拋出"ORA-01001: invalid cursor"異常。
(B)
同上，只要C1,C2其中一個被關閉，另一個就相應被關閉。
(C)
這個選項的結果是：
100
200
300
ERROR
既然我在三個FETCH結束前沒有關閉游標，我就能看到100-300。但是隨后我關閉了C1, 而且還試圖關閉C2。C1一旦被關閉，C2也自動被關閉，因此假如試圖再關閉C2就會報"ORA-01001: invalid cursor"錯誤。
(D)正確