recherche

Maison >base de données >tutoriel mysql >[MySQL 08] Procédure stockée

[MySQL 08] Procédure stockée

黄舟
黄舟original
2017-02-04 13:17:581613parcourir

Tout d'abord, la procédure stockée SQL est similaire à la méthode de définition et à la méthode d'appel en Java.

1. Créer une procédure stockée

Dans MySQL, la forme de base d'une procédure stockée : 

CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
        [characteristic ...] routine_body

  • Le paramètre sp_name est le nom de la procédure stockée;

  • proc_parameter représente la liste des paramètres de la procédure stockée;

  • le paramètre caractéristique spécifie les caractéristiques de la procédure stockée ; 🎜>

    • routine_body est le contenu du code SQL Vous pouvez utiliser BEGIN...END pour marquer le début et la fin du code SQL.

    • Chaque paramètre de proc_parameter se compose de 3 parties. Ces trois parties sont les types d'entrée et de sortie, les noms de paramètres et les types de paramètres. Sa forme est la suivante : 

[ IN | OUT | INOUT ] param_name type
    IN représente les paramètres d'entrée ;

    • OUT représente les paramètres de sortie ; 🎜>

  • INOUT signifie qu'il peut s'agir d'une entrée ou d'une sortie ;

  • le paramètre param_name est le nom du paramètre de la procédure stockée ;
  • Le paramètre type spécifie le type de paramètre de la procédure stockée, qui peut être n'importe quel type de données de la base de données MySQL

  • Définition simplifiée :

    • Appel :

Remarque : 
create procedure 过程名(参数...)
begin
    SQL语句...end
Avant de définir, vous devez remplacer l'instruction par défaut se terminant par ';' par quelque chose d'autre, tel que '&&', afin que le point-virgule défini dans le La procédure ne sera pas considérée comme la fin de la déclaration (sinon elle sera soumise directement). Après l'avoir défini, vous devez restaurer ';' au terminateur par défaut.

2. Procédures stockées sans paramètres 
call 过程名(实参)


3. Procédures stockées avec paramètres

4. >
//建立存储过程mysql> delimiter %%
mysql> create procedure p1()    
-> begin    
-> select Sname,Ssex from student;    
-> end %%mysql> delimiter ;  
//调用存储过程mysql> call p1(); 
+--------+------+| Sname  | Ssex |
+--------+------+| 赵英    | 女   |
| 郑美丽  | 女   |
| 李哲    | 男   |
| 王洋    | 女   |
| 王平    | 男   |
| 赵东新  | 男   |
| 王新    | 男   |
| 陶丽平  | 男   |
| 陈文    | 女   |
| 蔡天    | 女   || 杨洋    | 男   |
+--------+------+

//查询mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 || 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+//建立存储过程mysql> delimiter &&
mysql> create procedure p4(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11))    -> begin    
-> insert into employee value(employeeID,name,job,departmentID);    -> select * from employee;    -> end &&mysql> delimiter ;  
//调用存储过程mysql> call p4('7322','cid','udewhuuwdwq',127);
+------------+------------+-------------+--------------+| employeeID | name       | job         | departmentID |
+------------+------------+-------------+--------------+| 6651       | Ajay Patel | Programmer  |          128 |
| 7513       | Nora Edwar | Programmer  |          128 |
| 9006       | Candy Burn | Systems Ad  |          128 |
| 9842       | Ben Smith  | DBA         |           42 |
| 9843       | Pert Park  | DBA         |           42 |
| 9845       | Ben Patel  | DBA         |          128 |
| 9846       | Red Right  | NULL        |          128 |
| 9847       | Run Wild   | NULL        |          128 |
| 9848       | Rip This J | NULL        |          128 |
| 9849       | Rip This J | NULL        |          128 |
| 9850       | Reader U   | NULL        |          128 |
| 6651       | Ajay Patel | Programmer  |          128 || 7322       | cid        | udewhuuwdwq |          127 |
+------------+------------+-------------+--------------+
5. Utilisation de variables

Dans les procédures et fonctions stockées, les variables peuvent être définies et utilisées. Les utilisateurs peuvent utiliser le mot-clé DECLARE pour définir des variables. La variable peut alors se voir attribuer une valeur. La portée de ces variables se trouve dans la section du programme BEGIN...END. Cette section expliquera comment définir des variables et attribuer des valeurs aux variables. 
//建立存储过程mysql> delimiter &&
mysql> create procedure p9(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11),out num int)
    -> begin
    -> insert into employee value(employeeID,name,job,departmentID);
    -> select count(*) into num from employee;
    -> end &&

