D4-A05 : Les fonctions et les procédures stockées avec Mysql

Tout comme Oracle possède son langage PL/SQL et MS SQL Server son Transact-SQL, MySQL possède aussi un langage de programmation, et donc MariaDB également. La syntaxe reste propre au SGBD.

Une procédure stockée, aussi appelée stored procedure en anglais, est un concept utilisé en administration de base de données afin d’exécuter un ensemble d’instructions SQL. Une telle procédure est stockée au sein du Système de Gestion de Base de Donneés (SGBD) et peut être appelée à tout moment par son nom afin d’exécuter celle-ci.

Les procédures stockées permettent aussi l'utilisation de bibliothèques et de fonctions sur le serveur. Les langages de programmation modernes ont déjà intégré ce type de concept, et l'utilisation de ces langages de programmation externes reste valable et utile en dehors de la base de données.

Pourquoi utiliser les PS(procédures stockées)/triggers, quelques exemples en vrac :

Simplifier : un même code qui doit souvent être effectuée peut être enregistré afin d’être appelé rapidement
Amélioration des performances : les opérations peuvent être exécutées du côté du serveur de base de données et envoyées directement prête à l’emploi par la solution informatique qui va utiliser ces données. Par ailleurs, cela va réduire les échanges entre le client et le serveur
Sécurité : des applications peuvent avoir accès uniquement aux procédures stockées, sans avoir accès aux données des tables directement, et/ou s’assurer que l’accès aux données soit toujours effectué de la même manière

Les procédures stockées et les fonctions sont créées avec les commandes CREATE PROCEDURE et CREATE FUNCTION.

Une procédure est appelée avec la commande CALL, et ne peut retourner de valeur que via les variables de retour (à travers des variables utilisateur, précédées par @, qui sont globales dans une session et peuvent être utilisées en dehors des blocs BEGIN … END..).

Les fonctions peuvent retourner une valeur scalaire, et être appelées depuis une commande, tout comme toute autre fonction. Les procédures stockées peuvent appeler une autre routine stockée. Une routine est une procédure stockée ou une fonction.

DELIMITER //
CREATE PROCEDURE nomproc
([IN|OUT|INOUT] typeparam param,...)
BEGIN
  //corps procédure//
END //
DELIMITER ;

Pour appeler la procédure, il est possible d’exécuter la requête SQL suivante:

CALL nomproc(valeurparam);

DELIMITER //
CREATE FUNCTION nomfonction(param1 TYPE, param2 TYPE, ...)  
RETURNS TYPE  
DETERMINISTIC  
BEGIN  
    -- Déclarations de variables
    DECLARE variable1 TYPE;

    -- Logique de la fonction
    SET variable1 = ...;

    -- Retourner une valeur
    RETURN variable1;
END //
DELIMITER ;

Pour appeler la fonction, il est possible d’exécuter la requête SQL suivante:

SELECT nomfonction(valeurparam1,...);

delimiter |
CREATE FUNCTION bonjour (s CHAR(20)) RETURNS CHAR(50)
  RETURN CONCAT('Bonjour, ',s,'!');
|

Que fait cette fonction ?
Appel :
Afficher le résultat :

delimiter |
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   SELECT COUNT(*) INTO param1 FROM Immeuble;
END
|

Que fait cette procédure ?
Appel :
La tester dans le contexte “Parc immobilier” (TP Déclencheurs)
Afficher le résultat :
Modifier cette procédure pour que le nom de la table soit géré par un paramètre en entrée (☝En MySQL, les noms de tables ne peuvent pas être passés dynamiquement comme des paramètres dans une requête SQL classique. Pour contourner cela, il faut utiliser une requête dynamique avec PREPARE et EXECUTE)

ALTER PROCEDURE | FUNCTION sp_name [characteristic …]

DROP PROCEDURE | FUNCTION [IF EXISTS] sp_name

Le corps de la procédure, entre le BEGIN et le END est un bloc d'instructions qui peut avoir besoin de structuration, on peut notamment utiliser des variables locales, des structures conditionnelles et des boucles.

Une ressource : https://zestedesavoir.com/tutoriels/730/administrez-vos-bases-de-donnees-avec-mysql/952_securiser-et-automatiser-ses-actions/3955_procedures-stockees/

Pour créer un gestionnaire d'erreur dans une procédure :

DECLARE { EXIT | CONTINUE } HANDLER FOR { numero_erreur | { SQLSTATE identifiant_erreur } | condition } 
    instruction ou bloc d'instructions

Un gestionnaire d'erreur définit une instruction (une seule !), ou un bloc d'instructions (BEGIN … END;), qui va être exécuté en cas d'erreur correspondant au gestionnaire.

Tous les gestionnaires d'erreur doivent être déclarés au même endroit : après la déclaration des variables locales, mais avant les instructions de la procédure.

Un gestionnaire peut, soit provoquer l'arrêt de la procédure (EXIT), soit faire reprendre la procédure après avoir géré l'erreur (CONTINUE).
On peut identifier le type d'erreur que le gestionnaire va reconnaître de trois manières différentes : un numéro d'erreur, un identifiant, ou une CONDITION.
Un gestionnaire étant défini grâce au mot-clé DECLARE, comme les variables locales, il a exactement la même portée que celles-ci.

Les codes erreurs se trouvent sur la documentation officielle.

le résultat d'un SELECT dans un bloc d'instructions, en utilisant la commande SELECT colonne(s) INTO variable(s), assigne les valeurs sélectionnées à des variables. Cependant, SELECT … INTO ne peut être utilisé que pour des requêtes qui ne ramènent qu'une seule ligne de résultats.

Les curseurs permettent de parcourir un jeu de résultats d'une requête SELECT, quel que soit le nombre de lignes récupérées, et d'en exploiter les valeurs.

Quatre étapes sont nécessaires pour utiliser un curseur.

Déclaration du curseur : avec une instruction DECLARE.
```
DECLARE nom_curseur CURSOR FOR requete_select;
```

Définition de la fin du jeu d'enregistrement :

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET ok = 1;

Ouverture du curseur : on exécute la requête SELECT du curseur et on stocke le résultat dans celui-ci.
```
OPEN nom_curseur;
```
Parcours du curseur : on parcourt une à une les lignes.

tarif_loop: LOOP -- boucle pour le parcours du jeu d'enregistrement
  FETCH curseur INTO var1, ...; -- lecture d'une ligne du jeu d'enregistrement
  IF ok = 1 THEN -- test fin du jeu d'enregistrement
	LEAVE tarif_loop; 
  END IF;
  -- traitement de la ligne lue
END LOOP;