Introduction aux procédures stockées dans MySQL 5

MySQL 5 a introduit une pléthore de nouvelles fonctionnalités, les procédures stockées étant l’une des plus importantes. Dans ce tutoriel, nous allons nous concentrer sur ce qu’ils sont et comment ils peuvent vous rendre la vie plus facile..

Si vous travaillez beaucoup avec MySQL, vous pouvez consulter la gamme de scripts de code MySQL et de plug-ins sur Envato Market..

introduction

? Une routine stockée est un ensemble d'instructions SQL pouvant être stockées sur le serveur..?

Une procédure stockée est une méthode pour encapsuler des tâches répétitives. Ils permettent les déclarations de variables, le contrôle de flux et d’autres techniques de programmation utiles.

Le? Académique? La position à ce sujet est assez claire et soutient l'utilisation intensive des procédures stockées. D'autre part, lorsque vous prenez en compte les opinions de ceux qui travaillent avec eux tous les jours, vous remarquerez que les réactions varient d'un soutien total et sans faille à une haine totale. Gardez cela à l'esprit.

Avantages

Partager la logique avec d'autres applications. Les procédures stockées encapsulent des fonctionnalités; cela garantit que l'accès aux données et leur manipulation sont cohérents entre différentes applications.
Isoler utilisateurs à partir de tables de données. Cela vous donne la possibilité d'accorder l'accès aux procédures stockées qui manipulent les données mais pas directement aux tables..
Fournir un Sécurité mécanisme. Compte tenu de l'élément précédent, si vous ne pouvez accéder aux données qu'à l'aide des procédures stockées définies, personne d'autre ne peut exécuter une opération. EFFACER Déclaration SQL et effacer vos données.
À améliorer les performances car cela réduit le trafic réseau. Avec une procédure stockée, plusieurs appels peuvent être fusionnés en un seul..

Les inconvénients

Charge accrue sur le serveur de base de données - la majeure partie du travail est effectuée côté serveur et moins côté client.
Il y a un bon courbe d'apprentissage. Vous devrez apprendre la syntaxe des instructions MySQL pour écrire des procédures stockées..
Vous êtes répéter la logique de votre application à deux endroits différents: le code de votre serveur et le code des procédures stockées, ce qui rend les choses un peu plus difficiles à gérer.
Migration système de gestion de base de données différent (DB2, SQL Server, etc.) peut être potentiellement plus difficile.

L'outil avec lequel je travaille dans ce didacticiel, le navigateur de requêtes MySQL, est assez standard pour les interactions avec les bases de données. L'outil de ligne de commande MySQL est un autre excellent choix. Je note ceci car le populaire phpMyAdmin ne supporte pas l'exécution de procédures stockées.

De plus, je vais utiliser des structures de table très rudimentaires, afin de faciliter l'explication. Je montre des procédures stockées, et elles sont assez complexes sans se soucier des grandes tables.

Étape 1 - Choisir un délimiteur

Le délimiteur est le caractère ou la chaîne de caractères que vous utiliserez pour indiquer au client mySQL que vous avez fini de taper une instruction SQL. Pendant des siècles, le séparateur a toujours été un point-virgule. Cela pose cependant des problèmes, car dans une procédure stockée, on peut avoir plusieurs instructions et chacune d’elles doit se terminer par un point-virgule. Dans ce tutoriel, je vais utiliser? //?

Étape 2 - Comment travailler avec une procédure stockée

Création d'une procédure stockée

DELIMITER // CREATE PROCEDURE 'p2' () LANGUE SQL DETERMINISTIC SQL SECURITY COMMENTAIRE DEFINER 'Une procédure' BEGIN SELECT 'Hello World!'; FIN//

La première partie de la déclaration crée la procédure. Les clauses suivantes définissent les caractéristiques facultatives de la procédure. Ensuite, vous avez le nom et enfin le corps ou le code de routine.

Les noms de procédures stockées ne font pas la distinction entre les majuscules et les minuscules et vous ne pouvez pas créer de procédures portant le même nom. Dans un corps de procédure, vous ne pouvez pas insérer d'instructions de manipulation de base de données.

Les quatre caractéristiques d'une procédure sont les suivantes:

