Dans la plupart des SGBD-R, il existe un langage de programmation
impératif classique qui permet d’écrire des successions d’opérations
relationnelles en utilisant les possibilités habituelles de la
programmation impérative type Pascal, C ou C++ (variable, affectation,
tests,boucle, exception, procédure et fonction…).
PL-SQL est le nom générique de ce langage pour tous les SGBD. La syntaxe
du PL-SQL est en partie standardisée.
Ce type de programme est très interessant pour :
Développer un Bloc simple PL/SQL
[ DECLARE ]
- Variables, constantes, curseurs,
exceptions utilisateurs
BEGIN
- Ordres SQL
- Instructions de Contrôle PL/SQL
[ EXCEPTION ]
- Traitements à effectuer lors d'erreurs
END ;CM4 toulouse guillaume Cabanac : introduction
PL/SQL
CM5 toulouse guillaume Cabanac : PL/SQL Exceptions et
curseurs
Créez une variable nommée nom_utilisateur et initialisez-la avec votre nom. Affichez le contenu de la variable nom_utilisateur à l’aide d’une instruction SELECT.
-- Création de la variable
SET @variable1 = 'Valeur1';
-- Affichage de la variable
SELECT @variable1 AS variable1;
-- Création de la variable dans une requête
SELECT CONCAT('test:',2) INTO @variable2 FROM DUAL;
-- Affichage de la variable
SELECT @variable2 AS variable2;Créez une procédure stockée nommée ajouter_utilisateur qui prend en paramètres le nom et l’âge d’un utilisateur, et insère ces informations dans une table utilisateurs. Appelez la procédure avec quelques exemples.
-- Création de la table utilisateurs
DROP TABLE IF EXISTS utilisateurs;
CREATE TABLE utilisateurs (
id INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
age INT,
majeur INT
);
DROP PROCEDURE IF EXISTS AjouterUtilisateur;
-- Création de la procédure stockée
DELIMITER //
CREATE PROCEDURE AjouterUtilisateur(IN nom_utilisateur VARCHAR(50), IN age_utilisateur INT)
BEGIN
INSERT INTO utilisateurs (nom, age) VALUES (nom_utilisateur, age_utilisateur);
END //
DELIMITER ;
-- Appel de la procédure
CALL AjouterUtilisateur('John Doe', 25);
CALL AjouterUtilisateur('Jane Doe', 30);
-- Affichage du contenu de la table utilisateurs
SELECT * FROM utilisateurs;Modifier la fonction AjouterUtilisateur pour mettre en majuscule le nom et en minuscule le prénom. (il faut modifier l’appel de la procédure et passer dans 2 paramètres distincts le nom et le prénom)
Créez une fonction appelée calculer_moyenne qui prend en paramètre deux nombres et retourne leur moyenne. Utilisez la fonction pour calculer la moyenne de deux ensembles de nombres différents.
-- Création de la fonction
DELIMITER //
CREATE FUNCTION CalculerMoyenne(a INT, b INT)
RETURNS DECIMAL(5,2)
BEGIN
DECLARE moyenne DECIMAL(5,2);
SET moyenne = (a + b) / 2;
RETURN moyenne;
END //
DELIMITER ;
-- Utilisation de la fonction
SELECT CalculerMoyenne(10, 20) AS MoyenneSet1;
SELECT CalculerMoyenne(5, 16.3333) AS MoyenneSet2;
DROP FUNCTION IF EXISTS CalculerMoyenne;REMARQUE : erreur de droit sur MySQL(Oracle) sur la machine de l’iut :
Commande en tant que root :
SET GLOBAL log_bin_trust_function_creators = 1;
Reprendre l’exercice, modifier le type des variables pour calculer la moyenne de 2 monétaires
Afficher les procédures
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.routines;Créez une procédure qui prend un paramètre en entrée (par exemple, age_utilisateur) et affiche “Majeur” si l’âge est supérieur ou égal à 18, sinon affiche “Mineur”. Testez la procédure en passant différentes valeurs à age_utilisateur.
DROP PROCEDURE IF EXISTS MiseAJourAge;
-- Création de la procédure
DELIMITER //
CREATE PROCEDURE MiseAJourAge(IN age_utilisateur INT)
BEGIN
IF age_utilisateur >= 18 THEN
SELECT 'Majeur' AS Statut;
ELSE
SELECT 'Mineur' AS Statut;
END IF;
END //
DELIMITER ;
-- Appel de la procédure
CALL MiseAJourAge(20);
CALL MiseAJourAge(15);Modifier la fonction MiseAJourAge pour mettre à jour la colonne age et majeur de la table utilisateur. La procédure MiseAJourAge prend 2 paramètres en entrée : age_utilisateur et id_utilisateur, la colonne majeur de l’utilisateur vaut 1 si l’utilisateur est majeur et 0 sinon.
test :
-- test : Appel de la procédure
SELECT * FROM utilisateurs;
CALL MiseAJourAge(1,20);
CALL MiseAJourAge(2,15);
SELECT * FROM utilisateurs;Créez une table nombres avec une colonne valeur. Utilisez une boucle pour insérer des valeurs de 1 à 10 dans la table. Affichez le contenu de la table nombres.
-- Création de la table
DROP TABLE IF EXISTS nombres;
CREATE TABLE nombres (
valeur INT
);
DROP PROCEDURE IF EXISTS RemplirNombres;
-- Insertion des valeurs de 1 à 10
DELIMITER //
CREATE PROCEDURE RemplirNombres()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
INSERT INTO nombres (valeur) VALUES (i);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- Appel de la procédure
CALL RemplirNombres();
-- Affichage du contenu de la table
SELECT * FROM nombres;Créez une table historique_utilisateurs avec des colonnes telles que id_utilisateur, nom_utilisateur, action et date_action. Créez un déclencheur qui enregistre une nouvelle ligne dans historique_utilisateurs à chaque fois qu’un utilisateur est ajouté à la table utilisateurs.
-- Création de la table historique_utilisateurs
DROP TABLE IF EXISTS historique_utilisateurs;
CREATE TABLE historique_utilisateurs (
id_utilisateur INT AUTO_INCREMENT PRIMARY KEY,
nom_utilisateur VARCHAR(50),
action VARCHAR(50)
);
DROP TRIGGER IF EXISTS after_insert_utilisateur;
-- Création du déclencheur
DELIMITER //
CREATE TRIGGER after_insert_utilisateur
AFTER INSERT ON utilisateurs
FOR EACH ROW
BEGIN
INSERT INTO historique_utilisateurs (id_utilisateur, nom_utilisateur, action)
VALUES (NEW.id, NEW.nom, 'Ajout');
END //
DELIMITER ;
-- Ajout d'un utilisateur pour déclencher le déclencheur
INSERT INTO utilisateurs (nom, age) VALUES ('lionel', 28);
-- Affichage du contenu de la table historique_utilisateurs
SELECT * FROM historique_utilisateurs;
SELECT * FROM utilisateurs;Créer un nouveau “trigger” (déclencheur) pour mettre en majuscule le nom et en minuscule le prénom lors d’un INSERT
Un curseur est une structure de contrôle qui permet d’itérer à travers un ensemble de lignes renvoyées par une requête SQL. Il agit comme un pointeur sur un ensemble de résultats et permet de traiter chaque ligne de manière séquentielle.
-- procedure
-- https://dev.mysql.com/doc/refman/8.0/en/cursors.html
DROP PROCEDURE IF EXISTS testUtilisateurs ;
DELIMITER //
CREATE PROCEDURE testUtilisateurs()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE b CHAR(50);
DECLARE a INT;
DECLARE c INT;
DECLARE d INT;
DECLARE cur1 CURSOR FOR SELECT id,nom,age, majeur FROM utilisateurs;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a, b, c ,d;
IF done THEN
LEAVE read_loop;
END IF;
SELECT a,b,c,d ; -- affichage
END LOOP;
CLOSE cur1;
END //
DELIMITER ;
Une autre façon d’utiliser le curseur (boucle WHILE), idée exemple
DROP PROCEDURE IF EXISTS AfficherUtilisateurs ;
DELIMITER //
CREATE PROCEDURE AfficherUtilisateurs()
BEGIN
DECLARE v_id INT;
DECLARE v_nom VARCHAR(50);
DECLARE v_age INT;
DECLARE v_majeur INT;
DECLARE v_utilisateur VARCHAR(255);
DECLARE fin INT DEFAULT 0;
DECLARE curs_utilisateur CURSOR FOR SELECT id,nom,age, majeur FROM utilisateurs;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;
OPEN curs_utilisateur;
FETCH curs_utilisateur INTO v_id,v_nom,v_age,v_majeur;
WHILE (fin<>1) DO
SET v_utilisateur=CONCAT(v_id,' ; ', v_nom,' ; ',v_age,' ; ',v_majeur);
SELECT v_utilisateur;
FETCH curs_utilisateur INTO v_id,v_nom,v_age,v_majeur;
END WHILE;
CLOSE curs_utilisateur;
END //
DELIMITER ;Modifier le code ci-dessus pour afficher correctement les enregistrements dans la procédure ci-dessus (utiliser la fonction IFNULL)
ajouter un an à l’age de tous les utilisateurs dans la procédure ci-dessus
Test :
SELECT * FROM utilisateurs;
CALL AfficherUtilisateurs();
SELECT * FROM utilisateurs;Exemple : utilisation de 2 curseurs imbriqués