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

DECLARE

    sal             employees.salary%TYPE;

    sal_multiple    employees.salary%TYPE;

    factor          INTEGER := 2;

    CURSOR c1 IS

        SELECT salary, salary * factor FROM employees

        WHERE job_id LIKE 'AD_%';

BEGIN

    OPEN c1;  -- PL/SQL计算factor

    LOOP

        FETCH c1 INTO sal, sal_multiple;

        EXIT WHEN c1%NOTFOUND;

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

                             ', sal = ' || sal ||

                             ', sal_multiple = ' || sal_multiple);     

        factor := factor + 1;  -- 不会影响结果集

  END LOOP;

  CLOSE c1;

END;

--为了改变结果集，必须关闭光标，改变变量的值，然后再次打开光标

DECLARE

    sal           employees.salary%TYPE;

    sal_multiple  employees.salary%TYPE;

    factor        INTEGER := 2;

    CURSOR c1 IS

        SELECT salary, salary*factor FROM employees

        WHERE job_id LIKE 'AD_%';

BEGIN

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

    OPEN c1;  -- PL/SQL 计算factor

    LOOP

        FETCH c1 INTO sal, sal_multiple;

        EXIT WHEN c1%NOTFOUND;

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

                            ', sal_multiple = ' || sal_multiple);      

    END LOOP;

    CLOSE c1;

    factor := factor + 1;

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

    OPEN c1;  -- PL/SQL 计算factor

    LOOP

        FETCH c1 INTO sal, sal_multiple;

        EXIT WHEN c1%NOTFOUND;

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

                            ', sal_multiple = ' || sal_multiple);

    END LOOP;

    CLOSE c1;

END;