La langue : À des fins de portabilité; la valeur par défaut est SQL.
Déterministe : Si la procédure retourne toujours les mêmes résultats, avec la même entrée. C'est à des fins de réplication et de journalisation. La valeur par défaut est NON DÉTERMINISTE.
Sécurité SQL : Au moment de l'appel, vérifiez les privilèges de l'utilisateur. INVOCATEUR est l'utilisateur qui appelle la procédure. DEFINER est le créateur de la procédure. La valeur par défaut est DEFINER.
Commentaire : À des fins de documentation; La valeur par défaut est ""

Appel d'une procédure stockée

Pour appeler une procédure, il suffit de saisir le mot APPEL, suivi du nom de la procédure, puis des parenthèses, avec tous les paramètres les séparant (variables ou valeurs). Les parenthèses sont obligatoires.

CALL nom_procédure_stockée (param1, param2 ,?) procédure CALL1 (10, 'paramètre de chaîne', @parameter_var);

Modifier une procédure stockée

MySQL fournit un ALTER PROCEDURE instruction de modifier une routine, mais ne permet que la possibilité de modifier certaines caractéristiques. Si vous devez modifier le corps ou les paramètres, vous devez supprimer et recréer la procédure..

Supprimer une procédure stockée

DROP PROCEDURE INEXISTS p2;

Ceci est une commande simple. le SI EXISTE clause empêche une erreur au cas où la procédure n'existe pas.

Étape 3 - Paramètres

Voyons comment vous pouvez définir des paramètres dans une procédure stockée..

CREATE PROCEDURE proc1 () : La liste de paramètres est vide
CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : Un paramètre d'entrée. Le mot DANS est facultatif car les paramètres sont DANS (entrée) par défaut.
CREATE PROCEDURE proc1 (OUT nom_variable DATA-TYPE) : Un paramètre de sortie.
CREATE PROCEDURE proc1 (INOUT nomvar nom_de_données) : Un paramètre qui est à la fois entrée et sortie.

Bien sûr, vous pouvez définir plusieurs paramètres définis avec différents types.

Par exemple

 DELIMITER // CREATE PROCEDURE 'proc_IN' (IN var1 INT) DEBUT SELECT var1 + 2 AS result; FIN//

Exemple OUT

DELIMITER // CREATE PROCEDURE 'proc_OUT' (OUT var1 VARCHAR (100)) BEGIN SET var1 = 'Ceci est un test'; FIN //

Exemple INOUT

DELIMITER // CREATE PROCEDURE 'proc_INOUT' (OUT var1 INT) BEGIN SET var1 = var1 * 2; FIN //

Étape 4 - Variables

L'étape suivante vous apprendra à définir des variables et à stocker des valeurs dans une procédure. Vous devez les déclarer explicitement au début de la COMMENCER / FIN bloquer, ainsi que leurs types de données. Une fois que vous avez déclaré une variable, vous pouvez l’utiliser partout où vous pourriez utiliser une variable de session, un nom littéral ou un nom de colonne..

Déclarez une variable en utilisant la syntaxe suivante:

DECLARE nom_var DATA-TYPE DEFAULT defaultvalue;

Déclarons quelques variables:

DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DÉCLARER aujourd'hui TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Travailler avec des variables

Une fois les variables déclarées, vous pouvez leur attribuer des valeurs à l’aide des touches ENSEMBLE ou SÉLECTIONNER commander:

DELIMITER // CREATE PROCEDURE 'var_proc' (IN paramstr VARCHAR (20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DÉCLARER aujourd'hui TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = 'Je suis une chaîne'; SELECT CONCAT (str, paramstr), aujourd'hui FROM table2 WHERE b> = 5; FIN //

Étape 5 - Structures de contrôle de flux

MySQL supporte le SI, CAS, ITERATE, QUITTEZ LA BOUCLE, PENDANT et RÉPÉTER construit pour le contrôle de flux dans les programmes stockés. Nous allons examiner comment utiliser SI, CAS et TANDIS QUE en particulier, car ils se trouvent être les déclarations les plus couramment utilisées dans les routines.

`SI` déclaration

Avec le SI déclaration, nous pouvons gérer des tâches qui impliquent des conditions:

DELIMITER // CREATE PROCEDURE 'proc_IF' (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; SI variable1 = 0 ALORS CHOISIR variable1; FIN SI; IF param1 = 0 THEN SELECT 'Valeur du paramètre = 0'; ELSE SELECT 'Valeur du paramètre <> 0'; FIN SI; FIN //

`CAS` déclaration

