MySQL 5.0 prend en charge les procédures stockées depuis sa sortie.

Une procédure stockée est un objet de base de données qui stocke des programmes complexes dans la base de données pour être appelés par des programmes externes.

Une procédure stockée est un ensemble d'instructions SQL conçu pour accomplir une fonction spécifique. Elle est compilée, créée et enregistrée dans la base de données. Les utilisateurs peuvent l'appeler et l'exécuter en spécifiant le nom de la procédure stockée et en fournissant des paramètres (si nécessaire).

L'idée derrière les procédures stockées est simple : l'encapsulation et la réutilisation du code au niveau du langage SQL de la base de données.

Avantages

  • Les procédures stockées peuvent encapsuler et masquer une logique métier complexe.
  • Les procédures stockées peuvent renvoyer des valeurs et accepter des paramètres.
  • Les procédures stockées ne peuvent pas être exécutées à l'aide de la commande SELECT, car ce sont des sous-programmes, contrairement aux vues, aux tables ou aux fonctions définies par l'utilisateur.
  • Les procédures stockées peuvent être utilisées pour la validation des données, l'application de la logique métier, etc.

Inconvénients

  • Les procédures stockées sont souvent adaptées à des bases de données spécifiques, car les langages de programmation pris en charge diffèrent. Lorsqu'on passe à un système de base de données d'un autre fournisseur, les procédures stockées existantes doivent être réécrites.
  • L'optimisation des performances et la rédaction des procédures stockées sont limitées par divers systèmes de base de données.

I. Création et invocation des procédures stockées

  • Une procédure stockée est un bloc de code nommé utilisé pour accomplir une fonction spécifique.
  • Les procédures stockées créées sont enregistrées dans le dictionnaire de données de la base de données.

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

CREATE [DEFINER = { user | CURRENT_USER }] 　PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: 　　Valide SQL routine instruction [begin_label:] BEGIN 　　[statement_list] 　　　　…… END [end_label]

Syntaxe clé des procédures stockées MySQL

Déclarez le délimiteur d'instruction ; il peut être personnalisé :

DELIMITER $$
ou 
DELIMITER //

Déclarer la procédure stockée :

CREATE PROCEDURE demo_in_parameter(IN p_in int)

Symboles de début et de fin de la procédure stockée :

BEGIN .... END

Affectation de variable :

SET @p_in=1

Définition de variable :

DECLARE l_int int unsigned default 4000000;

Créer des procédures stockées et des fonctions stockées MySQL :

create procedure 存储过程名(参数)

Corps de la procédure stockée :

create function 存储函数名(参数)

Exemple

Créer une base de données et sauvegarder une table de données pour l'exemple d'opération :

mysql> create database db1; mysql> use db1; mysql> create table PLAYERS as select * from TENNIS.PLAYERS; mysql> create table MATCHES as select * from TENNIS.MATCHES;

Voici un exemple de procédure stockée qui supprime tous les matchs auxquels un joueur donné a participé :

mysql> delimiter $$　　# Changer temporairement le délimiteur d'instruction du point-virgule ; à deux $$ (personnalisable) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> 　　DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affectées (0.01 sec) mysql> delimiter;　　# Restaurer le délimiteur d'instruction à un point-virgule

Analyse :Par défaut, une procédure stockée est associée à la base de données par défaut. Si vous voulez créer la procédure stockée dans une base de données spécifique, préfixez le nom de la procédure avec le nom de la base de données. Lors de la définition de la procédure, utilisezDELIMITER $$commande pour changer le délimiteur d'instruction d'un point-virgule;temporairement en deux$$, afin que les points-virgules utilisés dans le corps de la procédure soient transmis directement au serveur et ne soient pas interprétés par le client (tel que mysql).

Appel de la procédure stockée :

call sp_name[(传参)];
mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 7 | 1 | 57 | 3 | 0 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 5 rows in set (0.00 sec) mysql> call delete_matches(57); Query OK, 1 ligne affectée (0.03 sec) mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 4 rows dans set (0.00 sec)

Analyse :Dans la procédure stockée, une variable p_playerno nécessitant un paramètre est définie. Lors de l'appel de la procédure stockée, 57 est affecté à p_playerno via le paramètre, puis les opérations SQL de la procédure stockée sont exécutées.

Corps de la procédure stockée

  • Le corps de la procédure stockée contient les instructions qui doivent être exécutées lors de l'appel de la procédure, telles que les instructions DML et DDL, les instructions IF-THEN-ELSE et WHILE-DO, les instructions DECLARE pour déclarer des variables, etc.
  • Format du corps de la procédure : commence par BEGIN et se termine par END (peut être imbriqué)
BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

Remarque :Chaque bloc imbriqué et chaque instruction qu'il contient doivent se terminer par un point-virgule. Le bloc BEGIN-END qui marque la fin du corps de la procédure (également appelé instruction composée) ne nécessite pas de point-virgule.

