Remerciez-le!

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

02/02/2023 1 Plan du cours – Partie 1 Langage plSQL Extension du langage (Env

02/02/2023 1 Plan du cours – Partie 1 Langage plSQL Extension du langage (Environnement Client-Serveur) Gestion des vues Gestion des procédures stockées Gestion des déclencheurs (Triggers) Gestion des transactions 02/02/2023 2 Plan du cours – Partie 2 Installation et configuration d’un serveur SQL (Pratique) Gestion des utilisateurs Stratégie de Sauvegarde et de restauration Importation et exportation des données 02/02/2023 3 PL/SQL • Pourquoi PL/SQL? • Variables, structures de contrôle, • Curseurs, interaction avec la base, • Sous-programmes, paquetages, • Exceptions, • Déclencheurs (triggers) 02/02/2023 4 Procédural Language/SQL • PL/SQL est un langage procédural qui intègre des ordres SQL, • Initialement typiquement Oracle ; apparaît dans la norme SQL3, • Il permet l’utilisation des blocs SQL et d’exprimer des règles de gestion complexes sous forme de procédures stockées et de triggers, • Il permet de lier plusieurs requêtes SQL avec des variables et dans des structures de programmation habituelles. 02/02/2023 5 Procédural Language/SQL • Programme PL/SQL = bloc (procédure anonyme, procédure nommée, fonction nommée) : • PL/SQL n’interprète pas une commande, mais un ensemble de commandes contenues dans un bloc PL/SQL. Ce bloc peut comporter plusieurs sous blocs, 02/02/2023 6 Procédural Language/SQL 02/02/2023 7 Structure d’un bloc PL/SQL DECLARE -- section de déclarations -- section optionnelle … BEGIN -- traitement, avec d’éventuelles directives SQL -- section obligatoire … EXCEPTION -- gestion des erreurs -- section optionnelle … END; 02/02/2023 8 Blocs imbriqués -- BLOC A DECLARE BEGIN -- BLOC B DECLARE BEGIN -- BLOC C DECLARE BEGIN EXCEPTION END; EXCEPTION END; EXCEPTION END; 02/02/2023 9 Les variables PL/SQL Types de variables PL/SQL : • Scalaires : par exemple NUMBER (5,2), VARCHAR2, DATE, BOOLEAN, CHAR, <Attribut>%TYPE… • Composites : %ROWTYPE, RECORD, TABLE Remarques : • Jusqu’à 30 caractères, • Insensible à la casse ! , 02/02/2023 10 Les variables PL/SQL • On peut documenter un code PL/SQL. Les différentes façons d’introduire des commentaires : – -- Commentaire sur une seule ligne – /* Commentaire sur plusieurs lignes */ • Toute variable PL/SQL doit obligatoirement être déclarée avant utilisation. Cette déclaration s’effectue dans la zone DECLRAE. 02/02/2023 11 Les variables PL/SQL nomvariable [CONSTANT] {type | variable%TYPE | table.%ROWTYPE } [NOT NULL] [{:= | DEFAULT } expression PL/SQL] Exemples de déclaration : vNumCli NUMBER(5) ; vNomCli VARCHAR2(12) ; vDateCde DATE ; vPoids NUMBER(5,2) ; vSexe CHAR(1) ; 02/02/2023 12 Les variables PL/SQL vNomClient clients.clinom%TYPE; vAdresseClient clients.cliadr%TYPE; Déclaration %ROWTYPE PL/SQL permet de déclarer une variable composite de même type que les tuples d’une table. vLignesClients clients%ROWTYPE; Les composantes de la variable composite, identifiées par vLignesClients.nomColonne, sont du même type que les colonnes correspondantes de la table. 02/02/2023 13 Affectation des valeurs aux variables PL/SQL On peut affecter des valeurs aux variables PL/SQL soit : • En utilisant l’opérateur d’assignation := vAge := SYSDATE – vDateNaissance ; vNomClient := UPPER ('Nom') ; • A partir d’un ordre SELECT … SELECT clinom, cliadresse INTO vNom, vAdresse FROM clients WHERE clinum = 1200 ; … 02/02/2023 14 Affectation des valeurs aux variables PL/SQL Remarque: L’ordre SELECT doit retourner une seule ligne, dans le cas contraire une erreur est générée. 02/02/2023 15 Les Collections et les enregistrements • Une collection est un ensemble ordonné d'éléments de même type. • Elle est indexée par une valeur de type numérique ou alphanumérique • Un enregistrement est composé de champs qui peuvent être de type différents. Il est similaire à une structure C. Variables RECORD Alors que la directive %ROWTYPE permet de déclarer une structure composée de colonnes de tables, elle ne convient pas à des structures de données personnalisées. 02/02/2023 16 Les Collections et les enregistrements • La syntaxe générale pour déclarer un RECORD est la suivante TYPE nomRecord IS RECORD ( nomChamp typeDonnées [[NOT NULL] {:= | DEFAULT} expression] [,nomChamp typeDonnées… ]… ); L’exemple suivant décrit l’utilisation d’un record DECLARE --Déclaration du RECORD contenant trois champs ; initialisation du champ job par défaut. TYPE emp_rec IS RECORD ( numEmp NUMBER(5), nomEmp VARCHAR2(12), foncEmp VARCHAR2(125) := ‘ANALYST', ); 02/02/2023 17 Les Collections et les enregistrements --Déclaration de deux variables de type RECORD. rec1_emp emp_rec; rec2_emp emp_rec; BEGIN --Initialisation des champs d’un RECORD. rec1_emp.empno := 7788; rec1_emp.ename := ‘SCOTT'; --Affectation d’un RECORD. rec2_emp := rec1_emp; END; 02/02/2023 18 Les Collections et les enregistrements Variables tableaux (type TABLE) • Taille dynamique • Déclaration d'une collection de type index by TYPE nom type IS TABLE OF type élément [NOT NULL] INDEX BY index_by_type ; index_by_type représente l'un des types suivants : BINARY_INTEGER PLS_INTEGER(9i) VARCHAR2(taille) LONG 02/02/2023 19 Les Collections et les enregistrements • Exemple d’un tableau de type TABLE DECLARE -- collection de type index by TYPE TAB_ENT IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ; tab TAB_ENT ; BEGIN FOR I IN 1..100 LOOP tab (I):= I; DBMS_OUTPUT.PUT_LINE(tab(I)); END LOOP ; END; / 02/02/2023 20 Les Collections et les enregistrements Les collections de type VARRAY • Ce type de collection possède une dimension maximale qui doit être précisée lors de sa déclaration. • Elle possède une longueur fixe et ses éléments sont numérotés à partir de la valeur 1 Déclaration d'une collection de type VARRAY TYPE nom type IS VARRAY (taille maximum) OF type élément [NOT NULL] ; 02/02/2023 21 Les Collections et les enregistrements Exemple d’un tableau de type VARRAY DECLARE TYPE TAB_VAR IS VARRAY(30) OF VARCHAR2(100) ; tab TAB_VAR := TAB_VAR('','','','','','','','','',''); BEGIN FOR I IN 1..10 LOOP tab(I):= TO_CHAR(I) ; DBMS_OUTPUT.PUT_LINE(tab(I)); END LOOP ; END; / 02/02/2023 22 Les Collections et les enregistrements • Fonctions pour les tableaux • PL/SQL propose un ensemble de fonctions qui permettent de manipuler des tableaux (également disponibles pour les nested tables et varrays). Ces fonctions sont les suivantes (les trois dernières sont des procédures) : • • EXISTS(x) Retourne TRUE si le xe élément du tableau existe. • COUNT Retourne le nombre d’éléments du tableau. • FIRST / LAST Retourne le premier/dernier indice du tableau (NULL si tableau vide). • PRIOR(x) / NEXT(x) Retourne l’élément avant/après le xe élément du tableau. • DELETE Supprime un ou plusieurs éléments au tableau. • DELETE(x) • DELETE(x,y) 02/02/2023 23 Structures de contrôle 1. Traitement conditionnel Syntaxe IF condition_1 THEN Instructions_1; ELSIF condition_2 Instructions_2; ELSE Instructions_3; END IF; Les opérateurs relationnels sont: =, <, >, !=, >=, <=, IS NULL, IS NOT NULL, BETWEEN, LIKE, AND, OR 02/02/2023 24 Structures de contrôle Le switch du langage C s’implémente en PL/SQL de la façon suivante : CASE /∗variable ∗/ WHEN /∗valeur 1 ∗/ THEN /∗instructions 1 WHEN /∗valeur 2 ∗/ THEN instructions 2 WHEN /∗valeur n ∗/ THEN /∗instructions n ∗/ ELSE /∗instructions par défaut ∗/ END CASE; 02/02/2023 25 Structures répétitives La boucle de base PL/SQL permet d’effectuer des traitements répétitifs grâce à la clause LOOP. BEGIN … LOOP Instructions END LOOP; … END; 02/02/2023 26 Affectation des valeurs aux variables PL/SQL Remarque : La boucle ci-dessus s’exécute indéfiniment, l’instruction EXIT permet de stopper l’exécution de la boucle LOOP. BEGIN … LOOP Instructions EXIT WHEN condition; END LOOP; … END; BEGIN … LOOP Instructions IF condition THEN EXIT; END IF; END LOOP; … END; 02/02/2023 27 Structures répétitives La boucle FOR : Syntaxe FOR compteur IN val_1..val_2 LOOP instructions; END LOOP; La boucle WHILE : Syntaxe WHILE condition LOOP instructions; END LOOP; 02/02/2023 28 Curseurs Un curseur est une zone mémoire de taille fixe capable de stocker plusieurs enregistrements et de gérer l’accès à ces enregistrements. Types de curseurs : – Curseurs implicites : Déclarés implicitement et manipulés par SQL pour toute requête SQL et pour les interrogations qui retournent un seul enregistrement 02/02/2023 29 Curseurs – Curseurs explicites : Déclarés et manipulés par l’utilisateur pour les interrogations qui retournent plus d’un enregistrement. Etapes d’utilisation d’un curseur : – Déclaration – Ouverture – Traitement des lignes – Fermeture 02/02/2023 30 Déclaration d’un curseur Syntaxe : CURSOR nom_curseur IS requête ; Cette déclaration se fait dans la section DECLARE. Exemple : DECLARE CURSOR c_clients IS SELECT clinum, clinom, cliadresse FROM clients; BEGIN … END; 02/02/2023 31 Déclaration d’un curseur L’ensemble de lignes renvoyées par une interrogation multi lignes s’appelle un ResultSet (jeu de résultats). 1100 1200 1300 VAUDAN HOMEYER BUELLET 31, Rue Tanger 75015 Paris 1, Av Foch 67000 Strasbourg 27, Rue Pierre Avia 75015 Paris curseur ResultSet 02/02/2023 32 Ouverture d’un curseur Elle permet : • L’allocation mémoire du curseur, • L’analyse syntaxique et sémantique de la requête, Elle se fait dans la section BEGIN. Syntaxe : OPEN c_clients ; 02/02/2023 33 Ouverture d’un curseur Exemple : DECLARE CURSOR c_clients IS SELECT clinum, clinom, cliadresse FROM clients; BEGIN OPEN c_clients; … END; 02/02/2023 34 Traitement des lignes Les lignes retournées par l’ordre SELECT sont traitées une par une. La valeur de chaque colonne doit être stockée dans une variable réceptrice. Syntaxe : FETCH c_salaries INTO variables_receptrices ; La clause FETCH ne récupère qu’un seul enregistrement. Pour accéder à l’ensemble des lignes de l’ordre SELECT, il faut prévoir une boucle. Le traitement des lignes se fait dans le corps du bloc PL/SQL. 02/02/2023 35 Exemple d’utilisation d’un curseur DECLARE vNumero empno.emp%TYPE; vSalaire sal.emp%TYPE; -- Déclaration du curseur CURSOR c_emp IS uploads/Management/ cours-plsqlnv.pdf