Remerciez-le!

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

Bases de données 2 (SQL, PL/SQL-ORACLE) EMSI-Rabat 3IIR 2022-2023 Equipe pédago

Bases de données 2 (SQL, PL/SQL-ORACLE) EMSI-Rabat 3IIR 2022-2023 Equipe pédagogique Prof. Mahmoud NASSAR Prof. Mohammed SALIHOUN Prof. Maria EL HAIBA m.elhaiba@emsi.ma Partie 2 : PL/SQL FOR ORACLE 1. Concepts de base de PL/SQL 2. Transactions & Curseurs 3. Gestion des exceptions, Fonctions, Procédures et Packages 4. Déclencheurs (Triggers) 5. Travaux Pratiques ORACLE PL/SQL FOR ORACLE Concepts de base PL/SQL Concept de base PL/SQL 1. MOTIV ATION 2. STRUCTURE D’UN BLOC PL/SQL 3. LES V ARIABLES 4. LES ENREGISTREMENTS 5. ASSIGNATION DES V ARIABLES ET AFFECTATION 6. STRUCTURES DE CONTRÔLE BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE MOTIVATION PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL PL/SQL (Sigle de Procedural Language / Structured Query Language) est un langage créé par Oracle et utilisé dans le cadre de bases de données relationnelles. Il est fondé sur les paradigmes de programmation procédurale et structurée. C’est une extension de SQL car il permet de combiner des requêtes SQL et des instructions procédurales (boucles, conditions...), dans le but de créer des traitements complexes destinés à être stockés sur le serveur de base de données (objets serveur), comme des procédures stockées ou des déclencheurs. BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Pour le Fournisseur N° 1 • Si Moyenne (Prix) > 1500 • Augmenter le prix de 5% • Sinn • Augmenter le prix de 3% Procédure : IF…….THEN ELSEIF ELSE END IF ID_Produit Libelle Marque Prix ID_Fournisseur P1 Epson40 Epson 2300 1 P2 HP300 HP 1000 1 P3 IBM1234 IBM 5600 2 P4 Lenovo320 Lenovo 2000 3 UPDATE Produit SET Prix =2500 WHERE ID_Fournisseur =1; Code PL/SQL Exemple : BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL vs SQL PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE Noyau PL/SQL PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE Prise en charge de SQL (Types de données, instructions, …) Prise en charge de la programmation orientée objet Meilleure performance (Bloc PL/SQL évitant d’encombrer le trafic réseau) Une productivité accrue (Un bloc est nommé pour devenir réutilisable) La portabilité (Un programme PL/SQL est indépendant du SE qui héberge le serveur) L'intégration très forte avec Oracle (PL/SQL est un langage propriétaire d’Oracle) Haute sécurité Avantages de PL / SQL PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE STRUCTURE D’UN BLOC PL/SQL PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE DECLARE --Section optionnelle Déclaration variables, constantes, types, curseurs,... BEGIN --Section obligatoire Contient le code PL/SQL EXCEPTION --Section optionnelle Traitement des erreurs END; --Obligatoire PL/SQL Structure d’un Bloc PL/SQL IL PEUX Y AVOIR DES BLOCS IMBRIQUÉES (UN BLOC DANS UN AUTRE). REMARQUE BASES DE DONNEES RELATIONNELLES ORACLE DECLARE --Section optionnelle Déclaration variables, constantes, types, curseurs,... BEGIN --Section obligatoire Contient le code PL/SQL DECLARE--Section optionnelle déclaration variables,constantes,types, curseurs,... BEGIN --Section obligatoire contient le code PL/SQL EXCEPTION --Section optionnelle traitement des erreurs END; --Obligatoire EXCEPTION --section optionnelle Traitement des erreurs END; --Obligatoire LA PORTEE DES VARIABLES EST LA MEME QUE DANS LES LANGAGES DE PROGRAMMATION REMARQUE PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE • Un bloc PL/SQL peut contenir :  Toute instruction du LMD (SELECT, INSERT, UPDATE, DELETE) • Chaque instruction se termine par ; • Les blocs peuvent être imbriqués :  Les sous-blocs ont la même structure que les blocs.  Une variable est visible dans le bloc où elle est déclarée et dans tous ses sous-blocs.  Si une variable est déclarée dans un premier bloc et aussi dans un sous-bloc, la variable du bloc supérieur n’est plus visible dans le sous-bloc. • Les commentairessont possibles : Sur plusieurs lignes avec : /*Mes commentaires*/ Sur une ligne précédée de : --Commentaire PL/SQL Synthèse d’un Bloc PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE LES VARIABLES PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE Nom variable Représente le nom de la variable composé de lettres, chiffres, $, _ ou # Le nom de la variable ne peut pas excéder 30 caractères CONSTANT Indique que la valeur ne pourra pas être modifiée dans le code du bloc PL/SQL NOT NULL Indique que la variable ne peut pas être NULL, et dans ce cas expressiondoit être indiqué. Type Représente le type de la variable correspondant à l'un des types suivants : Nom variable [CONSTANT] Type [ [NOT NULL] [:= expression | DEFAULT expression ] ; Remarque Si une variable est déclarée avec l’option CONSTANTE, elle doit être initialisée Si une variable est déclarée avec l’option NOT NULL, elle doit être initialisée PL/SQL Syntaxe de déclaration BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL TYPE SEMANTIQUE NUMBER[(e,d)] Nombre réel avec e chiffres significatifs stockés et d décimales PLS_INTEGER Nombre entier compris entre -2 147 483 647 et +2 147 483 647 CHAR [(n)] Chaîne de caractères de longueur fixe avec n compris entre 1 et 32767 (par défaut 1) VARCHAR2[(n)] Chaîne de caractères de longueur variable avec n compris entre 1 et 32767 BOOLEAN Ex. True ou False ou Null DATE Date (Ex. 01/01/1996 ou 01-01-1996 ou 01-JAN-96…) RAW[[n)] Chaîne de caractères ou données binaires de longueur variable avec n compris entre 1 et 32767. Le contenu d'une variable de ce type n'est pas interprété par PL/SQL LONG RAW Identique au type LONG qui peut contenir des données binaires LONG Chaîne de caractères de longueur variable avec au maximum 32760 octets ROWID Permet de stocker l'adresse absolue d'une ligne dans une table sous la forme d'une chaîne de caractères Exemples de types de variables PL/SQL Se référer au site : PL/SQL Data Types BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL • var1 varchar2(20); • var2 varchar2(30) := 'exemple d’affectation' ; • VarCons CONSTANT numeric(8,1) := 1234567.8; • MA_LIMITE_BUDGET CONSTANT REAL := 5000.00; • v_valide Boolean NOT NULL := TRUE; Exemples : Nom variable [CONSTANT] Type [ [NOT NULL] [:= expression | DEFAULT expression ] ; Rappel : Déclaration d’une variable BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Exemple: SUBTYPE nom_employeIS VARCHAR2(20) NOT NULL; Nom nom_employe; -- création d’une variable « Nom » de type « nom_employe » SUBTYPE nom_sous_type IS TYPE ; --à partir de la définition d’un sous type. nom_variable nom_table%ROWTYPE; --par référence à une structure de table nom_variable nom_table.nom_colonne%TYPE; -- à partir de la définition d’une colonne de la BD nom_variable nom_variable_ref%TYPE; --à partir d’une autre variable déclarée précédemment Exemple: Nom E_EMPLOYE.NOM%TYPE; -- création d’une variable du type du NOM de la table E_EMPLOYE Dat_COM DATE; Dat_LIV Dat_COM%TYPE; -- création d’une variable du même type que celui de « Dat_COM» Exemple: EMPLOYE E_EMPLOYE%ROWTYPE; -- création d’une variable table du même type que E_EMPLOYE BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL LES ENREGISTREMENTS BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL TYPE nom_type_rec IS RECORD ( nom_champ1 type_élément1 [[ NOT NULL] := expression ], nom_champ2 type_élément2 [[ NOT NULL] := expression ], … nom_champN type_élémentN[[ NOT NULL] := expression ] ) ; Nom_variable nom_type_rec ; Exemple: TYPE T_REC_EMP IS RECORD ( Num E_EMPLOYE.NO%TYPE, Nom E_EMPLOYE.NOM%TYPE, Pre E_EMPLOYE.PRENOM%TYPE ); EMP T_REC_EMP ; Accès: EMP.Num EMP.Nom EMP.Pre BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL ASSIGNATION DES VARIABLES (AFFECTATION) BASES DE DONNEES RELATIONNELLES ORACLE L’assignation ou l’affectation des variables peut se faire soit : PL/SQL Affectation des variables Dans le bloc PL/SQL A partir du clavier A partir d’une BD BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Nom := 'Mohamed'; MON_NUM:= 10 ; MA_CHAINE := 'Chaîne de caractères' ; MA_TAXE :=PRIX*TAUX; BONUS := SALAIRE * 0.10; MA_DATE:=‘12/12/2012’; MA_DATE:=to_date('01/01/2017', 'DD/MM/YYYY') ; MON_BOOLEAN := FALSE; MON_BOOLEAN := (NOM='toto'); VARIABLE := EXPRESSION • Lors de la déclaration • Dans le bloc PL/SQL BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Exemple : SET SERVEROUTPUT ON --Préparer le serveur pour l’affichage DECLARE –Déclaration d’une variable Variable VARCHAR2(10); BEGIN –Début du bloc Variable:= 'Bonjour'; DBMS_OUTPUT.PUT_LINE(Variable); END; --Fin du bloc / --A ajouter obligatoirement pour l’exécution du script Affichage des Variables Exécution : Application : Reprenez un même exemple en affichant une variable de votre choix. BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Il est possible d’utiliser des fonctions prédéfinies d’entrée et de sortie. Ces fonctions servent essentiellement durant la phase de test des procédures PL/SQL. Pour entrer une valeur : ACCEPT msg PROMPT ' Texte à afficher ' Pour récupérer la valeur de la variable saisie : Ajouter un & en préfixe à son nom Pour afficher une valeur : DBMS_OUTPUT.PUT_LINE(message); Concaténation de chaînes et variables : Utiliser l’Opérateur || Exemple : ACCEPTmsg PROMPT ' Entrer votre message : ' DECLARE message VARCHAR2(20) :='&msg'; BEGIN DBMS_OUTPUT.PUT_LINE ('Test : ' || message); END; / • A partir du clavier Exécution : BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL --Partie des messages pour inviter l'utilisateur à saisir des données prompt CREATION D'UN NOUVEAU CLIENT prompt ENTREZ LES DONNEES DU NOUVEAU CLIENT : ACCEPTL_NO prompt ‘NUMERO : ' prompt ACCEPT L_NOM prompt 'NOM : ' prompt ACCEPT L_SAL prompt 'SALAIRE : ' prompt ACCEPT L_DATE prompt 'DATE (dd/mm/yyyy) : ' prompt --Partie de déclaration, affectation et affichage DECLARE NO NUMBER(4):=&L_NO; NOM VARCHAR2(20):='&L_NOM'; SAL NUMBER(10,2):=&L_SAL; DT_REC DATE:='&L_DATE'; BEGIN DBMS_OUTPUT.PUT_LINE ('NUMERO: ' || NO); DBMS_OUTPUT.PUT_LINE ('NOM: ' || NOM); DBMS_OUTPUT.PUT_LINE ('SALAIRE: ' || SAL); DBMS_OUTPUT.PUT_LINE ('DATE: ' || DT_REC); END; / Exemple 1: BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL SQL> --Partie des messages pour inviter l'utilisateur à saisir des données SQL> prompt CREATION D'UN NOUVEAU CLIENT CREATION D'UN NOUVEAU CLIENT uploads/Management/ pl-sql-chapitre-1-concepts-de-base.pdf