Optimisation MySQL - exemples de code de procédures et de fonctions stockées personnalisées (image)

Pour créer des procédures stockées et des fonctions dans MYSQL, utilisez respectivement CREATE PROCEDURE et CREATE FUNCTION

Utilisez l'instruction CALL pour appeler des procédures stockées.

Les fonctions peuvent être appelées depuis l'extérieur d'une instruction, peuvent renvoyer une valeur scalaire

---

Créer une procédure stockée

Syntaxe

CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

proc_parameter spécifie la liste des paramètres de la procédure stockée. Le format de la liste est le suivant :

[IN|OUT|INOUT] param_name type

où in représente le paramètre d'entrée, out représente le paramètre de sortie, inout représente les deux entrées. et sortie ; param_name représente le nom du paramètre ; type représente le paramètre

Ce type peut être n'importe quel type dans la base de données MYSQL

a les valeurs suivantes :

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
routine_body:
    Valid SQL procedure statement or statements

LANGUAGE SQL : Indique que la partie routine_body est composée d'instructions SQL. Le langage actuellement pris en charge par le système est SQL et la seule valeur de l'attribut LANGUAGE.

[NOT] DETERMINISTIC : Spécifie la procédure stockée Les résultats de l'exécution sont-ils corrects ? DÉTERMINISTE signifie que le résultat est déterministe. Chaque fois que la procédure stockée est exécutée, la même entrée entraînera la

même sortie.

[NON] DÉTERMINISTE signifie que le résultat est incertain et que la même entrée peut obtenir des sorties différentes. Si aucune valeur n'est spécifiée, la valeur par défaut est [NOT] DETERMINISTIC

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA  : Indique les restrictions sur l'utilisation des instructions SQL par les sous-programmes. .

CONTAINS SQL indique que le sous-programme contient des instructions SQL, mais ne contient pas d'instructions pour lire et écrire des données ;

NO SQL indique que le sous-programme ne contient pas d'instructions SQL ; >READS SQL DATA : indique que le sous-programme contient des instructions pour lire des données ;

MODIFIES SQL DATA indique que le sous-programme contient des instructions pour écrire des données.

Par défaut, le système spécifiera CONTAINS SQL

