Oracle Parametrized Cursors

Oracle Parametrized Cursors

•   This was used when you are going to use the cursor in more than one place with different values for the same where clause.
•   Cursor parameters must be in mode.
•   Cursor parameters may have default values.
•   The scope of cursor parameter is within the select statement.

Ex:

     DECLARE

         cursor c(dno in number) is select * from dept where deptno = dno;

         v_dept dept%rowtype;

      BEGIN

         open c(20);

         loop

             fetch c into v_dept;

             exit when c%notfound;

            dbms_output.put_line('Dname = ' || v_dept.dname || ' Loc = ' || v_dept.loc);

         end loop;

         close c;

     END;

Output:

     Dname = RESEARCH Loc = DALLAS

PACKAGED CURSORS WITH HEADER IN SPEC AND BODY IN PACKAGE BODY

•   cursors declared in packages will not close automatically.In packaged cursors you can modify the select statement without making any changes to the cursor header in the package specification.
• Packaged cursors with must be defined in the package body itself, and then use it as global for the package.You can not define the packaged cursor in any subprograms.
•   Cursor declaration in package with out body needs the return clause.
• Ex:

CREATE OR REPLACE PACKAGE PKG IS

                         cursor c return dept%rowtype is select * from dept;

                procedure proc is

END PKG;

CREATE OR REPLACE PAKCAGE BODY PKG IS

      cursor c return dept%rowtype is select * from dept;

PROCEDURE PROC IS

BEGIN

      for v in c loop

           dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' || v.dname || '   

                                                  Loc = ' || v.loc);

      end loop;

END PROC;

END PKG;

Output:

SQL> exec pkg.proc

        Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK

        Deptno = 20 Dname = RESEARCH Loc = DALLAS

        Deptno = 30 Dname = SALES Loc = CHICAGO

                  Deptno = 40 Dname = OPERATIONS Loc = BOSTON

CREATE OR REPLACE PAKCAGE BODY PKG IS

      cursor c return dept%rowtype is select * from dept where deptno > 20;

PROCEDURE PROC IS

BEGIN

      for v in c loop

           dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' || v.dname || '   

                                                  Loc = ' || v.loc);

      end loop;

END PROC;

END PKG;

Output:

SQL> exec pkg.proc

               Deptno = 30 Dname = SALES Loc = CHICAGO

                  Deptno = 40 Dname = OPERATIONS Loc = BOSTON