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.
转载于:https://blog.51cto.com/majesty/1107616