• 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 comision și au venit în firmă înaintea șefului direct. Șeful direct al unui angajat este specificat în coloana manager_id. Procedura va afișa rezultatele.

Î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.

Ex. 2 Exemplu de ambiguitate:

O variabilă globală (publică) poate fi folosită pentru a inițializa un parametru al subprogramelor (public/privat) cât și în interiorul acestora.

Ex. 3. Exemplu de utilizarea a unei variabile publice.

O variabilă locală (privată) poate fi folosită pentru a inițializa un parametru al unei subprogram local (privat) și în interiorul subprogramelor (private/publice).

Ex. 4. Exemplu de utilizarea a unei variabile private.

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:

SELECT object_name, created, last_ddl_time, STATUS
FROM user_objects
WHERE object_type = 'PACKAGE';

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

SELECT object_name, created, STATUS
FROM user_objects 
WHERE 
	object_type = 'PACKAGE' AND
	object_name NOT IN (SELECT object_name 
			    FROM user_objects 
			    WHERE object_type = 'PACKAGE BODY');

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

SELECT text
FROM user_source
WHERE
	LOWER(name) = 'p_test' AND
	LOWER(TYPE) = 'package body' 
ORDER BY line;

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}

Un trigger este un bloc PL/SQL stocat pe server care se execută la apariția unui eveniment care modifică starea anumitor obiecte ale bazei de date. Termenul corespondent în literatura de specialitate românească este declanșator, dar este rar folosit și de aceea în continuare se va folosi termenul în limba engleză. Tipuri de evenimente care pot determina execuția unui trigger sunt:

• Comenzi INSERT, UPDATE, DELETE pe o tabelă
• Comenzi INSERT, UPDATE, DELETE pe un view cu opțiunea INSTEAD OF
• Comenzi CREATE, ALTER, DROP la nivel de schemă sau bază de date
• Comenzi SHUTDOWN, LOGON, LOGOFF la nivel de schemă sau bază de date

În general, triggerii se folosesc pentru:

• Gestionarea restricțiilor complexe de integritate
• Monitorizarea tranzacțiilor
• Efectuarea de replicări de tabele situate în diferite noduri ale unei baze de date distribuite
• Păstrarea semnăturii userilor care au efectuat operații pe baza de date
• Prelucrarea de informații statistice în legătură cu accesul tabelelor
• Jurnalizarea transparentă a evenimentelor

Printre avantajele utilizării triggerilor, se pot menționa:

• Declanșarea automată, la apariția evenimentului monitorizat
• Lansarea în execuție a unor proceduri stocate specifice
• Posibilitatea modificării în cascadă a mai multor obiecte corelate în baza de date
• Transparența față de utilizator

Sintaxa unui trigger este:

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
	{BEFORE | AFTER | INSTEAD OF}
	{DELETE | INSERT | UPDATE [OR {DELETE | INSERT | UPDATE }…] 
		[OF COLUMN[, COLUMN …] ]} 
	ON [schema.]tabel _name
	[referencing_clauses] 
	[FOR EACH ROW] 
	[WHEN (condition) ] 
	DECLARE
		trigger_variables
	BEGIN
		trigger_body
	END

Unde:

• trigger_name – numele triggerului PL/SQL
• schema – specifică schema pe care se definește triggerul sau în care există obiectele, în mod implicit este aleasă schema utilizatorului curent
• tabel _name – numele tabelul/view-ul pe care se monitorizează evenimentul
• column – numele coloanei (coloanelor) din tabelul/view-ul pe care se monitorizează evenimentul
• condition – reprezintă o condiție pentru executarea triggerului, fiind admise corelări dar nu și interogări
• trigger_variables – secțiunea de declarare a variabilelor locale ale triggerului
• trigger_body – reprezintă corpul triggerului
• BEFORE | AFTER – specifică momentul executării triggerului: înainte sau după apariția evenimentului
• INSTEAD OF – specifică că este permisă o operație de inserare, ștergere, modificare pe view-uri, pentru care nu este permisă operația în mod firesc
• INSERT | UPDATE | DELETE – specifică evenimentul pe care se declanșează triggerul
• FOR EACH ROW – specifică dacă execuția triggerului se face pentru fiecare linie afectată, cu respectarea condiției din WHEN

Sintaxa referencing_clauses:

REFERENCING {OLD [AS] old_variable NEW [AS] new_variable | 
			NEW [AS] new_variable OLD [AS] old_variable}

Se folosesc nume corelate pentru a specifica valorile noi și vechi ale rândului curent pentru care se declanșează triggerul. Aceste valori se pot folosi atât în clauza WHEN (old_variable.row_field, new_variable.row_field) cât și în blocul PL/SQL folosindu-se prefixate de două puncte : (de exemplu :old_variable.row_field, :new_variable.row_field).

