可以使用另外的游标变量或者宿主变量给游标变量赋值。
target_cursor_variable := source_cursor_variable;
如果source游标变量是打开的,赋值后target游标变量也是打开的。两个游标变量指
向同一区域。
CREATE OR REPLACE PROCEDURE P_6_24 AS
cv1 SYS_REFCURSOR;
cv2 SYS_REFCURSOR;
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
OPEN cv1 FOR 'SELECT last_name, job_id FROM employees WHERE job_id =
''AD_PRES'' ORDER
BY last_name';
cv2 := cv1 ;
LOOP
FETCH cv2 INTO v_lastname, v_jobid;
EXIT WHEN cv2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( v_jobid );
END LOOP;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
CLOSE cv2;
END;
下面示例中,先把cv1赋值给cv2。再关闭游标变量cv1,当再次打开后,在oracle中,
游标变量cv2从现象上来看依然与cv1保持一致(指向新的SQL语句);本次版本在这种场
景下,cv2会处于不确定状态,可能指向旧的游标上下文,也可能指向cv1新打开的游标上
下文。要想再次引用cv2,只能给cv2重新赋值。
CREATE OR REPLACE PROCEDURE P_6_24 AS
cv1 SYS_REFCURSOR;
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 114 -
cv2 SYS_REFCURSOR;
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
OPEN cv1 FOR 'SELECT last_name, job_id FROM employees WHERE job_id =
''AD_PRES'' ORDER
BY last_name';
cv2 := cv1 ;
close cv1;
OPEN cv1 FOR 'SELECT last_name, job_id FROM employees WHERE job_id =
''AD_PRES'' ORDER
BY last_name';
LOOP
FETCH cv2 INTO v_lastname, v_jobid;
EXIT WHEN cv2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( v_jobid );
END LOOP;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
CLOSE cv1;
END;