LE PL SQL partie 1

PARTIE 1 - LE LANGAGE PROCEDURAL DORACLE:LE LANGAGE PL/SQLI INTRODUCTIONLe PL/SQL est le langage procdural dORACLE, cest une extension du langage SQL qui est un langage ensembliste. PL/SQL = Procdural Language / SQLLintrt du PL/SQL est de pouvoir dans un mme traitement allier la puissance des instructions SQL et la souplesse dun langage procdural.Le fonctionnement de PL/SQL est bas sur linterprtation dun bloc de commandes. Ce mode de fonctionnement permet dobtenir des gains de transmission et des gains de performances :Dans lenvironnement SQL, les ordres du langage sont transmis et excuts les uns la suite des autresDans lenvironnement PL/SQL; les ordres SQL et PL/SQL sont regroups en BLOCs; un bloc ne demande quun seul transfert et une seule excution de lensemble des commandes contenues dans le bloc.II LE BLOC PL/SQLPL/Sql ninterprte pas une commande, mais un ensemble de commandes contenu dans un programme ou bloc PL/Sql.Un bloc est compos de trois sections :DECLAREDclarations des variables locales au bloc, constantes, exceptions, curseurs section facultativeBEGIN Commandes xcutables: Instructions PL/SQL et SQL. Possibilits de blocs imbriqussection obligatoireEXCEPTIONTraitement des erreurssection facultativeEND; ou END nom_bloc;Chaque instruction de nimporte quelle section doit se terminer par un ;.Possibilit de placer des commentaires : - commentaire sur une ligneou /* commentaire sur plusieurs lignes */EXEMPLE sous SQL*PLUS de Personnal Oracle 7 Ecriture du programme PL/SQL sous le bloc notes nomm PLSQL_EX1.sqlDECLARE- Dbut du programme sal_emp number(7,2);- variable locale au blocBEGIN /* Slectionner le salaire de lemploy saisi au pralable dans SQL*PLUS (num_emp) , laugmenter de 10% si ce salaire est infrieur 1000 */ SELECT sal into sal_emp FROM empwhere empno = &num_emp; If sal_emp select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 17/12/80 800 20 7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30. Sous SQLPLUS dfinir la variable num_empSQL define num_emp=7369ouSQL PROMPT Numro du salari dsir ? - ou crire ces 2 lignes directementSQL ACCEPT num_emp - dans le programme PL/SQl avant DECLARE Appel du programme Plsql_ex1.sql crit prcdemmentet sauvegard sous c:orawin95gautier:SQL start .gautierPLSQL_EX1(start ou )ancien 8: where empno = &num_emp;nouveau 8: where empno = 7369;ancien 12: WHERE empno = &num_emp;nouveau 12: WHERE empno = 7369;Procdure PL/SQL termine avec succs. Vrification de la modification sur la table empSQL select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 17/12/80 880 20 7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30 .III DECLARATION DES VARIABLESLa partie dclarative dans un bloc PL/Sql, peut comporter trois types de dclarations.Elle est dlimite par les mots-cls DECLARE, qui spcifie le dbut et BEGIN, qui signifie la fin de la dclaration et le dbut de la partie des commandes.Les types de dclarations possibles dans cette partie sont les suivants : dclaration des variables et des constantes, dclaration de curseurs, dclaration des exceptions.Les types de variables utilises en PL/Sql sont les suivantes : variables locales de type Oracle : reconnu par Oracle faisant rfrence au dictionnaire de donnes variables de lenvironnement extrieur PL/SQL champs dcran de Forms variables htes dfinies en langage hte dans Pro* variables dfinies dans Sql*Plus (prfixes de &)La dclaration dune variable consiste allouer un espace pour stocker et modifier une valeur. Elle est type et peut recevoir une valeur par dfaut et/ou un statut NOT NULL.1. Variables ou constantes locales de type Oracle et PL/SqlNom-de-variable CONSTANT type NOT NULL:= expression;TypeChar(n), Number(n,m), date, booleanExpressionpeut tre une constante ou un calcul faisant ventuellement rfrence une variable prcdemment dclareDECLARENom_du clientChar(30);X number / + 1;- initialisationPI constantnumber(7,5):= 3.14159;Rayonnumber:= 1;Surfacenumber(15,5):= pi * Rayon *2;Reponseboolean;2. Variables faisant rfrence au mme type quune colonne dune table ou mme type quune autre variableNom-de-variable nom_table.nom-colonne %type;ouNom-de-variable1 Nom-de-variable2%type;DECLAREEmp_NomEMP.Empno%type; - Mme type que la proprit EmpnoXnumber(10,3);YX%type;- Du mme type que x donc number(10,3)Ceci est intressant pour des raisons de simplification dcriture et dvolution .IV VALORISATION DES VARIABLES PL/SQLTrois possibilits de valorisation de variables sont disponibles :1. par loprateur daffectation : :=,2. par la clause Select . Into . .3. par le traitement dun curseur dans la section Begin. (que nous aborderons par la suite)a) affectation de valeurNom_Variable := Expression;Expression peut-tre:- une constante, une variable, un calculLes oprateurs de calcul sont:- +; -; *; /;*; |BEGINX:= 0;Vnom:= Monsieur | Vnom; - concatnationY:= (X+5) * Y;b) La clause select . intoLa difficult dans lutilisation de la clause Select rsulte du nombre de lignes ou doccurrences retourn.Si le Select retourne une et une seule ligne laffectation seffectue correctement.Par contre,Si le Select retourne 0 ligne : NO_DATA_FOUND ( test nom_variable IS NULL)Si le Select retourne plusieurs lignes : TOO_MANY_ROWS , une erreur PL/SQL est gnre.SELECT */Liste dexpression INTO Liste de variables FROM .;DECLARE VRefCHAR(10); VPrixArticles.Prix%TYPE; CltClients.%ROWTYPEBEGIN SELECT RefArt, PrixArt INTO Vref, Vprix FROM Articles WHERE DesArt = Cadeau; SELECT * INTO Clt FROM Clients WHERE NoClt = 10; END;V STRUCTURES DE CONTRLESa) Structure alternativeIF condition Then Instructions;Else instructions ; ELSIF condition Then instructions ; Else commandes ; END IF;Seules les clauses IF, THEN, END IF sont obligatoires.La condition peut utiliser les variables dfinies ainsi que tous les oprateurs prsents dans SQL =, , =, , IS NULL, IS NOT NULL, BETWEEN, LIKE, AND, OR, etc.b) La boucle POURFOR compteur IN exp_debut . exp_fin LOOP . instructions ; . END LOOP;Rgles : Dclaration implicite de la variable compteur exp_debut, exp_fin : sont des constantes, expressions ou variables compteur : est une variable de type entier, locale la boucle. Elle sincrmente de 1, aprs chaque traitement du contenu de la boucle, jusqu ce quil atteigne la valeur de droite c) La boucle TANT QUEWHILE condition LOOP . instructions ; . END LOOP;La condition est une expression dfinie en combinant les oprateurs : , = , !=, =; and, or, like, etc. Expression est une constante, une variable, le rsultat dune fonction.VI ECHANGES AVEC LEXTERIEURA priori il nexiste pas dinstruction daffichage et de saisie dans le langage PL/SQL.Sous SQL*Plus: on peut dfinir une variable ( rservation dune zone mmoire), et lafficher la fin du programme PL/SQL- EXEMPLE 2 : PLSQL_EX2.sqlBEGIN SELECT COUNT(*) INTO :X FROM DEPT;END;/SQL variable x numberSQL start ./gautier/plsql_ex2Procdure PL/SQL termine avec succs.SQL print x X- 5Remarque: X est prfixe par: variable hte. on peut saisir une valeur rechercherdans SQLPLUS (ou sous le fichier avant le DECLARE)SQL ./gautier/plsql_ex3prompt nom du dpartement dsir - ou ces 2 lignes sous SQL*PLUSaccept dept_nom- Exemple PLSQL_EX3.sqlDECLARE res dept%rowtype; - Mme type quune ligne de la tableBEGIN SELECT * into res from dept where dname = &dept_nom;END;/Mais le rsultat de la requte ne saffiche pas on peut dfinir une constante dans SQL*PLUS ou dans le fichierSQLdefine dept_nom = SALESSQL ./gautier/plsql_ex3/ sans les lignes prompt et acceptMais rien ne saffiche: La meilleure solution pour rcuprer le rsultat de lexcution dun programme PL/SQl consiste crer une table rsultat comportant les champs que lon dsire puis dafficher la fin du programme cette table.Sous NOTPAD- Exemple PLSQL_EX4.sqlPrompt Quel est le dpartement dsiraccept numerocreate table resultat(num number(2), nom char(14)/DECLARE numero dept.deptno%type; nomdept dept.dname%type;BEGIN SELECT deptno, dname into numero, nomdept from deptwhere deptno = № INSERT INTO resultatvalues(numero, nomdept);END;/select * from resultat/drop table resultat/Sous SQL*PLUSSQL ./gautier/plsql_ex4Quel est le dpartement dsir10Table cre.ancien 6: where deptno = №Remarque: Dans un prcompilateur Oracle, les variables htes dclares dans le langage sont dsormais partages avec le bloc PL/SQL Ce bloc est insr dans le programme PRO*C avec les 2 dlimiteurs EXEC SQL EXECUTE et END-EXEC.nouveau 6: where deptno = 10;Procdure PL/SQL termine avec succs. NUM NOM- - 10 ACCOUNTINGTable supprime.VII UTILISATION DU PACKAGE DBMS_OUPUTSous Oracle 7 , le package DBMS_OUTPUT permet dafficher des messages lcran dans des programmes PL/SQL. Cela va faciliter le test et le dbuggage des programmes.Pour cela il faut sous SQL*PLUS autoriser lutilisation de linstruction daffichage.SQL SET ServerOUTPUT ON SIZE 80000 (Optionnel: prcise le nombre de caractres maximum afficher)Dans un programme PL/SQL on peut alors utiliser linstruction:DBMS_OUTPUT.PUT_LINE(message .);- Exemple PLSQL_EX4b.sqlPrompt Quel est le dpartement dsiraccept numeroDECLARE numero dept.deptno%type; nomdept dept.dname%type;BEGIN SELECT deptno, dname into numero, nomdept from deptwhere deptno = № Dbms_Output.put_line(le dpartement | TO_CHAR(numero) | a pour nom | nomdept) ;END;/SQL c:asqlplsql_ex4bQuel est le dpartement dsir20ancien 6: where deptno = №nouveau 6: where deptno = 20;le dpartement 20 a pour nom RESEARCHProcdure PL/SQL termine avec succs.SQLRemarque: Pour ne plus autoriser laffichage: set ServerOutput offVIII EXERCICES DAPPLICATIONNous utiliserons la base de donnes EmployeExercice 1: Exo1_plsql.sqlEcrire le programme PL/SQL Lutilisateur saisit un nom demploy Si cet employ na pas de travail dfini, vous devez afficher le nom de lemploy suivi de na pas de travail. Si cet employ est un vendeur (SALESMAN) vous devez lui attribuer 1000 francs de commission et afficher le nom de lemploy suivi de a 1000 frs de commission Si cet employ est dans la table mais nest pas vendeur, vous devez lui affecter 0 de commission et afficher le nom de lemploy suivi de na pas de commissionVrifier si les modifications ont bien t effectues dans la table.Exercice 2: Exo2_plsql.sqlEcrire un programme PL/SQL permettant dafficher la factorielle de 9.Rappel 9! = 9 * 8 * 7 . * 1Exercice 3: Exo3_plsql.sqlVous devez rechercher en premier lieu le salaire de lemploy 7902. (dclaration dune constante)Tant que le salaire 4000, vous devez continuer chercher le salaire du chef de lemploy et ainsi de suite: Select . where empno = chef; Dans notre exemple cela sarrtera KingVous devez ainsi afficher le nom et le salaire sur lequel le programme se termine.CORRECTION DES EXERCICES:EXO1_PLSQL.SQLPrompt Quel employ cherchez-vous ?accept vnomDECLARE vjob emp.job%type; message char(30); BEGIN Select job into vjob from emp where ename=&vnom; - contrle de la valeur de vjob - If vjob is NULL then message:= &vnom | na pas de travail; else if vjob=SALESMAN then update emp set comm=1000 where ename=&vnom; message:= &vnom | a 1000 Frs de commission; else update emp set comm=0 where ename= &vnom; message:= &vnom | na pas de commission ; end if; end if; dbms_output.put_line(message); END ;/EXO2_PLSQL.SQLcreate table resultat(f1 number(15), msg char(50)/DECLARE fact number :=1; BEGIN FOR i IN 1.9 LOOP fact := fact*i; END LOOP ; insert into resultat values (fact, factorielle de 9);/ ou utilisation de dbms_output END ;/select * from resultat/drop table resultat/EXO3_PLSQL.SQLCREATE TABLE RESULTAT(nom1 char(30), sal1 number(8,2)/DECLARE salaire emp.sal%type; nom emp.ename%type; chef emp.mgr%type; num_debut constant number(4):=7902;BEGIN select ename, sal, mgr into nom, salaire, chef from emp where empno=num_debut; WHILE salaire 4000 LOOP select ename, sal, mgr into nom, salaire, chef from emp where empno=chef; END LOOP ; insert into resultat values (nom,Salaire); END ;/select * from resultat/drop table resultat/PARTIE 1 - LE LANGAGE PROCEDURAL DORACLE: LE LANGAGE PL/SQLI INTRODUCTIONII LE BLOC PL/SQLIII DECLARATION DES VARIABLESIV VALORISATION DES VARIABLES PL/SQLV STRUCTURES DE CONTRLESVI ECHANGES AVEC LEXTERIEURVII UTILISATION DU PACKAGE DBMS_OUPUTVIII EXERCICES DAPPLICATIONCORRECTION DES EXERCICES:Bibliographie: Oracle 7 Editions Laser Roger CHAPUISOracle 7 Langages Architecture Administration Eyrolles ABDELLATIF, LIMANE et ZEROUALOracle (version 7) _ Editions ENI Manuel pratique MEGA +Le langage PL/SQL Stage MAFPEN Christian FISCHERCours - Le PL SQL Partie 1Page 13Genevive Gautier