与游标变量相关的查询可以引用他的作用域中的任意变量。当使用OPEN FOR打开游
标变量时,PL/SQL会计算查询中的任意变量来标识结果集,对于变量的后续修改,不会改
变结果集。若要更改结果集,必须更改变量的值,然后再次为同一查询打开游标变量。
CREATE OR REPLACE PROCEDURE P_5_25 AS
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
cv SYS_REFCURSOR;
BEGIN
OPEN cv FOR 'SELECT salary, salary*? FROM employees WHERE job_id LIKE ''AD_PRES''' using factor ;
LOOP
FETCH cv INTO sal, sal_multiple;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
factor := factor + 1; -- Does not affect sal_multiple
END LOOP;
CLOSE cv;
END;
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 115 -
--Result:
--factor = 2
--sal = 4400
--sal_multiple = 8800
--factor = 3
--sal = 24000
--sal_multiple = 48000
--factor = 4
--sal = 17000
--sal_multiple = 34000
--factor = 5
--sal = 17000
--sal_multiple = 34000
CREATE OR REPLACE PROCEDURE P_6_26 AS
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
cv SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN cv FOR 'SELECT salary, salary*? FROM employees WHERE job_id LIKE ''AD_PRES''' using factor;
LOOP
FETCH cv INTO sal, sal_multiple;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
factor := factor + 1;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN cv FOR 'SELECT salary, salary*? FROM employees WHERE job_id LIKE ''AD_PRES''' using factor;
LOOP
FETCH cv INTO sal, sal_multiple;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE cv;
END;
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 116 -
--Result:
--factor = 2
--sal = 4400
--sal_multiple = 8800
--sal = 24000
--sal_multiple = 48000
--sal = 17000
--sal_multiple = 34000
--sal = 17000
--sal_multiple = 34000
--factor = 3
--sal = 4400
--sal_multiple = 13200
--sal = 24000
--sal_multiple = 72000
--sal = 17000
--sal_multiple = 51000
--sal = 17000
--sal_multiple = 51000