Table of Contents

Laboratorul 06 - PL/SQL Pachete și Triggere

Conținut

Pachete PL/SQL

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:

Un pachet este creat ca un obiect în dicționarul bazei de date.

Principalele avantaje oferite de un pachet sunt:

Specificațiile Pachetului

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 :

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:

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.

Pentru a vedea erorile de compilare se va folosi comanda: show errors.

Corpul Pachetului

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:

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.

Restricții

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

  • Exemplul se compilează fără erori.
  • Dacă executăm blocul, atunci o să apară o eroare!
  • Aceast mod de funționare poate să creeze o anumită confuzie.

Dacă schimbăm tipul celui de-al doilea parametru în ceva care nu este compatibil cu tipurile numerie, VARCHAR2 să zicem, totul merge cum trebuie.

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.

Informații din dicționarul bazei de date

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');

Pentru a vedea secțiunea de specificații a pachetului p_test, executăm următoarea cerere SQL. 

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

Pentru a lista codul sursă a pachetului p_test, se execută următoarea cerere SQL. 

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:

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;

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;

Pachetele standard pot fi activate sau dezactivate (ENABLE/DISABLE) cu comanda SET. 

SET standard_package {ON|OFF}

Triggere PL/SQL

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:

În general, triggerii se folosesc pentru:

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

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:

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

Numele implicite sunt OLD și NEW.

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:

Crearea unui trigger

Există două tipuri de triggeri:

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:

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

Triggeri de tip BEFORE

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.

Dacă compilarea unui trigger se face cu succes, apare mesajul: “Trigger created”

În caz că apar erori la crearea unui trigger apare mesajul de avertizare: “Warning: Trigger created with compilation errors.”

Pentru a vedea erorile de compilare se va folosi comanda: “show errors;”

Triggeri de tip AFTER

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.

  • Putem trage concluzia că un trigger de tip BEFORE se declanșează necondiționat de rezultatul comenzii SQL (chiar dacă se generează o eroare), pe când cel de tip AFTER nu se declanșează dacă comanda SQL generează o eroare.
  • S-a utilizat comanda ROLLBACK pentru ca modificările să nu rămână permanente.

Restricții în clauza WHEN

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 clauza WHEN, variabilele old și new se apelează fără caracterul ':'
  • În zona de execuție a triggerului, variabilele old și new se apelează cu caracterul ':'

Predicate condiționate

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

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;

Triggere cu opțiunea INSTEAD OF

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:

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?

În corpul triggerului nu trebuie să fie cuprinse instrucțiuni care să afecteze starea tabelei sau view-ului pe care se monitorizează evenimentul.

Informații din dicționarul bazei de date

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}