Sintaxa pentru crearea unui trigger de sistem este următoarea:

	CREATE [OR REPLACE] TRIGGER [schema.]trigger_name 
		{BEFORE | AFTER} 
		{DDL_event_list | DB_event_list} 
		ON {DATABASE | SCHEMA} 
		[WHEN (condition) ] 
	DECLARE
		trigger_variables
	BEGIN
		trigger_body
	END

Unde:

• DDL_event_list - CREATE, DROP, ALTER
• DB_event_list - STARTUP, SHUTDOWN, LOGON, LOGOFF, SERVERERROR, SUSPEND

Există două tipuri de triggeri:

• Triggeri pe o comandă – sunt executați o singură dată pentru evenimentul declanșator. De exemplu dacă se execută o comandă INSERT de mai multe linii, triggerul este executat o singură dată. În acest caz, nu este limitare la numărul de linii afectate de eveniment;
• Triggeri pe o linie – este executat ori de câte ori o linie a unei tabele este afectată de evenimentul declanșator. De exemplu, dacă se execută o comandă UPDATE care actualizează k linii, atunci triggerul este executat de k ori.

Un trigger poate fi executat înainte ca un eveniment să aibă loc (opțiunea BEFORE) sau după ce evenimentul s-a consumat (opțiunea AFTER). În general, triggerii de tip BEFORE sunt folosiți pentru:

• A salva valorile coloanelor înaintea executării unei comenzi UPDATE
• A decide dacă acțiunea triggerului trebuie sau nu executată (aceasta poate îmbunătăți performanțele serverului prin eliminarea procesării inutile)

Triggeri de tip AFTER sunt, în general, folosiți atunci când:

• Se dorește ca executarea triggerului să se facă după ce comanda s-a efectuat cu succes
• Nu au apărut erori de procesare care ar impune o comandă ROLLBACK pentru respectiva tranzacție
• Trebuie alterate și alte date corelate cu cele deja afectate

Triggerii de tip BEFORE se declanșează la apariția unui eveniment, dar înainte ca evenimentul să se termine;

Ex. 5 Să se scrie un trigger de tip BEFORE care printează un mesaj ori de câte ori se face un insert în tabela JOBS.

Să verificăm cum lucrează făcând un insert în tabela JOBS:

INSERT INTO jobs(job_id, job_title, min_salary, max_salary)
VALUES('IT_SA', 'System Administrator', 6000, 12000);

Se observă că triggerul s-a declanșat și operația s-a făcut cu succes.

Să veden ce se întâmplă dacă forțăm o eroare la inserare (pe coloana JOB_TITLE este o constrângere de tip NOT NULL):

INSERT INTO jobs(job_id, min_salary, max_salary)
VALUES('IT_TL', 7000, 19000);

Se observă că triggerul s-a declanșat normal, nu a ținut cont că inserarea nu a fost efectuată și a generat o eroare.

Triggerii de tip AFTER se declanșează după ce evenimentul declanșator se termină.

Ex. 6 Să se creeze un trigger de tip AFTER care afișează un mesaj ori de câte ori se face o modificare în tabela JOBS.

Să verificăm cum lucrează triggerul făcând o modificare a salariului maxim în tabelul JOBS:

UPDATE jobs SET max_salary = 14000 WHERE job_id = 'IT_SA';

Se observă că triggerul a fost declanșat la apariția evenimentului, în acest caz comanda UPDATE și s-a afișat mesajul de avertizare. Triggerul se declanșează chiar dacă nu este găsită nicio înregistrare care să îndeplinească condițiile din clauza WHERE.

UPDATE jobs SET max_salary = 14000 WHERE job_id = 'IT_TM';

Deoarece nu există nicio functie cu ob_id = 'IT_TM', nu a fost alterată nicio linie, totuși triggerul s-a declanșat, deoarece nu a apărut nicio eroare de execuție.

Dacă însă operația se termină cu o eroare, triggerul nu se mai declanșează.

UPDATE jobs SET job_id = NULL WHERE job_id = 'IT_SA';
ROLLBACK;

Am încercat să atribuim valoarea NULL pentru job_id, ceea ce este interzis prin definirea tabelei, ca urmare s-a generat o eroare de sistem pe constrângerea respectivă și triggerul nu s-a declanșat.

Dacă vrem să introducem o restricție pentru declanșarea triggerului, putem să folosim clauza WHEN. Această clauză se poate folosi numai pentru triggerii care se declanșează pentru fiecare linie ce suferă modificări, deci poate fi folosită doar cu opțiunea FOR EACH ROW. În clauza WHEN se acceptă numai condiționări directe sau corelate, nu se acceptă cereri sau subcereri.

