--- title: "PLSQL : Cours 1 : Procédure, Fonctions, Triggers" ... \newpage{} # PLSQL : Cours 1 : Procédure, Fonctions, Triggers ## Bloc PLSQL ```sql DECLARE ... BEGIN ... EXCEPTION ... END; ``` ## Déclarer une variable ```sql DECLARE identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initvalueexpr] ``` Exemples : ```sql DECLARE hiredate DATE; deptno NUMBER(2) NOT NULL := 10; location VARCHAR(13) := 'Paris'; comm CONSTANT NUMBER := 1400 ``` ## Assigner des valeurs ```sql identifier := expr; hiredate := '2020-01-01' ``` ## Types de données - VARCHAR2(maxlength) - NUMBER[(presicion,scale)] - DATE - CHAR[(maxlength)] - LONG - LONG RAW - BOOLEAN - BINARY_INTEGER On peut également affecter dynamiquement le type d'une autre variable ou d'une colonne de la base avec l'attributs `%TYPE` : ```sql DECLARE name EMP.NAME%Type; ``` On peut également récupérer un type Tuple depuis une table : ```sql DECLARE dept_record DEPT.%ROWTYPE; ``` > Le fonctions SQL (sauf fonctions d'agrégation sont valides en PLSQL) ## Interagir avec la base de donnée ### Récupérer des données ```sql DECLARE v_deptno NUMBER(2); v_loc VARCHAR(15); BEGIN SELECT deptno, loc INTO v_deptno,v_loc FROM dept WHERE dname = 'SALES': ``` ```sql DECLARE v_sum_salary EMP.SAL%TYPE; v_deptno NUMBER NOT NULL := 10; BEGIN SELECT SUM(sal) INTO v_sum_salary FROM EMP WHERE deptno = v_deptno ``` ### Manipuler des données On peut `INSERT` / `UPDATE` / `DELETE` des données : ```sql DECLARE v_empno EMP.EMPBO%TYPE BEGIN SELECT empno_sequence.NEXTVAL INTO v_empno FROM DUAL; INSERT INTO EMP(EMPNO,ENAME,JOB,DEPTNO) VALUES (v_empno),'HARDING','CLERK',10); END; ``` ```sql DECLARE v_sal_increase EMP.SAL%TYPE := 2000; BEGIN UPDATE EMP SET SAK = SAL + v_sal_increase WHERE JOB = 'ANALYST'; ``` ```sql DECLARE v_deptno EMP.DEPTNO%TYPE := 10 BEGIN DELETE FROM EMP WHERE DEPTNO = v_deptno ``` ### Gestion des Transactions On peut créer un point de sauvegarde : ```sql SAVEPOINT a; ``` Pour y revenir plus tard : ```sql ROLLBACK TO SAVEPOINT a; ``` Le Rollback n'est pas un "GOTO", il ne contrôle pas le flux d'exécution du code PLSQL, il va simplement annuler les opérations DML depuis le point de sauvegarde. On peut également valider la transaction : ```sql COMMIT; ``` ## Structures de contrôle On peut faire un If : ```sql IF ...condition... THEN ...statements... [ELSIF ...condition... THEN ...statements...] [ELSE ...statements...] END IF; ``` ## Boucles ### Boucle simple (équivalent do while) ```sql LOOP ...statement... EXIT [WHEN condition] END LOOP; ``` ### Boucle FOR ```sql FOR ...index variable... IN [REVERSE] ...start... .. ...end... LOOP ...statements... END LOOP; ``` ### Boucle WHILE ```sql WHILE ...condition... LOOP ...statements... END LOOP; ``` ## Curseur Structure de donnée permettant d'itérer sur les résultats d'une requête SQL. ### Déclarer un curseur ```sql CURSOR employees IS SELECT * FROM EMP; ``` ### Opérations sur un curseur - Open : ouvre le curseur ```sql OPEN employees; ``` - Fetch : récupère le prochain élément du curseur dans une variable ```sql FETCH employees INTO employee ``` - Close : ferme le curseur : ```sql CLOSE employees; ``` ### Attributs d'un curseur - `%ISOPEN` : `TRUE` si le curseur est ouvert - `%NOTFOUND` : `TRUE` si le dernier `FETCH` n'a pas retourné d'élément - `%FOUND` : `TRUE` si le dernier `FETCH` a retourné d'élément - `%ROWCOUNT` : Nombre d'éléments récupérer jusque là ### Curseur avec boucle FOR On peut itérer sur un curseur directement avec une boucle FOR, à la manière d'un FOR EACH. ```sql FOR employee IN employees LOOP ...statments... END LOOP; ``` ### Curseur particulier : SQL Le curseur SQL permet de récupérer des informations sur la dernière instruction DML exécutée : - `SQL%ROWCOUNT` : Nombre de ligne affectées - `SQL%FOUND` : `TRUE` si au moins une ligne affectée - `SQL%NOTFOUND` : `TRUE` si aucune ligne affectée - `SQL%ISOPEN` : toujours `FALSE` ## Exceptions On peut utiliser une clause `EXCEPTION` dans un bloc PLSQL pour gérer les exceptions levées. ```sql DECLARE ... BEGIN ... EXCEPTION ... END; ``` ### Syntaxe ```sql EXCEPTION WHEN ...exception1... [OR ...exception2...] THEN ...statements... [WHEN ...exception1... [OR ...exception2...] THEN ...statements...] ``` Exemple : ```sql DECLARE v_name EMP.NAME%TYPE; BEGIN SELECT NAME INTO v_empno FROM EMP WHERE EMPNO = 10 ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee with this Id'); END; ``` Pour récupérer respectivement le code de l'erreur et son message, utiliser les macros `SQLCODE` et `SQLREM`. ### Exception définies par l'utilisateur On peut aussi déclarer nos propres exception, et leur assigner un code d'erreur (entre -20 000 et -20 999) : ```sql DECLARE e_no_such_emp EXCEPTION; PRAGMA EXCEPTION_INIT(e_no_such_emp, -22292) ``` Pour lever une exception : ```sql RAISE e_no_such_emp; ``` ## Procédures Une procédure est une bloc PLSQL réutilisable qui effectue une action. ```sql CREATE [OR REPLACE] PROCEDURE ...nom... (...arg1... [...mode...] ...type, ...) IS ... bloc plsql... END ...nom...; ``` Supprimer une procédure : `DROP PROCEDURE ...nom...` ### Mode de passage de paramètres - `IN` : Données passée au sous programme à laquelle il accèdera en lecture uniquement - `OUT` : Utilisé pour retourner une valeur au programme appelant par effet de bord - `IN OUT` : Données passée au sous programme auquel il accède en lecture et écriture qui est retournée au programme parent. ## Fonctions Une fonction est une procédure qui retourne une valeur. ```sql CREATE [OR REPLACE] FUNCTION ...nom... (...arg1... [...mode...] ...type..., ...) RETURN ...type... IS ... bloc plsql... END ...nom...; ``` ## Packages Permet de lier les éléments qui ont un rapport logique ou sémantique entre eux. Un package est constitué d'un entête et d'un corp : ### Entête Pour les prototype des éléments: ```sql CREATE [OR REPLACE] PACKAGE ...nom... IS ...déclaration des prototypes... END ...name...; ``` ### Corps Pour les implémentations : ```sql CREATE [OR REPLACE] PACKAGE BODY ...nom... IS ...déclaration des implémentations... END ...name...; ``` ### Exemple ```sql CREATE OR REAPLACE PACKAGE time IS FUNCTION GetTimestamp RETURN DATE; PROCEDURE resetTimeStamp; END time; CREATE OR REPLACE PACKAGE BODY time IS StartTimesStamp DATE := SYSDATE; -- donnée privée du package FUNCTION GetTimeStamp RETURN DATE IS BEGIN RETURN StartTimesStamp; END GetTimeStamp; PROCEDURE ResetTimeStamp IS BEGIN StartTimestamp := SYSDATE; END ResetTimeStamp; END time; ``` ## Triggers Un trigger est un bloc PLSQL qui s'exécute automatiquement sur un événement particulier, les interactions avec la base de donnée. ### Syntaxe ```sql CREATE [OR REPLACE] TRIGGER ...nom... ...timing... ...event... [OR ...event2..., ] ON ...table... [FOR EACH ROW] [WHEN ...condition...] ...bloc PLSQL... ``` ### Timing - `BEFORE` : le trigger s'exécute avant le déclanchement de l'événement. - `AFTER` : le trigger s'exécute après le déclanchement de l'événement. ### Evènement L'événement en question peut être une des trois opérations DML : `INSERT`, `UPDATE` ou `DELETE`. ### Type Il existe deux types de trigger : - Statement : s'exécute une seule fois pour un événement (défaut) pas d'accès au contenu de chaque ligne - Row : s'exécute une fois pour chaque ligne affectée par l'événement. Possibilité d'accéder aux valeurs avec `:OLD` et `:NEW`. ### Prédicats conditionnel Pour les triggers qui se déclenchent sur plusieurs événements, on peut vérifier de quel événement il s'agit dans une condition : ```sql IF INSERTING THEN ... statements... END IF; ``` Le prédicat peut également porter sur une colonne précise de la table : ```sql IF UPDATING('SAL') THEN ... statements... END IF; ``` ### Activation ```sql ALTER TRIGGER ...nom trigger... DISABLE | ENABLE ``` Pour tous les trigger d'une table : ```sql ALTER TRIGGER ...nom table... DISABLE | ENABLE ALL TRIGGERS ```