Parametric Cursor in Oracle PL-SQL

Parametric cursor in Oracle PL-SQL- Learn how to pass values to Explicit Parametric cursors  with OPEN statement and with for loop. Also Learn how to call Explicit Cursor with Default Parameter Values.

Parametric cursor in Oracle PL-SQL

Parametric Cursors are the Explicit Cursors , accepts a list of parameters.

This type of cursors are static cursors , accepts passed-in parameter values when the Cursor opens.
You can pass different arguments to the cursor in different times in the PL-SQL block, when ever you open the cursor.

The following shows the syntax of a cursor with parameters:

Syntax

CURSOR cursor_name (parameter_list)
IS
  [select_query];

To open a cursor with parameters, you use the following syntax:

OPEN cursor_name (value_list);

Notes

• The Cursor defined with parameter is called Parametric cursor.
• ExplicIt Cursors accepts the parameter values.
• Cursor parameter can be assigned with default values.
• The Mode of cursor parameters can be only “IN” Mode.
• A parametric cursor can open and close explicitly several times in a PL/SQL block.

Example Program using Explicit Parameterized Cursor.

For below Example, please click on following employee_tbl1  for reference  table.

DECLARE
  Salary_record employee_tbl1%ROWTYPE;
  CURSOR c1 (max_salary NUMBER)
  IS
    SELECT * FROM employee_tbl1 WHERE salary <
  max_salary;
BEGIN
  OPEN c1(4000);
  LOOP
    FETCH c1 INTO Salary_record;
    EXIT
  WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Name = ' || Salary_record.name ||
     ', salary = ' || Salary_record.salary);
  END LOOP;
  CLOSE c1;
END;

If 4000is passed in as the value of max_salary, only the name and salary data for those employees whose salary is less than 4000is returned:

Output

Name = Pam, salary = 3000
Name = John, salary = 3500
Name = Todd, salary = 2800

Example program Parameterized Cursor with for loop.

DECLARE
  CURSOR c1 (max_salary NUMBER)
  IS
    SELECT * FROM employee_tbl1 WHERE salary <
  max_salary;
BEGIN
  FOR crec IN c1(4000)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || crec.name || 
    ', salary = ' || crec.salary);
  END LOOP;
END;

Output

Name = Pam, salary = 3000
Name = John, salary = 3500
Name = Todd, salary = 2800

Example Program -Parameterized Cursor With Default values using for loop

DECLARE
  CURSOR c1 (max_salary NUMBER :=4000)
  IS
    SELECT * FROM employee_tbl1 WHERE salary <
  max_salary;
BEGIN
  FOR crec IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || crec.name ||  
    ', salary = ' || crec.salary);
  END LOOP;
END;

Output

Name = Pam, salary = 3000
Name = John, salary = 3500
Name = Todd, salary = 2800

Example Program Explicit Parameterized Cursor with default values.

DECLARE
  Salary_record employee_tbl1%ROWTYPE;
  CURSOR c1 (max_salary NUMBER :=4000)
  IS
    SELECT * FROM employee_tbl1 WHERE salary <
  max_salary;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO Salary_record;
    EXIT
  WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Name = ' || Salary_record.name || 
     ', salary = ' || Salary_record.salary);
  END LOOP;
  CLOSE c1;
END;

Output

Name = Pam, salary = 3000
Name = John, salary = 3500
Name = Todd, salary = 2800