Lemur zaprasza
[Chapter 1] 1.14 PackagesChapter 1Oracle PL/SQL Language Pocket Reference 1.14 PackagesA package is a collection of PL/SQL objects that are grouped together.There are a number of benefits to using packages, including information hiding, object-oriented design, top-down design, object persistence across transactions, and improved performance. Elements that can be placed in a package include procedures, functions, constants, variables, cursors, exception names, and TYPE statements (for index-by tables, records, REF CURSORs, etc.).1.14.1 Overview of Package Structure A package can have two parts: the specification and the body. The package specification is required and lists all the objects that are publicly available (may be referenced from outside the package) for use in applications. It also provides all the information a developer needs in order to use objects in the package; essentially, it is the package's API.The package body contains all code needed to implement procedures, functions, and cursors listed in the specification, as well as any private objects (accessible only to other elements defined in that package), and an optional initialization section. If a package specification does not contain any procedures or functions and no private code is needed, then that package does not need to have a package body.The syntax for the package specification is:CREATE [OR REPLACE] PACKAGE package_name [ AUTHID CURRENT_USER | DEFINER ] -- Oracle8i IS | AS [definitions of public TYPEs ,declarations of public variables, types and objects ,declarations of exceptions ,pragmas ,declarations of cursors, procedures and functions ,headers of procedures and functions] END [package_name];The syntax for the package body is:CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS [definitions of private TYPEs ,declarations of private variables, types and objects ,full definitions of cursors ,full definitions of procedures and functions] [BEGIN executable_statements [EXCEPTION exception_handlers ] ] END [package_name];The optional OR REPLACE keywords are used to rebuild an existing package, preserving its privileges. The declarations in the specifications cannot be repeated in the body. Both the executable section and the exception section are optional in a package body. If the executable section is present, it is called the initialization section and executes only once -- the first time any package element is referenced during a session.You must compile the package specification before the body specification. When you grant EXECUTE authority on a package to another schema or to PUBLIC, you are giving access only to the specification; the body remains hidden.Here's an example of a package:CREATE OR REPLACE PACKAGE time_pkg IS FUNCTION GetTimestamp RETURN DATE; PRAGMA RESTRICT_REFERENCES (GetTimestamp, WNDS); PROCEDURE ResetTimestamp; END time_pkg; CREATE OR REPLACE PACKAGE BODY time_pkg IS StartTimeStamp DATE := SYSDATE; -- StartTimeStamp is package data. FUNCTION GetTimestamp RETURN DATE IS BEGIN RETURN StartTimeStamp; END GetTimestamp; PROCEDURE ResetTimestamp IS BEGIN StartTimeStamp := SYSDATE; END ResetTimestamp; END time_pkg; 1.14.2 Referencing Package ElementsThe elements declared in the specification are referenced from the calling application via dot notation:package_name.package_elementFor example, the built-in package DBMS_OUTPUT has a procedure PUT_LINE, so a call to this package would look like this: DBMS_OUTPUT.PUT_LINE('This is parameter data');1.14.3 Package Data Data structures declared within a package specification or body, but outside any procedure or function in the package, are package data. The scope of package data is your entire session; it spans transaction boundaries, acting as globals for your programs.Keep the following guidelines in mind as you work with package data:The state of your package variables is not affected by COMMITs and ROLLBACKs.A cursor declared in a package has global scope. It remains OPEN until you close it explicitly or your session ends. A good practice is to hide your data structures in the package body and provide "get and set" programs to read and write that data. This technique protects your data.1.14.3.1 The SERIALLY_REUSABLE pragmaIf you need package data to exist only during a call to the packaged functions or procedures, and not between calls of the current session, you can save runtime memory by using the pragma SERIALLY_REUSABLE. After each call, PL/SQL closes the cursors and releases the memory used in the package. This technique is applicable only to large user communities executing the same routine. Normally, the database server's memory requirements grow linearly with the number of users; with SERIALLY_REUSABLE, this growth can be less than linear, since work areas for package states are kept in a pool in the SGA (System Global Area) and are shared among all users. This pragma must appear in both the specification and the body:CREATE OR REPLACE PACKAGE my_pkg IS PRAGMA SERIALLY_REUSABLE; PROCEDURE foo; END my_pkg; CREATE OR REPLACE PACKAGE BODY my_pkg IS PRAGMA SERIALLY_REUSABLE; PROCEDURE foo IS ... END my_pkg;1.14.4 Package Initialization The first time a user references a package element, the entire package is loaded into the SGA of the database instance to which the user is connected. That code is then shared by all sessions that have EXECUTE authority on the package. Any package data are then instantiated into the session's UGA (User Global Area), a private area in either the SGA or PGA (Program Global Area). If the package body contains an initialization section, that code will be executed. The initialization section is optional and appears at the end of the package body, beginning with a BEGIN statement and ending with the EXCEPTION section (if present) or the END of the package.The following package initialization section runs a query to transfer the user's minimum balance into a global package variable. Programs can then reference the packaged variable (via the function) to retrieve the balance, rather than executing the query repeatedly:CREATE OR REPLACE PACKAGE usrinfo IS FUNCTION minbal RETURN VARCHAR2; END usrinfo; / CREATE OR REPLACE PACKAGE BODY usrinfo IS g_minbal NUMBER; -- Package data FUNCTION minbal RETURN VARCHAR2 IS BEGIN RETURN g_minbal; END; BEGIN -- Initialization section SELECT minimum_balance INTO g_minbal FROM user_configuration WHERE username = USER; EXCEPTION WHEN NO_DATA_FOUND THEN g_minbal := NULL; END usrinfo;1.13 Triggers 1.15 Calling PL/SQL Functions in SQL O'Reilly & Associates. All rights reserved. |