Explication détaillée de la création de procédures stockées par MySQL (procédures et fonctions stockées)

En termes simples, une procédure stockée est une combinaison d'une ou plusieurs instructions SQL, qui peut être considérée comme un fichier batch, mais son rôle ne se limite pas au traitement par lots.

(1) Créer une procédure stockée
(2) Créer une fonction stockée
(3) Utilisation de variables
(4) Définir des conditions et des gestionnaires
(5) Utilisation du curseur
(6) Utilisation du contrôle de processus

(recommandation d'apprentissage gratuite : tutoriel vidéo mysql)

---

(1) Créer une procédure stockée

Pour créer une procédure stockée, vous devez utiliser l'instruction create procedure Le format de syntaxe de base est le suivant :

create procedure sp_name( [ proc_parameter ] )[ characteristics ... ] routine_body

create procedure est utilisé pour créer un mot-clé de fonction stockée ; sp_name est le nom de la procédure stockée ; proc_parameter est la liste des paramètres de la procédure stockée :

[in | out | inout] param_name type

in représente. le paramètre d'entrée
• out représente le paramètre de sortie
inout indique qu'il peut être entré ou sorti
• param_name indique le nom du paramètre indique le type de paramètre

characteristics précise les caractéristiques de la procédure stockée, avec les valeurs suivantes :

• langage SQL : Description La partie routine_body est composée d'instructions SQL Le langage actuellement supporté par. le système est SQL et SQL est la seule valeur de l'attribut de langage.

• [non] déterministe : Indique si le résultat de l'exécution de la procédure stockée est correct. Déterministe signifie que la même entrée obtiendra la même sortie à chaque fois que la procédure stockée est exécutée ; alors que non déterministe signifie que la même entrée peut obtenir des sorties différentes. La valeur par défaut n'est pas déterministe.

• {contient du SQL | aucun SQL | lit la date SQL | modifie la date SQL } : Indique les restrictions sur l'utilisation des instructions SQL par les sous-programmes. contient SQL indique que le sous-programme contient des instructions SQL ; aucun SQL indique que le sous-programme ne lit pas de données SQL ; indique que le sous-programme contient des instructions pour lire des données ; modifie des données SQL indique que le sous-programme contient des instructions pour écrire des données. La valeur par défaut contient SQL.

• Sécurité SQL {definer | invocateur} : Spécifiez qui a l'autorisation d'exécuter. le définisseur signifie que seul le définisseur peut l'exécuter ; l'invocateur signifie que l'appelant autorisé peut l'exécuter. La valeur par défaut est le définisseur.

• comment 'string' : informations de commentaire, qui peuvent être utilisées pour décrire des procédures ou des fonctions stockées.

routine_body est le contenu du code SQL Vous pouvez utiliser start...end pour représenter le début et la fin du code SQL.

[Exemple 1] Créez une procédure stockée pour afficher la table des fruits. L'instruction de code est la suivante :

create procedure proc()
	BEGIN
	select * from fruits;
	END ;

Ce code crée une procédure stockée pour afficher la table des fruits. est le suivant :

mysql> delimiter //mysql> create procedure Proc()
    -> begin
    -> select * from fruits;
    -> end //Query OK, 0 rows affected (0.36 sec)mysql> delimiter ;

Astuce : La fonction de l'instruction "delimiter //" est de définir le symbole de fin de MySQL sur //, car le symbole de fin d'instruction par défaut de MySQL est un point-virgule " ;". Ceci est fait pour éviter les conflits avec SQL dans les procédures stockées. Les terminateurs d'instruction sont en conflit. Une fois la procédure stockée définie, utilisez « delimiter ; » pour restaurer le terminateur par défaut. Lorsque vous utilisez la commande delimiter, vous devez éviter d'utiliser la barre oblique inverse "" car la barre oblique inverse est un caractère d'échappement dans MySQL.

[Exemple 2] Créez une procédure stockée nommée CountProc Le code est le suivant :

create procedure CountProc (OUT paraml int)beginselect count(*) into paraml from fruits;end;

Le code ci-dessus crée une procédure stockée pour obtenir le nombre d'enregistrements dans la table des fruits. . Le nom est CountProc. Après le calcul count(*), le résultat est mis dans le paramètre paraml. Le résultat de l'exécution du code est le suivant :

mysql> delimiter  //mysql> create procedure CountProc(OUT paraml int )
    -> begin
    -> select count(*) into paraml from fruits;
    -> end //Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;

(2) Créer une fonction stockée

Pour créer une fonction stockée, vous devez utiliser l'instruction create function La syntaxe de base est la suivante. suit :

create function func_name ( [ func_parameter] )
returns type
[characteristic ...] routine_body

create function est le mot-clé utilisé pour créer une fonction stockée
• func_name représente le nom de la fonction stockée
• func_parameter est la liste des paramètres de la procédure stockée. La liste des paramètres est sous la forme :
• [in | out | inout] param_name type

Parmi eux, in représente les paramètres d'entrée, out représente les paramètres de sortie, inout représente à la fois l'entrée et la sortie. le type de paramètre ; l'instruction return type représente le type de données renvoyées par la fonction ; la caractéristique spécifie les caractéristiques de la fonction stockée, la valeur est la même que lors de la création de la procédure stockée.

[Exemple 3] Créez une fonction de stockage nommée NameByZip. Cette fonction renvoie le résultat de la requête de l'instruction select. Le type numérique est de type chaîne :

create function NameByZip()returns char( 50)return(select s_name from suppliers where s_call ='48075');

L'exécution. le résultat du code est le suivant :

mysql> delimiter //mysql> create function NameByZip()
    -> returns char(50)
    -> return (select s_name from suppliers where s_call = '48075');
    -> //Query OK, 0 rows affected (0.06 sec)mysql> delimiter;

Si une instruction ruturn dans une fonction stockée renvoie une valeur d'un type différent du type spécifié dans la clause return de la fonction, la valeur de retour sera forcée au type approprié. .

Remarque : La spécification de paramètres comme in, out ou inout n'est légale que pour les procédures. (La fonction utilise toujours par défaut le paramètre in.) La clause return ne peut être spécifiée que pour la fonction et est obligatoire pour la fonction. Il est utilisé pour spécifier le type de retour de la fonction et le corps de la fonction doit contenir une instruction de valeur de retour.

(3) Utilisation de variables

Les variables peuvent être déclarées et utilisées dans des sous-programmes, et la portée de ces variables est dans le programme de début... de fin.

1. Définir les variables

Utilisez l'instruction declar pour définir les variables dans la procédure stockée. Le format de syntaxe est le suivant :

declare var_name[,varname]... date_type [default value];

var_name est le nom de la variable locale. . La clause de valeur par défaut fournit une valeur par défaut pour la variable. En plus d'être déclarée comme constante, la valeur peut également être spécifiée comme expression. S'il n'y a pas de clause par défaut, la valeur initiale est nulle.

【例4】定义名称为myparam的变量，类型为int类型，默认值为100，代码如下：

declare myparam int default 100;

2.为变量赋值

set var_name = expr [,var_name = expr]...;

存储程序中的set语句是一般set语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

【例5】声明3个变量，分别为var1，var2和var3，数据类型为int，使用set为变量赋值，代码如下：

declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;

MySQL中还可以通过select…into为一个或多个变量赋值，语句如下：

select col_name[,...] into var_name[,...] table_expr;

这个select语法把选定的列直接存储到对应位置的变量。col_name表示字段名称;var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和where子句。

【例6】声明变量fruitname和fruitprice，通过select…into语句查询指定记录并为变量赋值,代码如下:

declare fruitname char(50);declare fruitprice decimal(8,2);select f_name,f_price into fruitname,fruitpricefrom fruits where f_id='a1;

(4)定义条件和处理程序

特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样就增强了存储程序处理问题的能力，避免程序异常停止运行。

1.定义条件
定义条件使用declare语句，语法格式如下：

declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code

• condition_name表示条件的名称
• condition_type表示条件的类型
• sqlstate_value和mysql_error_code都可以表示MySQL的错误
• sqlstate_value为长度为5的字符类型错误代码
• mysql_error_code为数值类型错误代码

例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值为1142。

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的declare handler语句中。

【例7】定义"error 1148(42000)"错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：

[方法一]：使用sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000'[]方法二]:使用mysql_error_codedeclare command_not_allowed condition for 1148

2.定义处理程序

定义处理程序时，使用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个值：continue、exit和undo。
• continue表示遇到错误不处理，继续执行；
• exit遇到错误马上退出；
• undo表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。

condition_value表示错误类型，可以有以下取值：

• sqlstate[value] sqlstate_value包含5个字符串错误值
• condition_name表示declare condition定义的错误条件名称
• sqlwarning匹配所有以01开头的sqlstate错误代码
• notfound 匹配所有以02开头的sqlstate错误代码
• sqlexception匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码
• mysql_error_code匹配数值类型错误代码

sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

【例8】定义处理程序的几种方式如下：

方法1：捕获sqlstate_valuedeclare continue handler for sqlstate '42S02' set @info='No_SUCH_TABLE';方法2:捕获mysql_error_codedeclare continue handler for 1146 set @info='No_SUCH_TABLE';方法3:先定义条件,然后调用declare no_such_table condition for 1146;declare continue handler for NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';方法4:使用sqlwarningdeclare exit handler for sqlwarning set @info='ERROR';方法5:使用not founddeclare exit handler for not found set @info=' NO_SUCH_TABLE ';方法6:使用sqlexceptiondeclare exit handler forsqlexception set @info='ERROR';

上述代码是6种定义处理程序的方法。

第一种，捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第二种，捕获mysql_error_code值。如果遇到mysql_error_code值为1146，就执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第三种，先定义条件再调用条件。这里先定义no_such_table条件，遇到1146错误就执行continue操作。
第四种，使用sqlwarning。sqlwarning捕获所有以01开头的sqlstate_value值，然后执行exit操作，并且输出"ERROE"信息。
第五种，使用not found。not found捕获所有以02开头的sqlstate_value值，然后执行exit操作，并且输出"NO_SUCH_TABLE"信息。
第六种，使用SQLEXCEPTION。sqlexception捕获所有没有被sqlwarning或not found捕获的sqlstate_value值，然后执行exit操作，并且输出"ERROR"信息。

【例9】定义条件和处理程序，具体执行的过程如下：

mysql> create table test.t(s1 int,primary key (s1));Query OK, 0 rows affected (0.14 sec)mysql> delimiter //mysql> create procedure handlerdemo()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x =1;
    -> insert into test.t values(1);
    -> set @x=2;
    -> insert into test.t values(1);
    -> set @x=3;
    -> end;
    -> //Query OK, 0 rows affected (0.06 sec)[调用存储过程]mysql> delimiter ;mysql> call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看调用过程结果]mysql> select @x;+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

可以看到，@x 是一个用户变量，执行结果@x等于3，这表明MySQL被执行到程序末尾。

• "var_name"表示用户变量，使用set语句为其赋值。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

(5)光标的使用

MySQL中光标只能在存储过程和函数中使用。

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.声明光标

MySQL中使用declare关键字来声明光标，语法形式如下：

declare cursor_name cursor for select_statement

其中，cursor_name参数表示光标的名称；select_statement表示select语句的内容，返回一个用于创建光标的结果集。

【例10】声明名称为cursor_fruit的光标，代码如下：

declare cursor_fruit cursor for select f_name,f_price from fruits;

该代码中光标名称为cursor_fruit，select语句部分从fruits表汇总查询出f_name和f_price字段的值。

2.打开光标

open cursor_name{光标名称}

这个语句打开先前声明的名称为cursor_name的光标。

【例11】打开名称为cursor_fruit的光标，代码如下：

open cursor_fruit ;

3.使用光标

使用光标的语法格式:

fetch cursor_name into var_name [，var_name ] ... {参数名称}

其中，cursor_name参数表示光标的名称；var_name表示将光标中的select语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

【例12】使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price两个变量中，代码如下:

fetch cursor_fruit into fruit_name,fruit_price;

4.关闭光标
关闭光标的语法格式:

close cursor_name(光标名称)

这个语句关闭先前打开的光标。
如果未被明确地关闭，那么光标将在它被声明的复合语句的末尾被关闭。

【例13】关闭名称为cursor_fruit的光标，代码如下：

close cursor_fruit;

(6)流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句。每个流程中可能包含一个单独语句，或者是使用begin…end构造的符合语句，构造可以被嵌套。

1.if语句

if语句包含多个条件判断，根据判断的结果为true或false执行相应的语句，语法格式如下：

if expr_condition then statement_list	[elseif expr_condition then statement_list]...
	[else statement_list]end if

如果expr_condition求值为真，相应的SQL语句列表被执行；如果没有expr_condition匹配，则else子句里的语句列表被执行。statement_list列表可包括一个或多个语句。

MySQL中还有一个if()函数，它不同于这里描述的if语句。

【例14】if语句示例

if val is null
	then select ‘val is null’;
	else select 'val is not null';end if

该示例判断val值是否为空，如果为空输出字符串"val is null"；否则输出字符串"val is not null"。if语句都需要使用end if来结束。

2.case语句

case是另一个进行条件判断的语句，有两种语句格式，第一种：

case case_expr	when when_value then statement_list	[when when_value then statement_list]...
	[else statement_list]end case

• case_expr表示条件判断的表达式，决定了哪一个when语句会被执行
• when_value表示表达式可能的值。
• 如果某个when_value表达式和case_expr表达式结果相同，则执行对应的then关键字后面的statement_list中的语句。
• statement_list表示不同when_value值的执行语句。

【例15】使用case流程控制语句的第1种格式，判断val值等于1、等于2或者两者都不等，SQL语句如下：

case val	when 1 then select ‘val is 1’；	when 2 then select ‘val is 2’；	else select ‘val is not 1 or 2’；end case;

当val值为1时，输出字符串"val is 1";当val值为2时，输出字符串"val is 2";否则输出字符串"val is not 1 or 2"。

case语句的第2种格式如下：

case
	when expr_condition then statement_list	[when expr_condition then statement_list]
	[else statement_list]end case

• expr_condition表示条件判断语句
• statement_list表示不同条件的执行语句

该语句中，when语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应then关键字后面的statement_list语句。如果没有条件匹配，else子句里的语句被执行。

注意：存储程序中的case语句和case控制流程函数的区别：
存储程序中的case语句不能有else null子句，并且用end case替代end来终止。

【例16】使用case流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0，SQL语句如下：

case
	when val is null then select  ‘val is null’;
	when val < 0 then  select &#39;val is less than 0&#39;;
	when val > 0 then select 'val is greater than 0';
	else select 'val is 0';end case;

当val值为空时，输出字符串"val is null";当val值小于0时，输出字符串"val is less than 0";当val值大于0时，输出字符串"val is greater than 0";否则输出字符串"val is 0"。

3.loop语句

loop循环语句用来重复执行某些语句，与if和case语句相比，loop只是创建一个循环操作过的过程，并不进行条件判断。退出循环过程使用leave子句。loop语法格式如下:

[loop_label:] loop
	statement_listend loop [loop_label]

loop_label表示loop语句的标注名称，该参数可省略。statement_list参数表示需要循环执行的语句。

【例17】使用loop语句进行循环操作，id值小于等于10之前，将重复执行循环过程，SQL语句如下：

declare id int default 10add_loop:loopset id = id +1;
	if >=10 then leave add_loop;
	end if;end loop add_ loop;

该示例循环执行id加1的操作。当id值小于10时，循环重复执行。当id值大于或等于10时，使用leave语句退出循环。loop循环都以end loop结束。

4.leave语句

leave语句用来退出任何被标注的流程控制构造，leave语句基本格式如下：

leave label

其中，label参数表示循环的标志。leave和begin…end或循环一起被使用。

【例18】使用leave语句退出循环，代码如下：

add_num:loopset @count=@count+1;if @count=50 then leave add_num;end loop add_num;

该示例循环执行count加1的操作，当count的值等于50时，使用leave语句跳出循环。

5.iterate语句

iterater label语句将执行顺序转到语句段开头处，语法格式如下：

iterate label

iterate只可以出现在loop、repeat和while语句内。iterate的意思为"再次循环"，label参数表示循环的标志。iterate语句必须跟在循环标志前面。

【例19】iterate语句示例：

create procedure doiterate()begin
 declare p1 int default 0;
 declare p1 int default 0;
 my_loop:loop;
 set p1 = p1 + 1;
 if p1 < 10 then iterate my_loop;
 elseif p1 > 20 then leave my_loop;
 end if;
 select 'p1 is between 10 and 20';end loop my_loop;end

首先定义p1=0，当p1的值小于10时重复执行p1加1操作；当p1大于等于10并且小于等于20时，打印消息"p1 is between 10 and 20";当p1大于20时，退出循环。

6.repeat语句

repeat语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，若表达式为真，则循环结束；否则重复执行循环中的语句。repeat语句的语法格式如下：

[repeat_label:] repeat
	statement_list
until expr_conditionend repeat [repeat_label]

repeat_label为repeat语句的标注名称，该参数可以省略；repeat语句内的语句或语句群被重复，直至expr_condition为真。

【例20】repeat语句示例，id值等于10之前，将重复执行循环过程，代码如下：

declare id int default 0；repeatset id = id + 1；
until id >= 10end repeat;

该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。repeat循环都以end repeat结束。

7.while语句

while语句创建一个带条件判断的循环过程，与repeat不同，while在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。while语句的基本格式如下：

[while_label:] while expr_condition do
	statement_listend while [while_label]

• while_label为while语句的标注名称
• expr_condition为进行判断的表达式，如果表达式结果为真，while语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

【例21】while语句示例，i值小于10时，将重复执行循环过程，代码如下：

declare i int default 0；while i <10 doset i = i + 1;end while;

相关免费学习推荐：mysql数据库(视频)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!