PL/SQL Parameterized Cursor

We can also pass parameters to the cursors same like a subprogram IN parameter, these types of cursor are called as parameterized cursor, in parameterized cursor we are defining formal parameters when we are declaring a cursor and passing actual parameter when we are opening the cursor.

Note: In Oracle when we are defining formal parameters in cursors, procedure, functions, then we are not allow to use datatypes size in formal parameter declaration.

DECLARE

CURSOR C1 (p_deptno number) IS select * from emp where deptno = p_deptno;

I emp%TYPE;

BEGIN

OPEN C1(10)

LOOP

FETCH C1 INTO I;

EXIT when C1%NOTFOUND;

Dbms_output.put_line(i.ename||’ ‘||i.sal);

END LOOP;

CLOSE C1;

END;

Below is the syntax:

CURSOR CursorName(FormalParameterName DataType)

IS

Select Statement

Where ColumnName = FormalParameterName;

OPEN CursorName(ActualValue);           

Write a PL/SQL program for passing tab as a parameter from emp table display employees working as ‘Clerk’ or ‘Analyst’ and also display that report statically.

Employee working as ANALYST

SCOTT

FORD

Employees working as CLERK

ADAMS

JAMES

MILLER

SMITH

Note: While passing parameter column name should be different

Example: CURSOR C1(p_job varchar2, p_job varchar2)  —– Wrong Method We are not allow to user like this

Number of times we can open and close the cursor, syntax shown below

DECLARE

CURSOR C1(p_job varchar2) is select * from emp where job  = p_job;

I emp%rowtype;

BEGIN

OPEN C1(‘ANALYST’);

Dbms_output.put_line(‘Employees working as Analyst’);

LOOP

Fetch C1 into I;

EXIT when C1%NOTFOUND;

Dbms_output.put_line(i.ename);

END LOOP;

CLOSE C1;

OPEN C1(‘&JOB’);

Dbms_output.put_line(‘Employees working as clerk’);

LOOP

FETCH C1 into I;

EXIT When C1%NOTFOUND;

Dbms_output.put_line(i.ename);

END LOOP;

CLOSE C1;

END;

Note: Before we are re-opening the cursor then we must close the cursor properly otherwise oracle server returns an error ‘ORA-6511’ cursor already open.

Note: When we are not opening the cursor but we are try to perform cursor operation, then oracle server returns an error ‘ORA-1001: Invalid Cursor’.