• Pachete PL/SQL
• Specificațiile unui pachet
• Corpul unui pachet
• Restricții în definirea unui pachet
• Informații din dicționarul de date
• Pachete standard

Un pachet (package) este o bibliotecă de obiecte stocate pe server, de tipul procedurilor stocate, funcțiilor, cursoarelor, tipurilor de date, excepțiilor, variabilelor și constantelor. Toate obiectele declarate în secțiunea de creare a unui pachete sunt globale (publice) și pot fi apelate din orice program PL/SQL, asemănător variabilelor globale din alte limbaje de programare; Un pachet este compus din două secțiuni distincte:

• O secțiune de creare (create package) care conține specificațiile globale (publice) de declarare a conținutului (structura obiectelor)
• O secțiune care cuprinde corpul pachetelor (package body) în care sunt descrise efectiv obiectele și subprograme, variabile, structuri locale (private)

Principalele avantaje oferite de un pachet sunt:

• Modularitatea aplicațiilor
• Posibilitatea declarării de obiecte globale
• Îmbunătățirea performanțelor sistemului de gestiune
• Ușurința în proiectarea aplicațiilor
• Adăugarea de funcționalități noi

Specificațiile reprezintă partea publică a unui pachet și au următoarea sintaxă:

	CREATE [OR REPLACE] PACKAGE package_name {IS|AS}
		global (public) types AND variable declarations
		global (public) subprogram specifications
	END [package_name]

Unde :

• package_name – numele pachetului
• global (public) types and variable declarations – reprezintă declarațiile globale (publice) ale cursoarelor, excepțiilor, constantelor, variabilelor și tipurilor de date și descrierea acestora * din punct de vedere al structurii
• global (public) subprogram specifications – reprezintă numele procedurilor și funcțiilor cu parametrii formali aferenți. Acestea sunt globale (publice).

Subprogramele, variabilele și cursoarele care vor fi apelate din exterior trebuie să fie cuprinse în secțiunea de creare a pachetului, pe când subprogramele, cursoarele, excepțiile, constantele, variabilele și tipurile de date folosite doar în pachet vor fi declarate doar în secțiunea body a acestuia. Referirea într-un program PL/SQL a unei componente globale (publice) a unui pachet se face folosind numele componentei, având ca prefix numele pachetului:

	package_name.type_name
	package_name.item_name
	package_name.subprogram_name

Unde:

• package_name – numele pachetului
• type_name – numele unui tip de date
• item_name – numele unui cursor, excepție, constantă sau variabilă locală
• subprogram_name – numele unei proceduri sau funcții

Dacă compilarea unui pachet se face cu succes, apare mesajul: Package created. În caz că apar erori la crearea unui pachet apare mesajul de avertizare: Warning: Package created with compilation errors.

Corpul unui pachet (package body) conține descrierea efectivă a procedurilor și funcțiilor definite în specificații. Această parte poate să conțină și componente locale (private) care sunt folosite doar în interiorul pachetului.

Sintaxa este:

	CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
		LOCAL TYPE AND variable declarations
		subprogram bodies
		[BEGIN
			initialization statements
		END;]
	END [package_name]

Unde:

• package_name – numele pachetului
• local type and variable declarations – reprezintă declarațiile locale ale cursoarelor, excepțiilor, constantelor, variabilelor și tipurilor de date
• subprogram bodies – codul sursă al procedurilor și funcțiilor definite în specificații
• initialization statements – codul de inițializare

Cursoarele, tipurile de date, excepțiile, variabilele și constantele declarate în corpul pachetului vor avea caracter local (privat), deci vor fi accesibile numai în blocurile în care au fost definite. La primul apel al unui obiect dintr-un pachet, întregul pachet este inițializat. Inițializarea implică încărcarea unui pachet de pe disc în memorie și alocarea de spațiu în memorie pentru variabilele globale (publice).

Dacă apelul se referă la o procedură sau funcție, inițializarea este urmată de execuția codului deja compilat. Fiecare sesiune de lucru are o copie proprie a variabilelor din pachet. Sesiuni diferite pot apela același pachet, dar fiecare apel al pachetului aițializări prin atribuiri directe sau apelează proceduri de inițializare.

Ex. 1 Să se scrie un pachet p_angajare, care conține o funcție și o procedură, pentru a face o listă cu angajații care au venit în firmă înaintea unui director al companiei(care le este șef direct) și au primit comision. Șeful direct al unui angajat este specificat în coloana mgr. Procedura va insera rezultatele în tabela lista.

Pasul I – crearea tabelei lista

Pasul II – crearea specificațiilor

Pasul III – crearea corpului pachetului

Pasul IV – execuția pachetului

Pasul V – rezultatul execuției

SELECT *
FROM lista;

În pachete nu se permite declararea a două proceduri sau funcții cu același nume, dacă parametrii acestora diferă numai prin nume sau mod (IN, OUT, IN OUT). Trebuie ca cel puțin un parametru să fie de un alt tip, iar tipul nu trebuie să fie din aceeași familie(de exemplul tipul CHAR este din aceeași familie cu VARCHAR2). Aceeași situație este și în cazul rezultatelor returnat de o funcție.

