Wednesday 18 September 2013

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

1 comment:

Related Posts Plugin for WordPress, Blogger...

ShareThis