How To Pass a Parameter to a Cursor

Q

How To Pass a Parameter to a Cursor? - Oracle DBA FAQ - Working with Cursors in PL/SQL

✍: FYIcenter.com

A

When you define a cursor, you can set a formal parameter in the cursor. The formal parameter will be replaced by an actual parameter in the OPEN cursor statement. Here is a good example of a cursor with two parameters:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR emp_cur(low NUMBER, high NUMBER)
    IS SELECT * FROM employees WHERE salary >= low
      AND salary <= high;
BEGIN
  FOR row IN emp_cur(12000,15000) LOOP
    DBMS_OUTPUT.PUT_LINE(row.first_name || ' '
      || row.last_name 
      || ': ' || row.salary); 
  END LOOP; 
END;
/
Nancy Greenberg: 12000
John Russell: 14000
Karen Partners: 13500
Alberto Errazuriz: 12000
Michael Hartstein: 13000
Shelley Higgins: 12000

2007-04-28, 5122👍, 0💬