Oracle Parametrized Cursors



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
Share on Google Plus

About sekhar

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

0 comments :

Post a Comment