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
0 comments :
Post a Comment