Procédures stockées PL/SQL
Une procédure est simplement un programme PL/SQL nommé, compilé et stocké dans la base. On trouvera ci après le code de la plus petite procédure PL/SQL au monde 😉
SQL> CREATE OR REPLACE PROCEDURE proc_mini IS BEGIN NULL; END; / Procedure cree.
Pour être un peu plus précis, ce code est une commande SQL, qui crée la procédure PL/SQL, et donc compile et stocke dans la base le bloc PL/SQL compris entre le BEGIN et le END, en le référencant par ‘proc_mini’.
Et pour exécuter cette procédure de manière autonome :
SQL> EXECUTE proc_mini Procedure executee avec succes.
Voici le célèbre hello.pl (libre et originale adapdation du hello.c) ici sous forme de procédure stockée.
Un exemple un petit peu moins minimaliste, de procédure avec paramètre est donné ci-après :
PROCEDURE cree_client (p_nom VARCHAR2, p_ville VARCHAR2) IS BEGIN INSERT INTO clients (no_cli, nom_cli, ville_cli) VALUES (seq_noclient.NEXTVAL, p_nom, p_ville); COMMIT ; END;
Fonctions PL/SQL
Une fonction est une procédure retournant une valeur.
Globalement une fonction aura la forme suivante
CREATE OR REPLACE FUNCTION nom_fonction (nom_parametre type_parametre) RETURN type_du_retour IS declaration variable locales; BEGIN ... RETURN variable_resultat END; /
Exemple
CREATE OR REPLACE FUNCTION solde (no INTEGER) RETURN REAL IS le_solde REAL; BEGIN SELECT solde INTO le_solde FROM clients WHERE no_cli = no; RETURN le_solde; END; /
On peut par exemple appeler cette fonction et utiliser son résultat au sein d’un ordre SQL :
SQL> SELECT solde(1000) FROM dual ; Solde(1000) ----------- 12024,50
l’appel ‘bête’ de l’exécution d’une fonction comme si c’était une procédure (ouh la la !) ne fonctionne évidemment pas! mais on est souvent tenté de le faire quand même.
Soit la fonction stockée mdp() qui accepte un paramètre numérique. SI on l’appelle sans prendre garde au fait que c’est une fonction, on obtient :
SQL> execute mdp(2); BEGIN mdp(2); END; * ERREUR a la ligne 1 : ORA-06550: line 1, column 7: PLS-00221: 'MDP' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Gestion des droits et programmes stockés
Pour pouvoir créer une procédure stockée (ou une fonction…c’est le même droit pour les deux) il faut le droit ‘CREATE PROCEDURE’.
Si l’on n’a pas ce privilège, voila ce qui se passe:
SQL> connect SCOTT/tiger Connected. SQL> SQL> CREATE PROCEDURE test IS 2 BEGIN 3 NULL; 4 END; 5 . SQL> / CREATE PROCEDURE test * ERROR at line 1: ORA-01031: insufficient privileges
Il faut alors que le DBA donne le droit explicitement à l’utilisateur (ici SCOTT) de créer des procedures:
SQL> CONNECT SYSTEM/ORACLE SQL> GRANT CREATE PROCEDURE TO SCOTT; SQL> CONNECT SCOTT/TIGER SQL> CREATE OR REPLACE PROCEDURE test 2 IS 3 BEGIN 4 NULL; 5 END; 6 . SQL> / Procedure created.
Par défaut le créateur d’un programme stocké a tous les droits sur ce programme:
- Compilation (ALTER no)m_proc COMPILE;
- execution (EXECUTE nom_proc)
- suppression (DROP PROCEDURE nom_proc)
un autre utilisateur (hormis le DBA bien sûr) n’a aucun droit par défaut.
Ainsi si un utilisateur U1 crée une procédure P, le droit EXECUTE doit explicitement être donné à U2 pour qu’il puisse exécuter P (soit par U1 soit par le DBA:
SQL> GRANT EXECUTE ON p TO u2;
Une procédure peut s’exécuter de 2 façons différentes au niveau des droits:
- avec les privilèges du créateur de la procédure (DEFINER’s rights)
- avec les privilèges de l’utilisateur / exécuteur de la procédure (INVOKER’s Rights)
Packages PL/SQL
Un package est un module de programmes incluant procédures et / ou fonctions fonctionnellement dépendantes.
Un package est composé de 2 parties :
– la spécification (introduite par ‘CREATE PACKAGE’) liste les entêtes de procédures et fonctions contenues dans le package,
– le corps du package (introduit par ‘CREATE PACKAGE BODY’) qui contient le code effectif des procédures et fonctions déclarées précédemment.
exemple
SQL> CREATE PACKAGE clients AS -- spécifications du package PROCEDURE insere_client (no INTEGER, nom VARCHAR2, ...); PROCEDURE supprime_client (no INTEGER); ... END; /
SQL> CREATE PACKAGE BODY clients AS -- le corps du package PROCEDURE insere_client (no INTEGER, nom VARCHAR2, ...) IS BEGIN ... INSERT INTO clients VALUES (no, nom, ...); END;
PROCEDURE supprime_client (no INTEGER) IS BEGIN DELETE FROM clients WHERE no_cli = no; END; ... END; -- du package /
Triggers
Un trigger est un morceau de code PL/SQL, stocké dans la base, déclenché lors de l’occurrence d’un événement particulier. Ils permettent notamment de synchroniser des opérations entre plusieurs tables.
On s’intéressera ici principalement aux triggers au niveau TABLE
Avantages et inconvénients des triggers
A l’instar des contraintes d’intégrité, le code des triggers est centralisé dans la base de données, et sera exécuté quel que soit l’outil client (sql developer, application métier, sqlplus). Le comportement sera donc le même partout, et ne dépendra pas du programme client, de plus il ne pourra pas être contourné.
L’inconvénient principal du trigger est qu’il consomme des ressources. Ces dernières peuvent augmenter les temps de traitement, lors dse mises à jour, a fortiori si elles sont en battch et massives.
Note: On est parfois tenté de créer des triggers pour implémenter certaines règles de gestion. La plupart du temps les contraintes remplissent plus efficacement ce rôle.
Principes généraux des triggers
La plupart du temps les triggers sont déclenchés par la modification du contenu d’une table. La liste des événements déclencheurs apparaît ci-après :
| Type d’ordre | ordre déclencheur |
| LMD | DELETE, INSERT, UPDATE |
| LDD / Base de données | CREATE, ALTER, DROP |
| Base de données | SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN |
Les ordres du LDD et de la gestion de transaction (CREATE, ALTER, DROP, COMMIT, SAVEPOINT, sont interdits pour les triggers autres que base de données (triggers système).
Un déclencheur s’exécute dans le cadre d’une transaction. Il ne peut donc pas contenir d’instruction COMMIT ou ROLLBACK ou toute instruction générant une fin de transaction implicite (ordre DDL)
Syntaxe générale
Create [Or Replace] Trigger <Nom Trigger> [ Before After] <Opération DML> On <Nom Table> [ For Each Row ] [When <Condition >] Declare Begin Exception End ; /
rem: Le corps du trigger est défini par un bloc PL/SQL anonyme
[FOR EACH ROW] – Si est précisé, indique que le trigger est déclenché pour chaque ligne modifiée. Dans le cas contraire, n’est déclenché qu’une seule fois.
Le bloc PL/SQL qui constitue le trigger peut être exécuté avant ou après la vérification des contraintes d’intégrité. Il peut être exécuté pour chaque ligne affectée par l’ordre DML ou bien une seule fois pour la commande.
- BEFORE : le code dans le corps du triggers s’exécute avant les événements de déclenchement LMD
- AFTER : le code dans le corps du triggers s’exécute avant les événements de déclenchement LMD
Seules les valeurs des colonnes de la ligne courante sont accessibles. Oracle utilise deux variables de type enregistrement OLD et NEW.
OLD stocke la valeur avant mise à jour (et n’a bien sûr pas de sens pour un ordre INSERT)
NEW stocke la nouvelle valeur (et n’a bien sûr pas de sens pour un ordre…DELETE)
Ces deux variables pourront être utilisées dans la clause WHEN du trigger et dans la section exécutable. Dans cette dernière , elle seront prfixées par un opérateur ‘.’
La clause WHEN (condition) permet également de restreindre le champ du trigger en imposant une condition.
Exemple
Créons un déclencheur très basique qui ne fait qu’afficher le numéro et le nom d’un employé que l’on veut supprimer de la table EMP
SQL> CREATE OR REPLACE TRIGGER TRG_BDR_EMP 2 BEFORE DELETE -- avant supression 3 ON EMP -- sur la table EMP 4 FOR EACH ROW -- pour chaque ligne 5 Declare 6 LC$Chaine VARCHAR2(100); 7 Begin 8 dbms_output.put_line( 'Suppression de l''employé n° ' || To_char( :OLD.empno ) 9 || ' -> ' || :OLD.ename ) ; 10 End ; 11 / Déclencheur créé.
Supprimons maintenant un employé
SQL> set serveroutput on SQL> delete from emp where empno = 7369 Suppression de l'employé n° 7369 -> SMITH 1 ligne supprimée. SQL> rollback; Annulation (rollback) effectuée.
La DRH annonce que désormais, tout nouvel employé devra avoir un numéro supérieur ou égal à 10000. Il faut donc interdire toute insertion qui ne reflète pas cette nouvelle directive
SQL> CREATE OR REPLACE TRIGGER TRG_BIR_EMP 2 BEFORE INSERT -- avant insertion 3 ON EMP -- sur la table EMP 4 FOR EACH ROW -- pour chaque ligne 5 Begin 6 If :NEW.empno < 10000 Then 7 RAISE_APPLICATION_ERROR ( -20010, 'Numéro employé inférieur à 10000' ) ; 8 End if ; 9 End ; 10 / Déclencheur créé.
Voir un autre exemple avec FOR EACH ROW et sans: historqiue dse modifs de salaire détaillé (avec lignes/noms concernés et simple alerte d’une modif
cf: http://plsql-tutorial.com/plsql-triggers.htm
Manipulation des triggers
- Activer ou désactiver un Trigger: Alter Trigger <Nom_Trigger> [ Enable |Disable ] ;
- Supprimer un Trigger: Drop Trigger <Nom Trigger >;
- Déterminer les triggers de votre BD: Select Trigger_Name From User_triggers