concepte avansate in pl/sql
DESCRIPTION
Concepte avansate in PL/SQLTRANSCRIPT
Limbajul PL/SQL
Curs VICONCEPTE AVANSATE IN PL/SQL
Limbajul PL/SQL
Obiective / Continut
Aparitia, propagarea si gestionarea exceptiilor
PL/SQL.
Tipuri de exceptii :
– Exceptii predefinite Oracle.
– Alte exceptii Oracle.
– Exceptii definite de utilizator.
Proceduri PL/SQL: stocate pe server sau pe
partea de client.
Limbajul PL/SQL
Obiective / Continut
Crearea, apelarea si transmiterea parametrilor
unei proceduri stocate.
Functii PL/SQL: gestionare si apelare.
Pachete stocate: parti, elemente componente si
mod de apelare.
Declansatori (‘trigger’): creare si stergere.
Reguli de declansare a triggerilor.
Limbajul PL/SQL
Exceptii in PL/SQL
Ce este o exceptie ?– Un eveniment ce apare la executia unui bloc PL/SQL– Are asociata un identificator care poate fi captat si gestionat
corespunzator
Cum apare o exceptie ?– Cind apare o eroare Oracle– Poate fi lansata explicit de utilizator
Cum se gestioneaza o exceptie ?– Se poate “prinde” in sectiunea de tratare a exceptiilor– Se poate propaga in mediul apelant
Limbajul PL/SQL
Gestionarea exceptiilor
Captarea exceptiei
DECLAREDECLARE
BEGINBEGIN
END;END;
Exceptia este ridicata
EXCEPTIONEXCEPTION
Exceptia este prinsa
Propagarea exceptiei
DECLAREDECLARE
BEGINBEGIN
END;END;
Exceptia este ridicata
EXCEPTIONEXCEPTION
Exceptia nu este prinsa
Exceptia se propaga in mediul apelant
Limbajul PL/SQL
Tipuri de exceptii
Exceptii predefinite Oracle
Alte exceptii Oracle
Exceptii definite de utilizator
}} Lansate Lansate implicitimplicit
Lansate explicitLansate explicit
Limbajul PL/SQL
Tratarea exceptiilor
Sintaxa:EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .]
EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .]
Limbajul PL/SQL
Reguli pentru tratarea exceptiilor
Clauza WHEN OTHERS este ultima in cadrul unui bloc PL/SQL.
Cuvintul cheie EXCEPTION semnaleaza sectiunea de tratare a exceptiilor.
Sunt permise mai multe ‘handlere’ de tratare intr-o sectiune de tratare a exceptiilor.
Doar un singur ‘handler’ de exceptie este procesat inainte de parasirea sectiunii de exceptii.
Limbajul PL/SQL
Exceptii predefinite Oracle
In sectiunea de tratarea exceptiilor se apeleaza numele standard de exceptii Oracle .
Exemple de exceptii standard Oracle:– NO_DATA_FOUND– TOO_MANY_ROWS– INVALID_CURSOR– ZERO_DIVIDE– DUP_VAL_ON_INDEX
Limbajul PL/SQL
Exceptii predefinite Oracle
Exemplu:
BEGIN SELECT ...;EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; WHEN OTHERS THEN statement1; statement2; statement3;END;
Limbajul PL/SQL
Alte exceptii Oracle
DeclarareDeclarare
Numirea exceptiei
AsociereAsociere
PRAGMA EXCEPTION_INIT
Sectiunea DeclarativaSectiunea Declarativa
Referire Referire
Gestionarea exceptiei captate
Sectiunea de Sectiunea de tratarea exceptiilortratarea exceptiilor
Limbajul PL/SQL
Alte exceptii Oracle
Tratarea exceptiei cu numarul -2992 pentru incalcarea unei constringeri de integritate:
DECLARE e_produs_invalid EXCEPTION; PRAGMA EXCEPTION_INIT (
e_produs_invalid, -2292); v_mesaj VARCHAR2(50);BEGIN. . .EXCEPTION WHEN e_produs_invalid THEN :g_mesaj := ‘Codul produsului
specificat nu este valid.';. . .END;
DECLARE e_produs_invalid EXCEPTION; PRAGMA EXCEPTION_INIT (
e_produs_invalid, -2292); v_mesaj VARCHAR2(50);BEGIN. . .EXCEPTION WHEN e_produs_invalid THEN :g_mesaj := ‘Codul produsului
specificat nu este valid.';. . .END;
e_produs_invalid EXCEPTION;PRAGMA EXCEPTION_INIT (
e_produs_invalid, -2292);
e_products_invalid
Limbajul PL/SQL
Exceptii utilizator
• Numirea exceptiei
DeclarareDeclarare
Sectiunea Sectiunea declarativadeclarativa
Ridicare Ridicare exceptieexceptie
• Apelarea explicita a exceptiei prin comanda RAISE
Sectiunea Sectiunea executabilaexecutabila
Referire Referire
• Gestionarea Gestionarea exceptiei exceptiei ridicateridicate
Sectiunea de Sectiunea de tratarea exceptiilortratarea exceptiilor
Limbajul PL/SQL
Exceptii utilizator
Exemplu:
[DECLARE] e_cant_ramasa EXCEPTION;. . .BEGIN. . . RAISE e_cant_ramasa;. . .EXCEPTION WHEN e_cant_ramasa THEN :g_mesaj := ‘Exista cantitate in stoc.';. . .END;
[DECLARE] e_cant_ramasa EXCEPTION;. . .BEGIN. . . RAISE e_cant_ramasa;. . .EXCEPTION WHEN e_cant_ramasa THEN :g_mesaj := ‘Exista cantitate in stoc.';. . .END;
e_cant_ramasa EXCEPTION;
RAISE e_cant_ramasa;
e_amount_remaining
Limbajul PL/SQL
Functii pentru exceptii
SQLCODE - retuneaza valoarea numerica corespunzatoare unui cod numeric
SQLERRM - returneaza mesajul de eroare asociat unui cod de eroare
Limbajul PL/SQL
Functii pentru exceptii
Exemplu:DECLARE v_cod_eroare NUMBER; v_mesaj_eroare VARCHAR2(255);BEGIN...EXCEPTION... WHEN OTHERS THEN ROLLBACK; v_cod_eroare := SQLCODE ; v_mesaj_eroare := SQLERRM ;
INSERT INTO erori VALUES(v_cod_eroare, v_mesaj_eroare);
END;
SQLCODESQLERRM
Limbajul PL/SQL
Propagarea exceptiilor
BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF;EXCEPTION WHEN e_integrity THEN ... WHEN e_no_rows THEN ...END;
DECLARE . . . e_no_rows exception; e_integrity exception; PRAGMA EXCEPTION_INIT (e_integrity, -2292);BEGIN FOR c_record IN ang_cursor LOOP
END LOOP;EXCEPTION WHEN NO_DATA_FOUND THEN . . . WHEN TOO_MANY_ROWS THEN . . .END;
Blocurile incluse pot
gestiona exceptiile sau le pot transmite blocului extern.
BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF;EXCEPTION WHEN e_integrity THEN ... WHEN e_no_rows THEN ...END;
Limbajul PL/SQL
RAISE_APPLICATION_ERROR
Sintaxa:
Procedura ce permite lansarea exceptiilor utilizator din programe stocate.
Poate fi folosita in sectiunea executabila sau de tratare a erorilor dintr-un bloc PL/SQL.
Intoarce exceptii intr-un mod consistent cu celelalte erori Oracle.
raise_application_error (error_number,message[, {TRUE | FALSE}]);
raise_application_error (error_number,message[, {TRUE | FALSE}]);
Limbajul PL/SQL
Proceduri PL/SQL
O procedura este un bloc PL/SQL cu nume care executa o actiune.
Tipuri de proceduri:– stocate pe server– stocate pe client
O procedura poate fi stocata in baza de date, ca un obiect al bazei de date, pentru executii repetate.
Procedura poate fi creata pe partea de client folosind un instrument specific (Developer).
Limbajul PL/SQL
Dezvoltarea procedurilor stocate
FisierFisierTextText
Cod sursaCod sursa
p-codep-code
EditareEditare
Stocare in baza de date
Compilare
Executie
Editor de Editor de textetexte
OracleOracleProcedure Procedure
BuilderBuilder
Limbajul PL/SQL
Crearea procedurilor stocate
Sintaxa:
CREATE [OR REPLACE] PROCEDURE nume_procedura (argument1 [mod1] tipdate1, argument2 [mod2] tipdate2, …) . . .IS [AS]Bloc PL/SQL;
CREATE [OR REPLACE] PROCEDURE nume_procedura (argument1 [mod1] tipdate1, argument2 [mod2] tipdate2, …) . . .IS [AS]Bloc PL/SQL;
Limbajul PL/SQL
Crearea procedurilor din mediul SQL*Plus
Scrierea codului procedurii intr-un editor de texte asociat si se salveaza intr-un fisier script.
Se ruleaza fisierul script din mediul SQL*Plus pentru a compila codul sursa si pentru a stoca procedura in baza de date.
Se apeleaza procedura dintr-un mediu ce interactioneaza cu serverul de date pentru a vedea daca aceasta se executa corect (Ex. SQL*Plus: EXECUTE nume_proc(arg1,…)
Limbajul PL/SQL
Modul de transmitere a parametrilor
Mediul Mediul apelantapelant
ProceduraProcedura
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
parametru INparametru IN
parametru OUTparametru OUT
parametru IN OUTparametru IN OUT
Limbajul PL/SQL
Modul de transmitere a parametrilor
IN OUT
Trebuie specificat
Este transmisa in subprogram; este returnat mediului apelant
Variabila initializata
Trebuie sa fie o variabila
OUT
Trebuie specificat
Este returnat mediului apelant
Variabila neinitializata
Trebuie sa fie o variabila
IN
Predefinit
Valoarea este
pasata insubprogram
Parametrul formal actioneaza ca o constanta
Parametrul actualpoate fi o expresie, constanta, variabila initializata
Limbajul PL/SQL
Parametri tip IN: Exemplu
SQL> CREATE OR REPLACE PROCEDURE marire_salar 2 (v_id in angajat.marca%TYPE) 3 IS 4 BEGIN 5 UPDATE angajat 6 SET sal = sal * 1.10 7 WHERE marca = v_id; 8 END marire_salar; 9 /Procedure created.Procedure created.
SQL> EXECUTE marire_salar(7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE marire_salar 2 (v_id in angajat.marca%TYPE) 3 IS 4 BEGIN 5 UPDATE angajat 6 SET sal = sal * 1.10 7 WHERE marca = v_id; 8 END marire_salar; 9 /Procedure created.Procedure created.
SQL> EXECUTE marire_salar(7369)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
v_idv_id73697369
Limbajul PL/SQL
Parametri tip OUT: Exemplu
Mediul apelantMediul apelant Procedura QUERY_ANGProcedura QUERY_ANG
76547654 v_marcav_marca
v_numev_nume
v_salarv_salar
v_ comv_ com
MARTINMARTIN
12501250
14001400
Limbajul PL/SQL
Parametri tip OUT: Exemplu
SQL> CREATE OR REPLACE PROCEDURE query_ang 1 (v_marca IN angajat.marca%TYPE, 2 v_nume OUT angajat.nume%TYPE, 3 v_salar OUT angajat.sal%TYPE, 4 v_com OUT angajat.com%TYPE) 5 IS 6 BEGIN 7 SELECT nume, sal, com 8 INTO v_nume, v_salar, v_com 9 FROM angajat 10 WHERE marca = v_marca; 11 END query_ang; 12 /
SQL> CREATE OR REPLACE PROCEDURE query_ang 1 (v_marca IN angajat.marca%TYPE, 2 v_nume OUT angajat.nume%TYPE, 3 v_salar OUT angajat.sal%TYPE, 4 v_com OUT angajat.com%TYPE) 5 IS 6 BEGIN 7 SELECT nume, sal, com 8 INTO v_nume, v_salar, v_com 9 FROM angajat 10 WHERE marca = v_marca; 11 END query_ang; 12 /
Limbajul PL/SQL
Apelare din SQL*Plus
SQL> START ang_query.sqlProcedure created.Procedure created.
SQL> START ang_query.sqlProcedure created.Procedure created.
SQL> VARIABLE g_nume varchar2(15)SQL> VARIABLE g_salar numberSQL> VARIABLE g_com number
SQL> VARIABLE g_nume varchar2(15)SQL> VARIABLE g_salar numberSQL> VARIABLE g_com number
SQL> EXECUTE query_ang (7654, :g_nume, :g_salar, 2 :g_com)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE query_ang (7654, :g_nume, :g_salar, 2 :g_com)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_numeG_NUME---------------MARTIN
Limbajul PL/SQL
Parametri tip IN OUT: Exemplu
SQL> CREATE OR REPLACE PROCEDURE format_telefon 2 (v_nrtelef IN OUT VARCHAR2) 3 IS 4 BEGIN 5 v_nrtelef := '(' || SUBSTR(v_nrtelef,1,3) || 6 ')' || SUBSTR(v_nrtelef,4,3) || 7 '-' || SUBSTR(v_nrtelef,7); 8 END format_telefon; 9 /
Mediul apelantMediul apelant Procedura FORMAT_TELEFONProcedura FORMAT_TELEFON
v_nrtelefv_nrtelef'(800)633-0575' '(800)633-0575' '8006330575' '8006330575'
Limbajul PL/SQL
Apelare din SQL*Plus
SQL>VARIABLE g_nrtelef varchar2(15)
SQL> BEGIN :g_nrtelef := '8006330575'; END; 2 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE format_telefon (:g_nrtelef)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_nrtelef
SQL>VARIABLE g_nrtelef varchar2(15)
SQL> BEGIN :g_nrtelef := '8006330575'; END; 2 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE format_telefon (:g_nrtelef)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_nrtelef
G_NRTELEF---------------(800)633-0575
Limbajul PL/SQL
Transmiterea parametrilor
Exista trei modalitati de transmiterea parametrilor:
– Pozitional (implicit) - pozitia parametrilor formali trebuie sa corespunda cu pozitia parametrilor actuali
– Dupa nume - la apelare valoarea argumentului actual este prefixata de numele parametrului formal
– Combinat - pozitional sau dupa nume
Limbajul PL/SQL
Apelarea procedurilor
Crearea procedurii ADD_DEPART :
SQL> CREATE OR REPLACE PROCEDURE add_depart 1 (v_den IN depart.den%TYPE DEFAULT ‘?', 2 v_loc IN depart.loc%TYPE DEFAULT ‘?') 3 IS 4 BEGIN 5 INSERT INTO depart 6 VALUES (dept_nrdept.NEXTVAL, v_den, v_loc); 7 END add_depart; 8 /
SQL> CREATE OR REPLACE PROCEDURE add_depart 1 (v_den IN depart.den%TYPE DEFAULT ‘?', 2 v_loc IN depart.loc%TYPE DEFAULT ‘?') 3 IS 4 BEGIN 5 INSERT INTO depart 6 VALUES (dept_nrdept.NEXTVAL, v_den, v_loc); 7 END add_depart; 8 /
Limbajul PL/SQL
Apelarea procedurilor
SQL> begin 2 add_depart; 3 add_depart ( 'TRAINING', 'NEW YORK'); 4 add_depart ( v_loc => 'DALLAS', v_den =>
'EDUCATION') ; 5 add_depart ( v_loc => 'BOSTON') ; 6 end; 7 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> begin 2 add_depart; 3 add_depart ( 'TRAINING', 'NEW YORK'); 4 add_depart ( v_loc => 'DALLAS', v_den =>
'EDUCATION') ; 5 add_depart ( v_loc => 'BOSTON') ; 6 end; 7 /PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> SELECT * FROM depart;
NRDEPT DENUMIRE LOCALIT------ -------------- ------------- ... ... ... 41 unknown unknown 42 TRAINING NEW YORK 43 EDUCATION DALLAS 44 unknown BOSTON
Limbajul PL/SQL
Apelarea procedurilor
Exemplu de apelare dintr-un bloc anonim:
DECLARE v_id NUMBER := 7900;BEGIN marire_salar(v_id); --apelarea proceduriiCOMMIT;...
END;
DECLARE v_id NUMBER := 7900;BEGIN marire_salar(v_id); --apelarea proceduriiCOMMIT;...
END;
Limbajul PL/SQL
Apelarea procedurilor
Exemplu de apelare dintr-o procedura:
SQL> CREATE OR REPLACE PROCEDURE procesare_ang 2 IS 3 CURSOR ang_cursor IS 4 SELECT marca 5 FROM ang; 6 BEGIN 7 FOR ang_rec IN ang_cursor LOOP 8 marire_salar(ang_rec.marca); --apelare procedure 9 END LOOP; 10 COMMIT; 11 END procesare_ang; 12 /
SQL> CREATE OR REPLACE PROCEDURE procesare_ang 2 IS 3 CURSOR ang_cursor IS 4 SELECT marca 5 FROM ang; 6 BEGIN 7 FOR ang_rec IN ang_cursor LOOP 8 marire_salar(ang_rec.marca); --apelare procedure 9 END LOOP; 10 COMMIT; 11 END procesare_ang; 12 /
Limbajul PL/SQL
Stergerea procedurilor
Procedurile stocate pot fi sterse de pe server. Sintaxa:
Exemplu:
Pot fi sterse si procedurile de la nivel de client.
DROP PROCEDURE nume_procedureDROP PROCEDURE nume_procedure
SQL> DROP PROCEDURE marire_salar;Procedure dropped.
SQL> DROP PROCEDURE marire_salar;Procedure dropped.
Limbajul PL/SQL
Functii PL/SQL
Functia este blocul PL/SQL cu nume care intoarce o valoare.
O functie poate fi stocata in baza de date ca un obiect stocat si executata in mod repetat.
O functie poate fi apelata ca o parte a unei expresii.
Limbajul PL/SQL
Crearea functiilor
Sintaxa:
CREATE [OR REPLACE] FUNCTION nume_functie (argument1 [mode1] tipdate1, argument2 [mode2] tipdate2, . . .RETURN tipdateIS|ASBloc PL/SQL;
CREATE [OR REPLACE] FUNCTION nume_functie (argument1 [mode1] tipdate1, argument2 [mode2] tipdate2, . . .RETURN tipdateIS|ASBloc PL/SQL;
Limbajul PL/SQL
Crearea functiilor din SQL*Plus
Scrierea codului functiei intr-un editor de texte asociat si se salveaza intr-un fisier script.
Se ruleaza fisierul script din mediul SQL*Plus pentru a compila codul sursa si pentru a stoca functia in baza de date.
Se apeleaza functia dintr-un mediu ce interactioneaza cu serverul de date pentru a vedea daca aceasta se executa corect.
Limbajul PL/SQL
Crearea functiilor din SQL*Plus
Exemplu:
SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_marca IN angajat.marca%TYPE) 3 RETURN NUMBER 4 IS 5 v_salar angajat.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salar 9 FROM angajat 10 WHERE marca = v_marca; 11 RETURN (v_salar); 12 END get_sal; 13 /
SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_marca IN angajat.marca%TYPE) 3 RETURN NUMBER 4 IS 5 v_salar angajat.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salar 9 FROM angajat 10 WHERE marca = v_marca; 11 RETURN (v_salar); 12 END get_sal; 13 /
Limbajul PL/SQL
Apelarea functiilor PL/SQL
Se poate apela o functie ca parte a unei expresii PL/SQL.
Se poate apela din instructiuni SQL ca orice functie SQL standard.
Apelare din mediul gazda: declararea unei varibile a mediului gazda
(varibila de legatura) asignarea valorii de return a functiei variabilei
de legatura
Limbajul PL/SQL
Apelarea functiilor in SQL*Plus
Mediul apelantMediul apelant Functia GET_SALFunctia GET_SAL
v_marcav_marca79347934
RETURN v_salarRETURN v_salar
SQL> START get_salar.sqlSTART get_salar.sqlProcedure createdProcedure created.
SQL> VARIABLE g_salar numberSQL> VARIABLE g_salar number
SQL> EXECUTE :g_salar := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> EXECUTE :g_salar := get_sal(7934)PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.
SQL> PRINT g_salar G_SALAR
------------------ 1300
SQL> PRINT g_salar G_SALAR
------------------ 1300
Limbajul PL/SQL
Avantajele functiilor in expresii SQL
Imbunatatirea comenzilor SQL pentru a executa actiuni complexe, acolo unde functiile standard nu sunt suficiente.
Imbunatatirea eficientei comenzilor SQL: functiile utilizate in clauza WHERE.
Gestionarea sirurilor de caractere. Imbunatatirea interogarilor paralele (optiunea
Parallel Server).
Limbajul PL/SQL
Functii in comenzi SQL
O functie utilizator poate sa apara intr-o comanda SQL in:
lista de selectie a comenzii SELECT conditiile din clauzele WHERE sau HAVING a
comenzii SELECT clauzele GROUP BY, ORDER BY a comenzii
SELECT clauza VALUES a comenzii INSERT clauza SET a comenzii UPDATE
Limbajul PL/SQL
Restrictiile functiilor utilizator in comenzi SQL
O functie utilizator trebuie sa fie stocata. Functia utilizator trebuie sa fie de rind si nu de
grup. O functie utilizator accepta numai argumente
de tip IN. Tipurile de date ale argumentelor si de return
sunt caracter, numeric si data calendaristica. Comenzile DML nu sunt permise intr-o functie. Nu sunt permise apeluri la programe care
incalca regulile de mai sus.
Limbajul PL/SQL
Stergerea functiilor stocate
Sintaxa
Exemplu:
SQL> DROP FUNCTION get_sal;Function dropped.
SQL> DROP FUNCTION get_sal;Function dropped.
DROP FUNCTION function_nameDROP FUNCTION function_name
Limbajul PL/SQL
Procedura sau functie ?
ProceduraProcedura
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
argument INargument IN
argument OUTargument OUT
argument IN OUTargument IN OUT
Mediul Mediul apelantapelant
Mediul Mediul apelantapelant
FunctionFunction
(DECLARE)(DECLARE)
BEGINBEGIN
EXCEPTIONEXCEPTION
END;END;
argument INargument IN
Limbajul PL/SQL
Procedura sau functie ?
Comparatie intre procedura si functie:
Procedura
Se executa ca o instructiune PL/SQL
Nu contine sintaxa RETUN tipdate
Poate returna zero,una sau mai multe valori
Functia
Apelata ca parte a unei expresii
Contine sintaxa RETURN tipdate
Trebuie sa intoarca o valoare
Limbajul PL/SQL
Pachete in PL/SQL
Un pachet grupeaza elemente similare logic cum ar fi cursoare, variabile, tipuri PL/SQL, proceduri si functii.
Un pachet are doua parti:– interfata sau specificatiile– corpul sau implementarea
Un pachet nu poate fi apelat, parametrizat sau imbricat.
Permite serverului Oracle sa incarce mai multe obiecte simultan in memorie.
Limbajul PL/SQL
Avantajele pachetelor PL/SQL
Modularitate Imbunatatirea proiectarii aplicatiilor Ascunderea implementarii Imbunatatirea functionalitatii Imbunatatirea performantei Supraincarcare
Limbajul PL/SQL
Structura unui pachet
Declaratia procedurii ADeclaratia procedurii A
Definirea procedurii BDefinirea procedurii B
Interfata pachetului
Corpul pachetului
1
2
3
4
2Definirea procedurii A
5
Limbajul PL/SQL
Dezvoltarea unui pachet
FisierFisierTextText
Cod sursaCod sursa
p-codep-code
EditareEditare
Stocare in baza de date
Compilare
Executie
Editor de Editor de textetexte
OracleOracleProcedure Procedure
BuilderBuilder
Limbajul PL/SQL
Crearea unui pachet in SQL*Plus
Scrierea codului interfetei si corpului pachetului intr-un editor de texte asociat si se salveaza intr-un fisier script.
Se ruleaza fisierul script din mediul SQL*Plus pentru a compila codul sursa si pentru a stoca pachetul in baza de date.
O interfata a unui pachet poate exista fara corp dar nu si invers.
Se apeleaza elementele din interfata dintr-un mediu ce interactioneaza cu serverul de date.
Limbajul PL/SQL
Crearea pachetului
Sintaxa creare interfata pachet:
Sintaxa creare corp pachet:
CREATE [OR REPLACE] PACKAGE nume_pachetIS | AS declarare obiect public (tip de date, variabila) declarare procedura sau functieEND nume_pachet;
CREATE [OR REPLACE] PACKAGE nume_pachetIS | AS declarare obiect public (tip de date, variabila) declarare procedura sau functieEND nume_pachet;
CREATE [OR REPLACE] PACKAGE BODY nume_pachetIS | AS declarare obiect privat (tip de date, variabila) corpurile procedurilor si functiilorEND nume_pachet;
CREATE [OR REPLACE] PACKAGE BODY nume_pachetIS | AS declarare obiect privat (tip de date, variabila) corpurile procedurilor si functiilorEND nume_pachet;
Limbajul PL/SQL
Interfata pachet
Pachetul COM_PACKAGE
G_COMG_COM
RESET_COMRESET_COMdeclararea proceduriideclararea procedurii
Interfata pachetului
1
2
Limbajul PL/SQL
Interfata pachet
Exemplu creare interfata pachet:
Exemplu apelare elemente pachete:
SQL>CREATE OR REPLACE PACKAGE com_package IS
2 g_com NUMBER := 10;
3 PROCEDURE reset_com
4 (v_com IN NUMBER);
5 END com_package;
6 /
SQL>EXECUTE com_package.g_com := 5
SQL>EXECUTE com_package.reset_com(8)
Limbajul PL/SQL
Creare pachet
G_COMG_COM
RESET_COMRESET_COMdeclararea proceduriideclararea procedurii
VALIDARE_COMVALIDARE_COMdefinirea functieidefinirea functiei
Interfata pachet
Corp pachet
1
2
3
2RESET_COMRESET_COMdefinirea proceduriidefinirea procedurii
Pachetul COM_PACKAGE
Limbajul PL/SQL
Creare corp pachet
SQL>CREATE OR REPLACE PACKAGE BODY com_package IS 2 FUNCTION validare_comm 3 (v_com IN NUMBER) RETURN BOOLEAN 4 IS 5 v_max_com NUMBER; 6 BEGIN 7 SELECT MAX(com) 8 INTO v_max_com 9 FROM angajat; 10 IF v_com > v_max_com THEN RETURN(FALSE); 11 ELSE RETURN(TRUE); 12 END IF; 13 END validare_com; 14 END com_package; 15 /
Limbajul PL/SQL
Creare corp pachet
SQL>PROCEDURE reset_com 2 (v_com IN NUMBER) 3 IS 4 v_valid BOOLEAN; 5 BEGIN 6 v_valid := validare_com(v_com); 7 IF v_valid = TRUE THEN 8 g_com := v_com; 9 ELSE 10 RAISE_APPLICATION_ERROR 11 (-20210,‘Comision invalid'); 12 END IF; 13 END reset_com; 14 END com_package; 15 /
Limbajul PL/SQL
Apelare elemente pachet
Exemplu apelare functie dintr-o procedura in cadrul aceluiasi pachet:
CREATE OR REPLACE PACKAGE BODY com_package IS. . .
PROCEDURE reset_comm(v_comm IN NUMBER)IS v_valid BOOLEAN;BEGIN v_valid := validare_com(v_com); IF v_valid = TRUE THEN
g_com := v_com; ELSERAISE_APPLICATION_ERROR (-20210, ‘Comision invalid'); END IF;END reset_com;
END com_package;
Limbajul PL/SQL
Apelare elemente pachet
Apelarea unei proceduri dintr-un pachet in SQL*Plus:
Apelarea unei proceduri dintr-un pachet aflat intr-o schema diferita:
Apelarea unei proceduri impachetate dintr-o baza de date la distanta:
SQL> EXECUTE com_package.reset_com(1500);
SQL> EXECUTE scott.com_package.reset_com(1500);
SQL> EXECUTE com_package.reset_com@ny (1500);
Limbajul PL/SQL
Apelare elemente pachet
Apelare variabila globala in cadrul unei proceduri din acelasi pachet:CREATE OR REPLACE PACKAGE BODY com_package IS
. . .PROCEDURE reset_com(v_com IN NUMBER)IS
v_valid BOOLEAN;BEGIN
v_valid := validare_com(v_com);IF v_valid = TRUE THEN
g_com := v_com;ELSERAISE_APPLICATION_ERROR (-20210,‘Comision invalid');END IF;
END reset_com;END com_package;
Limbajul PL/SQL
Apelare elemente pachet
Apelare variabila globala dintr-o procedura standalone:
CREATE OR REPLACE PROCEDURE angajare (v_nume IN angajat.nume%TYPE, v_sef IN angajat.sef%TYPE, v_functie IN angajat.functie%TYPE, v_sal IN angajat.sal%TYPE)IS v_com angajat.com%TYPE;. . .BEGIN. . .
v_com := com_package.g_com;. . .END angajare;
Limbajul PL/SQL
Stergere pachet
Sintaxa:
Exemplu:
DROP PACKAGE package_nameDROP PACKAGE package_name
DROP PACKAGE BODY package_name DROP PACKAGE BODY package_name
Limbajul PL/SQL
Supraincarcare in PL/SQL
Permite folosirea aceluiasi nume pentru diferite subprograme in cadrul aceluiasi pachet.
Parametrii formali ai subprogramelor trebuie sa difere ca numar, ordine sau tip de date.
Limbajul PL/SQL
Supraincarcare
Initializarea a doua tabele PL/SQL:
PROCEDURE initialize(tab OUT datetabtyp, n INTEGER) ISBEGIN FOR i in 1..n LOOP tab(i) := SYSDATE; END LOOP;END initialize;
PROCEDURE initialize(tab OUT realtabtyp, n INTEGER) ISBEGIN FOR i in 1..n LOOP tab(i) := 1000; END LOOP;END initialize;
Limbajul PL/SQL
Declaratii anticipate
Apelarea unei functii definite ulterior:
. . . PROCEDURE bonus(. . .) IS BEGIN calcul_bonus(. . .); --apelare ilegala . . . END; PROCEDURE calcul_bonus(. . .) IS BEGIN . . . END;. . .
Limbajul PL/SQL
Declaratii anticipate
. . .
PROCEDURE calcul_bonus(. . .); -- declarare anticipata
. . .
PROCEDURE bonus(. . .) IS -- definire procedura
BEGIN
calcul_bonus(. . .); -- apelare procedura
. . .
END;
PROCEDURE calcul_bonus(. . .) IS -- definire procedura
BEGIN
. . .
END;
. . .
Limbajul PL/SQL
Proceduri ‘one-time-only’
CREATE OR REPLACE PACKAGE BODY com_package IS FUNCTION validare_com . . . END validare_com;
PROCEDURE reset_com . . . END reset_com;
BEGIN SELECT AVG(com) INTO g_com FROM angajat;END com_package;
Limbajul PL/SQL
Reguli de folosire a functiilor din pachete
Instructiunile DML nu sunt permise. Numai functiile locale pot actualiza variabilele
pachetului. Functiile care actualizeaza variabilele
pachetului nu pot fi folosite la interogari paralele.
Nu sunt permise apeluri la subprograme care incalca aceste reguli.
Limbajul PL/SQL
Nivelul de puritate al unei functii
WNDS - nu poate scrie date in baza de date WNPS - nu poate actualiza variabile din pachet RNDS - nu poate citi date din baza de date RNPS - nu poate citi variabile din pachet
PRAGMA RESTRICT_REFERENCES (function_name,
WNDS
[,WNPS]
[,RNDS]
[,RNPS] );
Limbajul PL/SQL
PRAGMA RESTRICT_REFERENCES
CREATE OR REPLACE PACKAGE finance as -- interfata pachet
interest REAL; -- variabila publica . . .
FUNCTION compound (years IN NUMBER, amountIN NUMBER, rate IN NUMBER)
RETURN NUMBER; . . .
PRAGMA RESTRICT_REFERENCES (compound, WNDS, RNPS, WNPS);END finance;CREATE OR REPLACE PACKAGE BODY finance AS -- corp pachet
FUNCTION compound (years IN NUMBER, amountIN NUMBER, rate IN NUMBER) RETURN NUMBER
ISBEGIN
RETURN (amount * POWER ((rate/100) + 1, years));END compound;
END finance;
Limbajul PL/SQL
Apelare functie
Exemplu apelare functie impachetata in cadrul unui bloc anonim PL/SQL:
DECLAREinterest NUMBER
BEGIN. . . SELECT finance.compound(yrs, amt, rte) -- apel functie INTO interest FROM accounts WHERE acctno = acct_id;. . .END;
Limbajul PL/SQL
Pachete furnizate de serverul Oracle
DBMS_ALERT DBMS_APPLICATION_INFO DBMS_DDL DBMS_DESCRIBE DBMS_MAIL DBMS_OUTPUT DBMS_SQL DBMS_PIPE DBMS_UTILITY
Limbajul PL/SQL
Declansatori (‘Trigger’)
Un declansator este un bloc PL/SQL care se executa implicit atunci cind apare un eveniment.
Un declansator poate fi la nivel de baza de date sau de aplicatie (Developer/2000).
Limbajul PL/SQL
Obiectivele unui trigger
Triggerul unei baze de date se declanseaza automat la executia unei comenzi DML.
Executa actiuni corelate logic. Sunt creati pentru operatii globale. Nu sunt necesari pentru operatii implementate
de alte mecanisme ale serverului Oracle (constringeri de integritate, tip ‘check’).
Daca nu sunt proiectati corespunzator pot duce la inrautatirea performantelor bazei de date sau la actiuni imprevizibile.
Limbajul PL/SQL
Exemplu trigger
AplicatieAplicatie
SQL> INSERT INTO ANGAJAT 2 . . .;
SQL> INSERT INTO ANGAJAT 2 . . .;
Tabela ANGAJAT MARCA
7838
7698
7369
7788
NUME
KING
BLAKE
SMITH
SCOTT
FUNCTIE
PRESIDENT
MANAGER
CLERK
ANALYST
SAL
5000
2850
800
3000
Trigger VERIF_SAL
Limbajul PL/SQL
Crearea triggerilor
Sintaxa:
CREATE [OR REPLACE] TRIGGER nume_triggertiming eveniment1 [OR eveniment2 OR eveniment3]ON nume_tabela Bloc PL/SQL;
CREATE [OR REPLACE] TRIGGER nume_triggertiming eveniment1 [OR eveniment2 OR eveniment3]ON nume_tabela Bloc PL/SQL;
Limbajul PL/SQL
Componente trigger
Timing: BEFORE sau AFTER - momentul cind se declanseaza triggerul fata de comanda DML
Evenimentul de declansare: INSERT, UPDATE sau DELETE.
Numele tabelei pentru care se creaza triggerul. Tipul triggerului: linie sau instructiune. Clauza WHEN: conditii suplimentare Corpul triggerului care are structura unui bloc
PL/SQL anonim.
Limbajul PL/SQL
Componente trigger
Momentul cind se declanseaza triggerul: BEFORE - corpul triggerului se executa inainte
ca instructiunea DML ce il declanseaza sa se execute.
AFTER - corpul triggerului se executa dupa ce instructiunea DML ce il declanseaza se executa
INSTEAD OF - codul triggerului se va executa in locul comenzii DML ce il declanseaza (util pentru vizualizari).
Limbajul PL/SQL
Componente trigger
Evenimentul care declanseaza triggerul:– INSERT, UPDATE, DELETE– Orice combinatie a celor trei instructiuni
Tipul triggerului:– la nivel de instructiune - codul se executa o data pentru
evenimentul declansator; acesta este tipul predefinit.– la nivel de instructiune: codul se executa o data pentru fiecare
linie afectata de operatia DML care a declansat triggerul
Corpul triggerului:– bloc PL/SQL anonim ce se executa la activarea triggerului
Limbajul PL/SQL
Trigger de tip instructiune si de tip linie
Exemplu 1:
Exemplu 2:
SQL> INSERT INTO depart (nrdept, denumire, localit) 2 VALUES (50, 'EDUCATION', 'NEW YORK');
SQL> INSERT INTO depart (nrdept, denumire, localit) 2 VALUES (50, 'EDUCATION', 'NEW YORK');
SQL> UPDATE angajat 2 SET sal = sal * 1.1 3 WHERE nrdept = 30;
SQL> UPDATE angajat 2 SET sal = sal * 1.1 3 WHERE nrdept = 30;
Limbajul PL/SQL
Lansare triggeri
Ordinea de executie a triggerilor:
MARCAMARCA
78397839
76987698
77887788
NUMENUME
KINGKING
BLAKEBLAKE
SMITHSMITH
NRDEPTNRDEPT
3030
3030
3030
BEFORE statement triggerBEFORE statement trigger
BEFORE row triggerBEFORE row triggerAFTER row triggerAFTER row triggerBEFORE row triggerBEFORE row triggerAFTER row triggerAFTER row triggerBEFORE row triggerBEFORE row triggerAFTER row triggerAFTER row trigger
AFTER statement triggerAFTER statement trigger
Limbajul PL/SQL
Trigger tip BEFORE
Exemplu:
SQL> CREATE OR REPLACE TRIGGER secure_ang
2 BEFORE INSERT ON angajat
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))
5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN
6 '08' AND '18'
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 ’Se pot adauga valori in tabelul EMP
numai in timpul programului de lucru !');
10 END IF;
11 END;
12 /
SQL> CREATE OR REPLACE TRIGGER secure_ang
2 BEFORE INSERT ON angajat
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))
5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN
6 '08' AND '18'
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 ’Se pot adauga valori in tabelul EMP
numai in timpul programului de lucru !');
10 END IF;
11 END;
12 /
Limbajul PL/SQL
Apelare trigger
Exemplu:
SQL> INSERT INTO angajat (marca, nume, nrdept)
2 VALUES (7777, 'BAUWENS', 40);
INSERT INTO angajat (marca, nume, nrdept)
*
ERROR at line 1:
ORA-20500: Se pot adauga valori in tabelul EMP
numai in timpul programului de lucru !
ORA-06512: at "SCOTT.SECURE_EMP", line 4
ORA-04088: error during execution of trigger
'SCOTT.SECURE_ANG'
SQL> INSERT INTO angajat (marca, nume, nrdept)
2 VALUES (7777, 'BAUWENS', 40);
INSERT INTO angajat (marca, nume, nrdept)
*
ERROR at line 1:
ORA-20500: Se pot adauga valori in tabelul EMP
numai in timpul programului de lucru !
ORA-06512: at "SCOTT.SECURE_EMP", line 4
ORA-04088: error during execution of trigger
'SCOTT.SECURE_ANG'
Limbajul PL/SQL
Predicate conditionaleSQL>CREATE OR REPLACE TRIGGER secure_ang 2 BEFORE INSERT OR UPDATE OR DELETE ON angajat 3 BEGIN 4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR 5 (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') THEN 6 IF DELETINGDELETING THEN 7 RAISE_APPLICATION_ERROR (-20502, 8 ‘Se pot sterge angajati numai in orele de lucru.'); 9 ELSIF INSERTINGINSERTING THEN 10 RAISE_APPLICATION_ERROR (-20500, 11 'Se pot adauga angajati numai in orele de lucru.'); 12 ELSIF UPDATING ('SAL')UPDATING ('SAL') THEN 13 RAISE_APPLICATION_ERROR (-20503, 14 ‘Se poate modifica salarul numai in orele de lucru.'); 15 ELSE 16 RAISE_APPLICATION_ERROR (-20504, 17‘Se pot modifica datele despre angajati numai in orele de lucru.'); 18 END IF; 19 END IF; 20 END; 21 /
SQL>CREATE OR REPLACE TRIGGER secure_ang 2 BEFORE INSERT OR UPDATE OR DELETE ON angajat 3 BEGIN 4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR 5 (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') THEN 6 IF DELETINGDELETING THEN 7 RAISE_APPLICATION_ERROR (-20502, 8 ‘Se pot sterge angajati numai in orele de lucru.'); 9 ELSIF INSERTINGINSERTING THEN 10 RAISE_APPLICATION_ERROR (-20500, 11 'Se pot adauga angajati numai in orele de lucru.'); 12 ELSIF UPDATING ('SAL')UPDATING ('SAL') THEN 13 RAISE_APPLICATION_ERROR (-20503, 14 ‘Se poate modifica salarul numai in orele de lucru.'); 15 ELSE 16 RAISE_APPLICATION_ERROR (-20504, 17‘Se pot modifica datele despre angajati numai in orele de lucru.'); 18 END IF; 19 END IF; 20 END; 21 /
Limbajul PL/SQL
Creare trigger la nivel de linie
Sintaxa:
CREATE [OR REPLACE] TRIGGER nume_triggertiming eveniment1 [OR eveniment2 OR eveniment3]ON nume_tabel [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN conditie]Bloc PL/SQL;
CREATE [OR REPLACE] TRIGGER nume_triggertiming eveniment1 [OR eveniment2 OR eveniment3]ON nume_tabel [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN conditie]Bloc PL/SQL;
Limbajul PL/SQL
Exemplu trigger la nivel de linie
SQL>CREATE OR REPLACE TRIGGER audit_ang 2 AFTER DELETE OR INSERT OR UPDATE ON angajat 3 FOR EACH ROW 4 BEGIN 5 IF DELETING THEN 6 UPDATE audit_table SET del = del + 1 7 WHERE user_name = user AND table_name = ‘ANGAJAT' 8 AND column_name IS NULL; 9 ELSIF INSERTING THEN 10 UPDATE audit_table SET ins = ins + 1 11 WHERE user_name = user AND table_name = ‘ANGAJAT' 12 AND column_name IS NULL; 13 ELSIF UPDATING ('SAL') THEN 14 UPDATE audit_table SET upd = upd + 1 15 WHERE user_name = user AND table_name = ‘ANGAJAT' 16 AND column_name = 'SAL'; 17 ELSE 18 UPDATE audit_table SET upd = upd + 1 19 WHERE user_name = user AND table_name = ‘ANGAJAT' 20 AND column_name IS NULL; 21 END IF; 22 END; 23 /
SQL>CREATE OR REPLACE TRIGGER audit_ang 2 AFTER DELETE OR INSERT OR UPDATE ON angajat 3 FOR EACH ROW 4 BEGIN 5 IF DELETING THEN 6 UPDATE audit_table SET del = del + 1 7 WHERE user_name = user AND table_name = ‘ANGAJAT' 8 AND column_name IS NULL; 9 ELSIF INSERTING THEN 10 UPDATE audit_table SET ins = ins + 1 11 WHERE user_name = user AND table_name = ‘ANGAJAT' 12 AND column_name IS NULL; 13 ELSIF UPDATING ('SAL') THEN 14 UPDATE audit_table SET upd = upd + 1 15 WHERE user_name = user AND table_name = ‘ANGAJAT' 16 AND column_name = 'SAL'; 17 ELSE 18 UPDATE audit_table SET upd = upd + 1 19 WHERE user_name = user AND table_name = ‘ANGAJAT' 20 AND column_name IS NULL; 21 END IF; 22 END; 23 /
Limbajul PL/SQL
Exemplu trigger la nivel de linie
Folosirea optiunilor NEW si OLD:
SQL>CREATE OR REPLACE TRIGGER audit_ang_values
2 AFTER DELETE OR INSERT OR UPDATE ON angajat
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_ang_values (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :old.marca,:old.nume,
9 :new.enume, :old.functie, :new.functie,
10 :old.sal, :new.sal);
11 END;
12 /
SQL>CREATE OR REPLACE TRIGGER audit_ang_values
2 AFTER DELETE OR INSERT OR UPDATE ON angajat
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO audit_ang_values (user_name,
6 timestamp, id, old_last_name, new_last_name,
7 old_title, new_title, old_salary, new_salary)
8 VALUES (USER, SYSDATE, :old.marca,:old.nume,
9 :new.enume, :old.functie, :new.functie,
10 :old.sal, :new.sal);
11 END;
12 /
Limbajul PL/SQL
Trigger la nivel de linie restrictionat
SQL>CREATE OR REPLACE TRIGGER calcul_comision 2 BEFORE INSERT OR UPDATE OF sal ON angajat 3 FOR EACH ROW 4 WHEN (new.functie = 'SALESMAN') 5 BEGIN 6 IF INSERTING THEN :new.com := 0; 7 ELSE 8 IF :old.com IS NULL THEN 9 :new.com :=0; 10 ELSE 11 :new.com := :old.com * (:new.sal/:old.sal); 12 END IF; 13 END IF; 14 END; 15 /
SQL>CREATE OR REPLACE TRIGGER calcul_comision 2 BEFORE INSERT OR UPDATE OF sal ON angajat 3 FOR EACH ROW 4 WHEN (new.functie = 'SALESMAN') 5 BEGIN 6 IF INSERTING THEN :new.com := 0; 7 ELSE 8 IF :old.com IS NULL THEN 9 :new.com :=0; 10 ELSE 11 :new.com := :old.com * (:new.sal/:old.sal); 12 END IF; 13 END IF; 14 END; 15 /
Limbajul PL/SQL
Trigger si procedura
Comparatie intre trigger si procedura:
Trigger
CREATE TRIGGER
Dictionarul de date contine
sursa si p-codul
Se apeleaza implicit
Nu sunt permise COMMIT,
SAVEPOINT, ROLLBACK
Procedura
CREATE PROCEDURE
Dictionarul de date contine
sursa si p-codul
Se apeleaza explicit
Sunt permise COMMIT,
SAVEPOINT, ROLLBACK
Limbajul PL/SQL
Gestionare triggeri
Activarea sau dezactivarea unui trigger:
Activarea sau dezactivarea tuturor triggerilor:
Compilarea unui trigger:
Stergerea unui trigger:
ALTER TRIGGER trigger_name DISABLE | ENABLEALTER TRIGGER trigger_name DISABLE | ENABLE
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERSALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS
ALTER TRIGGER trigger_name COMPILEALTER TRIGGER trigger_name COMPILE
DROP TRIGGER trigger_name DROP TRIGGER trigger_name
Limbajul PL/SQL
Testare triggeri
Se testeaza operatiile DML care trebuie sau nu sa declanseze triggeri pe un tabel.
Se testeaza clauza WHEN. Se testeaza daca triggerul se declanseaza
dintr-o operatie simpla DML si printr-o procedura.
Se testeaza efectul triggerului declansat de un alt trigger.
Limbajul PL/SQL
Model executie triggeri
1. Se executa triggerii la nivel de instructiune de tip BEFORE.
2. Pentru fiecare linie afectata:– 2.1.Se executa triggerii de tip BEFORE de la nivel de linie– 2.2.Se executa operatiile DML si se verifica constringerile – 2.3.Se executa triggerii de tip AFTER de la nivel de linie
3. Se verifica constringerile de tip intirziat 4.Se executa triggerii la nivel de instructiune de
tip AFTER.
Limbajul PL/SQL
Reguli triggeri
Regula 1 : Nu se schimba valoarea intr-o coloana cheie primara, straina sau unica intr-o tabela supusa la constringeri.
Regula 2: Nu se citesc date intr-o tabela aflata in modificare (‘mutating table’).
Limbajul PL/SQL
Utilizare triggeri
Securitate Auditare si monitorizare Integritatea datelor Integritatea referentiala Replicarea datelor Coloane derivate (calculate) Monitorizarea evenimentelor
Limbajul PL/SQL
Concluzii
ProceduraProcedura PachetPachet TriggerTrigger
xxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxx
Procedura AProcedura Adeclaratiedeclaratie
Procedura BProcedura Bdefinitiedefinitie
Procedura AProcedura Adefinitiedefinitie
Variabile Variabile localelocale
Limbajul PL/SQL
Concluzii
Tratarea exceptiilor in PL/SQL:– exceptii predefinite Oracle– exceptii ne-predefinite Oracle– exceptii utilizator
Proceduri stocate Functii stocate Pachete stocate:
– Interfata (specificatii)– Corp (implementare)
Limbajul PL/SQL
Concluzii
Triggeri pentru baze de date. Operatie declansatoare: INSERT, UPDATE,
DELETE. Tip trigger:
– BEFORE– AFTER– INSTEAD OF
Tip trigger:– instructiune (‘statement’)– rind (‘row’)