PL/SQL program packages

A package is a grouping or packaging together of elements of PL/SQL code into a named scope. Packages provide a structure (both logically and physically) in which you can organize your programs and other PL/SQL elements such as cursors, TYPEs, and variables. They also offer significant, unique functionality, including the ability to hide logic and data from view and to define and manipulate “global” or session-persistent data.

The package is a powerful and important element of the PL/SQL language. It should be the cornerstone of any application development project. What makes packages so powerful and important? Consider their advantages. With packages, you can:

1) Enhance and maintain applications more easily. As more and more of the production PL/SQL code base moves into maintenance mode, the quality of PL/SQL applications will be measured as much by the ease of maintenance as by overall performance. Packages can make a substantial difference in this regard. From data encapsulation (hiding all calls to SQL statements behind a procedural interface to avoid repetition), to enumerating constants for literal or “magic” values, to grouping together logically related functionality, package-driven design and implementation lead to reduced points of failure in an application.

2) Improve overall application performance. By using packages, you can improve the performance of your code in a number of ways. Persistent package data can dramatically improve the response time of queries by caching static data, thereby avoiding repeated queries of the same information. Oracle’s memory management also optimizes access to code defined in packages.

3) Shore up built-in weaknesses. It is quite straightforward to construct a package on top of existing functionality where there are drawbacks. (Consider, for example, the UTL_FILE and DBMS_OUTPUT built-in packages, in which crucial functionality is badly or partially implemented.) You don’t have to accept these weaknesses; instead, you can build your own package on top of Oracle’s to correct as many of the problems as possible.

4) Minimize the need to recompile code. As you will read in this chapter, a package usually consists of two pieces of code: the specification and the body. External programs (not defined in the package) can call only programs listed in the specification. If you change and recompile the package body, those external programs are not invalidated. Minimizing the need to recompile code is a critical factor in administering large bodies of application logic.

1

package B

specification

body

package A

specification

body

A package partsA package consists of up to two chunks of code:

1) the specification (required);2) the body (optional, but almost always present).

The specification defines how a developer can use the package: which programs can be called, what cursors can be opened, and so on. The body contains the implementation of the programs (and, perhaps, cursors) listed in the specification, plus other code elements as needed.

User schema

Other objects

2

Some Package-Related Concepts

1. Information hiding is the practice of removing from view information about one’s system or application. Why would a developer ever want to hide information? Couldn’t it get lost? Information hiding is actually quite a valuable principle and coding technique. First of all, humans can deal with only so much complexity at a time. A number of researchers have demonstrated that remembering more than seven (plus or minus two) items in a group, for example, is challenging for the average human brain (this is known as the “human hrair limit,” an expression that comes from the book Watership Down). By hiding unnecessary detail, you can focus on the important stuff. Second, not everyone needs to know—or should be allowed to know—all the details. I might need to call a function that calculates CEO compensation, but the formula itself could very well be confidential. In addition, if the formula changes, the code is insulated from that change.2. Public and private. Closely related to information hiding is the fact that packages are built around the concepts of public and private elements. Public code is defined in the package specification and is available to any schema that has EXECUTE authority on the package. Private code, on the other hand, is defined in and visible only from within the package. External programs using the package cannot see or use private code. When you build a package, you decide which of the package elements are public and which are private. You also can hide all the details of the package body from the view of other schemas/developers. In this way, you use the package to hide the implementation details of your programs. This is most important when you want to isolate the most volatile aspects of your application, such as platform dependencies, frequently changing data structures, and temporary workarounds. In early stages of development you can also implement programs in the package body as “stubs,” containing just enough code to allow the package to compile. This technique allows you to focus on the interfaces of your programs and the way they connect to each other. 2. The package specification contains the definition or specification of all the publicly available elements in the package that may be referenced outside of the package. The specification is like one big declaration section; it does not contain any PL/SQL blocks or executable code. If a specification is well designed, a developer can learn from it everything necessary to use the package. There should never be any need to go “behind” the interface of the specification and look at the implementation, which is in the body. 3. The body of the package contains all the code required to implement elements defined in the package specification. The body may also contain private elements that do not appear in the specification and therefore cannot be referenced outside of the package. The body of the package resembles a standalone module’s declaration section. It contains both declarations of variables and the definitions of all package modules. The package body may also contain an execution section, which is called the initialization section because it is run only once, to initialize the package.