le CAS instruction est un autre moyen de vérifier les conditions et de choisir le chemin approprié. C'est un excellent moyen de remplacer plusieurs SI déclarations. La déclaration peut être écrite de deux manières différentes, offrant une grande souplesse pour gérer plusieurs conditions..

DELIMITER // CREATE PROCEDURE 'proc_CASE' (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 LORSQUE 0 PUIS INSCRIRE DANS LES VALEURS de la table1 (param1); WHEN 1 THEN INSERT INTO table1 VALEURS (variable1); ELSE INSERT INTO table1 VALEURS (99); CAS DE FIN; FIN //

ou:

DELIMITER // CREATE PROCEDURE 'proc_CASE' (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 ALORS INSERT INTO table1 VALEURS (param1); WHEN variable1 = 1 ALORS INSERT INTO table1 VALEURS (variable1); ELSE INSERT INTO table1 VALEURS (99); CAS DE FIN; FIN //

`TANDIS QUE` déclaration

Il existe techniquement trois boucles standard: TANDIS QUE boucles, BOUCLE boucles, et RÉPÉTER boucles. Vous avez également la possibilité de créer une boucle en utilisant le? Darth Vader? des techniques de programmation: la ALLER À déclaration. Découvrez cet exemple de boucle en action:

DELIMITER // CREATE PROCEDURE 'proc_WHILE' (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; PENDANT variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Étape 6 - Curseurs

Le curseur est utilisé pour parcourir un ensemble de lignes renvoyées par une requête et traiter chaque ligne.

MySQL prend en charge le curseur dans les procédures stockées. Voici un résumé de la syntaxe essentielle pour créer et utiliser un curseur.

DÉCLARER le nom du curseur CURSEUR POUR SÉLECTIONNER? ; / * Déclarez et remplissez le curseur avec une instruction SELECT * / DECLARE CONTINUE HANDLER FOR NOT FOUND / * Spécifiez ce qu'il faut faire si aucun enregistrement n'est trouvé * / OPEN nom-curseur; / * Ouvre le curseur pour utiliser * / FETCH nom-curseur INTO variable [, variable]; / * Affecte des variables avec les valeurs de colonne actuelles * / CLOSE nom-curseur; / * Ferme le curseur après utilisation * /

Dans cet exemple, nous allons effectuer quelques opérations simples à l'aide d'un curseur:

DELIMITER // CREATE PROCEDURE 'proc_CURSOR' (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSEUR DE SELECT col1 FROM table1; DÉCLARE CONTINUER HANDLER POUR SET NON TROUVÉ b = 1; OPEN cur1; SET b = 0; SET c = 0; PENDANT QUE b = 0 FETCH cur1 IN a; SI b = 0 ALORS, SET c = c + a; FIN SI; FIN PENDANT; CLOSE cur1; SET param1 = c; FIN //

Le curseur a trois propriétés importantes que vous devez connaître pour éviter des résultats inattendus:

Asensible : Une fois ouvert, le curseur ne reflétera pas les modifications apportées à ses tables source. En fait, MySQL ne garantit pas que le curseur sera mis à jour, vous ne pouvez donc pas vous en fier.
Lecture seulement : Les curseurs ne peuvent pas être mis à jour.
Non Scrollable : Les curseurs ne peuvent être parcourus que dans un sens, en avant, et vous ne pouvez pas ignorer les enregistrements de la récupération.

Conclusion

Dans cette leçon, nous avons abordé les principes de base des procédures stockées et certaines propriétés spécifiques les concernant. Bien sûr, vous devez poursuivre vos études dans des domaines tels que la sécurité, les instructions SQL et les performances avant de maîtriser les routines MySQL..

Vous devez évaluer les avantages que les procédures stockées peuvent potentiellement apporter à vos applications, puis procéder à une implémentation raisonnable et adaptée à vos besoins. J'utilise généralement des procédures; leurs avantages en termes de sécurité, de maintenance du code et de conception des logiciels les rendent dignes d’utilisation, à mon avis. De plus, rappelez-vous que les procédures dans MySQL sont toujours un travail en cours. Vous devez vous attendre à des améliorations en termes de fonctionnalités et de performances dans le futur.

S'il vous plaît n'hésitez pas à commenter et partager vos idées et opinions. Et jetez un coup d'oeil aux scripts de code theяMySQL et aux pluginsяon d'Envato Market pour voir si vous trouvez quelque chose pour vous aider.

Code