Oracle PL/SQL - Adding Formal Parameter to Existing Cursor

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

Introduction

You can add formal parameters to a cursor, and specify default values for the added parameters.

Then you need not change existing references to the cursor.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w ww .  jav  a 2 s  .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-2000', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-2010', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> DECLARE
  2    CURSOR c (job VARCHAR2, max_sal NUMBER, hired DATE DEFAULT '31-DEC-99') 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      AND hire_date > hired
  8      ORDER BY salary;
  9
 10    PROCEDURE print_overpaid IS
 11      last_name_   emp.last_name%TYPE;
 12      first_name_  emp.first_name%TYPE;
 13      overpayment_      emp.salary%TYPE;
 14    BEGIN
 15      LOOP
 16        FETCH c INTO last_name_, first_name_, overpayment_;
 17        EXIT WHEN c%NOTFOUND;
 18        DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ || ' (by ' || overpayment_ || ')'); 
 19      END LOOP;
 20    END print_overpaid;
 21
 22  BEGIN
 23    DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
 24    OPEN c('CODER', 100);  -- existing reference
 25    print_overpaid;
 26    CLOSE c;
 27
 28    DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2004:');
 29    OPEN c('TESTER', 1000,  '31-DEC-04');  -- new reference
 30    print_overpaid;
 31    CLOSE c;
 32  END;
 33  /
Overpaid Sales Representatives:
King, Steven (by 23900)
Overpaid Sales Representatives Hired After 2004:
Lee, Joe (by 24000)

PL/SQL procedure successfully completed.

SQL>

Related Topic