3

4. Initialization should not be a new concept for a programmer. In the context of packages, however, it takes on a specific meaning. Rather than initializing the value of a single variable, you can initialize the entire package with arbitrarily complex code. Oracle takes responsibility for making sure that the package is initialized only once per session.5. Session persistence. As a database programmer, the concept of persistence should also be familiar. After all, a database is all about persistence: I insert a row into the database on Monday, fly to the Bahamas for the rest of the week, and when I return to work on the following Monday, my row is still in the database. It persisted! Another kind of persistence is session persistence. This means that if I connect to the Oracle database (establish a session) and execute a program that assigns a value to a package-level variable (i.e., a variable declared in a package specification or body, outside of any program in the package), that variable is set to persist for the length of my session, and it retains its value even if the program that performed the assignment has ended. It turns out that the package is the construct that offers support in the PL/SQL language for session-persistent data structures.

4

Diagramming Privacy

Let’s go back to the public/private dichotomy for a moment. The distinction drawn between public and private elements in a package gives PL/SQL developers unprecedented control over their data structures and programs. A fellow named Grady Booch came up with a visual way to describe this aspect of a package (now called, naturally, the Booch diagram). Take a look at Figure Notice the two labels Inside and Outside. Outside consists of all the programs you write that are not a part of the package at hand (the external programs). Inside consists of the package body (the internals or implementation of the package).

5

Use of package specification

create or replace package PAKETE_2 is type TIPS_2 is table of integer index by varchar(15);end PAKETE_2;

create or replace procedure OUTPUT(aa PAKETE_2.TIPS_2) is i varchar2(15);begin i := aa.FIRST; while i is not NULL loop DBMS_OUTPUT.PUT_LINE (aa(i) || ' ' || i); i := aa.NEXT(i); end loop;end;

declare aa PAKETE_2.TIPS_2;begin aa('nulle') := 0; aa('viens') := 1; aa('divi') := 2; IZVADE(aa);end;

Result:0 nulle1 viens2 divi

6

Package body

create or replace body PACKAGE_NAME as

variables definition; -- lokālie mainīgie

cursors;

PROCEDURE … ; ējums

PROCEDURE … ; --

end PACKAGE_NAME;

7

Programmu paketes struktūras piemērs

Paketes specifikācijas definējums:

create or replace package PAKETE_1 as

PUBLIC tipa mainīgie un tipi (globālie mainīgie un tipi) procedure PROCEDŪRA_1 (parametrs_11 IN NUMBER, parametrs_12 IN NUMBER); procedure PROCEDŪRA_2 (parametrs_21 IN NUMBER, parametrs_22 IN NUMBER);

end PAKETE_1;

Paketes ķermeņa definējums:

create or replace package body PAKETE_1 as

PRIVATE tipa manīgie un tipi (lokālie mainīgie un tipi)

procedure PROCEDŪRA_1 (parametrs_11 IN NUMBER, parametrs_12 IN NUMBER) is begin . . . end PROCEDŪRA_1;

procedure PROCEDŪRA_2 (parametrs_21 IN NUMBER, parametrs_22 IN NUMBER) is begin . . . end PROCEDŪRA_2;

Paketes inicializācijas daļabegin . . .

end PAKETE_1;

8

Use of program package

create or replace package PROGR_P astype CURS_TYPE is REF CURSOR;function F_DATA return CURS_TYPE;end PROGR_P;

create or replace package body PROGR_P asfunction F_DATA return CURS_TYPE is

curs_a CURS_TYPE;row FIRMS%rowtype;

begin open curs_a for select * from FIRMS;

fetch curs_a into row;return curs_a;

end;end PROGR_P;

declaretype CURS_TYPE is REF CURSOR;curs_a CURS_TYPE;type TAB_TYPE is table of FIRMS%rowtype;tab TAB_TYPE := TAB_TYPE();

begincurs_a := PROGR_P.F_DATA();tab.extend;fetch curs_a into tab(1);tab.extend;fetch curs_a into tab(2);close curs_a;DBMS_OUTPUT.PUT_LINE(tab(1).F_NUM || ' ' || tab(1).F_NAM || ' ' || tab(1).CRE_DAT);DBMS_OUTPUT.PUT_LINE(tab(2).F_NUM || ' ' || tab(2).F_NAM || ' ' || tab(2).CRE_DAT);