SQL SECURITY { DEFINER | INVOKER >

 : Indiquez qui a l'autorisation d'exécuter. DEFINER signifie que seul le définisseur peut l'exécuter

INVOKER signifie que l'appelant autorisé peut l'exécuter. Par défaut, le système est désigné par DEFINER

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, qui. peut être utilisé avec BEGIN...END pour indiquer le début et la fin du code SQL.

L'instruction suivante crée une procédure stockée qui interroge toutes les données de la table t1

DROP PROCEDURE IF EXISTS Proc;

DELIMITER //
CREATE PROCEDURE Proc()
BEGIN
  SELECT * FROM t3;
END//
DELIMITER ;

CALL Proc();

Le t3 table est notre La logique de la table

créée dans la section précédente est

1. Déterminez d'abord s'il existe une procédure stockée Proc(), et si c'est le cas, supprimez-la

<. 🎜>2. Créer une procédure stockée Proc()

3. Exécuter une procédure stockée Proc()

Remarque : la fonction "DELIMITER //" L'instruction consiste à définir le caractère de fin de MYSQL sur //, car le terminateur d'instruction par défaut de MYSQL est un point-virgule ;, afin d'éviter tout conflit avec le terminateur d'instruction SQL dans la procédure stockée

, vous devez utiliser DELIMITER pour changer le caractère de fin de la procédure stockée et terminer la procédure stockée avec "END//".

Utilisez DELIMITER une fois la procédure stockée définie pour restaurer le terminateur par défaut. DELIMITER peut également spécifier d'autres symboles comme terminateurs ! ! ! ! ! ! ! ! !

Si vous écrivez ainsi, vous obtiendrez l'erreur suivante. Les débutants peuvent facilement commettre cette erreur, moi y compris

Créer un fichier. nommé La procédure stockée de CountProc, le code est le suivant :

CREATE PROCEDURE Proc()
BEGIN
  SELECT * FROM t3;
END

Query: CREATE PROCEDURE Proc() BEGIN SELECT * FROM t3

Error Code: 1064
You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec
---------------------------------------------------
Query: END

Error Code: 1064

La fonction du code ci-dessus est de créer une procédure stockée pour obtenir le nombre d'enregistrements dans la table t3. Le nom est CountProc. Après calcul,

COUNT(*) Le résultat est mis dans le paramètre param1.

DROP PROCEDURE IF EXISTS CountProc;
DELIMITER //
CREATE PROCEDURE CountProc(OUT param1 INT)
BEGIN
SELECT    COUNT(*) INTO  param1 FROM s_visit_log;
END//
DELIMITER ;

CALL CountProc(@aaa);
SELECT @aaa;

Remarque : lorsque vous utilisez la commande DELIMITER, vous devez éviter d'utiliser le caractère barre oblique inverse (), car la barre oblique inverse est le caractère d'échappement de MYSQL ! ! !

Fonction stockée

---

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

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 fonction stockée. fonction. La liste des paramètres est la suivante

CREATE FUNCTION func_name([func_parameter])
RETURNS TYPE
[characteristics...] routine_body

Parmi eux, IN représente le paramètre d'entrée, OUT représente le paramètre de sortie et INOUT représente à la fois l'entrée et la sortie

param_name représente le paramètre ; name; type représente le type du paramètre, qui peut être n'importe quel type dans la base de données MYSQL

[IN|OUT|INOUT]PARAM_NAMETYPE

L'instruction RETURNS TYPE indique le type de données renvoyées par la fonction caractéristiques : spécifie les caractéristiques de la fonction stockée, la valeur est ; la même chose que lors de la création de la procédure stockée

Créez une fonction stockée avec le nom NameByT, qui renvoie une instruction SELECT Le résultat de la requête, le type numérique est une chaîne

DELIMITER //

CREATE FUNCTION NameByT()
RETURNS CHAR(50)
RETURN (SELECT NAME FROM t3 WHERE id=2);
//
DELIMITER ;

Remarque : lors de l'utilisation du type de données RETURNS CHAR(50), RETURNS Il y a S, mais lorsque RETURN (SELECT NAME FROM t3 WHERE id=2), RETURN n'a pas de S

Alors parfois, vous pouvez penser que MYSQL est très Désolé, qui sait, je l'ai mal écrit

这里有一个方法，就是利用SQLYOG的代码格式化功能，选中要格式化的代码，然后按F12，如果能格式化，证明你的代码没有问题，如果不能格式化

证明你写的代码有问题！！！

不加s的话就会出现语法错误了

<strong>Query: create function NameByT() return char(50) return (select name from t3 where id=2)

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;return char(50)
return (select name from t3 where id=2)&#39; at line 2

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.003 sec
-----------------------------</strong>

调用函数

<strong>SELECT nameByT()</strong>

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制转换为恰当的类型。

例如，如果一个函数返回一个SET或ENUM值，但是RETURN语句返回一个整数，对于SET成员集的相应ENUM成员，从函数返回的值

是字符串。

指定参数为IN、OUT、INOUT只对PROCEDURE是合法的。

（FUNCTION中总是默认是IN参数）RETURNS子句对FUNCTION做指定，对函数而言这是强制的。

他用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句

---

变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN...END程序中

1、定义变量

在存储过程中定义变量

<strong>DECLARE var_name[,varname]...date_type[DEFAULT VALUE];</strong>

var_name为局部变量的名称。DEFAULT VALUE子句给变量提供一个默认值。值除了可以被声明为一个常数外，还可以被指定为一个表达式。

如果没有DEFAULT子句，初始值为NULL

<strong>DECLARE MYPARAM INT DEFAULT 100;</strong>

2、为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值，MYSQL中使用SET语句为变量赋值

<strong>SET var_name=expr[,var_name=expr]...</strong>

在存储过程中的SET语句是一般SET语句的扩展版本。

被SET的变量可能是子程序内的变量，或者是全局服务器变量，如系统变量或者用户变量

他运行SET a=x,b=y,....

声明3个变量，分别为var1，var2和var3

<strong>DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;</strong>

MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值

<strong>DECLARE NAME CHAR(50);
DECLARE id DECIMAL(8,2);
SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2;</strong>

---

定义条件和处理程序

特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，

处理程序定义了在遇到这些问题时候应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。

这样可以增强存储程序处理问题的能力，避免程序异常停止运行

1、定义条件

<strong>DECLARE condition_name CONDITION FOR[condition_type]
[condition_type]:
SQLSTATE[VALUE] sqlstate_value |mysql_error_code</strong>

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语句中

定义ERROR1148(42000)错误，名称为command_not_allowed。

可以用两种方法定义

<strong>//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE &#39;42000&#39;

//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148</strong>

2．定义处理程序

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

<strong>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</strong>

其中，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表示一些存储过程或函数的执行语句。

下面是定义处理程序的几种方式。代码如下：

<strong>//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE &#39;42000&#39;
SET @info=&#39;CAN NOT FIND&#39;;
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1148SET @info=&#39;CAN NOT FIND&#39;;
//方法三：先定义条件，然后调用
DECLARE can_not_find CONDITION FOR 1146 ;
DECLARE CONTINUE HANDLER FOR can_not_find SET
@info=&#39;CAN NOT FIND&#39;;
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info=&#39;ERROR&#39;;
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=&#39;CAN NOT FIND&#39;;
//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=&#39;ERROR&#39;;</strong>

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

第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42000，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。

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

第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1148错误就执行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"信息

定义条件和处理程序

<strong>CREATE TABLE t8(s1 INT,PRIMARY KEY(s1))

DELIMITER //
CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE &#39;23000&#39; SET @X2=1;
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END;
//
DELIMITER ;

/* 调用存储过程*/
CALL handlerdemo();

/* 查看调用存储过程结果*/
SELECT @X</strong>

@X是一个用户变量，执行结果@X等于3，这表明MYSQL执行到程序的末尾。

如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;，这一行不存在

第二个INSERT因PRIMARY KEY约束而失败之后，MYSQL可能已经采取EXIT策略，并且SELECT @X可能已经返回2

注意：@X表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端所使用

即有作用域的，该客户端退出时，客户端连接的所有变量将自动释放

这里的变量跟SQLSERVER没有什么区别，都是用来存储临时值的

MYSQL这里的条件和预定义程序其实跟SQLSERVER的自定义错误是一样的

---

光标

MYSQL里叫光标，SQLSERVER里叫游标，实际上一样的

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1．声明光标

MySQL中使用DECLARE关键字来声明光标。其语法的基本形式如下：

<strong>DECLARE cursor_name CURSOR FOR select_statement ;</strong>

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

下面声明一个名为cur_employee的光标。代码如下：

<strong>DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;</strong>

上面的示例中，光标的名称为cur_employee；SELECT语句部分是从employee表中查询出name和age字段的值。

2．打开光标

MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下：

<strong>OPEN  cursor_name ;</strong>

其中，cursor_name参数表示光标的名称。

下面打开一个名为cur_employee的光标，代码如下：

<strong>OPEN  cur_employee ;</strong>

3．使用光标

MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下：

<strong>FETCH cur_employee INTO var_name[,var_name…] ;</strong>

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

下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下：

<strong>FETCH  cur_employee INTO emp_name, emp_age ;</strong>

上面的示例中，将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。

4．关闭光标

MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下：

<strong>CLOSE  cursor_name ;</strong>

其中，cursor_name参数表示光标的名称。

【示例14-11】 下面关闭一个名为cur_employee的光标。代码如下：

<strong>CLOSE  cur_employee ;</strong>

上面的示例中，关闭了这个名称为cur_employee的光标。关闭之后就不能使用FETCH来使用光标了。

注意：MYSQL中，光标只能在存储过程和函数中使用！！

到目前为止存储函数，存储过程、变量、条件、预定义程序、光标跟SQLSERVER差不多，只不过语法不同，结构不同

刚开始的时候会有不适应

---

流程控制的使用

存储过程和函数中可以使用流程控制来控制语句的执行。

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合语句，构造可以被嵌套

1．IF语句

IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

<strong>IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF</strong>

其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

注意：MYSQL还有一个IF()函数，他不同于这里描述的IF语句

下面是一个IF语句的示例。代码如下：

<strong>IF age>20 THEN SET @count1=@count1+1;
ELSEIF age=20 THEN SET @count2=@count2+1;
ELSE SET @count3=@count3+1;
END IF;</strong>

该示例根据age与20的大小关系来执行不同的SET语句。

如果age值大于20，那么将count1的值加1；如果age值等于20，那么将count2的值加1；

其他情况将count3的值加1。IF语句都需要使用END IF来结束。

2．CASE语句

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下：

<strong>CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE</strong>

其中，case_value参数表示条件判断的变量；

when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句。

CASE语句还有另一种形式。该形式的语法如下：

<strong>CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE</strong>

其中，search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句。

下面是一个CASE语句的示例。代码如下：

<strong>CASE age
WHEN 20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE ;</strong>

代码也可以是下面的形式：

<strong>CASE
WHEN age=20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE ;</strong>

本示例中，如果age值为20，count1的值加1；否则count2的值加1。CASE语句都要使用END CASE结束。

注意：这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句

并且用END CASE替代END来终止！！

3．LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下：

<strong>[begin_label:] LOOP
statement_list
END LOOP [end_label]</strong>

其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；

statement_list参数表示需要循环执行的语句。

下面是一个LOOP语句的示例。代码如下：

<strong>add_num: LOOP
SET @count=@count+1;
END LOOP add_num ;</strong>

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。

LOOP循环都以END LOOP结束。

4．LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下：

<strong>LEAVE label</strong>

其中，label参数表示循环的标志。

下面是一个LEAVE语句的示例。代码如下：

<strong>add_num: LOOP
SET @count=@count+1;
IF @count=100 THEN
LEAVE add_num ;
END LOOP add_num ;</strong>

该示例循环执行count加1的操作。当count的值等于100时，则LEAVE语句跳出循环。

5．ITERATE语句

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下：

<strong>ITERATE label</strong>

其中，label参数表示循环的标志。

下面是一个ITERATE语句的示例。代码如下：

<strong>add_num: LOOP
SET @count=@count+1;
IF @count=100 THEN
LEAVE add_num ;
ELSE IF MOD(@count,3)=0 THEN
ITERATE add_num;
SELECT * FROM employee ;
END LOOP add_num ;</strong>

该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除3，则跳出本次循环，不再执行下面的SELECT语句。

说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

使用这两个语句时一定要区分清楚。

6．REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

<strong>[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]</strong>

其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

下面是一个ITERATE语句的示例。代码如下：

<strong>REPEAT
SET @count=@count+1;
UNTIL @count=100
END REPEAT ;</strong>

该示例循环执行count加1的操作，count值为100时结束循环。

REPEAT循环都用END REPEAT结束。

7．WHILE语句

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时，执行循环内的语句。

WHILE语句的基本语法形式如下：

<strong>[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]</strong>

其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；

statement_list参数表示循环的执行语句。

下面是一个ITERATE语句的示例。代码如下：

<strong>WHILE @count<100 DO
SET @count=@count+1;
END WHILE ;</strong>

该示例循环执行count加1的操作，count值小于100时执行循环。

如果count值等于100了，则跳出循环。WHILE循环需要使用END WHILE来结束。

---

调用存储过程和函数

存储过程和存储函数都是存储在服务器端的SQL语句的集合，要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现

存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的

执行存储过程和存储函数需要拥有EXECUTE权限

EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中

调用存储过程

MySQL中使用CALL语句来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的语句。

然后，将结果返回给输出值。

CALL语句的基本语法形式如下：

<strong>CALL sp_name([parameter[,…]]) ;</strong>

其中，sp_name是存储过程的名称；parameter是指存储过程的参数。

<strong>CALL proc()</strong>

调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。

换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。

区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

下面定义一个存储函数，然后调用这个存储函数。

代码执行如下：

<strong>-- 创建存储函数
DELIMITER //
CREATE FUNCTION name_from_t3(id INT )
RETURNS CHAR(80)
RETURN (SELECT  NAME  FROM  t3  WHERE  id=id );
//
DELIMITER ;


SELECT name_from_t3(2);</strong>

上述存储函数的作用是根据输入的id值到t3表中查询记录。

查询出id字段的值等于id的记录。然后将该记录的name字段的值返回。

---

查看存储过程和函数

存储过程和函数创建以后，可以查看存储过程和函数的状态和定义。

通过SHOW STATUS语句来查看存储过程和函数的状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。

通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息

1、SHOW STATUS语句查看存储过程和函数的状态

MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法形式如下：

<strong>SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE  &#39; pattern &#39; ] ;</strong>

其中，PROCEDURE参数表示查询存储过程；FUNCTION参数表示查询存储函数；

LIKE ' pattern '参数用来匹配存储过程或函数的名称。

下面查询名为name_from_t3的函数的状态。代码执行如下：

<strong>SHOW  FUNCTION STATUS LIKE &#39;%name_from_t3%&#39;

Db
Name
Type
Definer
Modified
Created
Security_type
Comment
character_set_client
collation_connection
Database Collation
------
------------
--------
--------------
-------------------
-------------------
-------------
-------
--------------------
--------------------
------------------
school
name_from_t3
FUNCTION
root@localhost
2014-06-21 18:52:39
2014-06-21 18:52:39
DEFINER
utf8
utf8_general_ci
utf8_general_ci</strong>

查询结果显示了函数的创建时间、修改时间和字符集等信息。

注意：SHOW STATUS语句只能查看存储过程或函数是操作哪一个数据库、存储过程或函数的名称、类型、谁定义的、创建和修改时间、字符编码等信息。

但是，这个语句不能查询存储过程或函数的具体定义。如果需要查看详细定义，需要使用SHOW CREATE语句

2、SHOW CREATE语句查看存储过程和函数的定义

MySQL中可以通过SHOW CREATE语句查看存储过程和函数的状态。其基本语法形式如下：

<strong>SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;</strong>

其中，PROCEDURE参数表示查询存储过程；

FUNCTION参数表示查询存储函数；

sp_name参数表示存储过程或函数的名称

下面查询名为name_from_t3的函数的定义。代码执行如下

<strong>SHOW CREATE FUNCTION  name_from_t3

Function
sql_mode
Create
Function                                                                                                                                  character_set_client  collation_connection  Database Collation
------------  --------  -----------------------------------------------------------------------------------------------------------------------------------------------  --------------------  --------------------  ------------------
name_from_t3
CREATE DEFINER=`root`@`localhost` FUNCTION `name_from_t3`(id INT ) RETURNS char(80) CHARSET utf8                                                 utf8                  utf8_general_ci       utf8_general_ci
                        RETURN (SELECT  NAME  FROM  t3  WHERE  id=id )</strong>

3、从information_schema.Routines表中查看存储过程和函数的信息

存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。

其基本语法形式如下：

<strong>SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=&#39; sp_name &#39; ;</strong>

其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；

sp_name参数表示存储过程或函数的名称。

下面从Routines表中查询名为name_from_t3函数的信息。

代码执行如下：

<strong>SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=&#39;name_from_t3&#39;

SPECIFIC_NAME
ROUTINE_CATALOG
ROUTINE_SCHEMA
ROUTINE_NAME
ROUTINE_TYPE
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
CHARACTER_SET_NAME
COLLATION_NAME
DTD_IDENTIFIER
ROUTINE_BODY
ROUTINE_DEFINITION
EXTERNAL_NAME
EXTERNAL_LANGUAGE
PARAMETER_STYLE
IS_DETERMINISTIC
SQL_DATA_ACCESS  SQL_PATH  SECURITY_TYPE  CREATED
LAST_ALTERED         SQL_MODE  ROUTINE_COMMENT  DEFINER
CHARACTER_SET_CLIENT  COLLATION_CONNECTION  DATABASE_COLLATION
-------------  ---------------  --------------  ------------
------------  ---------  ------------------------  ----------------------  -----------------  -------------
 ------------------  ---------------  --------------  ------------  ----------------------------------------------  -------------  -----------------  ---------------  ----------------  ---------------  --------  -------------  -------------------  -------------------  --------  ---------------  --------------  --------------------  --------------------  ------------------
name_from_t3   def
school
name_from_t3  FUNCTION
char
80
240
(NULL)
(NULL)  utf8
utf8_general_ci  char(80)        SQL
RETURN (SELECT  NAME  FROM  t3  WHERE  id=id )  (NULL)
(NULL)
SQL
NO
CONTAINS SQL
(NULL)
DEFINER
2014-06-21 18:52:39
2014-06-21 18:52:39
root@localhost  utf8
utf8_general_ci
utf8_general_ci</strong>

查询结果显示name_from_t3的详细信息。

注意：在information_schema数据库下的Routines表中，存储着所有存储过程和函数的定义。

如果使用SELECT语句查询Routines表中的存储过程和函数的定义时，一定要使用ROUTINE_NAME字段指定存储过程或函数的名称。

否则，将查询出所有的存储过程或函数的定义。

---

修改存储过程和函数

修改存储过程和函数是指修改已经定义好的存储过程和函数。

MySQL中通过ALTER PROCEDURE语句来修改存储过程。

通过ALTER FUNCTION语句来修改存储函数。

MySQL中修改存储过程和函数的语句的语法形式如下：

<strong>ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT &#39;string&#39;</strong>

其中，sp_name参数表示存储过程或函数的名称；

characteristic参数指定存储函数的特性。

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序中不包含SQL语句；

READS SQL DATA表示子程序中包含读数据的语句；

MODIFIES SQL DATA表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行。

DEFINER表示只有定义者自己才能够执行；

INVOKER表示调用者可以执行。

COMMENT 'string'是注释信息。

说明：修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。

但是，这两个语句的结构是一样的，语句中的所有参赛都是一样的。

而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

修改存储过程和函数，只能修改他们的权限，目前MYSQL还不提供对已存在的存储过程和函数代码的修改

如果要修改，只能通过先DROP掉，然后重新建立新的存储过程和函数来实现

在SQLYOG里选中选中函数，然后右键ALTER FUNCTION的时候，也是这样，先DROP掉，然后重新建立新的函数来实现

<strong>DELIMITER $$
DROP FUNCTION IF EXISTS `name_from_t3`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `name_from_t3`(id INT ) RETURNS CHAR(80) CHARSET utf8
RETURN (SELECT  NAME  FROM  t3  WHERE  id=id )$$

DELIMITER ;</strong>

---

删除存储过程和函数

删除存储过程和函数指删除数据库中已经存在的存储过程和函数。

MySQL中使用DROP PROCEDURE语句来删除存储过程。通过DROP FUNCTION语句来删除存储函数。

<strong>DROP { PROCEDURE| FUNCTION } sp_name;</strong>

其中，sp_name参数表示存储过程或函数的名称

下面删除存储过程Proc和存储函数name_from_t3。删除存储过程的代码如下：

<strong>DROP  PROCEDURE  Proc;</strong>

删除存储函数name_from_employee的代码如下：

<strong>DROP  FUNCTION  name_from_t3;</strong>

删除完毕之后，查询information_schema.Routines来确认上面的删除是否成功

<strong>SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=&#39;proc&#39; OR ROUTINE_NAME=&#39;name_from_t3&#39;;</strong>

---

总结

TIPS:

存储过程里面是可以调用其他存储过程的，使用CALL语句调用其他存储过程就可以了

存储过程参数列表里的参数名尽量不要和数据库中表的字段名一样，否则有可能出错

存储过程的参数可以使用中文，在定义存储过程的时候加上character set gbk就可以了

例如

<strong>CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) CHARACTER SET gbk,OUT u_age INT)</strong>

补充

DECLARE 语句要写在存储过程里面，否则会报错！！

<strong>DROP PROCEDURE IF EXISTS Proc;

DELIMITER //
CREATE PROCEDURE Proc()
BEGIN
DECLARE command_not_allowed CONDITION FOR SQLSTATE &#39;42000&#39; ;
END//
DELIMITER ;

CALL Proc();</strong>

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!