1) Package Header Cursor

1@@@@Packaged Cursors There are two kind case in package header declare cursor:   1..declare the explicitly cursor with SELECT statment   2..declare the explicitly cursor only without SELECT statment      however, you need assign the RETURN rowtype is the SELECT statment      which in package body.      I can declare a cursor in a package, in either the body or the   specification. The state of this cursor (i.e., whether it is opened or closed, the pointer   to the location in the result set) persists for the session, just like any other packaged   data. This means that it is possible to open a packaged cursor in one program, fetch   from it in a second, and close it in a third. Declaring packaged cursors   If you declare only the header, then you must add a RETURN 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.    @@@Example: Cursor in Package     SYS@ocm> !cat tmp.sql     DROP TABLE books     /     CREATE TABLE books         (  author       VARCHAR2(100)          , title        VARCHAR2(250)          , page_count   NUMBER(5)           , CONSTRAINT pk_books PRIMARY KEY (author,title) )     /     BEGIN       DELETE FROM books;       INSERT INTO books VALUES('fatpander_small_one','Fatpander Travel',100);       INSERT INTO books VALUES('fatpander_big_one','Fatpander Travel',120);       COMMIT;     END;     /     CREATE OR REPLACE PACKAGE book_info     IS         --A cursor with SELECT statement.         CURSOR byauthor_cur              ( author_in  IN  books.author%TYPE )         IS           SELECT * FROM books           WHERE author = author_in;             --A cursor without SELECT statment, but need a RETURN keyword         --, which return rowtype in SELECT statment in the package body         CURSOR bytitle_cur             (  title_filter_in IN  books.title%TYPE )         RETURN books%ROWTYPE;             --This type for creating a collection to cache below cursor         TYPE author_summary_rt IS RECORD             (  author             books.author%TYPE              , total_page_count   PLS_INTEGER               , total_book_count   PLS_INTEGER );             CURSOR summary_cur             (  author_in  IN  books.author%TYPE  )         RETURN author_summary_rt;             PROCEDURE display (  book_rec  IN  books%ROWTYPE);     END;     /     SYS@ocm> @tmp.sql     Table dropped.     Table created.     PL/SQL procedure successfully completed.     Package created.        @@@Implement the package body     SYS@ocm> !cat tmpx.sql     CREATE OR REPLACE PACKAGE BODY book_info     IS         --Implement the cursor in package specification         --which no SELECT statement, only with RETURN.         CURSOR bytitle_cur             (  title_filter_in  IN  books.title%TYPE)         RETURN books%ROWTYPE         IS           SELECT * FROM books            WHERE title LIKE title_filter_in;                 CURSOR summary_cur             (  author_in IN  books.author%TYPE )         RETURN author_summary_rt         IS           SELECT author, SUM(page_count), COUNT(*)             FROM books            WHERE author = author_in;                 PROCEDURE display             (  book_rec  IN  books%ROWTYPE  )         IS         BEGIN            NULL;         END;     END book_info;     /     SYS@ocm> @tmpx.sql     Package body created.