Exemplu de ambiguitate:

Exemplul se compilează fără erori, însă dacă executăm blocul atunci o să apară o eroare:

O variabilă globală (publică) poate fi folosită pentru a inițializa un parametru al subprogramelor (public/privat) cât și în interiorul acestora. De exemplu construcția următoare este permisă:

O variabilă locală (privată) poate fi folosită pentru a inițializa un parametru al unei subprogram local (privat) și în interiorul subprogramelor (private/publice). De exemplu construcția următoare este permisă:

Deoarece pachetele sunt create ca oricare alt obiect, din dicționarul bazei de date putem să aflăm informații despre ele făcând interogări pe view- urile sistemului de gestiune ORACLE. De exemplu dacă vrem să vedem toate pachetele create de userul curent, data când au fost create, data ultimei modificări și starea lor, putem să executăm următoarea cerere SQL:

Pentru a vedea care pahete au specificații și nu au secțiunea body se execută următoarea cerere SQL:

Specificațiile și corpul unui pachet se pot șterge din dicționar folosind comanda DDL, DROP:

DROP PACKAGE pakage_name;

Pentru a șterge numai corpul unui pachet se folosește comanda SQL:

DROP PACKAGE BODY package_name;

Pentru a da privilegii de execuție a pachetelor altor utilizatori, utilizatorul care a creat pachetul (sau administratorul) poate folosi comanda DCL, GRANT:

GRANT EXECUTE ON package_name TO user_name;

Utilizatorul grantificat poate apela un obiect din pachetul respectiv, specificând în apel userul, pachetul și obiectul. De exemplu, o procedură fără parametri poate fi apelată direct din SQL*Plus astfel:

EXECUTE user_name.package_nane.procedure_name;

O procedură poate fi apelată și dintr-un bloc SQL:

DECLARE
BEGIN 
  ...
  user_name.package_name.procedure_name; ...
END;

Pentru a revoca privilegiile de execuție se folosește comanda REVOKE:

REVOKE EXECUTE ON package_name FROM user_name;

Serverul Oracle conține câteva pachete standard(de sistem), care sunt instalate odată cu serverul de baze de date. Câteva din cele mai uzuale pachete standard sunt:

• DBMS_STANDARD conține proceduri care ajută programatorul în interacțiunea cu Oracle. De exemplu, procedura raise_application_error este folosită pentru definirea propriilor mesaje;
• DBMS_OUTPUT conține proceduri pentru afișare, folosite în depanarea sau execuția programelor. De exemplu, procesura put_line este folosită pentru afișarea de mesaje în SQL*Plus;
• DBMS_PIPE permite comunicarea între sesiuni diferite, folosind o zonă de memorie comună (pipe), pentru schimbul de informații. O sesiune poate folosi două proceduri pack_message și send_message, pentru a împacheta și pune mesajul în zona comună și apoi a-l trimite către o altă sesiune. Sesiunea receptoare poate folosi două proceduri receive_message și unpack_message, care fac operațiunile inverse. De exemplu, se pot scrie rutine C++, care permit serverelor externe să capteze informații și să le trimită către proceduri stocate în baza de date Oracle.

UTL_FILE – permite programatorilor PL/SQL să scrie și să citească fișiere text gestionate de sistemul de operare. Pentru aceasta, se poate folosi funcția fopen pentru deschiderea fișierului și procedura get_line pentru citirea linie cu linie;

• UTL_HTML permite programatorilor PL/SQL să acceseze Internetul sau Oracle Web Server, folosind protocolul HTTP; Pachetul acceptă un URL, se conectează la site-ul specificat și întoarce datele solicitate, de regulă în format HTML;
• DBMS_SQL permite unui programator PL/SQL să execute comenzi DDL sau comenzi SQL standard, în mod dinamic;
• DBMS_ALERT permite utilizarea triggerelor pentru alertare, în cazul în care intervin modificări în baza de date.

Informațiile din dicționar, legate de pachetele standard, se obțin folosind view-urile de sistem DBA_OBJECTS și DBA_SOURCE, acesibile userului system sau celor care au privilegiul DBA.

Pentru vizualizarea mai multor informații din dicționar, legate de pachetele standard, se poate folosi cererea SQL:

SELECT text
FROM dba_source WHERE LOWER(name) = 'package_name' 
  AND LOWER(TYPE)='package'
ORDER BY line;

SET standard_package {ON|OFF}

Ex. 2 Să se scrie un pachet care conține:

• O funcție care calculează venitul maxim pe un departament;
• O funcție care calculează vechimea cea mai mare în companie, a unui angajat din deprtamentul din care face parte;
• O procedură care apelează cele două funcții pentru a face o listă cu angajații care au venitul sau vechimea cea mai mare în propriul departament (calculată ca număr de luni complete);

Procedura face o listă care are antetul:

• DEPARTAMENT
• NUME
• VENIT
• VECHIME