###### tags: `PL/SQL`

# Define and use cursors in a package

## Introduction
- You can separate a cursor specification ("spec") from its body for placement in a package.
- That way, you can change the cursor body without having to change the cursor spec.

## Create the cursor spec
- Syntax:
`CURSOR cursor_name RETURN row_type;`
- no SELECT statement
- `RETURN` clause specifies the data type of the return value.


Example

CREATE PACKAGE emp_stuff AS
  -- Declare cursor spec
  CURSOR c1 RETURN employees%ROWTYPE;
END emp_stuff;

## Define the cursor body in the package body

- the number and data types of items in the SELECT list and the RETURN clause must match


Example

CREATE PACKAGE BODY emp_stuff AS
  CURSOR c1 RETURN employees%ROWTYPE 
  IS
    -- Define cursor body
    SELECT * FROM employees WHERE salary > 2500;
END emp_stuff;

## Use the package cursor in the block
- When you open a packaged cursor, it remains open until you close it or you disconnect from the session.

set serveroutput on
DECLARE   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_stuff.c1;
   LOOP
      FETCH emp_stuff.c1 INTO emp_rec;
-- do processing here ...
      EXIT WHEN emp_stuff.c1%NOTFOUND;
   END LOOP;
   CLOSE emp_stuff.c1;
END;
/

or

set serveroutput on
begin
 for rec in EMP_STUFF.c1 loop
    DBMS_OUTPUT.PUT_LINE (rec.employee_id || ' ' || rec.salary);
 end loop;
end;

Reference:
- [Using PL/SQL Packages](https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/packages.htm#LNPLS00910)