1) Initilization Package Means Caching
1@@@@ The Package Specification
It is easy to understand that, the code in package body with begin..end is initilized code
LIKE:
CREATE OR REPLACE PACKAGE BODY pkg_name
IS
BEGIN
--Here codes would be runed one time only.
END;
Here are some rules to keep in mind for package specification construction:
~ You can declare elements of almost any datatype, such as numbers, exceptions,
types, and collections, at the package level (i.e., not within a particular procedure
or function in the package). This is referred to as package-level data; generally, you
should avoid declaring variables in the package specification, although constants
are always ¡°safe.¡±
You cannot declare cursor variables (variables defined from a REF CURSOR type)
in a package specification (or body). Cursor variables are not allowed to persist at
the session level (see ¡°Working with Package Data¡± on page 633 for more information
about package data persistence).
~ You can declare almost any type of data structure, such as a collection type, a record
type, or a REF CURSOR type.
~ You can declare procedures and functions in a package specification, but you can
include only the header of the program (everything up to but not including the IS
or AS keyword). The header must end with a semicolon.
~ You can include explicit cursors in the package specification. An explicit cursor
can take one of two forms: it can include the SQL query as a part of the cursor
declaration, or you can ¡°hide¡± the query inside the package body and provide only
a RETURN clause in the cursor declaration. This topic is covered in more detail in
the section, ¡°Packaged Cursors¡± on page 635.
~ If you declare any procedures or functions in the package specification or if you
declare a CURSOR without its query, then you must provide a package body in
order to implement those code elements.
~ You can include an AUTHID clause in a package specification, which determines
whether any references to data objects will be resolved according to the privileges
of the owner of the package (AUTHID DEFINER) or of the invoker of the package
(AUTHID CURRENT_USER). See Chapter 24 for more information on this
feature.
~ You can include an optional package name label after the END statement of the
package, as in:
END my_package;
@@@Example: Note the initilization between begin and end;
SYS@ocm> !cat tmp.sql
DROP TABLE favorites
/
CREATE TABLE favorites
( name VARCHAR2(100)
, code INTEGER )
/
BEGIN
DELETE FROM favorites;
INSERT INTO favorites VALUES('fatpander_small_one',1);
INSERT INTO favorites VALUES('fatpander_big_one',2);
COMMIT;
END;
/
CREATE OR REPLACE PACKAGE favorites_pkg
AUTHID CURRENT_USER
IS
--Two constants;
c_chocolate CONSTANT PLS_INTEGER := 16;
c_strawberry CONSTANT PLS_INTEGER := 29;
--A nested table Type declaration
--A nested table declared from the generic type.
TYPE my_favorites_ntt IS TABLE OF INTEGER;
my_favorites_nt my_favorites_ntt;
--A REF CURSOR returning favorites information
TYPE fav_info_rct IS REF CURSOR
RETURN favorites%ROWTYPE;
--A procedure that accepts a list of favorites(nest table type)
--and display the favorites information from that list.
PROCEDURE show_favorites ( list_in IN my_favorites_ntt);
--A function that returns all the information in the favorites
--table about the most popular item.
FUNCTION most_popular RETURN fav_info_rct;
END;
/
SYS@ocm> @tmp.sql
Table dropped.
Table created.
PL/SQL procedure successfully completed.
Package created.
SYS@ocm> !cat tmpx.sql
CREATE OR REPLACE PACKAGE BODY favorites_pkg
IS
--A private vairable
g_most_popular PLS_INTEGER;
--Implementation of procedure
PROCEDURE show_favorites
( list_in IN my_favorites_ntt )
IS
BEGIN
FOR i IN list_in.FIRST .. list_in.LAST
LOOP
DBMS_OUTPUT.put_line( list_in(i) );
END LOOP;
END;
--Implementation of procedure
FUNCTION most_popular
RETURN fav_info_rct
IS
retval fav_info_rct;
null_cv fav_info_rct;
BEGIN
OPEN retval FOR
SELECT * FROM favorites
WHERE code = g_most_popular;
RETURN retval;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null_cv;
END;
PROCEDURE analyze_favorites
( year_in IN INTEGER )
IS
BEGIN
DBMS_OUTPUT.put_line('This year is '||year_in);
END;
BEGIN
--Initilization, one session one time
g_most_popular := c_chocolate;
analyze_favorites ( EXTRACT(YEAR FROM sysdate) );
END;
/
SYS@ocm> @tmpx.sql
Package body created.
@@@
SYS@ocm> !cat tmpxx.sql
DECLARE
show_nt favorites_pkg.my_favorites_ntt
:= favorites_pkg.my_favorites_ntt(1,2,3);
BEGIN
favorites_pkg.show_favorites(show_nt);
END;
/
SYS@ocm> @tmpxx.sql
This year is 2013 --One session one time!!! initilization.
1
2
3
PL/SQL procedure successfully completed.
SYS@ocm> @tmpxx.sql
1
2
3
PL/SQL procedure successfully completed.
转载于:https://blog.51cto.com/majesty/1107626