Oracle PL/SQL - Variables in Cursor Variable Queries

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

The query associated with a cursor variable can reference any variable in its scope.

Changing the values of the variables later does not change the result set.

The following code opens a cursor variable for a query that references the variable factor.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  w  w.j  a  v a  2s.com

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> DECLARE
  2    sal           emp.salary%TYPE;
  3    sal_multiple  emp.salary%TYPE;
  4    factor        INTEGER := 2;
  5
  6    cv SYS_REFCURSOR;
  7
  8  BEGIN
  9    OPEN cv FOR
 10      SELECT salary, salary*factor
 11      FROM emp;   -- PL/SQL evaluates factor
 12
 13    LOOP
 14      FETCH cv INTO sal, sal_multiple;
 15      EXIT WHEN cv%NOTFOUND;
 16      DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 17      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 18      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 19      factor := factor + 1;  -- Does not affect sal_multiple
 20    END LOOP;
 21
 22    CLOSE cv;
 23  END;
 24  /
factor = 2
sal          = 24000
sal_multiple = 48000
factor = 3
sal          = 25000
sal_multiple = 50000

PL/SQL procedure successfully completed.

SQL>

Related Topic