Ex. 7 Să se scrie un trigger de tip AFTER care se declanșează dacă salariul unui angajat cu funcția 'SH_CLERK' este majorat.

Care este rezultatul dacă se face o majorare de salariu pentru angajatul cu id-ul 182? Dar pentru cel cu id-ul 120?

UPDATE employees SET salary = 3000 WHERE employee_id = 182;
UPDATE employees SET salary = 3000 WHERE employee_id = 120;
ROLLBACK;

În cazul în care se execută mai multe comenzi DML, se pot folosi predicate condiționate în corpul triggerului. Predicatele condiționate sunt:

• INSERTING – returnează TRUE dacă triggerul se declanșează pe o comandă INSERT;
• UPDATING – retunează TRUE dacă triggerul se declanșează pe o comandă UPDATE;
• UPDATING (‘column_name’) – retunează TRUE dacă triggerul se declanșează pe o comandă UPDATE care modifică coloana specificată prin column_name;
• DELETING – returnează TRUE dacă triggerul se declanșează pe o comandă DELETE.

Ex. 8 Să se scrie un trigger de tip AFTER care se declanșează în momentul în care se face un insert, delete sau update pe coloanele SALARY și COMMISSION_PCT din tabela EMPLOYEES. Să se afiseze mesaje cu operați efectuată la declanșarea triggerului.

Verificări pentru Insert, Update si Delete:

INSERT INTO employees VALUES(1000, 'Ion', 'Ionescu', 'IIONESCU', '321.321.3214',
          TO_DATE('17-06-2015', 'dd-MM-yyyy'), 'IT_PROG', 4200, NULL, NULL, 90);
 
UPDATE employees SET commission_pct = nvl(commission_pct, 0) + 0.05 WHERE employee_id = 1000;
 
UPDATE employees SET salary = NVL(salary, 0) + 500 WHERE employee_id = 1000;
 
UPDATE employees SET salary = NVL(salary, 0) + 500, 
    commission_pct = nvl(commission_pct, 0) + 0.05  WHERE employee_id = 1000;
 
DELETE FROM employees WHERE employee_id = 1000;
 
ROLLBACK;

Acest trigger se definește numai pe view-uri, nu și pe tabele. Unele view-uri nu pot fi modificate prin comenzi DML, dar folosind un trigger cu opțiunea INSTEAD OF acest lucru este realizabil. View-urile care nu pot fi modificate prin comenzile UPDATE, INSERT sau DELETE sunt cele create printr-o interogare care conține în construcție:

• Un operator SET sau DISTINCT
• O funcție de agregare sau o funcție analitică
• Clauzele GROUP BY, ORDER BY, CONNECT BY sau START WITH
• O expresie tip colecție într-o clauză SELECT
• O subcerere într-o clauză SELECT
• Unele metode de JOIN

Orice view, aflat într-un astfel de caz, se poate face modificabil folosind un trigger cu opțiunea INSTEAD OF. Acest trigger trebuie să determine ce operație trebuie executată pentru modificarea tabelei pe care este creat view-ul respectiv. Dacă view-ul conține pseudocoloane sau expresii , acestea nu pot fi modificate direct printr-o comandă UPDATE, dar pot fi modificate prin trigger.

Ex. 9 Să creăm un view care selectează angajații care nu sunt sefi (un angajat este sef dacă apare pe coloana manager_id din tabelul employees).

Să facem o inserare în view-ul creat

INSERT INTO notsefi
VALUES('Treasury', 'Vasile', 'Ionescu', 'System Administrator', sysdate, 2000, 0.15);
 
INSERT INTO notsefi
VALUES('Mediu', 'Ion', 'Ionescu', 'System Administrator', sysdate, 2000, 0.15);
 
INSERT INTO notsefi
VALUES('Mediu', 'George', 'Ionescu', 'Help Deck', sysdate, 2000, 0.15);
 
ROLLBACK;

Comanda INSERT a generat o eroare, deoarece nu se acceptă inserarea într-un astfel de view.

Să creăm triggerul t_notsefi cu opțiunea INSTEAD OF care să permită operația de INSERT pe view.

Să se execute din nou comanenzile de insert de mai sus. Ce observați?

Informațiile despre triggeri se pot obține din dicționarul de date, ca pentru proceduri, funcții și pachete. De exemplu, dacă vrem să vedem toți triggerii creați de userul curent, data când au fost creați, data ultimei utilizări și starea lor, putem executa următoarea cerere SQL:

SELECT OBJECT_NAME,
  CREATED,
  LAST_DDL_TIME,
  STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER';

Un trigger se poate șterge din dicționar folosind comanda DDL :

DROP TRIGGER trigger_name;

Pentru a modifica starea unui trigger se folosească comanda DDL:

ALTER TRIGGER trigger_name {ENABLE | DISABLE}