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.
Example

CREATE PACKAGE pack_name AS
   CURSOR c1 is select * from emp;
   ...
END pack_name;

#2 Declaration in Package Body

If 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.
Example

CREATE PACKAGE BODY pack_name AS
   CURSOR c1 is select * from emp;
   ...
END pack_name;

#3 Separate Cursor Specs and Bodies with Package

In 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
Example

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 Packages

It is like global variable which you can use it in every program units in your database.
Example

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.

Thanks