LES PROCEDURES ET LES FONCTIONS STOCKEES

PROCEDURES

Sous MySql :

CREATE PROCEDURE augcomm(nomvend varchar(50))

BEGIN

   DECLARE           tx FLOAT;

   DECLARE           moyenne FLOAT;

   DECLARE           salaire FLOAT;

   select sal into salaire from emp

   where ename = nomvend

   and job = 'SALESMAN';

   select avg(sal) into moyenne from emp

   where job = 'SALESMAN';

   if salaire <= moyenne then

Set tx = 1.05;

   else

Set tx = 1.03;

   end if;

   update emp

   set comm = comm * tx

where ename = nomvend;

END|

Sous MySql

CREATE Procedure hierarchie(nomemp varchar(50))

BEGIN

   DECLARE metier varchar(40);

   DECLARE superieur int;

   DECLARE nom varchar(50);

   delete from resultat;

   select job, mgr into metier, superieur

   from emp

   where ename = nomemp;

   insert into resultat

   values (nomemp, metier);

   WHILE (superieur is not NULL) DO

                  select job, mgr, ename

                  into metier, superieur, nom

                  from emp

                   where empno = superieur;

                   insert into resultat

                   values (nom, metier);

   END WHILE ;

End|

Sous MySql

DELIMITER |

CREATE PROCEDURE camois(pannee int)

BEGIN

                DECLARE tot float;

                DECLARE i int;

                DELETE FROM ca;

                SET i = 1;

                WHILE(i <= 12) DO

                                SELECT IFNULL(sum(qte * prixcons), 0)

                                INTO tot

                FROM facture f, ligne_fact l, conso c

                                WHERE f.numfact = l.numfact

                                AND c.numcons = l.numcons

                                AND YEAR(datefact) = pannee

                                AND MONTH(datefact) = i;

                                INSERT INTO ca values (pannee, i, tot);

                                SET i = i + 1;

                END WHILE;     

END |

                Sous MySql.

                               Call camois(2006);

FONCTIONS

Sous MySql

create function meilleur(an int)

returns varchar(50)

DETERMINISTIC

begin

declare   nom varchar(40);

select s.nomserv

into nom

from conso, ligne_fact, facture, serveur s

where conso.numcons = ligne_fact.numcons

and ligne_fact.numfact = facture.numfact

and s.numserv = facture.numserv

and year(datefact)=an

 group by s.nomserv

having sum(prixcons*qte) >= all

(select sum(prixcons*qte)

from conso, ligne_fact, facture

where conso.numcons = ligne_fact.numcons

and ligne_fact.numfact = facture.numfact

and year(datefact)=an

group by facture.numserv);

return nom;

end|

Sous MySql

        Select NomFonction(paramètres) ;

Pour visualiser les procédures ou fonctions :

Show procedure status     ou

Show function status

Pour visualiser la structure des procédures ou des fonctions :

Show create procedure nomprocedure        ou

Show create function nomfonction