end;

2 CCC 23-JAN-063 AAA 06-DEC-07

FIRMSF_NUM F_NAM CRE_DAT1 BBB 2005-FEB-152 CCC 2006-JAN-233 AAA 2007-DEC-6

9

Ārēji definēto un iekšēji definēto datu izmantošana programmu paketēdeclare

X PAKETE_1.TABULAS_TIPS_1 := PAKETE_1.TABULAS_TIPS_1(1, 1);w number(16,6) := 0;

beginX(1) := 3;X(2) := 3;PAKETE_1.PROCEDURA_1(X, w);

end;

create or replace package PAKETE_1 astype TABULAS_TIPS_1 is table of NUMBER(16,6);X1 TABULAS_TIPS_1 := TABULAS_TIPS_1(2, 2);w number(16,6);procedure PROCEDURA_1(X IN TABULAS_TIPS_1, w OUT number);end PAKETE_1;

create or replace package body PAKETE_1 asprocedure PROCEDURA_1 (X IN TABULAS_TIPS_1, w OUT number) isbegin-- Aprēķins ar ārējiem (parametru) datiemw := 3* X(1)*X(1) + 7*X(2)*X(2);DBMS_OUTPUT.PUT_LINE(' X(1) = ' || X(1) || ' X(2) = ' || X(2) || ' w = ' || w);-- Aprēķins ar paketē definētiem datiemw := 3* X1(1)*X1(1) + 7*X1(2)*X1(2);DBMS_OUTPUT.PUT_LINE(' X1(1) = ' || X1(1) || ' X1(2) = ' || X1(2) || ' w = ' || w);end PROCEDURA_1;end PAKETE_1;

X(1) = 3 X(2) = 3 w = 90X1(1) = 2 X1(2) = 2 w = 40

10

Programmu paketes objektu izsaukšana Lai atsauktos uz programmu paketes objektu, jānorāda pakete un attiecīgā objekta nosaukums ar parametriem (ja tādi ir):

PAKETES_NOSAUKUMS.PROCEDŪRAS_NOSAUKUMS(parametru_saraksts);

Var eksistēt vairākas procedūras ar vienu nosaukumu un dažādiem ieejas parametriem (overloaded). Tas ir ērti darbībai ar funkciju, var norādīt konkrētajā situācijā vēlamos ieejas parametrus.

11

Programmu paketē definēto datu izmaiņadeclare

w number(16,6) := 0;begin

PAKETE_1.X(1) := 4;PAKETE_1.X(2) := 4;PAKETE_1.PROCEDURA_1(PAKETE_1.X, w);

end;

create or replace package PAKETE_1 astype TABULAS_TIPS_1 is table of NUMBER(16,6);X TABULAS_TIPS_1 := TABULAS_TIPS_1(2, 2);w number(16,6);procedure PROCEDURA_1(X IN TABULAS_TIPS_1, w OUT number);end PAKETE_1;

create or replace package body PAKETE_1 asprocedure PROCEDURA_1 (X IN TABULAS_TIPS_1, w OUT number) isbeginw := 3* X(1)*X(1) + 7*X(2)*X(2);DBMS_OUTPUT.PUT_LINE(' X(1) = ' || X(1) || ' X(2) = ' || X(2) || ' w = ' || w);end PROCEDURA_1;end PAKETE_1;

X(1) = 4 X(2) = 4 w = 160

12

Programmu paketes aktualizācija un inicializācija (initialization)

Pirmo reizi izsaucot paketi seansā tā tiek sagatavota jeb aktualizēta jeb konkretizēta (instantiated) lietošanai seansā. Pakete tiek nolasīta no diska atmiņas un tiek palaists tās p-kods. Visiem mainīgiem tiek izdalīta atmiņa. Lai tas automātiski notiktu, paketes ķermenim pievieno inicializācijas (initialization) daļu, novietojot to aiz visu objektu definējumiem. Šī daļa tiek veidota kā anonīmais PL/SQL bloks. Tā izpildās sesijā tikai vienreiz un:

