Cursors with Parameters

Cursors with Parameters

You can pass parameters to a cursor in a cursor FOR loop. This means that you can open and close an

explicit cursor several times in a block, returning a different active set on each occasion. For each

execution, the previous cursor is closed and reopened with a new set of parameters.
Each formal parameter in the cursor declaration must have a corresponding actual parameter in the

OPEN statement. Parameter data types are the same as those for scalar variables, but you do not

give them sizes. The parameter names are for references in the query expression of the cursor.


In the syntax:
cursor_name Is a PL/SQL identifier for the declared cursor
parameter_name Is the name of a parameter
datatype Is the scalar data type of the parameter
select_statement Is a SELECT statement without the INTO clause


Example


DECLARE
  V_NAME EMPLOYEES.LAST_NAME%TYPE;
  V_SAL  EMPLOYEES.SALARY%TYPE;
  -- DECLARATION OF CURSOR
  CURSOR EMPCUR(DID number)
   IS
   SELECT LAST_NAME,SALARY
   FROM EMPLOYEES
   WHERE department_ID=DID;
BEGIN
  OPEN EMPCUR(10);
  LOOP
     -- CURSOR WILL FETCH A ROW FROM ACTIVE SET.
     -- AFTER FETCHING IT WILL POINT TO NEXT ROW.
     FETCH EMPCUR INTO V_NAME,V_SAL;
     DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
     -- EXIT THE LOOP IF NO MORE ROWS TO FETCH
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
  OPEN EMPCUR(20);
  LOOP
     -- CURSOR WILL FETCH A ROW FROM ACTIVE SET.
     -- AFTER FETCHING IT WILL POINT TO NEXT ROW.
     FETCH EMPCUR INTO V_NAME,V_SAL;
     DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
     -- EXIT THE LOOP IF NO MORE ROWS TO FETCH
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
  OPEN EMPCUR(50);
  LOOP
     FETCH EMPCUR INTO V_NAME,V_SAL;
     DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
     -- EXIT THE LOOP IF NO MORE ROWS TO FETCH
     EXIT WHEN EMPCUR%NOTFOUND;
  END LOOP;
  CLOSE EMPCUR;
END;
/