2011-10-19 对REF CURSOR 的理解

2025-04-13 技术教程

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

64楼

我创建了这张表并填入了数据：

CREATETABLEplch_employees(employee_idINTEGER,last_nameVARCHAR2(100),salaryNUMBER)/BEGININSERTINTOplch_employeesVALUES(100,'Ellison',1000000);INSERTINTOplch_employeesVALUES(200,'Gates',1000000);INSERTINTOplch_employeesVALUES(300,'Zuckerberg',1000000);COMMIT;END;/

然后我写了这个块：

DECLAREc1SYS_REFCURSOR;c2SYS_REFCURSOR;l_idplch_employees.employee_id%TYPE;BEGINOPENc1FORSELECTemployee_idFROMplch_employeesORDERBYlast_name;/*FINISH*/EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.put_line('ERROR');END;/

下列的选项中哪些可用来代替上文的 /*FINISH*/, 从而使得这个块执行之后会显示下列三行：
100
200
300

(A)

FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);c2:=c1;FETCHc2INTOl_id;DBMS_OUTPUT.put_line(l_id);CLOSEc1;FETCHc2INTOl_id;DBMS_OUTPUT.put_line(l_id);

SQL>DECLARE2c1SYS_REFCURSOR;3c2SYS_REFCURSOR;4l_idplch_employees.employee_id%TYPE;5BEGIN6OPENc1FOR7SELECTemployee_idFROMplch_employeesORDERBYlast_name;89FETCHc110INTOl_id;11DBMS_OUTPUT.put_line(l_id);1213c2:=c1;1415FETCHc216INTOl_id;17DBMS_OUTPUT.put_line(l_id);1819CLOSEc1;2021FETCHc222INTOl_id;23DBMS_OUTPUT.put_line(l_id);2425EXCEPTION26WHENOTHERSTHEN27DBMS_OUTPUT.put_line('ERROR');28END;29/100200ERRORPL/SQLproceduresuccessfullycompletedSQL>

(B)

FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);c2:=c1;FETCHc2INTOl_id;DBMS_OUTPUT.put_line(l_id);CLOSEc2;FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);

SQL>DECLARE2c1SYS_REFCURSOR;3c2SYS_REFCURSOR;4l_idplch_employees.employee_id%TYPE;5BEGIN6OPENc1FOR7SELECTemployee_idFROMplch_employeesORDERBYlast_name;89FETCHc110INTOl_id;11DBMS_OUTPUT.put_line(l_id);1213c2:=c1;1415FETCHc216INTOl_id;17DBMS_OUTPUT.put_line(l_id);1819CLOSEc2;2021FETCHc122INTOl_id;23DBMS_OUTPUT.put_line(l_id);2425EXCEPTION26WHENOTHERSTHEN27DBMS_OUTPUT.put_line('ERROR');28END;29/100200ERRORPL/SQLproceduresuccessfullycompletedSQL>

(C)

FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);c2:=c1;FETCHc2INTOl_id;DBMS_OUTPUT.put_line(l_id);FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);CLOSEc1;CLOSEc2;

SQL>DECLARE2c1SYS_REFCURSOR;3c2SYS_REFCURSOR;4l_idplch_employees.employee_id%TYPE;5BEGIN6OPENc1FOR7SELECTemployee_idFROMplch_employeesORDERBYlast_name;89FETCHc110INTOl_id;11DBMS_OUTPUT.put_line(l_id);1213c2:=c1;1415FETCHc216INTOl_id;17DBMS_OUTPUT.put_line(l_id);1819FETCHc120INTOl_id;21DBMS_OUTPUT.put_line(l_id);2223CLOSEc1;24CLOSEc2;2526EXCEPTION27WHENOTHERSTHEN28DBMS_OUTPUT.put_line('ERROR');29END;30/100200300ERRORPL/SQLproceduresuccessfullycompletedSQL>

(D)

FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);c2:=c1;FETCHc2INTOl_id;DBMS_OUTPUT.put_line(l_id);FETCHc1INTOl_id;DBMS_OUTPUT.put_line(l_id);CLOSEc1;

SQL>DECLARE2c1SYS_REFCURSOR;3c2SYS_REFCURSOR;4l_idplch_employees.employee_id%TYPE;5BEGIN6OPENc1FOR7SELECTemployee_idFROMplch_employeesORDERBYlast_name;89FETCHc110INTOl_id;11DBMS_OUTPUT.put_line(l_id);1213c2:=c1;1415FETCHc216INTOl_id;17DBMS_OUTPUT.put_line(l_id);1819FETCHc120INTOl_id;21DBMS_OUTPUT.put_line(l_id);2223CLOSEc1;2425EXCEPTION26WHENOTHERSTHEN27DBMS_OUTPUT.put_line('ERROR');28END;29/100200300PL/SQLproceduresuccessfullycompletedSQL>

答案D

答案说明65楼

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