1) automātiski piešķir norādītajiem mainīgiem un konstantēm to sākotnējās vērtības;

2) izpilda inicializājas daļas programmas kodu.

If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state.

13

Programmu paketes inicializācijas daļas izmantošana declare

m1 number(16,6);m2 number(16,6);

beginm1 := PAKETE_1.X(1);m2 := PAKETE_1.X(2);DBMS_OUTPUT.PUT_LINE(' X(1) = ' || m1 || ' X(2) = ' || m2);

end;

create or replace package PAKETE_1 astype TABULAS_TIPS_1 is table of NUMBER(16,6);X TABULAS_TIPS_1 := TABULAS_TIPS_1(0, 0);w number(16,6);procedure PROCEDURA_1(X IN TABULAS_TIPS_1, w OUT number);end PAKETE_1;

create or replace package body PAKETE_1 asprocedure PROCEDURA_1 (X IN TABULAS_TIPS_1, w OUT number) isbeginw := 3* X(1)*X(1) + 7*X(2)*X(2);DBMS_OUTPUT.PUT_LINE(' X(1) = ' || X(1) || ' X(2) = ' || X(2) || ' w = ' || w);end PROCEDURA_1;-- Programmu paketes inicializācijabeginX(1) := 5;X(2) := 5;end PAKETE_1;

X(1) = 5 X(2) = 5

14

Lokālo mainīgo definēšana programmu paketes ķermenī

declarem1 number(16,6);m2 number(16,6);

beginm1 := PAKETE_1.X2(1); Error!m2 := PAKETE_1.X2(2); Error!

end;

create or replace package PAKETE_1 astype TABULAS_TIPS_1 is table of NUMBER(16,6);X TABULAS_TIPS_1 := TABULAS_TIPS_1(0, 0);w number(16,6);procedure PROCEDURA_1(X IN TABULAS_TIPS_1, w OUT number);procedure PROCEDURA_2;end PAKETE_1;

create or replace package body PAKETE_1 astype TABULAS_TIPS_2 is table of NUMBER(16,6);X2 TABULAS_TIPS_2 := TABULAS_TIPS_2(3, 3);w2 number(16,6);procedure PROCEDURA_1 (X IN TABULAS_TIPS_1, w OUT number) isbeginw := 3* X(1)*X(1) + 7*X(2)*X(2);DBMS_OUTPUT.PUT_LINE(' X(1) = ' || X(1) || ' X(2) = ' || X(2) || ' w = ' || w);end PROCEDURA_1;

procedure PROCEDURA_2 isbeginw2 := 3* X2(1)*X2(1) + 7*X2(2)*X2(2);DBMS_OUTPUT.PUT_LINE(' X2(1) = ' || X2(1) || ' X2(2) = ' || X2(2) || ' w2 = ' || w2);end PROCEDURA_2;end PAKETE_1;

beginPAKETE_1.PROCEDURA_2;

end;

X2(1) = 3 X2(2) = 3 w2 = 90

15

Programmu paketes piemērs (Oracle dokumentācija)Example creates a table, log, and a package, emp_admin, and then invokes package subprograms from an anonymous block. The package has both specification and body.The specification declares a public type, cursor, and exception, and three public subprograms. One public subprogram is overloaded (for information about overloaded subprograms. The body declares a private variable, defines the public cursor and subprograms that the specification declares, declares and defines a private function, and has an initialization part.The initialization part (which runs only the first time the anonymous block references the package) inserts one row into the table log and initializes the private variable number_hired to zero. Every time the package procedure hire_employee is invoked, it updates the private variable number_hired.

create table EMPLOYEES(LAST_NAME varchar2(20),FIRST_NAME varchar2(20),EMAIL varchar2(30),PHONE_NUMBER varchar2(11), JOB_ID varchar2(20), SALARY number(8,2), COMMISSION_PCT number, MANAGER_ID number, DEPARTMENT_ID number);

create table ZURNALS( DATE_OF)ACTION date, USER_ID varchar2(20), PACKAGE_NAME varchar2(30));

Programmu paketes specifikācija

