Remerciez-le!

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

M. Tondeur Hervé PL/SQL Oracle Introduction a Oracle PL/SQL PL/SQL (pour PROCED

M. Tondeur Hervé PL/SQL Oracle Introduction a Oracle PL/SQL PL/SQL (pour PROCEDURAL LANGUAGE/SQL) est un langage procédural d’Oracle corporation étendant SQL. Il permet de combiner les avantages d’un langage de programmation classique avec les possibilités de manipulation de données offertes par SQL. Avantages : – La modularité : Un bloc peut être nommé pour devenir une procédure ou une fonction catalogué, donc réutilisable. Une fonction ou procédure cataloguée peut être incluse dans un paquetage. – La portabilité : Un programme PL/SQL est indépendant du système d'exploitation qui héberge le serveur Oracle. En changeant de système, les applicatifs n'ont pas à être modifiés. – L'intégration avec les données des tables : On retrouvera avec PL/SQL tous les types de données et instructions disponibles sous SQL. 1 Structure d’un programme PL/SQL La structure de base d’un programme PL/SQL est celle de bloc (possiblement imbriqué). Il a généralement la forme suivante: DECLARE /* section de déclaration */ BEGIN /* corps du bloc de programme Il s’agit de la seule zone dont la présence est obligatoire */ EXCEPTION /* gestion des exceptions */ END; Le corps du programme (entre le BEGIN et le END) contient des instructions PL/SQL (assignements, boucles, appel de procédure) ainsi que des instructions SQL. Il s’agit de la seule partie qui soit obligatoire. Les deux autres zones, dites zone de déclaration et zone de gestion des exceptions sont facultatives. Les seuls ordres SQL que l’on peut trouver dans un bloc PL/SQL sont: SELECT, INSERT, UPDATE, DELETE. Les autres types d’instructions (par exemple CREATE, DROP, ALTER) ne peuvent se trouver qu’a l’extérieur d’un tel bloc. Chaque instruction se termine par un “;”. Le PL/SQL ne se soucie pas de la casse (majuscule vs. minuscule). On peut inclure des commentaires par - - (en début de chaque ligne commentée) ou par /*.... */ (pour délimiter des blocs de commentaires). 2 Variables et types Un nom de variable en PL/SQL comporte au plus 30 caractères. Toutes les variables ont un type On trouve trois sortes de types de variables en PL/SQL. A savoir : • un des types utilisés en SQL pour les colonnes de tables. • un type particulier au PL/SQL. • un type faisant référence à celui d’une (suite de) colonne(s) d’une table. Les types autorisés dans PL/SQL sont nombreux. Page 1/16 M. Tondeur Hervé PL/SQL Oracle On retiendra principalement: – Pour les types numériques : REAL, INTEGER, NUMBER (précision de 38 chiffres par défaut), NUMBER(x) (nombres avec x chiffres de précision), NUMBER(x,y) (nombres avec x chiffres de précision dont y après la virgule). – Pour les types alphanumériques : CHAR(x) (chaîne de caractère de longueur fixe x), VARCHAR(x) (chaîne de caractère de longueur variable jusqu’à x), VARCHAR2 (idem que précédent excepté que ce type supporte de plus longues chaînes et que l’on est pas obligé de spécifier sa longueur maximale). – Pour les types Dates : PL/SQL permet aussi de manipuler des dates (type DATE) sous différents formats. Une déclaration pourrait donc contenir les informations suivantes : DECLARE n NUMBER; mot VARCHAR(20) mot2 VARCHAR2 Affectation : Il existe plusieurs possibilités pour affecter une valeur à une variable : • L'affectation variable := expression • Par la directive DEFAULT • Par la directive INTO d'une requête (SELECT .... INTO variable FROM....) Exemple : DECLARE v_brevet VARCHAR2(6); v_brevet2 VARCHAR2(6); v_prime NUMBER(5,2); v_naissance DATE; v_trouvé BOOLEAN NOT NULL DEFAULT FALSE; BEGIN v_brevet := 'PL-1'; v_brevet2 := v_brevet; v_prime := 500.50; v_naissance := '04-07-1971'; v_trouvé := TRUE; SELECT brevet INTO v_brevet FROM pilote WHERE nom = 'Tondeur Hervé'; END; Une autre spécificité du PL/SQL est qu’il permet d’assigner comme type à une variable celui d’un champs d’une table (par l’opérateur %TYPE) ou d’une ligne entière (opérateur %ROWTYPE). %TYPE : déclare une variable selon la définition d'une colonne d'une table ou d'une vue existante. Elle permet de déclarer une variable conformément à une autre variable précédemment déclarée. %ROWTYPE : Permet de travailler au niveau d'un enregistrement. Ce dernier est composé d'un Page 2/16 M. Tondeur Hervé PL/SQL Oracle ensemble de colonnes. L'enregistrement peut contenir toutes les colonnes d'une table ou seulement certaines. Dans la déclaration suivante : DECLARE nom emp.nom%TYPE; employe emp%ROWTYPE; La variable nom est définie comme étant du type de la colonne nom de la table emp (qui doit exister au préalable). De même, employé est un vecteur du type d’une ligne de la table emp. A supposer que cette dernière ait trois champs numéro, nom, age de type respectifs NUMBER, VARCHAR, INTEGER, la variable employe disposera de trois composantes : employe.numero, employe.nom, employe.age, de même types que ceux de la table. Un premier petit programme (noter au passage l’instruction d’affectation ”a:=a+b”) : DECLARE a NUMBER; b NUMBER; BEGIN a:=a+b; END; Un deuxième petit programme (incluant une requête SQL) : DECLARE a emp.numero%TYPE; BEGIN SELECT numero INTO a FROM emp WHERE noemp=’21’; END; Ce dernier exemple donne comme valeur à la variable a le résultat de la requête (qui doit être du même type). Il est impératif que la requête ne renvoie qu’un et un seul résultat (c’est à dire qu’il n’y ait qu’un seul employé numéro 21). Autrement, une erreur se produit. Les variables RECORD : Ce sont des structures de données personnalisées. Nb : il peuvent contenir des LOB ou des extensions objets (REF, TABLE ou VARRAY). TYPE NomRecord IS RECORD (NomChamp TypeDonées [NOT NULL] {:= | DEFAULT} expression], .............); Exemple : DECLARE TYPE AvionAirBus_rec IS RECORD ( nserie CHAR(10), nomAvion CHAR(20), usine CHAR(10) := 'Blagnac', nbHVol NUMBER(7,2)); Page 3/16 M. Tondeur Hervé PL/SQL Oracle r_UnA320 avionAirBus_rec; BEGIN r_UnA320.nserie := 'A1'; r_UnA320.nomAvion := 'A320-200'; r_UnA320.nbHVol := 2500.60; END; Les variables tableaux : Permettent de définir et manipuler des types tableaux dynamiques, défini sans dimension initiale. Un type tableau est composé d'une clé primaire (de type BINARY_INTEGER) et d'une colonne (de type scalaire, TYPE, ROWTYPE ou RECORD) pour stocker chaque élément. TYPE nomTypeTableau IS TABLE OF {typeScalaire | variable%TYPE | table.colonne%TYPE} [NOT NULL] | table%ROWTYPE [INDEX BY BINARY_INTEGER]; Exemple : DECLARE TYPE brevet_typetab IS TABLE OF VARCHAR(6) INDEX BY BINARY_INTEGER; TYPE nompilote_typetab IS TABLE OF Pilote.nom%TYPE INDEX BY BINARY_INTEGER; TYPE pilotes_typetab IS TABLE OF Pilote%ROWTYPE INDEX BY BINARY_INTEGER; tab_brevets brevet_typetab; tab_nompilote nompilote_typetab; tab_pilotes pilotes_typetab; BEGIN tab_brevets(1) := 'PL-1'; tab_brevets(2) := 'PL-2'; tab_nompilote(7800) := 'Hervé'; tab_pilotes(0).brevet := 'PL-0'; END; Quelques fonction pour les tableaux : EXISTS(x) Retourne TRUE si le Xieme é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 vide). PRIOR(x)/NEXT(x) Retourne l'élément avant/après le X ieme élément du tableau. DELETE/DELETE(x)/DELETE(x,y) Supprime un ou plusieurs éléments du tableau. Les variables de substitution : Il est possible de passer en paramètres d'entrée d'un bloc PL/SQL des variables définies sous SQL*PLUS. On accède aux valeurs d'une telle variable dans le code PL/SQL en faisant préfixer le nom de la variable par un « & ». La directive ACCEPT permet de faire une lecture de la variable au clavier. Page 4/16 M. Tondeur Hervé PL/SQL Oracle Exemple : ACCEPT s_brevet PROMPT 'Entrer code Brevet : ' ACCEPT s_duréeVol PROMPT 'Entrer durée du Vol : ' DECLARE v_nom Pilote.nom%TYPE; v_nbHVol Pilote.nbHVol%TYPE; BEGIN SELECT nom, nbHVol INTO v_nom, v_nbHVol FROM Pilote WHERE brevet = '&s_brevet'; v_nbHVol := v_nbHVol + &s_duréeVol; DBMSOUTPUT.PUT_LINE('Total heures vol : ' || v_nbHVol || ' de ' || v_nom); END; Les variables de session : Il est possible de définir des variables de session globales définies sous SQL*PLUS au niveau d'un bloc PL/SQL. La directive à utiliser est VARIABLE, dans le code PL/SQL il faut faire préfixer le nom de la variable par « : ». L'affichage de la variable sous SQL*PLUS est réalisé par la directive PRINT. Exemple : VARIABLE g_compteur NUMBER; DECLARE v_compteur NUMBER(3) := 99; BEGIN :g_compteur := v_compteur + 1; END; PRINT g_compteur; 3 Opérateurs PL/SQL supporte les opérateurs suivants : • Arithmétique : +, - *, /, ** (exponentielle) • Concaténation : || • Parenthèses (contrôle des priorités entre opérations): () • Affectation: := • Comparaison : =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN • Logique : AND, OR, NOT 4 Structures de contrôle Comme n’importe quel langage procédural, PL/SQL possède un certain nombre de structures de contrôles évoluées comme les branchements conditionnels et les boucles. 4.1 Les branchements conditionnels Syntaxe de la structure IF : IF <condition> THEN commandes; Page 5/16 M. Tondeur Hervé PL/SQL Oracle [ ELSEIF <condition> THEN commandes; ] [ ELSE commandes; ] END IF; Un petit exemple : IF nomEmploye=’TOTO’ THEN salaire:=salaire*2; ELSEIF salaire>10000 THEN salaire:=salaire/2; ELSE salaire:=salaire*3; END IF; Syntaxe de la structure CASE : [<<étiquette>>] CASE <variable> WHEN <expr1> THEN instructions1; WHEN <expr2> THEN instructions2; .... [ELSE instructionsN+1;] END CASE [étiquette]; Exemple : CASE WHEN v_note>=16 THEN v_mention := 'TB'; WHEN v_note>=10 THEN v_mention := 'P'; ELSE v_mention := 'R'; END CASE 4.2 boucles PL/SQL admet trois sortes de boucles. La première est une boucle potentiellement infinie : LOOP commandes; END LOOP; Au moins une des instructions du corps de la boucle doit être une instruction de sortie : EXIT WHEN <condition>; Dès que la condition devient uploads/s1/ introduction-pl-sql-pdf.pdf