Declaring packaged cursors

If you are declaring an explicit cursor in a package specification, you have two options:

· Declare the entire cursor, including the query, in the specification. This is exactly the same as if you were declaring a cursor in a local PL/SQL block.

· Declare only the header of the cursor and do not include the query itself. In this case, the query is defined in the package body only. You have, in effect, hidden the implementation of the cursor.

If you declare only the header, then you must add aRETURN clause to a cursor definition that indicates the data elements returned by a fetch from the cursor. Of course, these data elements are actually determined by the SELECT statement for that cursor, but the SELECT statement appears only in the body, not in the specification.

The RETURN clause may be made up of either of the following datatype structures:

· A record defined from a database table using the %ROWTYPE attribute

· A record defined from a programmer-defined record

If you declare a cursor in a package body, the syntax is the same as if you were declaring it in a local PL/SQL block.

Here is a simple package specification that shows both of these approaches:

/* File on web: pkgcur.sql */ 1 CREATE OR REPLACE PACKAGE book_info 2 IS 3 CURSOR byauthor_cur ( 4 author_in IN books.author%TYPE 5 ) 6 IS 7 SELECT * 8 FROM books 9 WHERE author = author_in;10 11 CURSOR bytitle_cur (12 title_filter_in IN books.title%TYPE13 ) RETURN books%ROWTYPE;14 15 TYPE author_summary_rt IS RECORD (16 author books.author%TYPE,17 total_page_count PLS_INTEGER,18 total_book_count PLS_INTEGER);19 20 CURSOR summary_cur (21 author_in IN books.author%TYPE22 ) RETURN author_summary_rt;23 END book_info;

On lines 3-9 you can see a very typical explicit cursor definition, fully defined in the package specification. On lines 11-13, I define a cursor without a query. In this case, I am telling whoever is looking at the specification that if they open and fetch from this cursor they will receive a single row from the books table for the specified "title filter," the implication being that wild cards are accepted in the description of the title. On lines 15-18, I define a new record type to hold summary information for a particular author, and on lines 20-22, I declare a cursor that returns summary information (just three values) for a given author.

Let's take a look at the package body and then see what kind of code needs to be written to work with these cursors.

1 CREATE OR REPLACE PACKAGE BODY book_info 2 IS 3 CURSOR bytitle_cur ( 4 title_filter_in IN books.title%TYPE 5 ) RETURN books%ROWTYPE 6 IS 7 SELECT * 8 FROM books 9 WHERE title LIKE UPPER (title_filter_in);10 11 CURSOR summary_cur (12 author_in IN books.author%TYPE13 ) RETURN author_summary_rt14 IS15 SELECT author, SUM (page_count), COUNT (*)16 FROM books17 WHERE author = author_in;18 END book_info;

Because I had two cursors with a RETURN clause in my book information package specification, I must finish defining those cursors in the body. The select list of the query that I now add to the header must match, in number of items and datatype, the RETURN clause in the package specification; in this case, they do. If they do not match or the RETURN clause is not specified in the body, then the package body will fail to compile with one of the following errors:

20/11 PLS-00323: subprogram or cursor '<cursor>' is declared in a package specification and must be defined in the package body 5/13 PLS-00400: different number of columns between cursor SELECT statement and return value