Oracle PL/SQL - Variables in Explicit Cursor Queries

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursors

Introduction

An explicit cursor query can reference any variable in its scope.

When you open an explicit cursor, PL/SQL evaluates variables in the query and uses those values when identifying the result set.

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

In the following code, the explicit cursor query references the variable factor.

Demo

SQL>
SQL>-- w w  w  . j  a  v  a  2 s. co  m
SQL> drop table emp;

Table dropped.

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, 90);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 80);
SQL>
SQL> -- Variable in Explicit Cursor Query-No Result Set Change
SQL> DECLARE
  2    sal           emp.salary%TYPE;
  3    sal_multiple  emp.salary%TYPE;
  4    factor        INTEGER := 2;
  5
  6    CURSOR c1 IS
  7      SELECT salary, salary*factor FROM emp;
  8
  9  BEGIN
 10    OPEN c1;  -- PL/SQL evaluates factor
 11
 12    LOOP
 13      FETCH c1 INTO sal, sal_multiple;
 14      EXIT WHEN c1%NOTFOUND;
 15      DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
 16      DBMS_OUTPUT.PUT_LINE('sal          = ' || sal);
 17      DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
 18      factor := factor + 1;  -- Does not affect sal_multiple
 19    END LOOP;
 20    CLOSE c1;
 21  END;
 22  /
factor = 2
sal          = 24000
sal_multiple = 48000
factor = 3
sal          = 25000
sal_multiple = 50000

PL/SQL procedure successfully completed.

SQL>

Related Topic