PL/SQL: Procédures et Fonctions
Stockées (MySQL)
SupNum 2022
Motivation
Une procédure stockée s’exécute au sein du SGBD, ce qui évite les échanges réseaux qui sont
nécessaires quand les mêmes fonctionnalités sont implantées dans un programme externe
(Python, PHP, Java,…) communiquant en mode client/serveur avec la base de données.
Le programme s’exécute alors en communiquant avec le serveur pour exécuter les requêtes et
récupérer les résultats.
Chaque demande d’exécution d’un ordre SQL implique une transmission sur le réseau, du
programme vers le client, suivie d’une analyse de la requête par le serveur, de sa compilation et de
son exécution
Motivation
Ensuite, chaque fois que le programme client souhaite récupérer un n-uplet du résultat, il doit
effectuer un appel externe, via le réseau.
Tous ces échanges interviennent de manière non négligeable dans la performance de l’ensemble, et
cet impact est d’autant plus élevé que les communications réseaux sont lentes et/ou que le nombre
d’appels nécessaires à l’exécution du programme est important.
Solution
Le recours à une procédure stockée permet de regrouper du côté serveur
l’ensemble des requêtes SQL et le traitement des données récupérées.
La procédure est compilée une fois par le SGBD, au moment de sa création, ce qui
permet de l’exécuter rapidement au moment de l’appel.
Encapsulation, Performance, Efficacité, Réutilisabilité, Réduction du trafic réseau
PL/SQL: les procédures stockées
Procedural Language
Le PL/SQL permet de combiner des requêtes SQL, des boucles, des tests, ... pour définir
des procédures, des fonctions,
DROP PROCEDURE IF EXISTS PROC ;
DELIMITER //
CREATE PROCEDURE PROC(p_1,…, p_n)
BEGIN (obligatoire)
DECLARE
-- bloc d'instructions SQL
-- instructions PL/SQL ou sous-blocs
END // (obligatoire)
DELIMITER //← A ajouter obligatoirement dans l’exécution d’un script
Exemples de bloc PL/SQL
DELIMITER//
DROP PROCEDURE IF EXISTS getAllVilles //
CREATE PROCEDURE getAllVilles()
BEGIN
DECLARE V_VILLE VARCHAR(20) DEFAULT 'PARIS' ;
select plnum, plnom from pilote where VILLE=V_VILLE;
END//
Exemple de bloc PL/SQL
-- exemple de bloc PL/SQL
DELIMITER //
create procedure getNumNom()
BEGIN
DECLARE PNOM VARCHAR(20);
DECLARE PNUM INTEGER;
select plnum, plnom INTO PNUM, PNOM from pilote where SALAIRE=26000;
SELECT CONCAT(PNUM," ",PNOM);
END//
DELIMITER ;
CALL getNumNom();
-- un script qui affiche le nombre d’avions localisés dans une ville donnée
CREATE PROCEDURE GETNBVILLES(LOC VARCHAR(20))
BEGIN
DECLARE NB INT;
SELECT COUNT(*) INTO @NB FROM AVION WHERE LOCALISATION=LOC;
SELECT @NB AS 'NB D''AVIONS';
END
-- script de définition d’une procédure
-- procédure « insert_Pilote » : permet d’insérer un pilote avec :
-- son numéro, son nom et son numéro de département
-- les autres attributs doivent ne pas être obligatoires !
drop procedure if exists insert_pilote;
delimiter //
create procedure insert_pilote (v_plnum integer, v_plnom varchar(14), v_salaire integer)
comment 'permet d''insérer un employé avec ses numéro, nom et n° de dept'
begin
/* on insert dans la BD c'est juste un exemple de code */
insert into pilote(plnum, plnom, salaire) values (v_plnum, v_plnom, v_salaire);
end //
delimiter ;
call insert_pilote(200, 'TOTO',40000);
- - script de définition d’une procédure pour insérer les pilotes dont leurs salaires<=19000
- - dans une nouvelle table
drop procedure if exists insert_pilote;
drop table pilotsalaire; - - table resulat
create table pilotsalaire(num integer, nom varchar(20), sal integer);
delimiter //
create procedure insert_pilote2 ()
begin
declare num integer;
declare nom varchar(20);
declare sal integer;
SELECT plnum, plnom, salaire INTO num, nom, sal FROM pilote WHERE salaire<=19000;
insert into pilotsalaire values (num, nom, sal);
end //
delimiter ;
call insert_pilote2();
select * from pilotsalaire;
- - cette fonction renvoie le double de l’entier passe en
-- paramètre
DELIMITER //
create function doubleNB(nb INT) RETURNS integer
BEGIN
DECLARE newNB integer;
SET newNB=nb*2;
RETURN newNB;
END //
DELIMITER ;
SELECT PLNOM, SALAIRE, doubleNB(SALAIRE) FROM PILOTE
-- calculer les moyennes des salaires des pilotes de codes pairs et de codes impairs
DELIMITER //
DROP PROCEDURE IF EXISTS MOYENNES//
CREATE PROCEDURE MOYENNES(OUT M1 DECIMAL(7,2), OUT M2 DECIMAL(7,2))
BEGIN
BEGIN
SELECT AVG(SALAIRE) INTO MOYPAIR FROM PILOTE WHERE PLNUM%2=0;
END;
BEGIN
SELECT AVG(SALAIRE) INTO MOYIMPAIR FROM PILOTE WHERE PLNUM%2<>0;
END;
END//
DELIMITER ;
CALL MOYENNES(@M1,@M2);
SELECT @M1 AS ‘moyenne1’, @M2 AS ‘moyenne2’;
CREATE PROCEDURE JOURS(d INT)
BEGIN Une procédure qui reçoit un entier et
IF(D=1) THEN
SELECT "LUNDI"; affiche le jour correspondant.
ELSEIF(D=2) THEN VERSION IF
SELECT "MARDI";
ELSEIF(D=3) THEN
SELECT "MERCREDI";
ELSEIF(D=4) THEN
SELECT "JEUDI";
ELSEIF(D=5) THEN
SELECT "VENDREDI";
ELSEIF(D=6) THEN
SELECT "SAMEDI";
ELSEIF(D=7) THEN
SELECT "DIMANCHE";
ELSE
SELECT "ERREUR, CE JOUR N'EXISTE PAS";
END IF;
END
CREATE PROCEDURE `DAYS_Case`(IN `D` INT)
BEGIN
CASE D Une procédure qui reçoit un entier et
WHEN 1 THEN
SELECT "LUNDI"; affiche le jour correspondant.
WHEN 2 THEN VERSION CASE
SELECT "MARDI";
WHEN 3 THEN
SELECT "MERCREDI";
WHEN 4 THEN
SELECT "JEUDI";
WHEN 5 THEN
SELECT "VENDREDI";
WHEN 6 THEN
SELECT "SAMEDI";
WHEN 7 THEN
SELECT "DIMANCHE";
ELSE
SELECT « ERREUR!";
END CASE;
END//