Étiquetage des blocs d'instructions :

[begin_label:] BEGIN
　　[statement_list]
END [end_label]

Par exemple :

label1: BEGIN 　　label2: BEGIN 　　　　label3: BEGIN 　　　　　　instructions; 　　　　END label3 ; 　　END label2; END label1

Les étiquettes ont deux objectifs :

  • 1. Améliorer la lisibilité du code
  • 2. Certaines instructions (par exemple, les instructions LEAVE et ITERATE) nécessitent des étiquettes

II. Paramètres des procédures stockées

Les paramètres des procédures stockées MySQL sont utilisés dans la définition des procédures stockées. Il existe trois types de paramètres : IN, OUT et INOUT, sous la forme :

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
  • Paramètre d'entrée IN : indique que l'appelant passe une valeur dans la procédure (la valeur d'entrée peut être un littéral ou une variable)
  • Paramètre de sortie OUT : indique que la procédure transmet une valeur à l'appelant (peut renvoyer plusieurs valeurs) (la valeur de sortie ne peut être qu'une variable)
  • Paramètre d'entrée/sortie INOUT : indique que l'appelant passe une valeur dans la procédure et que la procédure transmet une valeur à l'appelant (la valeur ne peut être qu'une variable)

1. Paramètre d'entrée IN

mysql> delimiter $$ mysql> create procedure in_param(in p_in int) -> begin -> 　　select p_in; -> 　　set p_in=2; -> select P_in; -> end$$ mysql> delimiter ; mysql> set @p_in=1; mysql> call in_param(@p_in); +------+ | p_in | +------+ | 1 | +------+ +------+ | P_in | +------+ | 2 | +------+ mysql> select @p_in; +-------+ | @p_in | +-------+ | 1 | +-------+

D'après ce qui précède, on peut voir que p_in est modifié dans la procédure stockée, mais cela n'affecte pas@p_inla valeur de cette dernière, car la première est une variable locale et la seconde une variable globale.

2. Paramètre de sortie OUT

mysql> delimiter // mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> // mysql> delimiter ; mysql> set @p_out=1; mysql> call out_param(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+ 　　# Comme OUT est un paramètre de sortie pour l'appelant et n'accepte pas de paramètres d'entrée, p_out dans la procédure stockée est NULL +-------+ | p_out | +-------+ | 2 | +-------+ mysql> select @p_out; +--------+ | @p_out | +--------+ | 2 | +--------+ 　　# Appel de la procédure stockée out_param, paramètre de sortie, modifiant la valeur de la variable p_out

3. Paramètre d'entrée/sortie INOUT

mysql> delimiter $$ mysql> create procedure inout_param(inout p_inout int) -> begin -> select p_inout; -> set p_inout=2; -> select p_inout; -> end -> $$ mysql> delimiter ; mysql> set @p_inout=1; mysql> call inout_param(@p_inout); +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql> select @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+ # Appel de la procédure stockée inout_param, qui accepte des paramètres d'entrée et produit également des paramètres de sortie, modifiant la variable

Remarque :

1. Si la procédure n'a pas de paramètres, vous devez quand même écrire des parenthèses après le nom de la procédure. Par exemple :

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

2. Assurez-vous que les noms de paramètres ne sont pas égaux aux noms de colonnes ; sinon, dans le corps de la procédure, le nom du paramètre sera traité comme un nom de colonne.

Suggestions :

  • Utilisez des paramètres IN pour les valeurs d'entrée.
  • Utilisez des paramètres OUT pour les valeurs de retour.
  • Utilisez le moins possible de paramètres INOUT.

III. Variables

1. Définition de variable

Les déclarations de variables locales doivent être placées au début du corps de la procédure stockée :

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

Ici, datatype est un type de données MySQL, tel que INT, FLOAT, DATE, VARCHAR(length)

Par exemple :

DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'Ceci ne sera pas complété';

2. Affectation de variable

SET 变量名 = 表达式值 [,variable_name = expression ...]

3. Variables utilisateur

Utilisation des variables utilisateur dans le client MySQL:

mysql > SELECT 'Hello World' into @x; mysql > SELECT @x; +-------------+ | @x | +-------------+ | Hello World | +-------------+ mysql > SET @y='Goodbye Cruel World'; mysql > SELECT @y; +---------------------+ | @y | +---------------------+ | Goodbye Cruel World | +---------------------+ mysql > SET @z=1+2+3; mysql > SELECT @z; +------+ | @z | +------+ | 6 | +------+

Utilisation des variables utilisateur dans les procédures stockées

mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello Monde | +----------------------------+

