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  /100200ERRORPL/SQL procedure successfully completedSQL>

(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  /100200ERRORPL/SQL procedure successfully completedSQL>

(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  /100200300ERRORPL/SQL procedure successfully completedSQL>

(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  /100200300PL/SQL procedure successfully completedSQL>

答案D

答案说明65楼

2011-10-19 答案：D(A)这个选项的结果是：100200ERROR这是因为我关闭C1之后，C2也会被关闭，所以第三个FETCH会抛出"ORA-01001: invalid cursor"异常。(B)同上，只要C1,C2其中一个被关闭，另一个就相应被关闭。(C)这个选项的结果是：100200300ERROR既然我在三个FETCH结束前没有关闭游标，我就能看到100-300。但是随后我关闭了C1, 而且还试图关闭C2。C1一旦被关闭，C2也自动被关闭，因此假如试图再关闭C2就会报"ORA-01001: invalid cursor"错误。(D)正确