<!-- If the browser does not support frames put the first topic content here --> <div class="WordSection1"> <p class="MsoNormal" style='text-align:justify'> </p> <p class="MsoNormal" style='text-align:justify'><span style= 'font-size:16.0pt'> <b>LES PROCEDURES ET LES FONCTIONS STOCKEES</b></span></p> <p class="MsoNormal" style='text-align:justify'><b><span style= 'font-size:14.0pt'> </span></b></p> <p class="MsoNormal" style='text-align:justify'><b><span style= 'font-size:14.0pt'>PROCEDURES</span></b></p> <p class="MsoNormal" style='margin-left:1.0cm;text-align:justify'> <b><span lang="EN-GB" xml:lang="EN-GB">Sous MySql :</span></b></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; margin-left:1.0cm;margin-right:0cm'> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">CREATE PROCEDURE augcomm(<b>nomvend varchar(50)</b>)</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><b><span lang="EN-GB" xml:lang="EN-GB">BEGIN</span></b></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><b><span lang="EN-GB" xml:lang="EN-GB"> DECLARE tx FLOAT;</span></b></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><b><span lang="EN-GB" xml:lang="EN-GB"> DECLARE moyenne FLOAT;</span></b></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><b><span lang="EN-GB" xml:lang="EN-GB"> DECLARE salaire FLOAT;</span></b></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> select sal into salaire from emp</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> where ename = nomvend</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> and job = 'SALESMAN';</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> select avg(sal) into moyenne from emp</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> where job = 'SALESMAN';</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> if salaire <= moyenne then</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang= "EN-GB"> <b>Set tx = 1.05;</b></span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> else</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang= "EN-GB"> <b>Set tx = 1.03;</b></span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> end if;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> update emp</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> set comm = comm * tx</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> </span> where ename = nomvend;</p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><b>END|</b></p> </div> <span style= 'font-size:10.0pt;font-family:"Times New Roman",serif'><br clear= "all" style='page-break-before:always' /></span> <p class="MsoNormal" style='margin-left:1.0cm'><b><span lang= "EN-GB" xml:lang="EN-GB">Sous MySql</span></b></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; margin-left:1.0cm;margin-right:0cm'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">CREATE Procedure hierarchie(nomemp varchar(50))</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> DECLARE metier varchar(40);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> DECLARE superieur int;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> DECLARE nom varchar(50);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> delete from resultat;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> select job, mgr into metier, superieur</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> from emp</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> where ename = nomemp;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> insert into resultat</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> values (nomemp, metier);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><b><span lang= "EN-GB" xml:lang="EN-GB"> WHILE (superieur is not NULL) DO</span></b></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> select job, mgr, ename</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> into metier, superieur, nom</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> from emp</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> where empno = superieur;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> insert into resultat</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> values (nom, metier);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><b><span lang= "EN-GB" xml:lang="EN-GB"> END WHILE ;</span></b></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">End|</span></p> </div> <p class="MsoNormal" style='text-align:justify;text-indent:35.4pt'> <span lang="EN-GB" xml:lang="EN-GB"> </span></p> <span lang="EN-GB" style= 'font-size:10.0pt;font-family:"Courier New"' xml:lang= "EN-GB"><br clear="all" style='page-break-before:always' /></span> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; margin-left:35.45pt;margin-right:0cm'> <p class="MsoPlainText" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">Sous MySql</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">DELIMITER |</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">CREATE PROCEDURE camois(pannee int)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> DECLARE tot float;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> DECLARE i int;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> DELETE FROM ca;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> SET i = 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> WHILE(i <= 12) DO</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> SELECT IFNULL(sum(qte * prixcons), 0)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> INTO tot</span></p> <p class="MsoNormal" style= 'text-indent:35.35pt;border:none;padding:0cm'><span lang="EN-GB" xml:lang= "EN-GB"> FROM facture f, ligne_fact l, conso c</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> WHERE f.numfact = l.numfact</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> AND c.numcons = l.numcons</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> AND YEAR(datefact) = pannee</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> AND MONTH(datefact) = i;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> INSERT INTO ca values (pannee, i, tot);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> SET i = i + 1;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang= "EN-GB"> END WHILE; </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" xml:lang="EN-GB">END |</span></p> </div> <p class="MsoNormal" style='text-align:justify'> Sous MySql.</p> <p class="MsoNormal" style='text-align:justify'> Call camois(2006);</p> <span style= 'font-size:10.0pt;font-family:"Times New Roman",serif'><br clear= "all" style='page-break-before:always' /></span> <p class="MsoNormal" style='text-align:justify'><b><span style= 'font-size:14.0pt'>FONCTIONS</span></b></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><b><span lang="EN-GB" xml:lang="EN-GB">Sous MySql</span></b></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; margin-left:35.4pt;margin-right:0cm'> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">create function meilleur(an int)</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">returns varchar(50)</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">DETERMINISTIC</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">begin</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">declare nom varchar(40);</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">select s.nomserv</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">into nom</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">from conso, ligne_fact, facture, serveur s</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'>where conso.numcons = ligne_fact.numcons</p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">and ligne_fact.numfact = facture.numfact</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">and s.numserv = facture.numserv</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">and year(datefact)=an</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> group by s.nomserv</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">having sum(prixcons*qte) >= all</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">(select sum(prixcons*qte)</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">from conso, ligne_fact, facture</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'>where conso.numcons = ligne_fact.numcons</p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">and ligne_fact.numfact = facture.numfact</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">and year(datefact)=an</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">group by facture.numserv);</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">return nom;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" xml:lang="EN-GB">end|</span></p> </div> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <i> </i></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <b>Sous MySql</b></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <i> Select NomFonction(paramètres) ;</i></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <i> </i></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> Pour visualiser les procédures ou fonctions :</p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <span lang="EN-GB" xml:lang="EN-GB">Show procedure status ou</span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <span lang="EN-GB" xml:lang="EN-GB">Show function status</span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> Pour visualiser la structure des procédures ou des fonctions :</p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <span lang="EN-GB" xml:lang="EN-GB">Show create procedure <i>nomprocedure</i> ou</span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <span lang="EN-GB" xml:lang="EN-GB">Show create function <i>nomfonction</i></span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-align:justify'> <span lang="EN-GB" xml:lang="EN-GB"> </span></p> </div>
Table des matières
Sujets
Recherche
Help content
Help content
Générée avec
chmProcessor
Générée avec
chmProcessor