USAGE OF PARAMETERIZED CURSOR (Oracle SQL/PLSQL)

Parameter is passed in cursor in the same way as how a procedure is passed a parameter except that the parameter can only be IN mode

  

The general form of a cursor with parameters is,

cursor cursor_name [(parameter_name datatype, ……..)]

is select_statement;

Pass parameter values to a cursor when the cursor is opened and the query is executed.

Open an explicit cursor several times with a different active set each time.

open cursor_name(parameter_value,……..);

• PASSING PARAMETERS USING DYNAMIC VARIABLES

The following example displays the usage of parameterized cursor, which asks for the value from the user at runtime and passes the parameter value. 

SQL>

declare

cursor c1 (dd number) is select * from emp where deptno =dd;

y number;

begin

    for x in c1(&y)

    loop

    insert into PROD values(x.empno,x.ename);

    end loop;

    end;

/

Enter value for y: 10

old   5: for x in c1(&y)

new   5: for x in c1(10)

PL/SQL procedure successfully completed.

SQL> select * from PROD;

   EMPNO ENAME

--------- ----------

     7782 CLARK

     7839 KING

     7934 MILLER

SQL> truncate table PROD;

Table truncated.

• USE THE CURSOR ALSO FOR A “COMPUTED FIELDS” (ORACLE SQL/PLSQL)

The following example displays the use of computed fields to define a cursor. 

SQL> declare

    y number :=100;

    cursor c1 is select sal+y salary from emp;

    begin

    for x in c1

    loop

    insert into PROD values(x.salary,'in for');

    end loop;

    end;

   /

PL/SQL procedure successfully completed.

SQL> select * from PROD;

   EMPNO ENAME

--------- ----------

      900 in for

     1700 in for

     1350 in for

     3075 in for

     1350 in for

     2950 in for

     2550 in for