MySQL数据库存储过程入门教程

作者：袖梨 2022-11-14

存储过程介绍

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

存储过程的优点

作为存储过程，有以下这些优点：

　　(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

　　(2)执行速度更快。存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接中读取。

　　(3)更强的安全性。存储过程是通过向用户授予权限(而不是基于表)，它们可以提供对特定数据的访问，提高代码安全，比如防止 SQL注入。

　　(4) 业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高

　　当然存储过程也有一些缺点，比如：

　　1 可移植性方面：当从一种数据库迁移到另外一种数据库时，不少的存储过程的编写要进行部分修改。

　　2 存储过程需要花费一定的学习时间去学习,比如学习其语法等。

存储过程学习笔记

Variables 变量

　　在复合语句中声明变量的指令是DECLARE。

　　(1) Example with two DECLARE statements

两个DECLARE语句的例子

代码如下 复制代码

CREATE PROCEDURE p8 ()

BEGIN

DECLARE a INT;

DECLARE b INT;

SET a = 5;

SET b = 5;

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; // /* I won't CALL this */

　　在过程中定义的变量并不是真正的定义，你只是在BEGIN/END块内定义了而已（译注：也就是形参）。

　　注意这些变量和会话变量不一样，不能使用修饰符@你必须清楚的在BEGIN/END块中声明变量和它们的类型。

　　变量一旦声明，你就能在任何能使用会话变量、文字、列名的地方使用。

　　(2) Example with no DEFAULT clause and SET statement

　　没有默认子句和设定语句的例子

代码如下 复制代码

CREATE PROCEDURE p9 ()

BEGIN

DECLARE a INT /* there is no DEFAULT clause */;

DECLARE b INT /* there is no DEFAULT clause */;

SET a = 5; /* there is a SET statement */

SET b = 5; /* there is a SET statement */

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; // /* I won't CALL this */

　　有很多初始化变量的方法。如果没有默认的子句，那么变量的初始值为NULL。你可以在任何时候使用SET语句给变量赋值。

　　(3) Example with DEFAULT clause

　　含有DEFAULT子句的例子

代码如下 复制代码

CREATE PROCEDURE p10 ()

BEGIN

DECLARE a, b INT DEFAULT 5;

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; //


　　我们在这里做了一些改变，但是结果还是一样的。在这里使用了DEFAULT子句来设定初始值，这就不需要把DECLARE和SET语句的实现分开了。

　　(4) Example of CALL

　　调用的例子

代码如下 复制代码

mysql> CALL p10() //

+--------+

| s1 * a |

+--------+

| 25 |

| 25 |

+--------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

　　

结果显示了过程能正常工作

　　(5) Scope

　　作用域

代码如下 复制代码

CREATE PROCEDURE p11 ()

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'outer';

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'inner';

SELECT x1;

END;

SELECT x1;

END; //


　　

现在我们来讨论一下作用域的问题。例子中有嵌套的BEGIN/END块，当然这是合法的。同时包含两个变量，名字都是x1，这样也是合法的。内部的变量在其作用域内享有更高的优先权。当执行到END语句时，内部变量消失，此时已经在其作用域外，变量不再可见了，因此在存储过程外再也不能找到这个声明了的变量，但是你可以通过OUT参数或者将其值指派 给会话变量来保存其值。

　　调用作用域例子的过程：

代码如下 复制代码

mysql> CALL p11()//

+-------+

| x1 |

+-------+

| inner |

+-------+

+-------+

| x1 |

+-------+

| outer |

+-------+

　　

我们看到的结果时第一个SELECT语句检索到最内层的变量，第二个检索到第二层的变量

Conditions and IF-THEN-ELSE 条件式和IF-THEN-ELSE

　　1.

　　现在我们可以写一些包含条件式的例子：

代码如下 复制代码

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

　　

这里是一个包含IF语句的过程。里面有两个IF语句，一个是IF语句END IF，另一个是IF语句ELSE语句END IF。我们可以在这里使用复杂的过程，但我会尽量使其简单让你能更容易弄清楚。

　　2.

CALL p12 (0) //

　　我们调用这个过程，传入值为0，这样parameter1的值将为0。

　　3.

代码如下 复制代码

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1; <--

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

　　

这里变量variable1被赋值为parameter1加1的值，所以执行后变量variable1为1。

　　4.

代码如下 复制代码

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN <--

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //


　　因为变量variable1值为1，因此条件"if variable1 = 0"为假，

IF

……

END IF

　　被跳过，没有被执行。

　　5.

代码如下 复制代码

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN <--

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

　　

到第二个IF条件，判断结果为真，于是中间语句被执行了

　　6.

代码如下 复制代码

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1; <--

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //


　　因为参数parameter1值等于0，UPDATE语句被执行。如果parameter1值为NULL，则下一条UPDATE语句将被执行现在表t中有两行，他们都包含值5，所以如果我们调用p12，两行的值会变成6。

　　7.

代码如下 复制代码

mysql> CALL p12(0)//

Query OK, 2 rows affected (0.28 sec)

mysql> SELECT * FROM t//

+------+

| s1 |

+------+

| 6 |

| 6 |

+------+

2 rows in set (0.01 sec)

　　结果也是我们所期望的那样。

CASE 指令

　　1.

CREATE PROCEDURE p13 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

CASE variable1

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

　　

如果需要进行更多条件真假的判断我们可以使用CASE语句。CASE语句使用和IF一样简单。

　　我们可以参考上面的例子：

　　2.

代码如下 复制代码

mysql> CALL p13(1)//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t//

+------+

| s1 |

+------+

| 6 |

| 6 |

| 19 |

+------+

3 rows in set (0.00 sec)

　　

执行过程后，传入值1，如上面例子，值19被插入到表t中。

　　Question

　　问题

　　问题: CALL p13(NULL) //的作用是什么？

　　另一个：这个CALL语句做了那些动作？

　　你可以通过执行后观察SELECT做了什么，也可以根据代码判断，在5秒内做出。

　　Answer

　　答案

代码如下 复制代码

mysql> CALL p13(NULL)//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t//

+------+

| s1 |

+------+

| 6 |

| 6 |

| 19 |

| 19 |

+------+

4 rows in set (0.00 sec)

　　答案是当你调用p13时，MySQL插入了另一条包含数值19的记录。原因是变量variable1的值为NULL，CASE语句的ELSE部分就被执行了。希望这对大家有意义。如果你回答不出来，没有问题，我们可以继续向下走。

Loops 循环语句

代码如下 复制代码

WHILE ... END WHILE

LOOP ... END LOOP

REPEAT ... END REPEAT

GOTO

　　下面我们将会创建一些循环。我们有三种标准的循环方式：

　　WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GO TO（译者语：最好不要用吧，用了就使流程混乱）。

代码如下 复制代码

WHILE ... END WHILE

CREATE PROCEDURE p14 ()

BEGIN

DECLARE v INT;

SET v = 0;

WHILE v < 5 DO

INSERT INTO t VALUES (v);

SET v = v + 1;

END WHILE;

END; //

　　

这是WHILE循环的方式。我很喜欢这种方式，它跟IF语句相似，因此不需要掌握很多新的语法。这里的INSERT和SET语句在WHILE和END WHILE之间，当变量v大于5的时候循环将会退出。使用

"SET v = 0;"

　　语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。

代码如下 复制代码

WHILE ... END WHILE example

mysql> CALL p14()//

Query OK, 1 row affected (0.00 sec)


　　以上就是调用过程p14的结果不用关注系统返回是"one row affected"还是"five rows affected"，因为这里的计数只对最后一个INSERT动作进行计数。

代码如下 复制代码

WHILE ... END WHILE example: CALL

mysql> select * from t; //

+------+

| s1 |

+------+

....

| 0 |

| 1 |

| 2 |

| 3 |

| 4 |

+------+

9 rows in set (0.00 sec)

　　

调用后可以看到程序向数据库中插入了5行。

代码如下 复制代码

REPEAT ... END REPEAT

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT;

SET v = 0;

REPEAT

INSERT INTO t VALUES (v);

SET v = v + 1;

UNTIL v >= 5

END REPEAT;

END; //


　　这是一个REPEAT循环的例子，功能和前面WHILE循环一样。区别在于它在执行后检查结果，而WHILE则是执行前检查。（译者语：可能等同于DO WHILE吧）

代码如下 复制代码

REPEAT ... END REPEAT: look at the UNTIL： UNTIL的作用

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT;

SET v = 0;

REPEAT

INSERT INTO t VALUES (v);

SET v = v + 1;

UNTIL v >= 5 <--

END REPEAT;

END; //

　　

注意到UNTIL语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。

　　REPEAT ... END REPEAT: calling ：调用

代码如下 复制代码

mysql> CALL p15()//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t//

+----------+

| COUNT(*) |

+----------+

| 14 |

+----------+

1 row in set (0.00 sec)

　　

我们可以看到调用p15过程后又插入了5行记录

代码如下 复制代码

LOOP ... END LOOP

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

　　

以上是LOOP循环的例子。

　　LOOP循环不需要初始条件，这点和WHILE循环相似，同时它又和REPEAT循环一样也不需要结束条件。

代码如下 复制代码


　　LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循环

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN <--

LEAVE loop_label;

END IF;

END LOOP;

END; //

　　在循环内部加入IF语句，在IF语句中包含LEAVE语句。这里LEAVE语句的意义是离开循环。

　　LEAVE的语法是LEAVE加循环语句标号，关于循环语句的标号问题我会在后面进一步讲解。
　　LOOP ... END LOOP: calling ：调用

代码如下 复制代码

mysql> CALL p16()//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t//

+----------+

| COUNT(*) |

+----------+

| 19 |

+----------+

1 row in set (0.00 sec)

　　

调用过程p16后，结果是另5行被插入表t中。

Labels 标号

代码如下 复制代码


CREATE PROCEDURE p17 ()

label_1: BEGIN

label_2: WHILE 0 = 1 DO LEAVE label_2; END

WHILE;

label_3: REPEAT LEAVE label_3; UNTIL 0 =0

END REPEAT;

label_4: LOOP LEAVE label_4; END LOOP;

END; //

　　最后一个循环例子中我使用了语句标号。现在这里有一个包含4个语句标号的过程的例子。我们可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号，语句标号只能在合法的语句前面使用。因此"LEAVE label_3"意味着离开语句标号名定义为label_3的语句或复合语句。

End Labels 标号结束符

代码如下 复制代码

CREATE PROCEDURE p18 ()

label_1: BEGIN

label_2: WHILE 0 = 1 DO LEAVE label_2; END

WHILE label_2;

label_3: REPEAT LEAVE label_3; UNTIL 0 =0

END REPEAT label_3 ;

label_4: LOOP LEAVE label_4; END LOOP

label_4 ;

END label_1 ; //

　　你也可以在语句结束时使用语句标号，和在开头时使用一样。这些标号结束符并不是十分有用。

　　它们是可选的。如果你需要，他们必须和开始定义的标号名字一样当然为了有良好的编程习惯，方便他人阅读，最好还是使用标号结束符。

LEAVE and Labels 跳出和标号

代码如下 复制代码


CREATE PROCEDURE p19 (parameter1 CHAR)

label_1: BEGIN

label_2: BEGIN

label_3: BEGIN

IF parameter1 IS NOT NULL THEN

IF parameter1 = 'a' THEN

LEAVE label_1;

ELSE BEGIN

IF parameter1 = 'b' THEN

LEAVE label_2;

ELSE

LEAVE label_3;

END IF;

END;

END IF;

END IF;

END;

END;

END;//

LEAVE

　　

语句使程序跳出复杂的复合语句。

ITERATE

　　迭代如果目标是ITERATE（迭代）语句的话，就必须用到LEAVE语句

代码如下 复制代码

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //


ITERATE

　　

（迭代）语句和LEAVE语句一样也是在循环内部的循环引用，它有点像C语言中的“Continue”，同样它可以出现在复合语句中，引用复合语句标号，ITERATE（迭代）意思是重新开始复合语句。

　　那我们启动并观察下面这个循环，这是个需要迭代过程的循环：

ITERATE: Walking through the loop


深入循环

代码如下 复制代码

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP <--

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

　　让这个已经定义了标号的循环运行起来。

ITERATE: Walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN <--

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //


　　v的值变成3，然后我们把它增加到4。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label; <--

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

　　然后开始ITERATE（迭代）过程。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP <--

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //


　　

这里的ITERATE（迭代）让循环又回到了循环的头部。

代码如下 复制代码

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label; <--

END IF;

END LOOP;

END; //

　　

当v的值变为5时，程序将执行LEAVE语句

代码如下 复制代码

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; // <--

　　

LEAVE的结果就是跳出循环，使运行指令到达复合语句的最后一步。

代码如下 复制代码

GOTO

CREATE PROCEDURE p...

BEGIN

...

LABEL label_name;

...

GOTO label_name;

...

END;

　　MySQL的存储过程中可以使用GOTO语句。虽然这不是标准SQL语句，而且在这里建立标号的方法也和惯例中的不一样。由于为了和其他DBMS兼容，这个语句会慢被淘汰，所以我们在MySQL参考手册中没有提及。

Grand combination

大组合

代码如下 复制代码

CREATE PROCEDURE p21

(IN parameter_1 INT, OUT parameter_2 INT)

LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER

BEGIN

DECLARE v INT;

label goto_label; start_label: LOOP

IF v = v THEN LEAVE start_label;

ELSE ITERATE start_label;

END IF;

END LOOP start_label;

REPEAT

WHILE 1 = 0 DO BEGIN END;

END WHILE;

UNTIL v = v END REPEAT;

GOTO goto_label;

END;//

　　

上面例子中的语句包含了我们之前讲的所有语法，包括参数列表，特性参数，BEGIN/END块复合语句，变量声明，IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。这是一个荒谬的存储过程，我不会运行它，因为里面有无尽的循环。但是里面的语法却十分合法。这些是新的流程控制和变量声明语句。下面我们将要接触更多新的东西。

相关文章

精彩推荐