Declaring cursors in packages

You can declare explicit cursors in any declaration section of a PL/SQL block. This means that you can declare such cursors within packages and at the package level; not within a particular procedure or function in the package. We'll explore packages in general in Chapter 17. You may want to look ahead at that chapter to acquaint yourself with the basics of packages before plunging into the topic of declaring cursors in packages.

Here are two examples:

CREATE OR REPLACE PACKAGE book_infoIS CURSOR titles_cur IS SELECT title FROM book; CURSOR books_cur (title_filter_in IN book.title%TYPE) RETURN book%ROWTYPE IS SELECT * FROM book WHERE title LIKE title_filter_in;END;

The first cursor, titles_cur, returns just the titles of books. The second cursor, books_cur, returns a record for each row in the book table whose title passes the filter provided as a parameter (such as "All books that contain `PL/SQL'"). Notice that the second cursor also utilizes the RETURN clause of a cursor, in essence declaring publicly the structure of the data that each FETCH against that cursor will return.

The RETURN clause of a cursor may be made up of any of the following datatype structures:

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

· A record defined from another, previously defined cursor, also using the %ROWTYPE attribute

· A record defined from a programmer-defined record

The number of expressions in the cursor's select list must match the number of columns in the record identified by table_name%ROWTYPE, cursor%ROWTYPE, or record_type. The datatypes of the elements must also be compatible. For example, if the second element in the select list is type NUMBER, then the second column in the RETURN record cannot be type VARCHAR2 or BOOLEAN.

Before exploring the RETURN clause and its advantages, let's first address a different question: why should you bother putting cursors into packages? Why not simply declare your explicit cursors wherever you need them directly in the declaration sections of particular procedures, functions, or anonymous blocks?

The answer is simple and persuasive. By defining cursors in packages, you can more easily reuse those queries and avoid writing the same logical retrieval statement over and over again throughout your application. By implementing that query in just one place and referencing it in many locations, you make it easier to enhance and maintain that query. You will also realize some performance gains by minimizing the number of times your queries will need to be parsed.

If you declare cursors in packages for reuse, you do need to be aware of one important factor. Data structures, including cursors, that are declared at the "package level" (not inside any particular function or procedure) maintain their values or persist for your entire session. This means that a packaged cursor will stay until you explicitly close it or until your session ends. Cursors declared in local blocks of code close automatically when that block terminates execution.

Now let's explore this RETURN clause and why you might want to take advantage of it. One of the interesting variations on a cursor declaration within packages involves the ability to separate the cursor's header from its body. the header of a cursor, much like the header of a function, is just that information a developer needs in order to write code to work with the cursor: the cursor's name, any parameters, and the type of data being returned. The body of a cursor is its SELECT statement.

Here is a rewrite of the books_cur in the book_info package that illustrates this technique:

CREATE OR REPLACE PACKAGE book_infoIS CURSOR books_cur (title_filter_in IN book.title%TYPE) RETURN book%ROWTYPEEND; CREATE OR REPLACE PACKAGE BODY book_infoIS CURSOR books_cur (title_filter_in IN book.title%TYPE) RETURN book%ROWTYPE IS SELECT * FROM book WHERE title LIKE title_filter_in;END;

Notice that everything up to but not including the IS keyword is the specification, while everything following the IS keyword is the body.

There are two reasons that you might want to divide your cursor as shown above:

Hide information

Packaged cursors are essentially black boxes. This is advantageous to developers because they never have to code or even see the SELECT statement. They only need to know what records the cursor returns, in what order it returns them, and which columns are in the column list. Among other things, this means they do not "second guess" the author of the cursor, believing they can improve upon the query. They simply use it as another predefined element in their application.

Minimize recompilation

If I hide the query definition inside the package body, I can make changes to the SELECT statement without making any changes to the cursor header in the package specification. This allows me to enhance, fix, and recompile my code without recompiling my specification, which means that all the programs dependent on that package will not be marked invalid and will not need to be recompiled.