mysql> delimiter ; 

//调用存储过程mysql> call p9('3632','dueh','xianggang',28,@num);//注意输出变量的输出mysql> select @num;
+------+
| @num |
+------+
|   15 |
+------+//////////////////////////系统变量名称：@@变量名/// //用户变量名称：@变量名 ///

1． Définir des variables

Vous pouvez utiliser le mot-clé DECLARE pour définir des variables dans MySQL. La syntaxe de base pour définir les variables est la suivante :

Parmi eux, le mot-clé DECLARE est utilisé pour déclarer des variables ; le paramètre var_name est le nom de la variable, où plusieurs variables peuvent être définies en même temps. time ; le paramètre type est utilisé pour spécifier le type de variable ; la clause DEFAULT value définit la valeur par défaut de la variable sur value. Lorsque la clause DEFAULT n'est pas utilisée, la valeur par défaut est NULL.

2． Attribuer des valeurs aux variables

Vous pouvez utiliser le mot-clé SET dans MySQL pour attribuer des valeurs aux variables. La syntaxe de base de l'instruction SET est la suivante : 
DECLARE var_name[,...] type [DEFAULT value]

Parmi eux, le mot-clé SET est utilisé pour attribuer une valeur à une variable ; le paramètre var_name est le nom de la variable ; une expression d’affectation. Une instruction SET peut attribuer des valeurs à plusieurs variables en même temps. Les instructions d'affectation pour chaque variable sont séparées par des virgules. 
DECLARE  my_sql  INT  DEFAULT 10 ; //定义变量my_sql，数据类型为INT型，默认值为10

Vous pouvez également utiliser l'instruction SELECT...INTO pour attribuer des valeurs aux variables dans MySQL. La syntaxe de base est la suivante :

SELECT col_name[,…] INTO var_name[,…] 
SET var_name = expr[,var_name = expr] ...
FROM table_name WEHRE condition

Parmi eux, le paramètre col_name représente le nom du champ de requête ; est le nom de la variable ; le paramètre table_name fait référence au nom de la table ; le paramètre condition fait référence à la condition de requête. 

SET my_sql = 30 ; //为变量my_sql赋值为30

6. Définir les conditions et les gestionnaires

Définir les conditions et les gestionnaires consiste à définir à l'avance les problèmes qui peuvent être rencontrés lors de l'exécution du programme. Et les solutions à ces problèmes peuvent être définies dans le gestionnaire. Cette approche permet de prédire à l’avance d’éventuels problèmes et de proposer des solutions. Cela peut améliorer la capacité du programme à gérer les problèmes et empêcher le programme de s'arrêter anormalement. Dans MySQL, les conditions et les gestionnaires sont définis via le mot-clé DECLARE. Cette section explique en détail comment définir les conditions et les gestionnaires.

1． Définir des conditions 

SELECT d_id INTO my_sql  
   FROM employee WEHRE id=2 ; //从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql
Vous pouvez utiliser le mot-clé DECLARE pour définir des conditions dans MySQL. Sa syntaxe de base est la suivante : 

DECLARE  condition_name  CONDITION  FOR  condition_value  
condition_value:  
      SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

//定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find
//方法一：使用sqlstate_value  DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
//方法二：使用mysql_error_code  DECLARE  can_not_find  CONDITION  FOR  1146 ;

2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement  
handler_type:  
    CONTINUE | EXIT | UNDO  
condition_value:  
    SQLSTATE [VALUE] sqlstate_value |
condition_name  | SQLWARNING  
       | NOT FOUND  | SQLEXCEPTION  | mysql_error_code

  • handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。

  • CONTINUE表示遇到错误不进行处理，继续向下执行； EXIT表示遇到错误后马上退出；

  • UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。

condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

//定义处理程序的几种方式
//方法一：捕获sqlstate_value  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @info='CAN NOT FIND';  
//方法二：捕获mysql_error_code  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
//方法三：先定义条件，然后调用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND';  
//方法四：使用SQLWARNING  
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
//方法五：使用NOT FOUND  
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
//方法六：使用SQLEXCEPTION  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

  • 第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出”CANNOT FIND”信息。

  • 第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出”CAN NOT FIND”信息。

  • 第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。

  • 第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

  • 第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出”CAN NOT FIND”信息。

  • 第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

以上就是 【MySQL 08】存储过程的内容，更多相关内容请关注PHP中文网（www.php.cn）！


Déclaration：
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Article précédent：【MySQL 07】Technologie de récupération de base de donnéesArticle suivant：【MySQL 07】Technologie de récupération de base de données

Articles Liés

Voir plus