Passage de variables utilisateur à portée globale entre des procédures stockées

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('La dernière procédure était ',@last_procedure); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('La dernière procédure était ',@last_proc | +-----------------------------------------------+ | Dernière procédure était p1 | +-----------------------------------------------+

Remarque :

  • 1. Les noms de variables utilisateur commencent généralement par @
  • 2. L'utilisation excessive des variables utilisateur peut rendre le programme difficile à comprendre et à gérer

IV. Commentaires

Les procédures stockées MySQL peuvent utiliser deux styles de commentaires

Deux tirets--: Ce style est généralement utilisé pour les commentaires monolignes.

Style C: Généralement utilisé pour les commentaires multilignes.

Par exemple :

mysql > DELIMITER // mysql > CREATE PROCEDURE proc1 --nameNom de la procédure stockée ->(IN parameter1 INTEGER) -> BEGIN -> DECLARE variable1 CHAR(10); -> IF parameter1 = 17 THEN -> SET variable1 = 'birds'; -> ELSE -> SET variable1 = 'beasts'; -> END IF; -> INSERT INTO table1 VALUES (variable1); -> END -> // mysql > DELIMITER ;

Appel des procédures stockées MySQL

Utilisez CALL suivi du nom de votre procédure et de parenthèses. À l’intérieur des parenthèses, ajoutez les paramètres nécessaires. Les paramètres comprennent les paramètres d’entrée, les paramètres de sortie et les paramètres d’entrée/sortie. Pour les méthodes d’appel spécifiques, reportez-vous aux exemples ci-dessus.

Interrogation des procédures stockées MySQL

Si nous voulons savoir quelles tables existent dans une base de données, nous utilisons généralementshowtables;pour les afficher. Ensuite, si nous voulons afficher les procédures stockées d’une base de données, pouvons-nous également utiliser cette méthode ? La réponse est : nous pouvons afficher les procédures stockées d’une base de données, mais d’une manière différente.

Nous pouvons utiliser l’instruction suivante pour interroger :

selectname from mysql.proc where db='数据库名';

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';

Si nous voulons connaître les détails d’une procédure stockée spécifique, que devons-nous faire ? Pouvons-nous également utiliser DESCRIBE table_name pour l’afficher comme nous le faisons avec les tables ?

La réponse est :Nous pouvons afficher les détails de la procédure stockée, mais nous devons utiliser une autre méthode :

SHOW CREATE PROCEDURE 数据库.存储过程名;

pour afficher les détails de la procédure stockée actuelle.

Modification des procédures stockées MySQL

ALTER PROCEDURE

La modification d’une procédure stockée précédemment spécifiée, créée avec CREATE PROCEDURE, n’affecte pas les procédures stockées ou les fonctions stockées associées.

Suppression des procédures stockées MySQL

La suppression d’une procédure stockée est relativement simple, tout comme la suppression d’une table :

DROPPROCEDURE

Supprimer une ou plusieurs procédures stockées de la table MySQL.

Instructions de contrôle dans les procédures stockées MySQL

(1). Portée des variables

Les variables internes ont une priorité plus élevée dans leur portée. Lorsque l'exécution atteint END, les variables internes disparaissent. À ce moment-là, elles sont déjà en dehors de leur portée et les variables ne sont plus visibles, car cette variable déclarée ne peut plus être trouvée en dehors de la procédure stockée. Cependant, vous pouvez enregistrer sa valeur via un paramètre OUT ou en affectant sa valeur à une variable de session.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > DELIMITER ;

(2). Instructions conditionnelles

1. Instructions if-then-else

mysql > DELIMITER // mysql > CREATE PROCEDURE proc2(IN parameter int) -> begin -> declare var int; -> set var=paramètre+1; -> if var=0 then -> insert into t values(17); -> end if; -> if paramètre=0 then -> update t set s1=s1+1; -> else -> update t set s1=s1+2; -> end if; -> end; -> // mysql > DELIMITER ;

2. instruction case:

mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in paramètre int) -> begin -> declare var int; -> set var=paramètre+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ; case when var=0 then insert into t values(30); when var>0 then when var<0 then else end case

(3). Instructions de boucle

1. while ···· end while

mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER ;
while 条件 do
    --循环体
endwhile

2. repeat···· end repeat

Il vérifie le résultat après avoir effectué l'opération, alors que while vérifie avant l'exécution.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin -> declare v int; -> set v=0; -> repeat -> insert into t values(v); -> set v=v+1; -> until v>=5 -> end repeat; -> end; -> // mysql > DELIMITER ;
repeat
    --循环体
until 循环条件  
end repeat;

3. loop ·····endloop

La boucle loop ne nécessite pas de condition initiale, comme la boucle while, et, comme la boucle repeat, elle ne nécessite pas de condition de fin. L'instruction leave est utilisée pour sortir de la boucle.

mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

4. ÉTIQUETTES :

Les étiquettes peuvent être placées avant les instructions begin, repeat, while ou loop. Les étiquettes d'instruction ne peuvent être placées que devant des instructions valides. Elles peuvent sortir d'une boucle, en faisant passer l'exécution à la dernière étape de l'instruction composée.

(4). ITERATE itération

ITERATE redémarre une instruction composée en référençant son étiquette :

mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;

Articles de référence :

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html