bases_de_donnees:oracle:pl_sql
no way to compare when less than two revisions
Différences
Ci-dessous, les différences entre deux révisions de la page.
Révision précédente | |||
— | bases_de_donnees:oracle:pl_sql [2022/02/02 00:42] (Version actuelle) – modification externe 127.0.0.1 | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ====== PL/SQL ====== | ||
+ | - Langage procédural d' | ||
+ | - Une extension de SQL | ||
+ | - Permet de mélanger la puissance du SQL avec la souplesse d'un langage procédural | ||
+ | |||
+ | ===== Avantages ===== | ||
+ | - Intégration du SQL : on peut intégrer dans un bloc PL/SQL : | ||
+ | - Des instructions du MD | ||
+ | |||
+ | |||
+ | * Gestion des curseurs | ||
+ | * Traitement des erreurs | ||
+ | * Amélioration des performances : un bloc PL/SQL avec plusieurs instructions SQL ne causera qu'un seul accès à la base de données | ||
+ | * Incorporation aux produits Oracle : blocs et procédures PL/SQL sont compilés et exécutés par le moteur PL/SQL intégré au moteur de la base de données ainsi qu'à des outils tels que Oracle*Forms et Oracle*Reports | ||
+ | |||
+ | ===== Bloc PL/SQL ===== | ||
+ | |||
+ | - La partie déclaration n'est présente que si le bloc a besoin de déclarer des variables | ||
+ | |||
+ | <code plsql> | ||
+ | [DECLARE] | ||
+ | declaration_statements | ||
+ | BEGIN | ||
+ | execution_statements | ||
+ | [EXCEPTION] | ||
+ | exception_handling_statements | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | - toute variable doit être déclarée avant d' | ||
+ | * '' | ||
+ | * '' | ||
+ | - Deux types de variables : | ||
+ | * Variables locales : déclarées à l' | ||
+ | * Variables définies dans un environnement extérieur à PL/SQL | ||
+ | |||
+ | |||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | x NUMBER := 16; | ||
+ | anniversaire DATE; | ||
+ | nom VARCHAR(20); | ||
+ | pi CONSTANT NUMBER := 3.1416; | ||
+ | </ | ||
+ | |||
+ | - visibilité -> une variable locale est visible dans un bloc ou elle a été déclarée et dans les blocs imbriquées si elle n'a pas été redéfinie | ||
+ | - variables définies dans un environnement extérieur à PL/SQL : | ||
+ | - Déclarées en dehors du bloc et utilisables dans le bloc | ||
+ | - Représentent des camps d' | ||
+ | - Préfiée par : dans un bloc PL/SQL | ||
+ | <code plsql> | ||
+ | SQL> | ||
+ | </ | ||
+ | |||
+ | ===== Utilisation de scripts ===== | ||
+ | Les blocs PL/SQL peuvent être placés dans des fichiers textes avec l' | ||
+ | |||
+ | Ensuite, charger le script à l'aide de la commande ouvrir, une fois le script affiché, insérer une barre oblique (''/'' | ||
+ | |||
+ | ====== Types ====== | ||
+ | {{ : | ||
+ | |||
+ | Les mêmes types utilisables dans la définition des colonnes des tables. | ||
+ | |||
+ | Types propres à PL/ | ||
+ | |||
+ | ^ Caractères | ||
+ | | < | ||
+ | ^ Composés | ||
+ | | < | ||
+ | |||
+ | ===== Compatibilité ===== | ||
+ | Ce sont des synonymes pour assurer une compatibilité avec les types standards ANSI/ISO et IBM. | ||
+ | |||
+ | - NUMBER -> DEC, DECIMAL, NUMERIC, DOUBLE PRECISION, FLOAT, REAL, INTEGER, INT, SMALLINT. | ||
+ | - BINARY_INTEGER -> NATURAL, NATURALN, POSITIVE, POSITIVN, SIGNTYPE. | ||
+ | - VARCHAR2 -> STRING, VARCHAR. | ||
+ | - CHAR -> CHARACTER. | ||
+ | |||
+ | |||
+ | ====== Opérateurs ====== | ||
+ | |||
+ | ^ Type ^ Symbole(s) | ||
+ | | Affectation | := | | ||
+ | | Addition | + | | ||
+ | | Soustraction | ||
+ | | Multiplication | ||
+ | | Division | ||
+ | | Exponentiation | ||
+ | | Concaténation | ||
+ | |||
+ | ===== La clause INTO ===== | ||
+ | S' | ||
+ | |||
+ | <code sql> | ||
+ | SELECT {*|liste d’expressions} | ||
+ | INTO liste de variables | ||
+ | FROM ...; | ||
+ | |||
+ | FETCH nom de curseur | ||
+ | INTO liste de variables; | ||
+ | </ | ||
+ | |||
+ | ====== Exemples ====== | ||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | numMenbre MEMBRE.NUMBER%TYPE; | ||
+ | unDocument DOCUMENT%ROWTYPE; | ||
+ | BEGIN | ||
+ | numMembre := 465; | ||
+ | SELECT nom, tel | ||
+ | FROM Membre | ||
+ | WHERE Numero = numMembre; | ||
+ | |||
+ | unDocument.ISBN := ' | ||
+ | unDocument.Titre := 'Intro à PL/ | ||
+ | unDocument.Auteur := ' | ||
+ | unDocument.Type := ' | ||
+ | unDocument.Editeur := ' | ||
+ | unDocument.Annee := ' | ||
+ | |||
+ | INSERT INTO Document VALUES unDocument; | ||
+ | UPDATE Document | ||
+ | SET ROW = unDocument | ||
+ | WHERE ISBN = " | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | TYPE Etudiant IS RECORD ( | ||
+ | no NUMBER(10); | ||
+ | nom CHAR(20); | ||
+ | note NUMBER | ||
+ | ); | ||
+ | etud Etudiant; | ||
+ | BEGIN | ||
+ | etud.no := 4422; | ||
+ | etud.nom := ' | ||
+ | etud.note := 79; | ||
+ | etud.note := etud.note * 1.1; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ====== Structures de contrôles ====== | ||
+ | Les principes des structures de contrôles sont les mêmes que dans la majorité des langages de programmation. | ||
+ | |||
+ | ===== Séquence ===== | ||
+ | Suite d' | ||
+ | |||
+ | ===== Alternative ===== | ||
+ | |||
+ | ==== L' | ||
+ | <code plsql> | ||
+ | IF condition THEN | ||
+ | instruction1; | ||
+ | ELSE | ||
+ | intruction2; | ||
+ | END IF; | ||
+ | </ | ||
+ | |||
+ | <code plsql> | ||
+ | IF condition1 THEN | ||
+ | instruction1; | ||
+ | ELSEIF condition2 THEN | ||
+ | intruction2; | ||
+ | ELSE | ||
+ | instuction3; | ||
+ | END IF; | ||
+ | </ | ||
+ | |||
+ | Condition : expression logique qui utilise les opérateurs '' | ||
+ | |||
+ | <code plsql> | ||
+ | IF NUM= 444 THEN | ||
+ | UPDTE MEMBRE SET NOM=' | ||
+ | WHERE NUMERO = NUM; | ||
+ | COMMIT; | ||
+ | ELSE | ||
+ | ROLLBACK; | ||
+ | END IF; | ||
+ | </ | ||
+ | |||
+ | ==== L' | ||
+ | La structure alternative '' | ||
+ | |||
+ | <code plsql> | ||
+ | /* Premier cas de CASE */ | ||
+ | [<< | ||
+ | CASE choix | ||
+ | WHEN valeur1 THEN instruction1; | ||
+ | WHEN valeur2 THEN instruction2; | ||
+ | WHEN valeur3 THEN instruction3; | ||
+ | [ELSE instructionx; | ||
+ | END CASE [etiquette]; | ||
+ | |||
+ | /* deuxieme forme de CASE */ | ||
+ | |||
+ | [<< | ||
+ | CASE TRUE | ||
+ | WHEN condition1 THEN instruction1; | ||
+ | WHEN condition2 THEN instruction2; | ||
+ | WHEN condition3 THEN instruction3; | ||
+ | [ELSE instructionx; | ||
+ | END CASE [etiquette]; | ||
+ | </ | ||
+ | < | ||
+ | |||
+ | <code plsql> | ||
+ | SET serveroutput on; | ||
+ | DECLARE | ||
+ | age NUMBER := 35; | ||
+ | msg VARCHAR(80); | ||
+ | BEGIN | ||
+ | CASE | ||
+ | WHEN age < 12 THEN msg := ' | ||
+ | WHEN age < 18 THEN msg := ' | ||
+ | WHEN age < 40 THEN msg := ' | ||
+ | WHEN age < 50 THEN msg := ' | ||
+ | ELSE msg := ' | ||
+ | END CASE; | ||
+ | dbms_output.put_line(' | ||
+ | END; | ||
+ | </ | ||
+ | Notez la ligne '' | ||
+ | |||
+ | ===== Répétitives ===== | ||
+ | |||
+ | ==== L' | ||
+ | <code plsql> | ||
+ | /* Premère forme */ | ||
+ | [<< | ||
+ | LOOP | ||
+ | intructions; | ||
+ | END LOOP [etiquette]; | ||
+ | </ | ||
+ | On sort de a boucle avec une instruction EXIT. | ||
+ | <code plsql> | ||
+ | EXIT [etiquette] [WHEN condition]; | ||
+ | </ | ||
+ | |||
+ | ==== L' | ||
+ | |||
+ | <code plsql> | ||
+ | [<< | ||
+ | FOR indice IN [REVERSE] exp1..exp2 LOOP | ||
+ | instructions; | ||
+ | END LOOP [etiquette]; | ||
+ | </ | ||
+ | |||
+ | ==== L' | ||
+ | <code plsql> | ||
+ | [<< | ||
+ | WHILE condition LOOP | ||
+ | instructions; | ||
+ | END LOOP [eiquette]; | ||
+ | </ | ||
+ | ==== Boucle simple ==== | ||
+ | |||
+ | <code plsql> | ||
+ | OPEN cursor_name [(parameter1, | ||
+ | LOOP | ||
+ | FETCH cursor_name | ||
+ | INTO row_structure_variable | column_variable1 [, | ||
+ | EXIT WHEN cursor_name%NOTFOUND; | ||
+ | statement; | ||
+ | END LOOP; | ||
+ | CLOSE cursor_name; | ||
+ | </ | ||
+ | |||
+ | ====== Les curseur ====== | ||
+ | Zone mémoire de taille fixe utilisée par le moteur de la base de données pour analyser et interpréter toute requête SQL. Les status d' | ||
+ | |||
+ | Deux types de curseurs : | ||
+ | - Implicite : qui sont générés et géré par Oracle pour toute requête SQL | ||
+ | - Explicite : généré et géré par l' | ||
+ | La déclaration des curseurs explicites est obligatoire. | ||
+ | - Le nom du curseur | ||
+ | - La requête '' | ||
+ | |||
+ | La syntaxe : | ||
+ | <code plsql> | ||
+ | CURSOR nom_curseur IS requete_SQL; | ||
+ | </ | ||
+ | |||
+ | ===== Ouverture et fermeture ===== | ||
+ | Dans la section de traitement (après '' | ||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | ===== Effets ===== | ||
+ | |||
+ | - Allocation de la mémoire | ||
+ | - Analyse syntaxique et sémantique de la requête | ||
+ | - Exécution de la requête | ||
+ | - Positionnement des verrous éventuels (si '' | ||
+ | |||
+ | ===== Traitement des lignes (FETCH) ===== | ||
+ | Les lignes du résultat de la requête sont traités une par une. La valeur de chaque colonne doit être stockée dans une variable de réception. | ||
+ | |||
+ | L' | ||
+ | |||
+ | <code plsql> | ||
+ | FETCH nom_curseur INTO [variable1, variable2, ...] | [record_name]; | ||
+ | </ | ||
+ | * // | ||
+ | * // | ||
+ | * // | ||
+ | |||
+ | L' | ||
+ | - Lit les données de l' | ||
+ | - Avance le pointeur au prochain enregistrement de l' | ||
+ | |||
+ | ==== Règles à suivre ==== | ||
+ | - Inclure le même nombre de variable dans la clause '' | ||
+ | - Respecter la position des colonnes, qui seront associés aux variables | ||
+ | - tester si le curseur contient des enregistrements. | ||
+ | |||
+ | ==== Exemple de FETCH ==== | ||
+ | Retrouve les 10 premiers employés un par un((Introduction to Oracle 9i PL/SQL Volume 1 (PDF), Oracle Press)) | ||
+ | <code plsql> | ||
+ | SET SERVEROUTPUT ON | ||
+ | DECLARE | ||
+ | v_empno | ||
+ | v_ename | ||
+ | CURSOR | ||
+ | SELECT | ||
+ | FROM employees; | ||
+ | BEGIN | ||
+ | OPEN emp_cursor; | ||
+ | FOR i IN 1..10 LOOP | ||
+ | FETCH emp_cursor INTO v_empno, v_name; | ||
+ | DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno) | ||
+ | ||' | ||
+ | END LOOP; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== Fermeture ===== | ||
+ | <code plsql> | ||
+ | |||
+ | ===== Forme simplifiée ===== | ||
+ | Qui __déclare implicitement__ la variable de parcours, ouvre le cuseur, réalise les '' | ||
+ | <code plsql> | ||
+ | FOR variable IN nom_curseur LOOP | ||
+ | instruction; | ||
+ | END LOOP; | ||
+ | </ | ||
+ | ===== Mise à jour avec les curseurs ===== | ||
+ | On peut utiliser un curseur pour modifier les données de la base de donnée. | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR c IS | ||
+ | SELECT * FROM Document | ||
+ | FOR UPDATE OF Titre; | ||
+ | BEGIN | ||
+ | -- ... | ||
+ | UPDATE Document | ||
+ | SET Titre = ' | ||
+ | WHERE CURRENT OF c; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Attributs ===== | ||
+ | Des attrributs peuvent être spécifiés sur un curseur. | ||
+ | |||
+ | - Curseur explicite -> '' | ||
+ | - Curseur implicite -> '' | ||
+ | |||
+ | ^ Attribut | ||
+ | | '' | ||
+ | | '' | ||
+ | | '' | ||
+ | | '' | ||
+ | |||
+ | ===== Paramètres ===== | ||
+ | |||
+ | CURSOR nom_curseur(param1 type1, param2 typ2, ...); | ||
+ | |||
+ | Puis, à l' | ||
+ | OPEN nom_curseur(val1, | ||
+ | |||
+ | |||
+ | |||
+ | ===== Exemple ===== | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR docs IS SELECT ISBN, Titre FROM Document; | ||
+ | i NUMBER(10); | ||
+ | t VARCHAR2(); | ||
+ | BEGIN | ||
+ | FOR d IN docs LOOP | ||
+ | i := d.ISBN; | ||
+ | t := d.Titre; | ||
+ | /* ... */ | ||
+ | END LOOP; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ====== Gestion des exceptions ====== | ||
+ | En Oracle il y a deux types d' | ||
+ | - Erreurs internes d' | ||
+ | - Erreurs dûe au programme | ||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | ... | ||
+ | BEGIN | ||
+ | ... | ||
+ | EXCEPTION | ||
+ | // traitement des exceptions | ||
+ | WHEN type1 THEN | ||
+ | | ||
+ | END; | ||
+ | </ | ||
+ | ===== Règles ===== | ||
+ | - On peut définir et donner un om à l' | ||
+ | - Lui assigner une entrée dans la section '' | ||
+ | |||
+ | ===== Les erreurs prédéfinies ===== | ||
+ | Toutes les erreurs Oracles ont un numéro d' | ||
+ | |||
+ | ^ Nom ^ Numéro (SQLCode) | ||
+ | | DUP_VAL_ON_INDEX | ||
+ | | NO_DATA_FOUND | ||
+ | | CURSOR_ALREADY_OPEN | -6511 | | ||
+ | | ZERO_DIVIDE | -1476 | | ||
+ | | TOO_MANY_ROWS | ||
+ | |||
+ | Pour les exceptions avec les nom connus, on peut les utiliser de cette façon : | ||
+ | <code plsql> | ||
+ | BEGIN | ||
+ | ... | ||
+ | EXCEPTION | ||
+ | WHEN TOO_MANY_ROWS THEN | ||
+ | ... | ||
+ | WHEN DUP_VAL_ON_INDEX THEN | ||
+ | ... | ||
+ | END; | ||
+ | </ | ||
+ | Ils font partie du portage standard, donc inutile de les déclarer. | ||
+ | |||
+ | Pour les exceptions qui n'ont pas de nom : | ||
+ | <code plsql> | ||
+ | </ | ||
+ | |||
+ | ===== Erreurs dûes au programme ===== | ||
+ | Le programmeur gère une siuation exceptionnelle, | ||
+ | |||
+ | Ces exceptions qui être déclarés explicitement et doivent être déclenchées avec '' | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | nom_exception EXCEPTION; | ||
+ | BEGIN | ||
+ | -- déclanchement | ||
+ | IF probleme THEN | ||
+ | RAISE nom_exception; | ||
+ | |||
+ | EXCEPTION | ||
+ | WHEN nom_exception THEN | ||
+ | ... | ||
+ | END; | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== Erreurs Oracle non-prédéfinies ===== | ||
+ | Deux façon de les gérer | ||
+ | - On la traite dans la clause OTHERS de la section '' | ||
+ | - Associer un nom au numéro d' | ||
+ | <code plsql> | ||
+ | PRAGMA EXCEPTION_INIT (nom_exception, | ||
+ | </ | ||
+ | |||
+ | Exemple : verrou mortel ou interblocage. | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | | ||
+ | | ||
+ | BEGIN | ||
+ | ... | ||
+ | EXCEPTION | ||
+ | WHEN verrou_mortel THEN | ||
+ | ROLLBACK; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | | ||
+ | | ||
+ | ... | ||
+ | BEGIN | ||
+ | ... | ||
+ | EXCEPTION | ||
+ | WHEN bad_emp_id OR bad_acct_no THEN -- user-defined | ||
+ | ROLLBACK; | ||
+ | WHEN ZERO_DIVIDE THEN -- predefined | ||
+ | INSERT INTO inventory VALUES (part_number, | ||
+ | COMMIT; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== RAISE_APPLICATION_ERROR ===== | ||
+ | Permet de déclancher des exceptions | ||
+ | <code plsql> | ||
+ | RAISE_APPLICATION_ERROR(numero, | ||
+ | </ | ||
+ | * Le numéro doit être entre -20999 et -20000. | ||
+ | * Le message c'est 2048 caractères maximum | ||
+ | * Optionnellement, | ||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ====== Procédures et fonctions ====== | ||
+ | On peut imbriquer des blocs PL/SQL : | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | ... | ||
+ | BEGIN | ||
+ | ... | ||
+ | DECLARE | ||
+ | ... | ||
+ | BEGIN | ||
+ | ... | ||
+ | EXCEPTION | ||
+ | ... | ||
+ | END; | ||
+ | ... | ||
+ | END; | ||
+ | </ | ||
+ | Mais évidemment, | ||
+ | |||
+ | ===== Procédure ===== | ||
+ | La syntaxe d'une procédure ressemble à : | ||
+ | <code plsql> | ||
+ | PROCEDURE p[(arg1 [IN|OUT] type, arg2, argN)] IS | ||
+ | -- Ici la déclaration de variable locales | ||
+ | BEGIN | ||
+ | ... | ||
+ | EXCEPTION | ||
+ | ... | ||
+ | END P; | ||
+ | </ | ||
+ | |||
+ | ===== Fonctions ===== | ||
+ | |||
+ | <code plsql> | ||
+ | FUNCTION F[(arg1 [IN|OUT] type, arg2, argN)] | ||
+ | RETURN type_de_retour IS | ||
+ | -- Ici la déclaration de variable locales | ||
+ | BEGIN | ||
+ | ... | ||
+ | RETURN resultat; | ||
+ | EXCEPTION | ||
+ | ... | ||
+ | END f; | ||
+ | </ | ||
+ | |||
+ | ===== Passage des arguments ===== | ||
+ | Trois méthodes (qui sont optionnelles) : | ||
+ | - '' | ||
+ | - '' | ||
+ | - '' | ||
+ | Comme c'est optionnel, ne pas spécifier revient à mettre '' | ||
+ | |||
+ | ===== Appel ===== | ||
+ | |||
+ | |||
+ | |||
+ | ===== Procédures et fonctions stockées ===== | ||
+ | C'est une procédure/ | ||
+ | |||
+ | Dans un bloc PL/SQL, on a juste à invoquer le nom de la procédure/ | ||
+ | SQL> | ||
+ | |||
+ | ==== Définition ==== | ||
+ | Pour définir les procédures ou les fonctions : | ||
+ | <code plsql> | ||
+ | CREATE [OR REPLACE] PROCEDURE p(...) IS ... | ||
+ | CREATE [OR REPLACE] FUNCTION f(...) IS ... | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ====== Packages ====== | ||
+ | Objet du schéma qui regroupe logiquement les éléments du PL/SQL (types de données, fonctions, procédures, | ||
+ | |||
+ | ===== Structure ===== | ||
+ | - Entête -> décrit le contenu, entêtes des procédures/ | ||
+ | - Corps -> définition des procédures/ | ||
+ | |||
+ | ==== Entête ==== | ||
+ | <code plsql> | ||
+ | CREATE PACKAGE nom_package AS | ||
+ | -- définition de types | ||
+ | -- déclaration de varaibles publiques | ||
+ | -- prototypes des curseurs | ||
+ | -- prototypes des procedures/ fonctions | ||
+ | END [nom_package]; | ||
+ | </ | ||
+ | |||
+ | Pour la définition d'un prototype de curseur dans le package : | ||
+ | <code plsql> | ||
+ | CURSOR nom_curseur RETURN type_resultat; | ||
+ | </ | ||
+ | |||
+ | Pour la définition d'un prototype de fonction/ | ||
+ | <code plsql> | ||
+ | FUNCTION nom_fonction(parmetres) RETURN type_retour; | ||
+ | PROCEDURE nom_procedure(parmetres); | ||
+ | </ | ||
+ | |||
+ | ==== Corps ==== | ||
+ | |||
+ | <code plsql> | ||
+ | CREATE PACKAGE BODY nom_package AS | ||
+ | -- déclarations de types et variables locales au package | ||
+ | -- définition procedures/ | ||
+ | -- corps des procedures/ | ||
+ | -- implémentation des curseurs publics | ||
+ | END [nom_package]; | ||
+ | </ | ||
+ | L' | ||
+ | <code plsql> | ||
+ | CURSOR nom_curseur RETURN type_resultat | ||
+ | IS SELECT ...; | ||
+ | </ | ||
+ | |||
+ | Faire précéder l' | ||
+ | nom_package.nom_element; | ||
+ | |||
+ | ====== Les déclencheurs ====== | ||
+ | Bloc PL/SQL associé à une table qui va s' | ||
+ | |||
+ | Par exemple, garantir qu'un membre de la bibliothèque ne peut pas emprunter plus de cinq documents. | ||
+ | |||
+ | ===== Syntaxe de la définition d'un déclencheur ===== | ||
+ | |||
+ | <code plsql> | ||
+ | CREATE [OR REPLACE] TRIGGER nom_du_declencheur | ||
+ | { BEFORE / AFTER / INSTEAD OF } -- peuvent être combinés avec l' | ||
+ | { INSERT / DELETE / UPDATE [OF col1, col2, ...]} -- peuvent être combinés avec l' | ||
+ | ON nom_de_la_table [FOR EACH ROW] | ||
+ | [WHEN (condition)] | ||
+ | -- Ensuite le bloc PL/SQL | ||
+ | |||
+ | </ | ||
+ | |||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | ===== Accès aux données de la table ===== | ||
+ | Dans le bloc PL/SQL, les données de la table sont inaccessibles, | ||
+ | |||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | |||
+ | |||
+ | ====== Exemple into ====== | ||
+ | |||
+ | |||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | doc document%ROWTYPE | ||
+ | ... | ||
+ | BEGIN | ||
+ | SELECT * | ||
+ | INTO doc | ||
+ | FROM document | ||
+ | WHERE isbn=' | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== Labo 2 ====== | ||
+ | |||
+ | ===== Exo 1 ===== | ||
+ | Proposez un bloc PL/SQL qui utilise un curseur pour afficher la liste des ISBN et Titres des document de la bibliothèque. | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR docs IS SELECT isbn, titre FROM document; | ||
+ | i number(10); | ||
+ | t varchar2(50); | ||
+ | BEGIN | ||
+ | FOR d IN docs LOOP | ||
+ | i := d.isbn; | ||
+ | t := d.titre; | ||
+ | dbms_output.put_line(' | ||
+ | END LOOP; | ||
+ | dbms_output.put_line(' | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Exo 2 ===== | ||
+ | Utiliser un curseur pour parcourir tous les exemplaires de la bibliothèque qui ont été empruntés au moins dix fois. | ||
+ | |||
+ | <code plsql> | ||
+ | /* | ||
+ | Labo 2 - exercice 2. | ||
+ | 1ère solution : le filtrage des données est effectué dans le select du curseur. | ||
+ | */ | ||
+ | DECLARE | ||
+ | CURSOR emps IS select isbn, | ||
+ | i number(10); | ||
+ | n number; | ||
+ | BEGIN | ||
+ | FOR e IN emps LOOP | ||
+ | i := e.isbn; | ||
+ | n := e.exemplaire; | ||
+ | dbms_output.put_line(' | ||
+ | END LOOP; | ||
+ | dbms_output.put_line(' | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | <code plsql> | ||
+ | /* | ||
+ | Labo 2 - exercice 2. | ||
+ | 2ème solution : le filtrage des données est effectué dans le traitement effectué sur le curseur. | ||
+ | */ | ||
+ | DECLARE | ||
+ | CURSOR exemps IS select isbn,numero from exemplaire; | ||
+ | i number(10); | ||
+ | n number; | ||
+ | BEGIN | ||
+ | FOR e IN exemps LOOP | ||
+ | select Count(*) | ||
+ | into n | ||
+ | from emprunt | ||
+ | where isbn=e.isbn AND exemplaire=e.numero; | ||
+ | -- dbms_output.put_line(' | ||
+ | IF (n>10) THEN | ||
+ | dbms_output.put_line(' | ||
+ | END IF; | ||
+ | END LOOP; | ||
+ | dbms_output.put_line(' | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | ===== Exo 3 ===== | ||
+ | Proposez un bloc PL/SQL qui utilise un curseur pour mettre à jour les états des exemplaires selon les règles suivantes : | ||
+ | - Si le nombre d' | ||
+ | - Si le nombre d' | ||
+ | - Si le nombre d' | ||
+ | - Sinon l' | ||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR exemps IS select * from exemplaire FOR UPDATE OF etat; | ||
+ | i number(10); | ||
+ | n number; | ||
+ | s exemplaire.etat%TYPE; | ||
+ | BEGIN | ||
+ | FOR e IN exemps LOOP | ||
+ | select Count(*) | ||
+ | into n | ||
+ | from emprunt | ||
+ | where isbn=e.isbn AND exemplaire=e.numero; | ||
+ | CASE | ||
+ | WHEN n<1 THEN s:= ' | ||
+ | WHEN n<2 THEN s:= ' | ||
+ | WHEN n<3 THEN s:= ' | ||
+ | ELSE s: | ||
+ | END CASE; | ||
+ | | ||
+ | UPDATE exemplaire | ||
+ | SET etat = s | ||
+ | WHERE CURRENT OF exemps; | ||
+ | END LOOP; | ||
+ | dbms_output.put_line(' | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== Exo 4 ===== | ||
+ | Utilise un curseur pour supprimer tous les membres dont l' | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR membres IS select * from membre WHERE FINABONNEMENT< | ||
+ | i number(10); | ||
+ | n number; | ||
+ | s exemplaire.etat%TYPE; | ||
+ | BEGIN | ||
+ | FOR m IN membres LOOP | ||
+ | select Count(*) | ||
+ | into n | ||
+ | from emprunt | ||
+ | where membre=m.numero AND rendule> | ||
+ | dbms_output.put_line(' | ||
+ | IF (n=0) THEN | ||
+ | --Mise à jour de la table emprunt : | ||
+ | UPDATE emprunt | ||
+ | SET membre=null | ||
+ | WHERE membre=m.numero; | ||
+ | |||
+ | --Suppression du membre : | ||
+ | DELETE FROM MEMBRE | ||
+ | WHERE numero = m.numero; | ||
+ | dbms_output.put_line(' | ||
+ | END IF; | ||
+ | END LOOP; | ||
+ | dbms_output.put_line(' | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== Labo 3 ====== | ||
+ | ===== Exo 1 ===== | ||
+ | Proposez un bloc PL/SQL qui prolonge de 90 jours les abonnements de tous les membres. | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR mbrs IS SELEC numero, | ||
+ | |||
+ | BEGIN | ||
+ | FOR m IN mbrs LOOP | ||
+ | UPDATE Membre | ||
+ | SET finabonnement=m.finabonnement+90 | ||
+ | WHERE CURRENT OF mbrs; | ||
+ | END FOR; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== Exo 2 ===== | ||
+ | Proposez un bloc PL/SQL qui affiche la liste des trois membres qui ont emprunté le plus de documents durant la dernière année. | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR smem IS | ||
+ | SELECT nom, prenom, membre.numero, | ||
+ | FROM membre, emprunt | ||
+ | WHERE MEMBRE.NUMERO> | ||
+ | AND RENDULE > SYSDATE-325 | ||
+ | GROUP BY NOM, PRENOM, MEMBRE.NUMERO | ||
+ | ORDER BY 4 DESC, 1; | ||
+ | nom MEMBRE.Nom%TYPE | ||
+ | prenom MEMBRE.PRENOM%TYPE | ||
+ | Numero NUMBER(10) | ||
+ | |||
+ | BEGIN | ||
+ | OPEN smem; | ||
+ | FOR i IN 1..3 LOOP | ||
+ | FETCH smem INTO nom, prenom, numero.membre; | ||
+ | DBMS_OUTPUT.put_line(' | ||
+ | END LOOP; | ||
+ | CLOSE smem; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | Solution alternative : | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR mbr IS | ||
+ | SELECT membre, COUNT(*) AS cpt | ||
+ | FROM emprunt | ||
+ | where rendule >= sysdate -365 | ||
+ | GRoup by membre | ||
+ | order by cpt desc; | ||
+ | nbr numbre := 0; | ||
+ | aff_membre MEMBRE%ROWTYPE; | ||
+ | BEGIN | ||
+ | for i in mbr loop | ||
+ | exit when mbr%NOTFOUND; | ||
+ | select * into aff_membre | ||
+ | from membre | ||
+ | where numero=i.membre; | ||
+ | DBMS_OUTPUT.put_line(' | ||
+ | nbr := nbr + 1; | ||
+ | exit when nbr>=3; | ||
+ | end loop; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Exo 3 ===== | ||
+ | Proposez un bloc PL/SQL qui prolonge d'une année les abonnements des trois membres qui ont emprunté le plus de documents durant la dernière année. | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | CURSOR mbr IS | ||
+ | SELECT membre, COUNT(*) AS cpt | ||
+ | FROM emprunt | ||
+ | where rendule >= sysdate -365 | ||
+ | GRoup by membre | ||
+ | order by cpt desc; | ||
+ | nbr numbre := 0; | ||
+ | aff_membre MEMBRE%ROWTYPE; | ||
+ | BEGIN | ||
+ | for i in mbr loop | ||
+ | exit when mbr%NOTFOUND; | ||
+ | select * into aff_membre | ||
+ | from membre | ||
+ | where numero=i.membre; | ||
+ | Update membre SET finabonnement=finabonnement+365 WHERE numero = mbr.numero; | ||
+ | DBMS_OUTPUT.put_line(' | ||
+ | nbr := nbr + 1; | ||
+ | exit when nbr>=3; | ||
+ | end loop; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Exo 4 ===== | ||
+ | Proposez un bloc PL/SQL qui affiche les noms téléphones des membres qui ont un retard de plus de 30 jours dans la remise d'au moins un document emprunté. | ||
+ | <code plsql> | ||
+ | SET SERVEROUPUT ON SIZE 50000; | ||
+ | |||
+ | DECLARE | ||
+ | cursor emp is select *from emprunt where rendu is null and rendule > sysdate - 30 order by membre; | ||
+ | type mbr is record(n membre.nom%type, | ||
+ | m mbr; | ||
+ | tre document.titre%type; | ||
+ | nummbr membre.numero%type := -1; | ||
+ | |||
+ | BEGIN | ||
+ | for i in emp loop | ||
+ | if nummbr <> i.membre then | ||
+ | nummbr := i.membre; | ||
+ | select nom, tel into m from membre where numero = nummbr; | ||
+ | dbms_output.put_line(' | ||
+ | end if; | ||
+ | select titre into tre from document where isbn = i.isbn; | ||
+ | dbms_output.put_line(' | ||
+ | end loop; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== Labo 4 ====== | ||
+ | ===== Ex 1 ===== | ||
+ | |||
+ | |||
+ | <code plsql> | ||
+ | PROCEDURE afficheDocParType(DocType document.type%type in) IS | ||
+ | CURSOR SDoc IS | ||
+ | SELECT ISBN, titre, auteur | ||
+ | FROM document | ||
+ | WHERE type = DocType; | ||
+ | |||
+ | BEGIN | ||
+ | FOR doc IN SDoc LOOP | ||
+ | dbms.output.putline(' | ||
+ | END LOOP; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Ex 2 ===== | ||
+ | |||
+ | <code plsql> | ||
+ | FUNCTION getNbDocParType(docType DOCUMENT.TYPE%TYPE) | ||
+ | RETURN NUMBER IS | ||
+ | |||
+ | DECLARE | ||
+ | n NUMBER; | ||
+ | BEGIN | ||
+ | SELECT COUNT(*) INTO n | ||
+ | FROM DOCUMENT | ||
+ | WHERE TYPE = docType; | ||
+ | RETURN n; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== Exemple d' | ||
+ | Exemple d' | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | t DOCUMENT.TYPE%TYPE | ||
+ | n NUMBER | ||
+ | BEGIN | ||
+ | t := ' | ||
+ | AfficherDocParType(t); | ||
+ | n := getNbDocParType(t); | ||
+ | DBMS_OUTPUT.putline(' | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ====== Labo 5 ====== | ||
+ | ===== Exercice 1 ===== | ||
+ | |||
+ | |||
+ | <code plsql> | ||
+ | CREATE OR REPLACE TRIGGER stats_emprunts | ||
+ | AFTER INSERT | ||
+ | ON EMPRUNT | ||
+ | FOR EACH ROW | ||
+ | BEGIN | ||
+ | INSERT INTO stat_emprunt(isbn) VALUES(: | ||
+ | EXCEPTION | ||
+ | WHEN DUP_VAL_ON_INDEX THEN | ||
+ | UPDATE stat_emprunt | ||
+ | SET NBEMPRUNT = NBEMPRUNT+1 | ||
+ | WHERE isbn = :new.isbn; | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | ===== Exercice 2 ===== | ||
+ | <code plsql> | ||
+ | set serveroutput on; | ||
+ | |||
+ | CREATE OR REPLACE TRIGGER supp_emprunt | ||
+ | BEFORE DELETE | ||
+ | ON EMPRUNT | ||
+ | FOR EACH ROW | ||
+ | |||
+ | BEGIN | ||
+ | IF (: | ||
+ | RAISE_APPLICATION_ERROR(-20100,' | ||
+ | END IF; | ||
+ | | ||
+ | INSERT INTO archiv_emprunt VALUES (: | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== Labo 6 ====== | ||
+ | |||
+ | ===== Exercice 1 ===== | ||
+ | Définissez un déclencheur qui supprime tous les exemplaires d’un document dès que le document est supprimé de la bibliothèque. | ||
+ | <code plsql> | ||
+ | CREATE OR REPLACE TRIGGER supp_exemplaires | ||
+ | BEFORE DELETE | ||
+ | ON BIB_DOCUMENT | ||
+ | FOR EACH ROW | ||
+ | |||
+ | BEGIN | ||
+ | DELETE FROM BIB_EXEMPLAIRE WHERE ISBN = :old.ISBN; | ||
+ | END; | ||
+ | </ | ||
+ | Ce trigger fonctionne, mais comme Oracle permet d' | ||
+ | |||
+ | ===== Exercice 2 ===== | ||
+ | Définissez un déclencheur qui permet de s’assurer que lors d’un emprunt, l’adhésion du membre est encore valide. | ||
+ | <code plsql> | ||
+ | CREATE OR REPLACE TRIGGER verif_emprunt | ||
+ | BEFORE INSERT | ||
+ | ON BIB_EMPRUNT | ||
+ | FOR EACH ROW | ||
+ | |||
+ | DECLARE | ||
+ | fa BIB_MEMBRE.FINABONNEMENT%TYPE; | ||
+ | |||
+ | BEGIN | ||
+ | SELECT finabonnement INTO fa FROM BIB_MEMBRE WHERE NUMERO = : | ||
+ | IF (fa <= SYSDATE) THEN | ||
+ | RAISE_APPLICATION_ERROR(-20100,' | ||
+ | END IF; | ||
+ | |||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== Exercice 3 ===== | ||
+ | |||
+ | Définissez un déclencheur qui interdit la modification du numéro de membre d’un emprunt. | ||
+ | |||
+ | <code plsql> | ||
+ | CREATE OR REPLACE TRIGGER interdire_modif | ||
+ | BEFORE UPDATE | ||
+ | ON BIB_EMPRUNT | ||
+ | FOR EACH ROW | ||
+ | |||
+ | BEGIN | ||
+ | IF (: | ||
+ | RAISE_APPLICATION_ERROR(-20100,' | ||
+ | ELSE | ||
+ | UPDATE bib_emprunt | ||
+ | SET | ||
+ | isbn = :new.isbn, | ||
+ | exemplaire = : | ||
+ | rendule = : | ||
+ | rendu = :new.rendu | ||
+ | WHERE Numero = : | ||
+ | END IF; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | ===== Exercice 4 ===== | ||
+ | |||
+ | Définissez un déclencheur qui met à jour automatiquement l’état des exemplaires en fonction du nombre d’emprunts. Par exemple : | ||
+ | * Si le nombre d’emprunts est inférieur à 2, l’état est mis à Neuf ; | ||
+ | * Si le nombre d’emprunts est inférieur à 4, l’état est mis à Bon ; | ||
+ | * Si le nombre d’emprunts est inférieur à 6, l’état est mis à Moyen ; | ||
+ | * Sinon, l’état est mis à Mauvais ; | ||
+ | |||
+ | <code plsql> | ||
+ | CREATE OR REPLACE TRIGGER maj_etats | ||
+ | AFTER INSERT | ||
+ | ON BIB_EMPRUNT | ||
+ | FOR EACH ROW | ||
+ | |||
+ | DECLARE | ||
+ | n NUMBER; | ||
+ | |||
+ | BEGIN | ||
+ | SELECT COUNT(*) INTO n FROM BIB_EMPRUNT WHERE EXEMPLAIRE = : | ||
+ | IF n<2 THEN | ||
+ | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
+ | ELSE IF n<4 THEN | ||
+ | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
+ | ELSE IF n<6 THEN | ||
+ | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
+ | ELSE | ||
+ | UPDATE BIB_EXEMPLAIRE SET etat = ' | ||
+ | END IF; | ||
+ | END; | ||
+ | </ | ||
+ | |||
+ | |||
bases_de_donnees/oracle/pl_sql.txt · Dernière modification : 2022/02/02 00:42 de 127.0.0.1