Basic Oracle Concepts and Programming Question:
How To Pass a Parameter to a Cursor in Oracle?
Answer:
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 ggl_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
CREATE OR REPLACE PROCEDURE ggl_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
| Previous Question | Next Question |
| Can Multiple Cursors Being Opened at the Same Time? | What Is an Oracle Cursor Variable? |