Cursor Declaration in Packages
When you use global cursor in package, you should determine where cursor declaration in your code.
Some developers declare cursor in package specification and others in package body.
My post today to illustrate difference between different approach of cursor declaration in package.
#1 Declaration in Package Specification
If you declare cursor in package specification, then it is like session variable that still available through whole alive session.
This approach take a lot of shared memory but if cursor is shared for a lot of plsql units then it has low cost.
CREATE PACKAGE pack_name AS CURSOR c1 is select * from emp; ... END pack_name;
#2 Declaration in Package BodyIf you declare cursor in package body then it will be available for each program unit in package. but not available for plsql units abroad of package procedures and functions.
CREATE PACKAGE BODY pack_name AS CURSOR c1 is select * from emp; ... END pack_name;
#3 Separate Cursor Specs and Bodies with PackageIn this approach you declare cursor specs in package specification without select statement.
That way, you can change the cursor body without having to change the cursor spec. You code the cursor spec in the package spec using this syntax
CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type;
In package body you should repeat complete declaration again for cursor
CREATE PACKAGE pack_name AS CURSOR c1 return emp%ROWTYPE; ... END pack_name;
CREATE PACKAGE BODY pack_name AS CURSOR c1 return emp%ROWTYPE
IS select * from emp;
... END pack_name;
In this approach, If you change query of cursor you only recompile package body only as no need to recompile package specification.
Benefits of Cursor Declaration in PackagesIt is like global variable which you can use it in every program units in your database.
DECLARE emp_rec emp%ROWTYPE; BEGIN OPEN pack_name.c1; LOOP FETCH pack_name.c1 INTO emp_rec; EXIT WHEN pack_name.c1%NOTFOUND; END LOOP; CLOSE pack_name.c1; END;
Unit of maintenance as it is one place to change your code.
Reusable code as you use the same code multiple times.