光标变量查询中的变量

与光标变量相关查询可以引用它的作用域内的任何变量。当使用OPEN FOR语句打开光标变量时，PL/SQL会计算查询中的任何变量值来识别结果集。后面对变量值的修改，不会改变结果集。

DECLARE

sal employees.salary%TYPE;

sal_multiple employees.salary%TYPE;

factor INTEGER := 2;

cv SYS_REFCURSOR;

BEGIN

OPEN cv FOR

SELECT salary, salary*factor

FROM employees

WHERE job_id LIKE 'AD_%'; -- 计算factor的值

LOOP

FETCH cv INTO sal, sal_multiple;

EXIT WHEN cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('factor = ' || factor ||

', sal = ' || sal ||

', sal_multiple = ' || sal_multiple);

factor := factor + 1;

END LOOP;

CLOSE cv;

END;

--如果想改变结果集，就必须先修改变量，然后再次打开光标变量

DECLARE

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*factor

FROM employees

WHERE job_id LIKE 'AD_%'; -- 计算factor

LOOP

FETCH cv INTO sal, sal_multiple;

EXIT WHEN cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('sal = ' || sal ||

', sal_multiple = ' || sal_multiple);

END LOOP;

factor := factor + 1;

DBMS_OUTPUT.PUT_LINE('factor = ' || factor);

OPEN cv FOR

SELECT salary, salary*factor

FROM employees

WHERE job_id LIKE 'AD_%'; -- 再次计算factor

LOOP

FETCH cv INTO sal, sal_multiple;

EXIT WHEN cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('sal = ' || sal ||

', sal_multiple = ' || sal_multiple);

END LOOP;

CLOSE cv;

END;

ITPub博客

PLSQL Language Referenc-PL/SQL静态SQL-光标变量-光标变量查询中的变量

光标变量查询中的变量

最新文章