Oracle Database Interview Question:

How To Pass a Parameter to a Cursor in Oracle?

Submitted by: Administrator

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

Submitted by: Administrator