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;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('ERROR');
END;
/下列的选项中哪些可用来代替上文的 , 从而使得这个块执行之后会显示下列三行：
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)正确
