- 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
