Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

07/01/2022 1 Cours PL/SQL Ines Bouzouita Ecole Nationale d’ingénieurs de Tunis

07/01/2022 1 Cours PL/SQL Ines Bouzouita Ecole Nationale d’ingénieurs de Tunis Section: 2ème année Ingénieurs 1 Plan 1. Généralités 2. Éléments de syntaxe PL/SQL 3. Les déclarations %type, %rowtype, Record, Table imbriquée, Varray 4. Les instructions Affectation, IF, WHILE, LOOP 5. Les curseurs 6. Procédures, fonctions et packages 7. Traitement des erreurs 8. Déclencheurs 9. Architecture PL/SQL dans Oracle 10. Conclusion: les avantages de PL/SQL Bibliographie Livres C. SOUTOU (2005) SQL pour Oracle, Eyrolles, Paris. Chapitre 6: PL/SQL ISBN 2- 212-11697-7 Support de cours ■Richard Grin, "Langage SQL", Université de Nice Sophia-Antipolis, 2008. 3 Pourquoi PL/SQL? Exemple : tout nouveau prix à insérer pour un CD DANS LA TABLE CD doit avoir un prix supérieur à celui des autres CD. • L’insertion, la suppression ou la mise à jour de certaines données peut nécessiter des calculs sur la base. • Utilisation de fonction propres à l’application dans des requêtes 1 2 3 4 07/01/2022 2 Intérêts – Opérationnalisation des SGBD-transactionnels: encapsulation de transactions dans des blocs PL/SQL – Programmes complexes travaillant sur les données de la base: les structures de contrôle classiques en programmation impérative (SQL se limite au LDD / LMD) – Modularité : possibilité de conserver/utiliser des procédures ou fonctions cataloguées dans des paquetages PL/SQL Intérêts • PL/SQL regroupe les requêtes SQL en un seul bloc qui est envoyé au serveur en un seul appel • PL/SQL améliore les performances (moins de communcations à travers le réseau) • C’est un langage portable : il peut fonctionner sur toute plateforme supportant Oracle Server • PL/SQL peut aussi coopérer avec les différents outils de développment d’application de Oracle Server (p.ex. Developer 2000) • Permet de créer des bibliothèques de code réutilisable 1- Généralités 1. PL/SQL est utilisé pour écrire des méthodes (fonctions et procédures) de type, triggers, packages, procédures et fonctions stockées dans Oracle. 2. D'autre part le langage PL/SQL permet de faire appel à des procédures externes, écrites dans un autre langage (généralement le langage C, Java). 3. Principe du langage PL/SQL  PL/SQL permet de définir un ensemble de commandes contenues dans un "bloc" PL/SQL.  Un bloc PL/SQL peut lui-même contenir des sous blocs et ainsi de suite.  La syntaxe PL/SQL est simple et lisible 5 6 7 8 07/01/2022 3 Bloc PL/SQL – Déclarations (variables, constantes, types, curseurs, etc...) – Code PL/SQL – Gestion des exceptions (erreurs) – Encapsulation possible de sous-blocs (Attention à la portée des variables) DECLARE -- declarations BEGIN -- code EXCEPTION -- code gestion erreurs END; DECLARE -- declarations BEGIN -- code EXCEPTION -- code gestion erreurs END; / 2- Eléments de syntaxe PL/SQL 3 Remarque: Slash à inclure à la ﬁn de vos blocs dans SQL*Plus . 2- Eléments de syntaxe PL/SQL La partie « Déclarations » Sert à définir les variables et les constantes utilisés dans le bloc Elle est optionnelle Elle est délimitée par DECLARE (qui spécifie le début) et BEGIN (qui signifie la fin) La partie « Commandes exécutables » et « Gestion des exceptions » constituent le corps du bloc PL/SQL. La partie « Commandes exécutables » est obligatoire La partie « Gestion des exceptions » est optionnelle. Ces 2 parties sont délimitées par les mot BEGIN, EXCEPTION et END Exemple BEGIN DBMS_OUTPUT.PUT_LINE('hello') ; END ; / Exemple Declare nom_emp char(15); salaire emp.sal%TYPE commission emp.comm%TYPE; nom_départ char(15); Begin Select ename, sal, comm, dname Into nom_emp, salaire, commission, nom_départ From emp, dept Where ename = ‘Mohamed’ and emp.deptno = dept.deptno … End; 9 10 11 12 07/01/2022 4 2- Eléments de syntaxe PL/SQL Une ligne de commentaire commence par le double signe -- et se termine à la fin de la ligne. Les commentaires sur plusieurs lignes sont délimités par les caractères /* */ (comme en C) -- Ceci est un commentaire sur une ligne /* Ceci est un commentaire qui tient sur plusieurs lignes comme dans les langages C et Java */ Les déclarations • Variables : types – scalaires SQL : DATE, VARCHAR(n), NUMBER... – PL/SQL simples : sous-types SQL (norme ISO) : INTEGER, INT, POSITIVE... – PL/SQL composites : %TYPE, %ROWTYPE, RECORD Variables de type Simple Declare nom char(15); salaire number(7,2); embauche DATE ; réponse boolean; Exemple de variables nom VARCHAR2(30); pi REAL := 3.14159; radius REAL := 1; area REAL := pi * radius**2; blood_type CHAR DEFAULT ’O’; acct_id INTEGER(4) NOT NULL := 9999; num_client VARCHAR2(5) NOT NULL DEFAULT 'AJ235‘ Déclaration de constantes Analogue à la déclaration de variables avec ajout du mot-clé CONSTANT Pi CONSTANT NUMBER := 3.14159; Variables de type Simple 13 14 15 16 07/01/2022 5 3- Les déclarations (suite) DECLARE - si dans un bloc, une A CHAR; B REAL; variable du même nom que BEGIN la variable GLOBALE existe /* A (CHAR) B (REAL) */ c'est la variable LOCALE DECLARE qui s'impose A INTEGER; C REAL; A: INTEGER BEGIN B,C: REAL …. END; un bloc ne peut utiliser les DECLARE D CHAR; variables d'un autre bloc de BEGIN A,D: CHAR même niveau B: REAL END; -- A ce niveau A: CHAR, B: REAL END; Variables : types – scalaires SQL : DATE, VARCHAR(n), NUMBER... – PL/SQL simples : sous-types SQL (norme ISO) : INTEGER, INT, POSITIVE... – PL/SQL composites : %TYPE, %ROWTYPE, RECORD – Paramètres d’invocation en SQL*Plus Variables : declaration Id_var type [NOT NULL] [ := | DEFAULT expression init]; Constantes : declaration Id_cte CONSTANT type [ := | DEFAULT expression init]; Exemples DECLARE c_euro CONSTANT NUMBER := 6.569; prix INT; /* prix initialisé à NULL */ date_courante DATE DEFAULT SYSDATE; 3. Les déclarations 4 Remarques - Identificateur : doit tjs commencer par une lettre en PL/SQL, ensuite on fait ce qu’on veut - NOT NULL : impose une contrainte de renseignement sur la variable. - Remarquer la forme de l’affectation en PL/SQL ( := ) alors qu’en SQL c’est ( = ) - Remarque la seconde forme de commentaires (/* …. */) équivalente à celle de C - Dans la déclaration, := et DEFAULT ont rigoureusement le même sens. • ATTENTION : il n’est pas possible de déclarer 3 variables à la suite dans une même directive. Une par une! Exemple declare Nb number; BEGIN Nb := 10; DBMS_OUTPUT.PUT_LINE(Nb); END; / 17 18 19 20 07/01/2022 6 Type %TYPE –Rattachement du type à celle d’un attribut de table ou vue id_variable id_table.id_attribut%TYPE –Rattachement du type de la variable à une autre variable déjà déclarée id_new_var id_definitevar%TYPE Les variables de type composite 5 Type %TYPE 1. Le type dynamique %type permet d'associer à une variable le type d'une autre variable, d'une constante ou d'une colonne de table  Intérêt: permet de ne pas avoir à modifier les programmes si on modifie le type  Exemple: DECLARE credit REAL(7,2); debit credit %type; 2. Le type dynamique %type permet de faire référence à une colonne d'une table  Exemple: On a dans la BD, la table : EMPLOYE (empno NUMBER, …)  On peut écrire un programme PL/SQL déclarant: DECLARE numéro Employé.empno %type --- la variable numéro a comme domaine de valeur NUMBER Application Écrire un programme PL/SQL qui permet d'afficher le nom de l'employé dont le NAS=123456789. Exemple DECLARE v1 Employé.NOM%Type ; BEGIN SELECT Nom INTO v1 FROM Employé WHERE NAS=123456789 ; DBMS_OUTPUT.PUT_LINE(v1) ; END ; / 21 22 23 24 07/01/2022 7 Type enregistrement : %ROWTYPE Description d’un enregistrement unique complet déclaration id_variable id_table%ROWTYPE utilisation id.variable.un_des_champs Exemple DECLARE c_euro CONSTANT NUMBER := 6.569; prix_euro Table_produits.Prix %TYPE ; prix_fr prix_euro %TYPE := prix_euro * c_euro ; produits Table_produits %ROWTYPE; BEGIN SELECT * INTO produits from Table_Produits WHERE id=1; prix_euro := produits.prix; %ROWTYPE correspond à un enregistrement unique Type enregistrement : %ROWTYPE Le type dynamique %rowtype permet d'associer à une variable un type composite correspondant :  à un tuple d'une table  ou au type composite d'un curseur (voir plus loin) Exemple: On a dans la BD un type:  create type EMPLOYE_typ (empno NUMBER, nom VARCHAR2(30), sal NUMBER)  create table Employe_tab (employe Employe_typ, age NUMBER)  Code PL-SQL: DECLARE emp_rec Employe_typ %ROWTYPE; ... BEGIN emp_rec.empno … emp_rec.nom… • Écrire un programme PL/SQL qui permet d'afficher le nom, le prénom et le salaire de l'employé dont le NAS=123456789. Exemple DECLARE emp Employé%RowType ; BEGIN SELECT * INTO emp FROM Employé WHERE NAS=123456789 ; DBMS_OUTPUT.PUT_LINE('Nom =' ||emp.Nom|| 'Prénom ='||emp.Prénom|| 'Salaire = '||emp.Salaire) ; END ; / 25 26 27 28 07/01/2022 8 Type personnalisés – RECORD : structure de donnée personnalisée TYPE nom_record IS RECORD (decl_chp1, decl_chp2 …); où decl_chp reprend la syntaxe classique d’une déclaration de variable. – TABLE : tableaux dynamiques (sans dimension prédéfinie) TYPE nom_array IS TABLE OF { type_sple | var%TYPE | table.att%TYPE | table.%ROWTYPE } DECLARE TYPE Type_sondageIS TABLE OF FLOAT; TYPE Type_vol is RECORD (num VARCHAR(10), depart DATE, arrivee DATE); sondage_ifop Type_sondages; vol_particulier Type_vol; BEGIN sondage_ifop(0):= 0.5; vol_particulier.num := ‘AF4563’; vol_particulier.depart := to_date(’12/01/2007’); vol_particulier.arrivee := to_date(’13/01/2007’); END; 3. Les déclarations 6 Type record Un record permet de définir des types composites (tuple): Exemple DECLARE TYPE AnEntry IS RECORD ( term VARCHAR2(20), meaning VARCHAR2(200)); TYPE DeptRec IS RECORD ( dept_id dept.deptno%TYPE, dept_name VARCHAR2(14), uploads/Management/ cours-plsql-enit.pdf