Oracle PL/SQL - Explicit Cursors with Parameters

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

Introduction

You can create an explicit cursor that has formal parameters.

And then you can pass different actual parameters to the cursor each time you open it.

In the cursor query, you can use a formal cursor parameter.

Outside the cursor query, you cannot reference formal cursor parameters.

To avoid confusion, use different names for formal and actual cursor parameters.

The following code creates an explicit cursor whose two formal parameters represent a job and its maximum salary.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   www.  j  a v a 2s.  c o m

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>
SQL> DECLARE
  2    CURSOR c  (job VARCHAR2, max_sal NUMBER) IS
  3      SELECT last_name, first_name, (salary - max_sal) overpayment
  4      FROM emp
  5      WHERE job_id = job
  6      AND salary > max_sal
  7      ORDER BY salary;
  8
  9    PROCEDURE print_overpaid IS
 10      last_name_   emp.last_name%TYPE;
 11      first_name_  emp.first_name%TYPE;
 12      overpayment_  emp.salary%TYPE;
 13    BEGIN
 14      LOOP
 15        FETCH c INTO last_name_, first_name_, overpayment_;
 16        EXIT WHEN c%NOTFOUND;
 17        DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
 18          ' (by ' || overpayment_ || ')');
 19      END LOOP;
 20    END print_overpaid;
 21
 22  BEGIN
 23    DBMS_OUTPUT.PUT_LINE('Overpaid Stock Clerks:');
 24    OPEN c('CODER', 5000);
 25    print_overpaid;
 26    CLOSE c;
 27    DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
 28    OPEN c('TESTER', 10000);
 29    print_overpaid;
 30    CLOSE c;
 31  END;
 32  /
Overpaid Stock Clerks:
King, Steven (by 19000)
Overpaid Sales Representatives:
Lee, Joe (by 15000)

PL/SQL procedure successfully completed.

SQL>

Related Topic