LE PL SQL partie 1.doc

cours le pl sql partie 1page 1 genevi ve gautier partie 1 le langage procedural d oracle le langage pl sql i introduction le pl sql est le langage proc dural d oracle c est une extension du langage sql qui est un langage ensembliste pl sql proc dural language sql l int r t du pl sql est de pouvoir dans un m me traitement allier la puissance des instructions sql et la souplesse d un langage proc dural le fonctionnement de pl sql est bas sur l interpr tation d un bloc de commandes ce mode de fonctionnement permet d obtenir des gains de transmission et des gains de performances dans l environnement sql les ordres du langage sont transmis et ex cut s les uns la suite des autres dans l environnement pl sql les ordres sql et pl sql sont regroup s en blocs un bloc ne demande qu un seul transfert et une seule ex cution de l ensemble des commandes contenues dans le bloc cours le pl sql partie 1page 2 genevi ve gautier ii le bloc pl sql pl sql n interpr te pas une commande mais un ensemble de commandes contenu dans un programme ou bloc pl sql un bloc est compos de trois sections declare begin exception end ou end nom bloc chaque instruction de n importe quelle section doit se terminer par un possibilit de placer des commentaires commentaire sur une ligne ou commentaire sur plusieurs lignes exemple sous sql plus de personnal oracle 7 ecriture du programme pl sql sous le bloc notes nomm plsql ex1 sql declare d but du programme sal emp number 7 2 variable locale au bloc begin s lectionner le salaire de l employ saisi au pr alable dans sql plus num emp l augmenter de 10 si ce salaire est inf rieur 1000 select sal into sal emp from emp where empno 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 d finir la variable num emp sql define num emp 7369 ou sql prompt num ro du salari d sir ou crire ces 2 lignes directement sql accept num emp dans le programme pl sql avant declare appel du programme plsql ex1 sql crit pr c demment et sauvegard sous c orawin95 gautier sql start gautier plsql ex1 start ou ancien 8 where empno nouveau 8 where empno 7369 ancien 12 where empno nouveau 12 where empno 7369 proc dure pl sql termin e avec succ s v rification de la modification sur la table emp sql 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 cours le pl sql partie 1page 4 genevi ve gautier iii declaration des variables la partie d clarative dans un bloc pl sql peut comporter trois types de d clarations elle est d limit e par les mots cl s declare qui sp cifie le d but et begin qui signifie la fin de la d claration et le d but de la partie des commandes les types de d clarations possibles dans cette partie sont les suivants d claration des variables et des constantes d claration de curseurs d claration des exceptions les types de variables utilis es en pl sql sont les suivantes variables locales de type oracle reconnu par oracle faisant r f rence au dictionnaire de donn es variables de l environnement ext rieur pl sql champs d cran de forms variables h tes d finies en langage h te dans pro variables d finies dans sql plus pr fix es de typechar n number n m date boolean expressionpeut tre une constante ou un calcul faisant ventuellement r f rence une variable pr c demment d clar e declare nom du clientchar 30 x number 1 initialisation pi constantnumber 7 5 3 14159 rayonnumber 1 surfacenumber 15 5 pi rayon 2 reponseboolean cours le pl sql partie 1page 5 genevi ve gautier 2 variables faisant r f rence au m me type qu une colonne d une table ou m me type qu une autre variable nom de variable nom table nom colonne type ou nom de variable1 nom de variable2 type declare emp nomemp empno type m me type que la propri t empno xnumber 10 3 yx type du m me type que x donc number 10 3 ceci est int ressant pour des raisons de simplification d criture et d volution iv valorisation des variables pl sql trois possibilit s de valorisation de variables sont disponibles 1 par l op rateur d affectation 2 par la clause select into 3 par le traitement d un curseur dans la section begin que nous aborderons par la suite a affectation de valeur nom variable expression expression peut tre une constante une variable un calcul les op rateurs de calcul sont begin x 0 vnom monsieur vnom concat nation y x 5 y b la clause select into la difficult dans l utilisation de la clause select r sulte du nombre de lignes ou d occurrences retourn si le select retourne une et une seule ligne l affectation s effectue 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 g n r e cours le pl sql partie 1page 6 genevi ve gautier select liste d expression into liste de variables from declare vref char 10 vprixarticles prix type cltclients rowtype begin select refart prixart into vref vprix from articles where desart cadeau select into clt from clients where noclt 10 end v structures de contr les a structure alternative if 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 d finies ainsi que tous les op rateurs pr sents dans sql is null is not null between like and or etc b la boucle pour for compteur in exp debut exp fin loop instructions end loop r gles d claration 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 s incr mente de 1 apr s chaque traitement du contenu de la boucle jusqu ce qu il atteigne la valeur de droite cours le pl sql partie 1page 7 genevi ve gautier c la boucle tant que while condition loop instructions end loop la condition est une expression d finie en combinant les op rateurs and or like etc expression est une constante une variable le r sultat d une fonction vi echanges avec l exterieur a priori il n existe pas d instruction d affichage et de saisie dans le langage pl sql sous sql plus on peut d finir une variable r servation d une zone m moire et l afficher la fin du programme pl sql sql variable x number sql start gautier plsql ex2 proc dure pl sql termin e avec succ s sql print x x 5 remarque x est pr fix e par variable h te on peut saisir une valeur rechercher dans sqlplus ou sous le fichier avant le declare sql gautier plsql ex3 prompt nom du d partement d sir ou ces 2 lignes sous sql plus accept dept nom exemple plsql ex3 sql declare res dept rowtype m me type qu une ligne de la table begin select into res from dept where dname end mais le r sultat de la requ te ne s affiche pas exemple 2 plsql ex2 sql begin select count into x from dept end cours le pl sql partie 1page 8 genevi ve gautier on peut d finir une constante dans sql plus ou dans le fichier sql define dept nom sales sql gautier plsql ex3 sans les lignes prompt et accept mais rien ne s affiche la meilleure solution pour r cup rer le r sultat de l ex cution d un programme pl sql consiste cr er une table r sultat comportant les champs que l on d sire puis d afficher la fin du programme cette table sous notpad exemple plsql ex4 sql prompt quel est le d partement d sir accept numero create 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 dept where deptno insert into resultat values numero nomdept end select from resultat drop table resultat sous sql plus sql gautier plsql ex4 quel est le d partement d sir 10 table cr e ancien 6 where deptno nouveau 6 where deptno 10 proc dure pl sql termin e avec succ s num nom 10 accounting table supprim e remarque dans un pr compilateur oracle les variables h tes d clar es dans le langage sont d sormais partag es avec le bloc pl sql ce bloc est ins r dans le programme pro c avec les 2 d limiteurs exec sql execute et end exec cours le pl sql partie 1page 9 genevi ve gautier vii utilisation du package dbms ouput sous oracle 7 le package dbms output permet d afficher des messages l cran dans des programmes pl sql cela va faciliter le test et le d buggage des programmes pour cela il faut sous sql plus autoriser l utilisation de l instruction d affichage sql set serveroutput on size 80000 optionnel pr cise le nombre de caract res maximum afficher dans un programme pl sql on peut alors utiliser l instruction dbms output put line message exemple plsql ex4b sql prompt quel est le d partement d sir accept numero declare numero dept deptno type nomdept dept dname type begin select deptno dname into numero nomdept from dept where deptno dbms output put line le d partement to char numero a pour nom nomdept end sql c asql plsql ex4b quel est le d partement d sir 20 ancien 6 where deptno nouveau 6 where deptno 20 le d partement 20 a pour nom research proc dure pl sql termin e avec succ s sql remarque pour ne plus autoriser l affichage set serveroutput off cours le pl sql partie 1page 10 genevi ve gautier viii exercices d application nous utiliserons la base de donn es employ e exercice 1 exo1 plsql sql ecrire le programme pl sql l utilisateur saisit un nom d employ si cet employ n a pas de travail d fini vous devez afficher le nom de l employ suivi de n a pas de travail si cet employ est un vendeur salesman vous devez lui attribuer 1000 francs de commission et afficher le nom de l employ suivi de a 1000 frs de commission si cet employ est dans la table mais n est pas vendeur vous devez lui affecter 0 de commission et afficher le nom de l employ suivi de n a pas de commission v rifier si les modifications ont bien t effectu es dans la table exercice 2 exo2 plsql sql ecrire un programme pl sql permettant d afficher la factorielle de 9 rappel 9 9 8 7 1 exercice 3 exo3 plsql sql vous devez rechercher en premier lieu le salaire de l employ 7902 d claration d une constante tant que le salaire 4000 vous devez continuer chercher le salaire du chef de l employ et ainsi de suite select where empno chef dans notre exemple cela s arr tera king cours le pl sql partie 1page 11 genevi ve gautier vous devez ainsi afficher le nom et le salaire sur lequel le programme se termine correction des exercices exo1 plsql sql prompt quel employ cherchez vous accept vnom declare vjob emp job type message char 30 begin select job into vjob from emp where ename contr le de la valeur de vjob if vjob is null then message else if vjob salesman then update emp set comm 1000 where ename message else update emp set comm 0 where ename message end if end if dbms output put line message end cours le pl sql partie 1page 12 genevi ve gautier exo2 plsql sql create table resultat f1 number 15 msg char 50 declar