<!-- If the browser does not support frames put the first topic content here --> <div class="Section1"> <h1 align="left" style='text-align:left;border:none'><a name= "_Toc316821929"></a><a name="_Toc316821770"></a><a name= "_Toc316654727"></a><a name="_Toc316654353"></a><a name= "_Toc306174202"></a><a name="_Toc306173789"></a><a name= "_Toc306034568"></a><a name="_Toc306027287"></a><a name= "_Toc305947022"></a><a name="_Toc305940536"></a><a name= "_Toc305940438"></a><a name="_Toc305863167"></a><a name= "_Toc305862982"></a><a name="_Toc305862589"><span style= 'font-size:16.0pt;text-decoration:none'>ETUDE DU LANGAGE PL/SQL</span></a></h1> <p class="MsoNormal" style='margin-left:3.0cm'><b><span style= 'font-size:16.0pt; font-family:"Courier New"'>LES PROCEDURES ET FONCTIONS STOCKEES</span></b></p> <p class="MsoNormal" style='margin-left:3.0cm'><b><span style= 'font-size:16.0pt; font-family:"Courier New"'>LES TRIGGERS</span></b></p> <p class="MsoNormal" style='margin-left:3.0cm'><b><span style= 'font-size:16.0pt; font-family:"Courier New"'> </span></b></p> <h1 align="left" style= 'margin-left:0cm;text-align:left;border:none'><span style= 'font-size:16.0pt'><span style= 'text-decoration:none'> </span></span></h1> <p class="Titre1"><span style='text-decoration:none'>1.<span style= 'font:7.0pt "Times New Roman"'> </span></span> ETUDE DU LANGAGE PL/SQL</p> <p class="MsoNormal"><span style= 'font-family:"Courier New"'> </span></p> <h2 style='margin-left:0cm'><a name="node0" id= "node0"></a><span style='font-size:14.0pt'>1.1. Introduction</span></h2> <p class="MsoNormal" style= 'margin-left:.9pt;text-align:justify;text-indent:-.9pt'> <span style='font-family:Courier'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-family:Courier'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Sql est un langage complet pour travailler sur une base de données, mais il ne comporte pas d'instructions procédurales.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>PL/SQL comprend quant à lui :</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:2.0cm;text-align:justify;text-indent: 0cm'> <span style='font-size:11.0pt;font-family:Symbol'>·</span> <span style='font-size:11.0pt'>la partie LMD de SQL (Select, Update, Insert, Delete),</span></p> <p class="MsoNormal" style='margin-left:2.0cm;text-align:justify'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:2.0cm;text-align:justify;text-indent: 0cm'> <span style='font-size:11.0pt;font-family:Symbol'>·</span> <span style='font-size:11.0pt'>la gestion des transactions (Commit, Rollback ...),</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:2.0cm;text-align:justify;text-indent: 0cm'> <span style='font-size:11.0pt;font-family:Symbol'>·</span> <span style='font-size:11.0pt'>les fonctions de manipulation telles MONTH, YEAR …,</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:2.0cm;text-align:justify;text-indent: 0cm'> <span style='font-size:11.0pt;font-family:Symbol'>·</span> <span style='font-size:11.0pt'>plus une partie procédurale (affectation, calculs, IF, WHILE, ...),</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>PL/SQL va en particulier nous permettre de créer de nouveaux objets dans la base de données : : <b>procédures</b> et <b>fonctions</b> ainsi que <b>triggers</b> (‘déclencheurs’). Ces objets sont des unités de traitements :</span></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>les <b>procédures</b> et <b>fonctions stockées</b> permettent d’effectuer certains traitements lourds ou confidentiels sur le serveur ; elles permettent aussi de factoriser les traitements dans les architectures client/serveur (plusieurs clients utilisent la même procédure).</span></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>les <b>triggers</b> permettent de maintenir dynamiquement la cohérence de la base de données et de décharger les programmes de cette tâche ; exemple : la mise à jour d’une table déclenche automatiquement une mise à jour sur une autre table.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>De plus, nous verrons que ces traitements stockés dans la base de données permettent d’alléger le trafic sur le réseau.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <span style='font-size:10.0pt;font-family:Courier'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify'><span style= 'font-family:Courier'> </span></p> <h2 style='margin-left:0cm'><a name="node1" id= "node1"></a><span style='font-size:14.0pt'>1.2. Structure d'un bloc Pl/SQL</span></h2> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>La structure d'un bloc est la suivante :</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-family:Courier'> </span></p> <div style='border:solid black 1.0pt;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal" style= 'text-indent:7.1pt;border:none;padding:0cm'><b><span style= 'font-size:15.0pt;font-family:Courier'>BEGIN</span></b><span style= 'font-family:Courier'><br /></span><span style= 'font-size:11.0pt'> DECLARE …<br /> Instructions SQL et PL/SQL;<br /> Peut éventuellement contenir un autre bloc Pl/Sql </span> <span style='font-family:Courier'><br /> </span><b><span style= 'font-size:15.0pt;font-family:Courier'>END |</span></b></p> </div> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Remarque :</span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Chaque instruction PL/SQL ou SQL de n'importe quelle section doit se terminer par un ';'.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>La structuration des instructions en blocs procure une plus grande lisibilité des traitements.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><u><span style='font-size:11.0pt'>Exemple de bloc PL/SQL :</span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:8.0pt'> </span></p> <div style='border:solid black 1.0pt;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span><span lang="EN-GB" style= 'font-size:11.0pt;font-family:Courier' xml:lang="EN-GB">DELIMITER |;<br /></span><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> DECLARE</span> <span lang="EN-GB" style= 'font-size:11.0pt' xml:lang="EN-GB"> code_prod int;<br /> DECLARE</span><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> qte_stock int;<br /></span><span lang= "EN-GB" style='font-size:11.0pt;font-family:Courier' xml:lang= "EN-GB"><br /> </span> <span lang="EN-GB" style= 'font-size:11.0pt' xml:lang="EN-GB">Select quantite, codeprod</span></p> <p class="MsoNormal" style= 'text-indent:1.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> into qte_stock, code_prod</span></p> <p class="MsoNormal" style= 'text-indent:1.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> from inventaire<br /> where produit='raquette tennis';<br /> <span style= 'color:lime'> -- contrôle du stock suffisant --<br /></span> If qte_stock > 0<br /></span> <span lang="EN-GB" style= 'font-size:11.0pt;font-family:Courier' xml:lang= "EN-GB"> </span> <span lang="EN-GB" style= 'font-size:11.0pt' xml:lang="EN-GB">then</span></p> <p class="MsoNormal" style= 'text-indent:1.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> update inventaire</span></p> <p class="MsoNormal" style= 'text-indent:1.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> set quantite=quantite-1<br /> where produit='raquette tennis';<br /> insert into achat<br /> values (code_prod, CURRENT_DATE);<br /> end if;<br /> <br /> END |</span></p> </div> <span lang="EN-GB" style= 'font-size:10.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB"><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='text-indent:1.0cm'><b><u><span style= 'font-size:16.0pt; font-family:"Courier New"'>Les déclarations PL/SQL</span></u></b></p> <p class="MsoNormal" style='margin-left:1.0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><a name="_Toc316654731"></a><a name= "_Toc316654357"></a><a name="_Toc306174209"></a><a name= "_Toc306173809"></a><a name="_Toc306034573"></a><a name= "_Toc306027292"></a><a name="_Toc305947027"></a><a name= "_Toc305940543"></a><a name="_Toc305940443"></a><a name= "_Toc305863172"></a><a name="_Toc305862987"></a><a name= "_Toc316821774"><span style='font-size:11.0pt'>La partie déclarative dans un bloc PL/SQL, peut comporter les trois types de déclarations suivants.</span></a></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>déclaration des variables et des constantes,</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>déclaration de curseurs,</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>déclaration des exceptions. (non traité ici)</span></p> <p class="MsoNormal"> </p> <h2><a name="_Toc316821933"></a><a name= "_Toc316821775"><span style='text-decoration: none'> </span></a></h2> <h2 style='margin-left:0cm'><a name="node2" id= "node2"></a><span style= 'font-size:14.0pt'>1.3. L'affectation des variables PL/SQL</span></h2> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <h3><a name="node3" id="node3"></a>1.3.1. Principaux types de données</h3> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style='font-size:11.0pt'>Chaque variable ou constante utilisée dans un bloc PL/SQL, possède un type de données qui détermine son format de stockage et sa plage de valeurs valides.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les types de données offerts par PL/SQL sont d'abord les types offerts pour les colonnes des tables MySql auxquels s'ajoutent d'autres types tel le type boolean.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"> </p> <h3 style='margin-right:-14.4pt'><a name= "_Toc316821935"></a><a name="_Toc316821777">1.3.2. Syntaxe de déclaration des variables</a> et constantes</h3> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Une constante est définie comme une variable sauf qu’on y ajoute le mot-clé CONSTANT, le contenu est alors non modifiable.</span></p> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les variables et constantes sont précédées du mot DECLARE et utilise la syntaxe suivante :</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <div style= 'border:solid black 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; margin-left:0cm;margin-right:-.05pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>Nomdonnée [<b><u>CONSTANT</u></b>] nomtype</span></p> </div> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><b><u><span style='font-size:11.0pt'>Remarque :</span></u></b></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>L'attribut <u>constant</u> permet de figer l'affectation.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Deux possibilités d'affectation sont disponibles :</span></p> <p class="MsoNormal"><span style= 'font-size:8.0pt'> </span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'>L'initialisation d'une variable se fait de la façon suivante : SET <i>nomvariable</i> = <i>valeur</i> ;</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Exemple : SET A = A + 1 ;</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'>L'initialisation peut également se faire par la clause INTO d'une requête SQL :</span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">SELECT nomcli FROM client</span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">INTO nom</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>WHERE numcli = 104 ;</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>L'affectation par la clause INTO s'effectue sous certaines conditions.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Si le <u>Select retourne une et une seule ligne,</u> l'affectation s'effectue correctement.</span></p> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Par contre,</span></p> <p class="MsoNormal"><i><span style='font-size:11.0pt'>. Si le <u>Select retourne 0 ligne</u> :</span></i> <i><span style= 'font-size:11.0pt;font-family:Wingdings'>è</span></i> <i><span style='font-size:11.0pt'>une erreur PL/SQL est générée.</span></i></p> <p class="MsoNormal"><i><span style='font-size:11.0pt'>. Si le <u>Select retourne plusieurs lignes</u> :</span></i> <i><span style='font-size:11.0pt;font-family: Wingdings'>è</span></i> <i><span style='font-size:11.0pt'>une erreur PL/SQL est générée.</span></i></p> <span style='font-size:10.0pt;font-family:Courier'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal"><u><span style='font-size:11.0pt'>Remarque :</span></u> <b><span style= 'font-size:11.0pt'> .</span></b> <span style='font-size:11.0pt'>Entre BEGIN et END, les instructions select ne servent qu'à alimenter des variables, elles ne provoquent aucun affichage à l'écran. Si le bloc d'instructions a pour but d'afficher un résultat à l'utilisateur, il faudra insérer le résultat dans une table temporaire à l'intérieur du bloc et faire un select de la table résultat à l'extérieur du bloc. La table temporaire (nommée resultat dans l'exemple) devra être créée avant l'exécution du bloc.</span></p> <p class="MsoNormal"><u><span style='font-size:11.0pt'><span style= 'text-decoration: none'> </span></span></u></p> <p class="MsoNormal"><u><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Exemple</span></u></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">On va utiliser la table emp : Emp(<u>empno</u>, ename, job, mgr, hiredate, sal , comm, #deptno)</span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:8.0pt' xml:lang="EN-GB"> </span></p> <div style= 'border:solid black 1.0pt;border-bottom:none;padding:0cm 4.0pt 0cm 4.0pt; margin-left:0cm;margin-right:2.0cm'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DELIMITER |;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE vjob varchar(15);<br /> DECLARE vnom varchar(30);<br /> DECLARE message varchar(50);<br /> <br /></span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">select job</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">into vjob</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">from emp</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span style= 'font-size:11.0pt'>where ename='MARTIN';<br /> <br /> -- Traitement selon le job et alimentation d'un champ message pour savoir ce qui a été effectué :</span></p> </div> <div style= 'border:solid black 1.0pt;border-top:none;padding:0cm 4.0pt 1.0pt 4.0pt; margin-left:.35pt;margin-right:2.0cm'> <p class="MsoNormal" style= 'text-indent:35.1pt;border:none;padding:0cm'><b><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">. . .<br /></span></b><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> insert into resultat</span></p> <p class="MsoNormal" style= 'text-indent:35.1pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">values (message);<br /> END |<br /> <br /></span></p> </div> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <h2 style='margin-left:0cm'><a name="node4" id= "node4"></a><span style='font-size:14.0pt'>1.4. Les structures conditionnelles</span></h2> <p class="MsoNormal"><a name="_Toc305863176"></a><a name= "_Toc305862991"></a><a name="_Toc305862594"> </a></p> <h3 style='margin-right:-14.4pt'><a name="node5" id= "node5"></a>1.4.1. L'instruction IF … <span lang= "EN-GB" xml:lang="EN-GB">THEN … END IF</span></h3> <p class="MsoNormal"><span lang="EN-GB" style='font-family:Courier' xml:lang="EN-GB"> </span></p> <p class="H3"><span lang="EN-GB" style= 'font-size:12.0pt;font-family:Helvetica; color:navy' xml:lang= "EN-GB">IF THEN</span></p> <p class="Preformatted"><span lang="EN-GB" style= 'font-size:11.0pt;font-family: "Times New Roman"' xml:lang= "EN-GB">IF condition</span></p> <p class="Preformatted"><span style= 'font-size:11.0pt;font-family:"Times New Roman"'>THEN </span></p> <p class="Preformatted"><span style= 'font-size:11.0pt;font-family:"Times New Roman"'> instruction ou séquence d'instructions;</span></p> <p class="Preformatted"><span style= 'font-size:11.0pt;font-family:"Times New Roman"'>END IF;</span></p> <p class="MsoNormal"><u><span style='font-size:11.0pt'><span style= 'text-decoration: none'> </span></span></u></p> <p class="MsoNormal"><u><span style= 'font-size:11.0pt'>Remarque</span></u> <span style= 'font-size:11.0pt'>: le then peut être placé à côté du if.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Il faut préciser que les instructions peuvent être écrites sur 1 ou plusieurs lignes, on peut même écrire plusieurs instructions sur une même ligne, le principal étant de placer un point-virgule à la fin de chaque instruction.</span></p> <p class="MsoNormal"><u><span style= 'font-family:Courier'><span style= 'text-decoration: none'> </span></span></u></p> <p class="H3"><span lang="EN-GB" style= 'font-size:12.0pt;font-family:Helvetica; color:navy' xml:lang= "EN-GB">IF THEN ELSE</span></p> <p class="Preformatted"><span lang="EN-GB" style= 'font-size:11.0pt;font-family: "Times New Roman"' xml:lang= "EN-GB">IF condition</span></p> <p class="Preformatted"><span lang="EN-GB" style= 'font-size:11.0pt;font-family: "Times New Roman"' xml:lang= "EN-GB">THEN</span></p> <p class="Preformatted"><span lang="EN-GB" style= 'font-size:11.0pt;font-family: "Times New Roman"' xml:lang= "EN-GB"> </span> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'>instruction ou séquence d'instructions;</span></p> <p class="Preformatted"><span style= 'font-size:11.0pt;font-family:"Times New Roman"'>ELSE</span></p> <p class="Preformatted"><span style= 'font-size:11.0pt;font-family:"Times New Roman"'> instruction ou séquence d'instructions;</span></p> <p class="Preformatted"><span style= 'font-size:11.0pt;font-family:"Times New Roman"'>END IF;</span></p> <p class="MsoNormal"><u><span style= 'font-family:Courier'><span style= 'text-decoration: none'> </span></span></u></p> <p class="MsoNormal"><u><span style= 'font-family:Courier'><span style= 'text-decoration: none'> </span></span></u></p> <p class="MsoNormal"><u><span style='font-size:11.0pt'>Remarques :</span></u></p> <p class="MsoNormal"><span style='font-size:11.0pt'>. Il est possible d'imbriquer les IF.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>. La condition peut utiliser des variables ainsi que les opérateurs suivants : =, <, >, <=, >=, <>, IS NULL, IS NOT NULL.</span></p> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang="EN-GB">IF condition1</span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB">THEN</span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> statement1;</span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB">ELSE </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> IF condition2</span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> THEN </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> statement2; </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> ELSE </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> IF condition3</span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> THEN </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> statement3; </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> END IF; </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang= "EN-GB"> END IF; </span></p> <p class="Preformatted"><span lang="EN-GB" xml:lang="EN-GB">END IF; </span></p> <p class="Preformatted"><b><span lang="EN-GB" style= 'font-size:20.0pt' xml:lang="EN-GB"> </span></b></p> <div style= 'border:solid black 1.0pt;border-bottom:none;padding:0cm 0cm 0cm 0cm; margin-left:0cm;margin-right:2.0cm'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> DELIMITER |;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> DECLARE vjob varchar(15);<br /> DECLARE vnom varchar(40);<br /> DECLARE message varchar(100);<br /> SET vnom = 'MARTIN';<br /> Select job</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> into vjob</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> from emp</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> where ename=vnom;<br /> <span style='color:lime'>-- traitement selon le job<br /> </span>If vjob is NULL<br /> then</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>SET message = vnom || ' est sans travail';<br /> else</span></p> <p class="MsoNormal" style= 'text-indent:.35pt;border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span> <span lang="EN-GB" style='font-size: 11.0pt' xml:lang="EN-GB">if vjob = 'SALESMAN'</span></p> <p class="MsoNormal" style= 'text-indent:.35pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> then</span></p> <p class="MsoNormal" style= 'text-indent:.35pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> update emp</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> set comm=1000</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> where ename=vnom;<br /> SET message = vnom , ' a 1000 Frs de commission';<br /> else<br /> update emp</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> set comm=0</span></p> <p class="MsoNormal" style= 'text-indent:35.45pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> where ename=vnom;<br /> SET message = vnom , ' n''a pas de commission ';<br /> end if;</span></p> </div> <div style= 'border:solid black 1.0pt;border-top:none;padding:0cm 0cm 0cm 0cm; margin-left:.35pt;margin-right:2.0cm'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> end if;<br /> insert into resultat values (message);<br /> END |</span></p> </div> <b><u><span lang="EN-GB" style= 'font-size:16.0pt;font-family:"Courier New"' xml:lang= "EN-GB"><br clear="all" style= 'page-break-before:always' /></span></u></b> <p class="MsoNormal"><a name="_Toc316821949"></a><a name= "_Toc316821791"></a><a name="_Toc316654741"></a><a name= "_Toc316654364"></a><a name="_Toc306174216"></a><a name= "_Toc306173816"></a><a name="_Toc306034580"></a><a name= "_Toc306027299"></a><a name="_Toc305947034"></a><a name= "_Toc305940549"></a><a name="_Toc305940449"></a><a name= "_Toc305863177"></a><a name="_Toc305862992"></a><a name= "_Toc305862595"><b><u><span lang="EN-GB" style= 'font-size:16.0pt;font-family:"Courier New"' xml:lang= "EN-GB"><span style= 'text-decoration:none'> </span></span></u></b></a></p> <h3 style='margin-right:-14.4pt'><a name="node6" id= "node6"></a>1.4.2. L'instruction CASE</h3> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Cette syntaxe, qui diffère du CASE normal , va permettre d' effectuer une action si une condition est vérifiée.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Il existe deux types de syntaxes : la première se rapprochant de l'utilisation du 'SWITCH' dans les langages de programmation, et</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>l'autre étant équivalente à une succession de 'IF ...</span> <span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">ELSE IF ... ELSE IF ... ELSE ...' </span> <span style= 'font-size:11.0pt'>.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Exemple Syntaxe1 :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>…</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>CASE var</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>WHEN 1 THEN SELECT 'Je suis 1';</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>WHEN 2 THEN SELECT 'Je suis 2';</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>ELSE SELECT 'Je suis autre chose que 1 et 2';</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END CASE;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Dans ce cas , la variable 'var' est comparée à chaque cas rencontré, et si aucune des valeurs n'est vérifiée dans les WHEN, alors</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>l'instruction après ELSE sera exécutée.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Exemple Syntaxe2 :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>…</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>CASE</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>WHEN var = 1 THEN SELECT 'Je suis 1';</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>WHEN var = 2 THEN SELECT 'Je suis 2';</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>ELSE SELECT 'Je suis autre chose que 1 et 2';</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END CASE;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Dans ce cas , la variable 'var' n'est pas évaluée ; au lieu de cela, nous passons d' un bloc de WHEN à l'autre en comparant si une expression est vraie. Si c'est le cas, nous exécutons alors l'instruction qui la suit, sinon, si aucune des expressions n'est vérifiée, alors l'instruction du ELSE est effectuée.</span></p> <p class="MsoNormal"><b><u><span style= 'font-size:16.0pt;font-family:"Courier New"'><span style= 'text-decoration:none'> </span></span></u></b></p> <span style= 'font-size:10.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <h2 style='margin-left:0cm'><a name="node7" id= "node7"></a><span style='font-size:14.0pt'>1.5. Les structures itératives</span></h2> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>PL/SQL nous offre la possibilité d'effectuer des traitements itératifs grâce à 3 types d'instructions : LOOP, REPEAT et WHILE.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <h3 style='margin-right:-14.4pt'><a name="node8" id= "node8"></a>1.5.1. L'instruction LOOP</h3> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>C'est une structure de boucle qui répète un bloc d'instructions tant qu'elle ne rencontre pas une instruction LEAVE pour l'arrêter.</span></p> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:3.0pt; margin-left:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB">Exemple :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE loop1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">LOOP</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END LOOP;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Aucune condition d'arrêt n'a été rencontrée, et une erreur ne sera rencontrée que lorsque la valeur maximale val i de INT sera rencontrée, soit le chiffre : 2 147 483 647.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Il est possible d'arrêter brusquement la boucle à l'aide d'une instruction du type : LEAVE nom_label</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Exemple d'arrêt lorsqu'un label est présent :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE loop1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">je_suis_un_label: LOOP</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> IF i = 10 THEN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> LEAVE je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> END IF;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">END LOOP je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Le code s'exécute, et si i vaut 10, alors la boucle est arrêtée grâce au nom du label.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Le label indiqué avant l'instruction de boucle doit être le même que celui qui la termine.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <h3 style='margin-right:-14.4pt'><a name="node9" id= "node9"></a>1.5.2. L'instruction REPEAT</h3> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Ce type de boucle permet de répéter un bloc d'instructions jusqu'à ce qu'une condition soit vérifiée.</span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Exemple :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE repeat1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">REPEAT</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">UNTIL i = 10 END REPEAT;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Une boucle est lancée jusqu'à ce que la valeur de variable locale i soit égale à 10.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Plus court que la version avec LOOP pour un résultat identique.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les labels sont supportés de la même manière qu'avec LOOP et prennent donc la même syntaxe.</span></p> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <h3 style='margin-right:-14.4pt'><a name="node10" id= "node10"></a><span style='font-weight:normal'>1.5.3. L'instruction WHILE</span></h3> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>C'est une structure de type boucle qui répète un bloc d'instructions tant qu'une condition est vraie.</span></p> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE">Exemple :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE while1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">WHILE i < 10 DO</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">END WHILE;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Tant que la variable locale i est inférieure à 10, le bloc d'instructions est exécuté.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <h3 style='margin-right:-14.4pt'><a name="node11" id= "node11"></a>1.5.4. L'instruction ITERATE</h3> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>ITERATE ne peut être utilisée qu'à l'intérieur d'une boucle LOOP, REPEAT ou WHILE et signifie : "exécute encore une fois la boucle".</span></p> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE">Exemple avec LOOP :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE loop1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">je_suis_un_label: LOOP</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> IF i < 10 THEN ITERATE je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> END IF;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>SELECT 'Je suis ', i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> LEAVE je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END LOOP je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>La boucle se lance, fait ses instructions , arrive sur la condition qui vérifie que la variable locale i est inférieure à 10 ; Si c'est le cas, la boucle est relancée.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Exemple avec REPEAT :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE repeat1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">je_suis_un_label: REPEAT</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> IF i < 10 THEN ITERATE je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> END IF;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>SELECT 'Je suis ', i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">UNTIL i < 20 END REPEAT je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">END |</span></p> </div> <span lang="EN-GB" style= 'font-size:11.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB"><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Exemple avec WHILE :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE PROCEDURE while1()</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DECLARE i INT DEFAULT 0;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">je_suis_un_label: WHILE i < 10 DO</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SET i := i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> IF i < 10 THEN ITERATE je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> END IF;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT 'Je suis ', i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END WHILE je_suis_un_label;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>END|</span></p> </div> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> </span></p> <span style= 'font-size:10.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><b><u><span style= 'font-size:16.0pt;font-family:"Courier New"'>Préambule : La validation, l’annulation et la notion de transaction</span></u></b></p> <p class="MsoNormal"><b><u><span style= 'font-size:16.0pt;font-family:"Courier New"'><span style= 'text-decoration:none'> </span></span></u></b></p> <p class="MsoBodyText">Nous avons vu que par défaut, toute erreur non interceptée ainsi que toute erreur interceptée pour laquelle le bloc d’exception contient raise_application_error provoquent l’annulation de toutes les mises à jour (au sens large). Dans les autres cas, les mises à jour sont validées (c’est à dire enregistrées).</p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Il est possible de demander explicitement la validation des mises à jour à n’importe quel(s) endroit(s) d’un bloc Pl/Sql, il suffit pour cela de placer l’instruction <i>commit ;</i> à l’endroit (ou aux endroits) souhaité (s).</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Toute erreur non interceptée ou toute erreur interceptée et renvoyée avec raise_application_error provoquera alors uniquement l’annulation des mises à jour effectuées depuis le dernier commit.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Pour demander explicitement l’annulation des mises à jour à n’importe quel endroit du bloc, il faut placer l’instruction <i>rollback ;</i></span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Quand l’exécution d’un bloc Pl/Sql démarre, démarre alors une <b>transaction</b> (il y a une instructionimplicite de démarrage de transaction) et quand l’exécution du bloc prend fin, il y a une instruction implicite de fin de transaction.</span></p> <p class="MsoNormal"><b><span style='font-size:11.0pt'>Une transaction est un ensemble indivisible d’opérations</span></b><span style='font-size:11.0pt'>c’est à dire que l’ensemble des opérations sera validé ou annulé.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoBodyText">Par défaut, MySql considère tout le bloc comme une seule transaction car s’il y a une erreur non interceptée ou bien une erreur interceptée et renvoyée, l’ensemble des opérations est annulé sinon l’ensemble des opérations est validé.</p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Le développeur peut faire plusieurs transactions à l’intérieur du bloc, en effet s’il place un commit ou un rollback dans le bloc, cela met fin à la transaction en cours et une autre transaction démarre.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoBodyText">Cette notion de transaction est très importante (par exemple pour garantir le débit/crédit lors d’opérations sur des comptes).</p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="Titre1" style= 'margin-top:0cm;margin-right:57.6pt;margin-bottom:0cm; margin-left:18.0pt;margin-bottom:.0001pt'> <span style='text-decoration:none'>2.<span style= 'font:7.0pt "Times New Roman"'> </span></span> LES PROCEDURES ET FONCTIONS STOCKEES</p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-family:Courier'> </span></p> <p class="MsoNormal"><b><u><span style= 'font-size:16.0pt'><span style= 'text-decoration: none'> </span></span></u></b></p> <h2 style='margin-left:0cm'><a name="node12" id= "node12"></a><span style='font-size:16.0pt'>2.1. Définition et utilisation</span></h2> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les procédures ou fonctions stockées sont des <b><u>blocs PL/SQL nommés</u></b> que l'on définit et stocke une seule fois dans la base.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les procédures ou fonctions stockées peuvent être paramétrées.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>La notion de procédure ou fonction stockée a été conçue dans l'esprit de grouper un ensemble de commandes SQL avec des instructions procédurales, pour constituer une unité de traitement pouvant être appelée à partir de n'importe quel programme ou directement sous MySql.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><b><i><span style= 'font-size: 11.0pt'>Intérêts d’une procédure ou fonction stockée au niveau du développement :</span></i></b></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>une procédure ou fonction stockée peut être appelée par plusieurs applications et elle reste indépendante de la partie appelante :</span></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt;font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>augmentation de la productivité lors de l’écriture des applications,</span></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt;font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>la recompilation d’une procédure ou fonction n’exige pas la recompilation de l’ensemble du code de l’application.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><b><i><span style= 'font-size: 11.0pt'>Intérêts d’une procédure lors de l’exécution de traitements :</span></i></b></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>réduction du trafic sur le réseau (le client envoie le nom de la procédure ou fonction à exécuter au lieu d'envoyer tout le texte d'une commande SQL, ceci sera revu dans le cours sur le client-serveur de traitements),</span></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>une procédure ou fonction est compilée lors de son stockage dans la base : à l’exécution, elle n'a plus à être compilée.</span></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt;font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>gain de temps.</span></p> <p class="MsoNormal" style='text-align:justify'> </p> <p class="MsoNormal" style='text-align:justify'> </p> <p class="MsoNormal" style='text-align:justify'><b><i><span style= 'font-size: 11.0pt'>Intérêts d’une procédure ou fonction au niveau de la sécurité :</span></i></b></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>une procédure ou fonction est un objet de la base appartenant à un utilisateur : les mécanismes de sécurité et de confidentialité fonctionnent (GRANT et REVOKE),</span></p> <p class="MsoNormal" style= 'margin-left:53.25pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:11.0pt'>-<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>l’accès à certaines tables peut n’être autorisé qu’au travers des procédures et fonctions : un utilisateur peut disposer d’autorisations d’exécution sur une procédure ou fonction sans disposer d’autorisation sur les tables manipulées par la procédure ou fonction</span> <span style= 'font-size:11.0pt;font-family:Wingdings'>è</span> <span style= 'font-size: 11.0pt'>on peut faire l'analogie avec les vues excepté qu'une vue est construite forcément sur un SELECT.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <span style= 'font-size:10.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><a name="_Toc317435370"></a><a name= "_Toc316913604"></a><a name="_Toc316913346"></a><a name= "_Toc316912945"></a><a name="_Toc316878224"></a><a name= "_Toc316836188"></a><a name="_Toc316833560"></a><a name= "_Toc316833334"></a><a name="_Toc316634934"></a><a name= "_Toc316634353"></a><a name="_Toc316634143"></a><a name= "_Toc316630118"></a><a name="_Toc316629551"></a><a name= "_Toc316562194"></a><a name="_Toc316561594"></a><a name= "_Toc305828868"></a><a name="_Toc305828207"></a><a name= "_Toc305824136"></a><a name="_Toc305821947"></a><a name= "_Toc305821357"></a><a name="_Toc305514292"></a><a name= "_Toc305430607"></a><a name="_Toc305429883"> </a></p> <h2 style='margin-left:0cm'><a name="node13" id= "node13"></a><span style='font-size:16.0pt'>2.2. Les procédures stockées</span></h2> <p class="MsoNormal" style='text-align:justify'><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Ecriture d'une procédure stockée</span></u></b></p> <p class="MsoNormal" style='margin-right:28.3pt'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='margin-right:28.3pt'><span style= 'font-size:11.0pt'>Version MySql :</span></p> <div style= 'border:solid black 1.0pt;border-bottom:solid windowtext 1.0pt; padding:0cm 0cm 1.0pt 0cm;margin-left:0cm;margin-right:28.3pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'>CREATE PROCEDURE nomprocedure [ (paramètres)]<br /></span></b><span style= 'font-size:11.0pt'> <i>-- bloc PL/Sql</i><br /> <b>BEGIN</b><br /> [Déclaration des variables locales;]<br /> Instructions SQL et PL/Sql;<br /> <b>END [nomprocedure];</b></span></p> </div> <p class="MsoNormal" style='margin-right:28.3pt'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='margin-right:28.3pt'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Remarques :</span></u></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Chaque paramètre est défini selon la syntaxe suivante :<br /> <b>nomparametre [IN | OUT | IN OUT] type</b><br /> IN : indique que le paramètre est passé en entrée (c'est le statut par défaut),<br /> OUT : indique que le paramètre est renseigné par la procédure,<br /> IN OUT : indique que le paramètre est en entrée-sortie.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-align:justify;text-indent: -14.15pt'> <span style='font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Le corps de la procédure commence par Begin et se termine par le mot-clé END suivi éventuellement par le nom de la procédure.</span></p> <p class="MsoNormal" style='margin-left:70.9pt'><span style= 'font-size:11.0pt'>Le corps d'une procédure comporte trois parties :</span></p> <p class="MsoNormal" style='margin-left:70.9pt'><span style= 'font-size:11.0pt'>- une partie déclarative qui est facultative,</span></p> <p class="MsoNormal" style='margin-left:70.9pt'><span style= 'font-size:11.0pt'>- une partie exécutable,</span></p> <p class="MsoNormal" style='margin-left:70.9pt'><span style= 'font-size:11.0pt'>- une partie optionnelle pour la gestion des erreurs.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-align:justify;text-indent: -14.15pt'> <span style='font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>La partie déclarative contient la déclaration des variables locales. Le mot-clé DECLARE est obligatoire devant chaque variable.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-align:justify;text-indent: -14.15pt'> <span style='font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>La partie exécutable doit au moins comporter une instruction.</span></p> <u><span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span></u> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'><span style= 'text-decoration:none'> </span></span></u></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Exemple : Augmentation du salaire de l'employé dont le n° est transmis, le montant à ajouter est également transmis</span></u></p> <p class="MsoNormal"><span style= 'font-size:12.0pt'> </span></p> <p class="MsoNormal" style='margin-left:2.0cm'><span lang="EN-GB" style='font-size: 12.0pt' xml:lang="EN-GB">Version MySql :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB">CREATE PROCEDURE AugmenterSalaire(IN NumSal double, montant double)</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> DECLARE SalaireActuel double;</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> Select sal</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> into SalaireActuel</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> From emp</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> Where empo = NumSal;</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> Update emp</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang="EN-GB"> set sal = sal + montant</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:12.0pt' xml:lang= "EN-GB"> </span> <span style= 'font-size:12.0pt'>Where empo = NumSal;</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span style= 'font-size:12.0pt'>END |</span></p> </div> <p class="MsoBodyTextIndent" style= 'margin:0cm;margin-bottom:.0001pt'> </p> <p class="MsoBodyTextIndent">Il n'y a aucun standard pour l'écriture des procédures stockées en pseudo-code. On peut proposer l'algorithme suivant pour la procédure ci-dessus :</p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'>PROCEDURE AugmenterSalaire (données : NumSal : entier, montant : réel)</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'> SalaireActuel : réel</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'> SalaireNull : Exception</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'>DEBUT</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'> Select sal</span></p> <p class="MsoNormal" style= 'line-height:90%;border:none;padding:0cm'><span style= 'font-size:11.0pt;line-height:90%'> </span> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang="EN-GB">into SalaireActuel</span></p> <p class="MsoNormal" style= 'line-height:90%;border:none;padding:0cm'><span lang="EN-GB" style= 'font-size:11.0pt;line-height:90%' xml:lang= "EN-GB"> From emp</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang="EN-GB"> Where empno = NumSal</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang="EN-GB"> </span> <span style= 'font-size:11.0pt;line-height:90%'>SI SalaireActuel = NULL</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> ALORS déclencher exception SalaireNull</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> </span> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang="EN-GB">SINON</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang= "EN-GB"> Update emp</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang= "EN-GB"> set sal = sal + montant</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang="EN-GB"> Where empno = NumSal</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt;line-height:90%' xml:lang="EN-GB"> </span> <span style= 'font-size:11.0pt;line-height:90%'>FSI</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'>EXCEPTION</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> SUIVANT exception</span></p> <p class="MsoNormal" style= 'line-height:90%;border:none;padding:0cm'><span style= 'font-size:11.0pt;line-height:90%'> NonTrouvé :</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> Envoyer l'erreur 'Employé inconnu'</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> SalaireNull :</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> Envoyer l'erreur 'Salaire non renseigné'</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style= 'font-size:11.0pt;line-height:90%'> FSUIVANT</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;line-height:90%;border:none; padding:0cm'> <span style='font-size:11.0pt;line-height:90%'>FIN</span></p> </div> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> </p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> </p> <p class="MsoNormal" style= 'margin-left:0cm;text-align:justify;text-indent:0cm'><span style= 'font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Création ou remplacement d'une procédure stockée</span></u></b></p> <p class="MsoNormal" style='text-align:justify'> </p> <p class="MsoNormal" style= 'margin-right:-7.1pt;text-align:justify;text-indent: 1.0cm'> <span style='font-size:11.0pt'>En MySql, la création de la procédure peut se faire sous l'onglet Sql. Il faut penser à changer le délimitateur qui est ; par défaut. Mettre par exemple |.</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> </p> <span style= 'font-size:10.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> </p> <p class="MsoNormal" style= 'margin-left:74.7pt;text-align:justify;text-indent: -74.7pt'> <span style='font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Mise au point d'une procédure stockée</span></u></b></p> <p class="MsoNormal" style='text-align:justify'> </p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Un message est affiché en cas d'erreur. Sinon le message "procédure créée…" est affiché.</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> </p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Appel d'une procédure stockée</span></u></b></p> <p class="MsoNormal"> </p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'>Ex. : CALL AugmenterSalaire(7369, 200);</p> </div> <p class="MsoNormal"> </p> <p class="MsoNormal" style='text-align:justify'> <b><i><u><span style='font-size: 11.0pt'>. Dans un bloc pl/sql ou dans une autre procédure ou fonction stockée :</span></u></i></b></p> <h2><a name="node14" id="node14"></a><span style= 'font-size:11.0pt;font-family:"Times New Roman";font-weight: normal'>Ex. :</span></h2> <p class="MsoNormal" style='text-align:justify'><i><span style= 'font-size:11.0pt'>. Avec des constantes en paramètres :</span></i></p> <p class="MsoNormal">BEGIN</p> <h2 style='margin-left:0cm;text-indent:35.45pt'><a name="node15" id="node15"></a><span style= 'font-size:11.0pt; font-family:"Times New Roman";font-weight:normal;text-decoration:none'>CALL AugmenterSalaire(7369,10.10);</span></h2> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> <b>…</b></span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>END;</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'> <b><i><u><span style='font-size: 11.0pt'>. Dans un programme hôte :</span></u></i></b></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'text-decoration: none'> </span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Non étudié ici.</span></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'text-decoration: none'> </span></u></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'text-decoration: none'> </span></u></p> <h2 style='margin-left:74.7pt;text-indent:-74.7pt'><a name= "_Toc317435372"></a><a name="_Toc316913606"></a><a name= "_Toc316913348"></a><a name="_Toc316912947"></a><a name= "_Toc316878226"></a><a name="_Toc316836190"></a><a name= "_Toc316833562"></a><a name="_Toc316833336"><span style= 'font-size:14.0pt;font-family:Symbol; font-weight:normal;text-decoration:none'>·<span style='font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:14.0pt'>Suppression d'une procédure</span></a> <span style= 'font-size:14.0pt'>stockée</span></h2> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style='font-size:11.0pt'>La suppression est assurée par la commande suivante :</span></p> <p class="MsoNormal"> </p> <div style= 'border:solid black 1.0pt;padding:0cm 0cm 0cm 0cm;margin-left:0cm; margin-right:28.3pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'><br /> DROP PROCEDURE nomprocedure;<br /> <br /></span></b></p> </div> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <h2 style='margin-left:0cm'><a name="node16" id= "node16"></a><span style='font-size:16.0pt'>2.3. Les fonctions stockées</span></h2> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <p class="MsoNormal"><span style= 'font-size:9.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>La structure d'une fonction est identique à celle d'une procédure, mais les fonctions possèdent une clause RETURNS type en "entête" et au moins une clause RETURN(valeur).</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Tout ce qui a été dit concernant les droits pour une procédure stockée est également valable pour les fonctions stockées.</span></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 9.0pt'><span style= 'text-decoration:none'> </span></span></u></b></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-align:justify;text-indent: -36.0pt'> <span style='font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Ecriture d'une fonction stockée</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:9.0pt'> </span></p> <div style= 'border:solid black 1.0pt;padding:0cm 0cm 0cm 0cm;margin-left:0cm; margin-right:7.05pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'>CREATE [OR REPLACE] FUNCTION nomfonction [ (paramètres)]<br /> RETURNS type<br /> [DETERMINISTIC]<br /></span></b><span style= 'font-size:11.0pt'> <b>BEGIN</b><br /> [Déclarations de variables locales;]<br /> Instructions SQL et PL/Sql;<br /> <b>RETURN(Valeur</b>);<br /> <b>END ;</b></span></p> </div> <b><u><span style= 'font-size:9.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span></u></b> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 9.0pt'><span style= 'text-decoration:none'> </span></span></u></b></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'>Remarques :</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:9.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Chaque paramètre est défini selon la syntaxe suivante : <b>nomparametre [IN | OUT | IN OUT] type</b><br /> <br /></span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Il est possible de placer plusieurs return(valeur) dans le corps de la fonction. Un return(valeur) provoque le retour immédiat à l'appelant avec une valeur de retour.</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:9.0pt;font-family:Courier'> </span></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Exemple : Calcul du nombre moyen d'années d'ancienneté pour les employés exerçant le job transmis en paramètre</span></u></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-family:Courier'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB">DELIMITER |</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE FUNCTION MoyenneAnc (jobsai VARCHAR(30)) RETURNS float</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB">DETERMINISTIC</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB">BEGIN</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> DECLARE ancmoyen float;</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> select avg(YEAR(curdate()) - YEAR(hiredate))</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> into ancmoyen</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> from emp</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> where job = jobsai;</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>RETURN (ancmoyen);</span></p> <p class="MsoNormal" style= 'text-align:justify;text-indent:2.0cm;border:none; padding:0cm'> <span style='font-size:11.0pt'>END |</span></p> </div> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'>Il n'y a aucun standard pour l'écriture des fonctions stockées en pseudo-code. On peut proposer l'algorithme suivant pour la fonction ci-dessus :</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <div style= 'border:solid windowtext 1.0pt;border-bottom:none;padding:1.0pt 0cm 0cm 4.0pt'> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span style= 'font-size:11.0pt'>FONCTION MoyenneAnc (donnée : jobsai : chaîne de caractères) : réel</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">ancmoyen : réel</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DEBUT</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> select avg(YEAR(curdate()) - YEAR(hiredate))</span></p> </div> <div style= 'border-top:none;border-left:solid windowtext 1.0pt;border-bottom: none;border-right:solid windowtext 1.0pt;padding:0cm 28.0pt 0cm 4.0pt; margin-left:0cm;margin-right:28.3pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> into ancmoyen</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> from emp</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> where job = jobsai</span></p> </div> <div style= 'border:solid windowtext 1.0pt;border-top:none;padding:0cm 0cm 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>retourner (ancmoyen)</span></p> <p class="MsoNormal" style= 'text-indent:2.0cm;border:none;padding:0cm'><span style= 'font-size:11.0pt'>FIN</span></p> </div> <p class="MsoNormal" style='text-indent:2.0cm'><span style= 'font-size:11.0pt'> </span></p> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-indent:2.0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:74.7pt;text-align:justify;text-indent: -74.7pt'> <span style='font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Création ou remplacement d'une fonction stockée</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Idem procédure stockée.</span></p> <p class="MsoNormal" style='margin-top:6.0pt;text-align:justify'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:74.7pt;text-align:justify;text-indent: -74.7pt'> <span style='font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Mise au point d'une fonction stockée</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Idem procédure stockée.</span></p> <p class="MsoNormal" style='margin-top:6.0pt;text-align:justify'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-align:justify;text-indent: -36.0pt'> <span style='font-size:14.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Appel à une fonction stockée</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><b><span style= 'font-size:11.0pt'>SOUS MYSQL : SELECT MoyenneAnc('CLERK');</span></b></p> </div> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <h2 style='margin-left:18.0pt;text-indent:-18.0pt'><a name="node17" id="node17"></a><span style= 'font-size:14.0pt; font-family:Symbol;font-weight:normal;text-decoration:none'>·<span style='font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:14.0pt'>Suppression d'une fonction stockée</span></h2> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style='font-size:11.0pt'>La suppression est assurée par la commande suivante :</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <div style= 'border:solid black 1.0pt;padding:0cm 0cm 0cm 0cm;margin-left:0cm; margin-right:28.3pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'><br /> DROP FUNCTION nomfonction ;<br /> <br /></span></b></p> </div> <span style= 'font-size:10.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"> </p> <p class="Titre1"><span style='text-decoration:none'>3.<span style= 'font:7.0pt "Times New Roman"'> </span></span> LES TRIGGERS (OU DECLENCHEURS)</p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-family:Courier'> </span></p> <h2><a name="_Toc317435389"></a><a name= "_Toc316913623"></a><a name="_Toc316913365"></a><a name= "_Toc316912964"></a><a name="_Toc316878243"></a><a name= "_Toc316836207"></a><a name="_Toc316833576"></a><a name= "_Toc316833350"></a><a name="_Toc316653898"></a><a name= "_Toc306550832"></a><a name="_Toc306550735"></a><a name= "_Toc306550477"></a><a name="_Toc306378154"><span style= 'font-size:14.0pt'>3.1. Définition</span></a></h2> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-family:Courier'> </span></p> <p class="MsoNormal" style= 'margin-left:70.9pt;text-align:justify;text-indent: -14.2pt'> <span style='font-size:11.0pt'>*<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Un trigger base de données est un ensemble de traitements PL/Sql.</span></p> <p class="MsoNormal" style= 'margin-left:70.9pt;text-align:justify;text-indent: -14.2pt'> <span style='font-size:11.0pt'>*<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Un trigger base de données est déclenché automatiquement par un ou plusieurs événements prédéfinis,<br /> il ne peut donc pas être appelé directement.</span></p> <p class="MsoNormal" style= 'margin-left:70.9pt;text-align:justify;text-indent: -14.2pt'> <span style='font-size:11.0pt'>*<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Un trigger base de données est attaché à une et une seule table.</span></p> <p class="MsoNormal" style= 'margin-left:70.9pt;text-align:justify;text-indent: -14.2pt'> <span style='font-size:11.0pt'>*<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Si une table est supprimée, les triggers base de données qui lui sont associés sont automatiquement supprimés.</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Un trigger base de données est identifié par cinq caractéristiques :</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-align:justify;text-indent: -14.15pt'> <span style='font-size:11.0pt'>1.<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Un <b><u>séquencement</u></b>,</span></p> <p class="MsoNormal" style='margin-left:70.9pt;text-align:justify'> <span style='font-size:11.0pt'>BEFORE ou AFTER : on précise si le trigger se déclenche avant ou après l'événement.</span></p> <p class="MsoNormal" style='margin-left:70.9pt;text-align:justify'> <span style='font-size:11.0pt'>Les triggers BEFORE sont généralement utilisés pour faire des contrôles.</span></p> <p class="MsoNormal" style='margin-left:70.9pt;text-align:justify'> <span style='font-size:11.0pt'>Les triggers AFTER sont généralement utilisés pour faire des mises à jour consécutives à l’ordre SQL déclencheur.</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-align:justify;text-indent: -14.15pt'> <span style='font-size:11.0pt'>2.<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Un <b><u>événement</u></b> qui le déclenche,</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:14.1pt'> <span style='font-size:11.0pt'>INSERT, UPDATE, DELETE : cet événement concerne une et une seule table, celle-ci devra être</span></p> <p class="MsoNormal" style= 'margin-left:56.75pt;text-align:justify;text-indent: 14.1pt'> <span style='font-size:11.0pt'>précisée.</span></p> <p class="MsoNormal" style= 'margin-left:70.8pt;text-align:justify;text-indent: -14.1pt'> <span style='font-size:11.0pt'>3.<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'> Un <b><u>type</u></b>,</span></p> <p class="MsoNormal" style='margin-left:70.8pt;text-align:justify'> <span style='font-size:11.0pt'>par LIGNE : accès aux informations de la ligne ayant déclenché le trigger et vérification des valeurs par rapport aux autres lignes de la table.</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-align:justify;text-indent: -14.15pt'> <span style='font-size:11.0pt'>4.<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Les <b><u>ordres</u></b> du traitement (bloc pl/sql).</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Les combinaisons possibles sont les suivantes :</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style= 'margin-left:60.2pt;border-collapse:collapse'> <tr> <td width="132" valign="top" style= 'width:99.3pt;border:solid windowtext 1.0pt; border-bottom:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><b><span style= 'font-size:11.0pt'>Séquencement</span></b></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border-top:solid windowtext 1.0pt; border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><b><span style= 'font-size:11.0pt'>Evénement</span></b></p> </td> </tr> <tr> <td width="132" valign="top" style= 'width:99.3pt;border:solid windowtext 1.0pt; border-bottom:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'>BEFORE</span></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border-top:solid windowtext 1.0pt; border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE">INSERT</span></p> </td> </tr> <tr> <td width="132" valign="top" style= 'width:99.3pt;border-top:none;border-left: solid windowtext 1.0pt;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE"> </span></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border-top:solid windowtext 1.0pt; border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE">UPDATE</span></p> </td> </tr> <tr> <td width="132" valign="top" style= 'width:99.3pt;border-top:none;border-left: solid windowtext 1.0pt;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE"> </span></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border-top:solid windowtext 1.0pt; border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="DE" style='font-size:11.0pt' xml:lang="DE">DELETE</span></p> </td> </tr> <tr> <td width="132" valign="top" style= 'width:99.3pt;border:solid windowtext 1.0pt; border-bottom:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">AFTER</span></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border-top:solid windowtext 1.0pt; border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'>INSERT</span></p> </td> </tr> <tr> <td width="132" valign="top" style= 'width:99.3pt;border-top:none;border-left: solid windowtext 1.0pt;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border-top:solid windowtext 1.0pt; border-left:none;border-bottom:none;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'>UPDATE</span></p> </td> </tr> <tr> <td width="132" valign="top" style= 'width:99.3pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> </td> <td width="104" valign="top" style= 'width:77.95pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'>DELETE</span></p> </td> </tr> </table> <p class="MsoNormal" style='text-align:justify'><span lang="DE" style='font-size: 11.0pt' xml:lang="DE"> </span></p> <span lang="DE" style= 'font-size:11.0pt;font-family:"Times New Roman"' xml:lang= "DE"><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify'><span lang="DE" style='font-size: 11.0pt' xml:lang="DE"> </span></p> <h2><a name="_Toc317435390"></a><a name= "_Toc316913624"></a><a name="_Toc316913366"></a><a name= "_Toc316912965"></a><a name="_Toc316878244"></a><a name= "_Toc316836208"></a><a name="_Toc316833577"></a><a name= "_Toc316833351"></a><a name="_Toc316653899"></a><a name= "_Toc306550833"></a><a name="_Toc306550736"></a><a name= "_Toc306550478"></a><a name="_Toc306378155"><span style= 'font-size:14.0pt'>3.2. Ecriture d'un trigger</span></a></h2> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <div style= 'border:solid windowtext 1.0pt;border-bottom:none;padding:1.0pt 0cm 0cm 1.0pt; margin-left:0cm;margin-right:148.8pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><u><span lang="EN-US" style='font-size:11.0pt' xml:lang= "EN-US"><br /></span></u></b><span lang="EN-US" style= 'font-size:11.0pt' xml:lang="EN-US"> <b><u>CREATE OR REPLACE TRIGGER</u></b><u> n</u>omtrigger</span></p> </div> <div style= 'border:solid windowtext 1.0pt;border-top:none;padding:0cm 29.0pt 1.0pt 1.0pt; margin-left:0cm;margin-right:177.15pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-US" style='font-size:11.0pt' xml:lang="EN-US"> Séquencement (before ou after)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> Evénement (insert, update ou delete)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> <b> ON</b> nom de la table</span></p> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'> FOR EACH ROW</span></b></p> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'> BEGIN</span></b></p> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'> </span></b> <span style='font-size:11.0pt'>ordres du traitement;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> <b>END </b> [nomtrigger];</span></p> </div> <p class="MsoNormal"><b><u><span style= 'font-size:11.0pt'><span style= 'text-decoration: none'> </span></span></u></b></p> <p class="MsoNormal"><b><u><span style='font-size:11.0pt'>Remarques :</span></u></b></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:74.7pt;text-indent:-18.0pt'><span style= 'font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>UPDATE OF nomcolonne On nomtable : permet d'indiquer que le trigger est déclenché uniquement en cas de modification de la colonne stipulée.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:74.7pt;text-indent:-18.0pt'><span style= 'font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Type : le type par défaut est par ordre. Il faudra donc préciser le type uniquement si on souhaite la vérification au niveau ligne (ce qui est le cas le plus fréquent), pour cela, il faudra écrire : FOR EACH ROW.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:74.7pt;text-align:justify;text-indent: -18.0pt'> <span style='font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Le bloc pl/sql ne peut pas contenir d'instructions pour gérer les transactions (Commit, Rollback).</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><b><u><span style= 'font-size:11.0pt'>Référencement des valeurs des colonnes :</span></u></b></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Pour référencer l'ancienne et/ou la nouvelle valeur d'une colonne, on utilise deux variables prédéfinies OLD et NEW.</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style= 'margin-left:60.2pt;border-collapse:collapse'> <tr> <td width="85" valign="top" style= 'width:63.85pt;border:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><b><span style= 'font-size:11.0pt'> </span></b></p> </td> <td width="170" valign="top" style= 'width:127.6pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><b><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">OLD</span></b></p> </td> <td width="189" valign="top" style= 'width:5.0cm;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><b><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">NEW</span></b></p> </td> </tr> <tr> <td width="85" valign="top" style= 'width:63.85pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">INSERT</span></p> </td> <td width="170" valign="top" style= 'width:127.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style='font-size:11.0pt'>NULL</span></p> </td> <td width="189" valign="top" style= 'width:5.0cm;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style='font-size:11.0pt'>nouvelle valeur</span></p> </td> </tr> <tr> <td width="85" valign="top" style= 'width:63.85pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'>UPDATE</span></p> </td> <td width="170" valign="top" style= 'width:127.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style='font-size:11.0pt'>ancienne valeur</span></p> </td> <td width="189" valign="top" style= 'width:5.0cm;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style='font-size:11.0pt'>nouvelle valeur</span></p> </td> </tr> <tr> <td width="85" valign="top" style= 'width:63.85pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style= 'font-size:11.0pt'>DELETE</span></p> </td> <td width="170" valign="top" style= 'width:127.6pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style='font-size:11.0pt'>ancienne valeur</span></p> </td> <td width="189" valign="top" style= 'width:5.0cm;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.55pt 0cm 3.55pt'> <p class="MsoNormal"><span style='font-size:11.0pt'>NULL</span></p> </td> </tr> </table> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span style='font-size:11.0pt'>L'accès à une colonne de :new et de :old s’écrit respectivement :</span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <b><span style= 'font-size:11.0pt'>new.nomcolonne </span></b></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <b><span style='font-size:11.0pt'>old.nomcolonne</span></b></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'><span style= 'text-decoration:none'> </span></span></u></b></p> <b><u><span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span></u></b> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'><span style= 'text-decoration:none'> </span></span></u></b></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'>Exemples :</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'><span style= 'text-decoration:none'> </span></span></u></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Si on a l'ordre insert suivant :</span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>insert into dept</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>values (90, 'Informatique', 'Immeuble A');</span></p> <p class="MsoBodyTextIndent"><b>Dans le trigger correspondant à l'insertion dans la table dept, new.deptno vaudra 90, new.dname vaudra Informatique et new.loc vaudra Immeuble A.</b></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'><span style= 'text-decoration:none'> </span></span></u></b></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Si on a l'ordre update suivant :</span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>update dept</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>set loc = 'Immeuble B'</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>where deptno=90;</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Dans le trigger correspondant à la modification dans la table dept, new.deptno vaudra 90, new.dname vaudra Informatique et new.loc vaudra Immeuble B. old.deptno vaudra 90, old.dname vaudra Informatique et old.loc vaudra Immeuble A.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Si on a l'ordre delete suivant :</span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>delete dept</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>where deptno=90;</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Dans le trigger correspondant à la suppression dans la table dept, old.deptno vaudra 90, old.dname vaudra Informatique et old.loc vaudra Immeuble B.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'>Déclenchement d’erreurs :</span></u></b></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Le trigger permet de vérifier la cohérence d’une instruction SQL de mise à jour : Insert, Update ou Delete. Il est souvent nécessaire de provoquer une erreur dans le trigger pour empêcher cette mise à jour.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>La commande permettant de déclencher une erreur personnalisée est de type :</span></p> <p class="MsoNormal" align="center" style='text-align:center'> <span style='font-size:11.0pt'>signal sqlstate "25000" set message_text ="<i>Votre message d'erreur</i>"<i>"</i></span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>"25000" est un exemple de numéro d'erreur. Il faut juste choisir un nombre supérieur à 20000.</span></p> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify'><u><span style= 'font-size:11.0pt'>Exemple : trigger pour refuser l’insertion d’un employé si sa commission ajoutée au total des commissions dépasse le total autorisé :</span></u></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:8.0pt'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt;color:#00B050'>-- Suppression du trigger ctrlcomm si celui-ci existe</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-US" style='font-size:11.0pt' xml:lang="EN-US">DROP TRIGGER IF EXISTS ctrlcomm;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-US" style='font-size:9.0pt' xml:lang="EN-US"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DELIMITER |</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-US" style='font-size:11.0pt;color:#00B050' xml:lang="EN-US">-- Création du trigger</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-US" style='font-size:11.0pt' xml:lang="EN-US">CREATE TRIGGER ctrlcomm BEFORE INSERT</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">ON emp FOR EACH ROW</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> DECLARE totcomm float;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:9.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT sum(comm)</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> INTO totcomm</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM emp;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:9.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> If (totcomm + new.comm) > 20000 then</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'> signal sqlstate "32000" set message_text ="Erreur-Commission trop importante"</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'> end if;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>end |</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>DELIMITER ;</span></p> </div> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:8.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>Il n'y a aucun standard pour l'écriture des triggers en pseudo-code. On peut proposer l'algorithme suivant pour le trigger ci-dessus :</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:8.0pt'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>TRIGGER ctrlcomm</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>Déclenchement : avant insertion dans table emp</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>Type trigger : niveau ligne</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>totcomm : réel</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">tot : exception</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:8.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DEBUT</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> select sum(comm)</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> into totcomm</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> from emp</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SI totcomm + :new.comm > 20000</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size: 11.0pt'>ALORS</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'> Déclencher exception tot</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'> FSI</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:8.0pt'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>EXCEPTION</span></p> <p class="MsoNormal" style= 'text-indent:14.1pt;border:none;padding:0cm'><span style= 'font-size:11.0pt'>SI exception = tot</span></p> <p class="MsoNormal" style= 'text-indent:14.1pt;border:none;padding:0cm'><span style= 'font-size:11.0pt'>ALORS</span></p> <p class="MsoNormal" style= 'text-indent:14.1pt;border:none;padding:0cm'><span style= 'font-size:11.0pt'> Envoyer l'erreur "Erreur-Commission trop importante"</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'> FSI</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-size:11.0pt'>FIN</span></p> </div> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><b><u><span style= 'font-size: 11.0pt'>Remarque</span></u></b></p> <p class="MsoNormal" style='margin-top:6.0pt;text-align:justify'> <span style='font-size:11.0pt'>Sur MySql, contrairement à Oracle par exemple, un trigger base de données ne peut s'exécuter à la fois en INSERT et/ou en UPDATE et/ou en DELETE</span></p> <p class="Preformatted" style='margin-left:2.0cm'><span style= 'font-size:11.0pt; font-family:"Times New Roman"'> </span></p> <p class="Preformatted" style='margin-left:2.0cm'> <b><u><span style='font-family: "Times New Roman"'><span style= 'text-decoration:none'> </span></span></u></b></p> <h2><a name="node18" id="node18"></a><span style= 'font-size:14.0pt'>3.3 Création ou remplacement d'un trigger</span></h2> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'>La création ou le remplacement d'un trigger dans la base s'effectue sous l'onglet SQL. Le trigger (ou déclencheur) est associé à une table. Sur les versions récentes de PhpMyAdmin l'onglet <Déclencheur> propose une interface graphique pour créer ou modifier les triggers.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <h2><a name="_Toc317435393"></a><a name= "_Toc316913627"></a><a name="_Toc316913369"></a><a name= "_Toc316912968"></a><a name="_Toc316878247"></a><a name= "_Toc316836211"></a><a name="_Toc316833580"></a><a name= "_Toc316833354"></a><a name="_Toc316653902"></a><a name= "_Toc306550836"></a><a name="_Toc306550739"></a><a name= "_Toc306550481"></a><a name="_Toc306378158"><span style= 'font-size:14.0pt'>3.4. Suppression d’un trigger</span></a></h2> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span lang="EN-US" style='font-size:11.0pt' xml:lang= "EN-US"> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 1.0pt 1.0pt 1.0pt; margin-left:0cm;margin-right:106.25pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><u><span lang="EN-US" style='font-size:11.0pt' xml:lang= "EN-US"><br /></span></u></b><span lang="EN-US" style= 'font-size:11.0pt' xml:lang="EN-US"> <b><u>DROP TRIGGER</u></b> nomtrigger ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-US" style='font-size:11.0pt' xml:lang="EN-US"> </span></p> </div> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span lang="EN-US" style='font-size:11.0pt' xml:lang= "EN-US"> </span></p> <p class="MsoNormal" style='text-align:justify;text-indent:2.0cm'> <span lang="EN-US" style='font-size:11.0pt' xml:lang= "EN-US"> </span></p> <h2><a name="_Toc317435395"></a><a name= "_Toc316913629"></a><a name="_Toc316913371"></a><a name= "_Toc316912970"></a><a name="_Toc316878249"></a><a name= "_Toc316836213"></a><a name="_Toc316833582"></a><a name= "_Toc316833356"></a><a name="_Toc316653904"></a><a name= "_Toc306550838"></a><a name="_Toc306550741"></a><a name= "_Toc306550483"></a><a name="_Toc306378164"><span style= 'font-size:14.0pt'>3.5. Avantages de l'utilisation des triggers</span></a></h2> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Implémentation des contraintes sémantiques et donc refus des mises à jour (au sens large) incohérentes,</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Implémentation de règles de validation et de sécurité complexes (on peut par exemple contrôler la date et l'heure de l'opération effectuée),</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Réaction aux instructions UPDATE, DELETE, INSERT ce qui permet d'enclencher un traitement suite à l'une de ces actions,</span></p> <p class="MsoNormal" style= 'margin-left:70.85pt;text-indent:-14.15pt'><span style= 'font-size:11.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Maintenance de plusieurs tables "miroir" sur différents sites d'un réseau : on assure ainsi le fait que toutes les modifications soient répercutées sur tous les sites en même temps,</span></p> <p class="MsoNormal" style= 'margin-left:2.0cm;text-align:justify;text-indent: 0cm'> <span style='font-size:11.0pt'>*<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Augmentation de la productivité de développement car le code du trigger est écrit une seule fois au lieu d'être écrit dans chaque programme applicatif voulant effectuer les contrôles nécessaires,</span></p> <p class="MsoNormal" style= 'margin-left:2.0cm;text-align:justify;text-indent: 0cm'> <span style='font-size:11.0pt'>*<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>Constitution d'audits personnalisés.</span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <span style= 'font-size:11.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:11.0pt'> </span></p> <h2><a name="_Toc317435402"><span style='font-size:14.0pt'>3.6. Différences entre triggers et contraintes d'intégrité déclarative</span></a><span style='font-size:14.0pt'>s</span></h2> <p class="MsoNormal"><span style= 'font-family:Courier'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les triggers et les contraintes de tables peuvent être utilisés tous les deux pour assurer la cohérence de la base de données.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Cependant les triggers et les contraintes d'intégrité de tables ne fonctionnent pas tout à fait de la même manière.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Une contrainte d'intégrité déclarative (primary key, foreign key, not null, unique …) s'applique à toutes les données et ne pourra être créée si certaines données déjà en place ne la vérifient pas.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Les triggers ne vont pas vérifier l'ensemble des données en place lors de leur création. Ils effectuent un traitement seulement lors de leur déclenchement. De ce fait, un trigger ne peut pas garantir que toutes les données présentes dans une table sont conformes aux règles définies par le trigger associé à cette table.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Un trigger permet de gérer la transition des données. Il permet de vérifier une contrainte particulière au moment où une donnée change de valeur.</span></p> <p class="MsoNormal"> </p> </div>
Table des matières
Sujets
Recherche
ETUDE DU LANGAGE PL/SQL
1.1. Introduction
1.2. Structure d'un bloc Pl/SQL
1.3. L'affectation des variables PL/SQL
1.3.1. Principaux types de données
1.3.2. Syntaxe de déclaration des variables et constantes
1.4. Les structures conditionnelles
1.4.1. L'instruction IF … THEN … END IF
1.4.2. L'instruction CASE
1.5. Les structures itératives
1.5.1. L'instruction LOOP
1.5.2. L'instruction REPEAT
1.5.3. L'instruction WHILE
1.5.4. L'instruction ITERATE
2.1. Définition et utilisation
2.2. Les procédures stockées
Ex. :
CALL AugmenterSalaire(7369,10.10);
· Suppression d'une procédure stockée
2.3. Les fonctions stockées
· Suppression d'une fonction stockée
3.1. Définition
3.2. Ecriture d'un trigger
3.3 Création ou remplacement d'un trigger
3.4. Suppression d’un trigger
3.5. Avantages de l'utilisation des triggers
3.6. Différences entre triggers et contraintes d'intégrité déclaratives
1.1. Introduction
1.2. Structure d'un bloc Pl/SQL
1.3.1. Principaux types de données
1.3.2. Syntaxe de déclaration des variables et constantes
1.3. L'affectation des variables PL/SQL
1.4. Les structures conditionnelles
1.4.1. L'instruction IF … THEN … END IF
1.4.2. L'instruction CASE
1.5. Les structures itératives
1.5.1. L'instruction LOOP
1.5.2. L'instruction REPEAT
1.5.3. L'instruction WHILE
1.5.4. L'instruction ITERATE
2.1. Définition et utilisation
2.2. Les procédures stockées
2.3. Les fonctions stockées
3.1. Définition
3.2. Ecriture d'un trigger
3.3 Création ou remplacement d'un trigger
3.4. Suppression d’un trigger
3.5. Avantages de l'utilisation des triggers
3.6. Différences entre triggers et contraintes d'intégrité déclaratives
CALL AugmenterSalaire(7369,10.10);
ETUDE DU LANGAGE PL/SQL
Ex. :
· Suppression d'une procédure stockée
· Suppression d'une fonction stockée
Générée avec
chmProcessor
Générée avec
chmProcessor