create or replace package EMP_ADMIN as-- Publisku raksta tipa, kursora un izņēmuma situācijas definēšana.type EmpRecTyp is record(emp_id NUMBER, sal NUMBER);cursor desc_salary return EmpRecTyp;INVALID_SALARY exception;-- Publisku apakšprogrammu definēšana.function Hire_Employee (LAST_NAME varchar2, FIRST_NAME varchar2, EMAIL varchar2, PHONE_NUMBER varchar2, JOB_ID varchar2, SALARY number, COMMISSION_PCT number, MANAGER_ID number, DEPARTMENT_ID number) return number;-- Pārslogotās Overload preceding public subprogram: PROCEDURE fire_employee (emp_id NUMBER); PROCEDURE fire_employee (emp_email VARCHAR2);

PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER); FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;END emp_admin;

16

Programmu paketes ķermenis

CREATE OR REPLACE PACKAGE BODY emp_admin AS number_hired NUMBER; -- private variable, visible only in this package

-- Kursora definēšana cursor Desc_Salary return EmpRecTyp is select EMPLOYEE_ID, SALARY from EMPLOYEES order by SALARY DESC;

-- Define subprograms declared in package specification:

FUNCTION hire_employee ( last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER) RETURN NUMBER IS new_emp_id NUMBER; BEGIN new_emp_id := employees_seq.NEXTVAL; INSERT INTO employees(employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES ( new_emp_id, hire_employee.last_name, hire_employee.first_name, hire_employee.email, hire_employee.phone_number, SYSDATE, hire_employee.job_id, hire_employee.salary, hire_employee.commission_pct, hire_employee.manager_id, hire_employee.department_id); number_hired := number_hired + 1; DBMS_OUTPUT.PUT_LINE('The number of employees hired is ' || TO_CHAR(number_hired)); RETURN new_emp_id; END hire_employee;

procedure Fire_Employee (emp_id NUMBER) is begin DELETE FROM employees WHERE employee_id = emp_id; end Fire_Employee;

PROCEDURE fire_employee (emp_email VARCHAR2) IS BEGIN

17

DELETE FROM employees WHERE email = emp_email; END fire_employee;

-- Define private function, available only inside package:

FUNCTION sal_ok (jobid VARCHAR2, sal NUMBER ) RETURN BOOLEAN IS min_sal NUMBER; max_sal NUMBER; BEGIN SELECT MIN(salary), MAX(salary) INTO min_sal, max_sal FROM employees WHERE job_id = jobid; RETURN (sal >= min_sal) AND (sal <= max_sal); END sal_ok;

PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS sal NUMBER(8,2); jobid VARCHAR2(10); BEGIN SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = emp_id;

IF sal_ok(jobid, sal + amount) THEN -- Invoke private function UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; ELSE RAISE invalid_salary; END IF; EXCEPTION WHEN invalid_salary THEN DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.'); END raise_salary;

FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp IS emp_rec EmpRecTyp; BEGIN OPEN desc_salary; FOR i IN 1..n LOOP FETCH desc_salary INTO emp_rec; END LOOP; CLOSE desc_salary; RETURN emp_rec; END nth_highest_salary;-- Programmu paketes ķermeņa daļas inicializēšanabegin insert into ZURNALS (date_of_action, user_id, package_name) VALUES (SYSDATE, USER, 'EMP_ADMIN'); number_hired := 0;end Emp_Admin;

18

Paketes apakšprogrammu izsaukšana.

DECLARE new_emp_id NUMBER(6);BEGIN new_emp_id := emp_admin.hire_employee ('Belden', 'Enrique', 'EBELDEN', '555.111.2222', 'ST_CLERK', 2500, .1, 101, 110); DBMS_OUTPUT.PUT_LINE ('The employee id is ' || TO_CHAR(new_emp_id)); emp_admin.raise_salary (new_emp_id, 100); DBMS_OUTPUT.PUT_LINE ('The 10th highest salary is '|| TO_CHAR (emp_admin.nth_highest_salary(10).sal) || ', belonging to employee: ' || TO_CHAR (emp_admin.nth_highest_salary(10).emp_id)); emp_admin.fire_employee(new_emp_id); -- You can also delete the newly added employee as follows: -- emp_admin.fire_employee('EBELDEN');END;

Result is similar to:

The number of employees hired is 1The employee id is 212The 10th highest salary is 12075, belonging to employee: 168There are now 107 employees.

19