MySQL-存储过程

什么是存储过程

---

我们前面所学习的MySQL语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，并非所有操作都那么简单，经常会有一个完整的操作需要多条SQL语句处理多个表才能完成。例如，为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表，此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。

存储过程是数据库存储的一个重要的功能，但是 MySQL 在 5.0 以前并不支持存储过程，这使得 MySQL 在应用上大打折扣。好在 MySQL 5.0 终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程通常有如下优点：

1. 封装性 存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

2. 可增强 SQL 语句的功能和灵活性 存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

3. 可减少网络流量 由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

4. 高性能 存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

5. 提高数据库的安全性和数据的完整性 使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

创建存储过程

---

MySQL 存储过程是一些 SQL 语句的集合，比如有的时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL语句的过程中还需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。下面我们来介绍一下如何创建一个存储过程。 基本语法 可以使用 CREATE PROCEDURE 语句创建存储过程。

语法格式如下：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体> [过程参数[,…] ] 格式 [ IN | OUT | INOUT ] <参数名> <类型>

语法说明如下：

1. 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2. 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

3. 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGINEND 标志。在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。为解决这个问题，通常可使用 DELIMITER 命令将结束命令修改为其他字符。

语法格式如下：

DELIMITER $$

语法说明如下： $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下SQL语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程，若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE <存储过程名>。

创建不带参数的存储过程

【实例 1】创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息，输入的 SQL 语句和执行过程如下所示。

mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()-> BEGIN-> SELECT * FROM tb_students_score;-> END //
Query OK， 0 rows affected (0.09 sec)

创建存储过程 ShowStuScore 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

mysql> DELIMITER ;
mysql> CALL ShowStuScore();
+--------------+---------------+
| student_name | student_score |
+--------------+---------------+
| Dany | 90 |
| Green | 99 |
| Henry | 95 |
| Jane | 98 |
| Jim | 88 |
| John | 94 |
| Lily | 100 |
| Susan | 96 |
| Thomas | 93 |
| Tom | 89 |
+--------------+---------------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

创建带参数的存储过程

【实例 2】创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息，输入的 SQL 语句和执行过程如下所示。

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetScoreByStu-> (IN name VARCHAR(30))-> BEGIN-> SELECT student_score FROM tb_students_score-> WHERE student_name=name;-> END //
Query OK, 0 rows affected (0.01 sec)

创建存储过程 GetScoreByStu 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

mysql> DELIMITER ;
mysql> CALL GetScoreByStu('Green');
+---------------+
| student_score |
+---------------+
| 99 |
+---------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)

修改存储过程

---

在实际开发过程中，业务需求修改的情况时有发生，这样，不可避免的需要修改 MySQL 中存储过程的特征 。基本语法 可以使用 ALTER PROCEDURE 语句修改存储过程的某些特征。

语法格式如下：

ALTER PROCEDURE <过程名> [ <特征> … ]

提示：这个语法用于修改存储过程的某些特征，如要修改存储过程的内容，可以先删除该存储过程，再重新创建。

修改存储过程的内容和名称 修改存储过程的内容可以通过删除原存储过程，再以相同的命名创建新的存储过

程。

修改存储过程的名称可以通过删除原存储过程，再以不同的命名创建新的存储过程。

删除存储过程

---

当 MySQL 数据库中存在废弃的存储过程是，我们需要将它从数据库中删除。 基本语法 存储过程被创建后，保存在数据库服务器上，直至被删除。可以使用 DROP PROCEDURE 语句删除数据库中已创建的存储过程。

语法格式如下：

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

语法说明如下：

1. 过程名 指定要删除的存储过程的名称。

2. IF EXISTS 指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。 注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

删除存储过程 删除存储过程 GetScoreByStu，查看存储过程的运行结果如下所示。

mysql> DROP PROCEDURE GetScoreByStu;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL GetScoreByStu('Green');
ERROR 1305 (42000): PROCEDURE test_db.GetScoreByStu does not exist

判断存储过程

---

IF

语法如下：

CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementIF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list] ...[ELSE statement_list]
END IF

创建一个student表并插入三条数据

mysql> create table student(-> id int,-> name varchar(30),-> score float);
Query OK, 0 rows affected (0.01 sec)mysql> insert into student values(1,'z3',30),(2,'l4',60),(3,'w5',90);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

创建存储过程，输入“a”返回大于等于60分数的学生信息，输入“b”返回大于等于80分数的学生信息，输入“c”返回大于等于90分数的学生信息，输入其他字符串则返回全部学生信息。

mysql> delimiter $$
mysql> create procedure t1(in chars varchar(10))-> begin-> if chars="a" then-> select * from student where score>=60;-> elseif chars="b" then-> select * from student where score>=80;-> elseif chars="c" then-> select * from student where score>=90;-> else select * from student;-> end if;-> end $$
Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;
mysql> call t1("a");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    2 | l4   |    60 |
|    3 | w5   |    90 |
+------+------+-------+
2 rows in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)mysql> call t1("b");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    3 | w5   |    90 |
+------+------+-------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call t1("c");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    3 | w5   |    90 |
+------+------+-------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call t1("abc");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | z3   |    30 |
|    2 | l4   |    60 |
|    3 | w5   |    90 |
+------+------+-------+
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

CASE

语法如下：

CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementCASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]
END CASE

同样，上面使用的if语法创建存储过程，这次使用case语法创建。输入“a”返回大于等于60分数的学生信息，输入“b”返回大于等于80分数的学生信息，输入“c”返回大于等于90分数的学生信息，输入其他字符串则返回全部学生信息。

mysql> create procedure t2(in chars varchar(10))-> begin-> case chars-> when "a" then select * from student where score>=60;-> when "b" then select * from student where score>=80;-> when "c" then select * from student where score>=90;-> else select * from student;-> end case;-> end$$
Query OK, 0 rows affected (0.01 sec)mysql> delimiter ;
mysql> call t2("a");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    2 | l4   |    60 |
|    3 | w5   |    90 |
+------+------+-------+
2 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call t2("b");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    3 | w5   |    90 |
+------+------+-------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call t2("c");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    3 | w5   |    90 |
+------+------+-------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call t2("d");
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | z3   |    30 |
|    2 | l4   |    60 |
|    3 | w5   |    90 |
+------+------+-------+
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

循环存储过程

---

WHILE

语法如下：

CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementName: 'DECLARE VARIABLE'
Description:
Syntax:
DECLARE var_name [, var_name] ... type [DEFAULT value]Syntax:
WHILE search_condition DOstatement_list
END WHILE

创建循环存储过程，实现1+2+3+4...100（我算过了和为5050）while先判断后执行（条件满足情况下，则执行循环体内容，不满足，则结束循环），详情看下面命令吧。

mysql> create procedure t3()-> begin-> declare num int default 0;            #定义num默认值为0-> declare summ int default 0;            #定义summ默认值为0-> while num<=100 do                    #num小于等于100后停止-> set summ=summ+num;                    #第一次循环为0，第二次为num上次循环的结果-> set num=num+1;                        #num+1继续循环-> end while;                            #跳过满足循环后跳出-> select summ;                            #打印出summ循环相加的结果-> end $$                                #结束
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call t3();
+------+
| summ |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

上面的命令虽然实现了循环，但是局限性很大只能打印出加到100，也就是说每次打印的都是5050，如果这次我们需要的更多呢？下面可以使用参数代替。

mysql> delimiter $$
mysql> create procedure t4(in a int)-> begin-> declare num int default 0;-> declare summ int default 0;-> while num<=a do-> set summ=summ+num;-> set num=num+1;-> end while;-> select summ;-> end $$
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call t4(10);
+------+
| summ |
+------+
|   55 |
+------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)mysql> call t4(1000);
+--------+
| summ   |
+--------+
| 500500 |
+--------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)

REPEAT

语法如下：

CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementSyntax:
REPEATstatement_list
UNTIL search_condition
END REPEAT

下面的循环也是根据1+2+3...+100，但是和while不同的是repeat的结构是先执行后判断（先执行相关操作 ，再判断是否满足条件，满足，结束循环）。

mysql> delimiter $$
mysql> create procedure t5(in a int)-> begin-> declare num int default 0;-> declare summ int default 0;-> repeat-> set summ=summ+num;-> set num=num+1;-> until num>a end repeat;-> select summ;-> end $$
Query OK, 0 rows affected (0.05 sec)mysql> delimiter ;
mysql> call t5(10);
+------+
| summ |
+------+
|   55 |
+------+
1 row in set (0.02 sec)Query OK, 0 rows affected (0.03 sec)mysql> call t5(100);
+------+
| summ |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

学习不是一两天的事，活到老学到老，坚持下去总有一天会有成果。MySQL专栏持续更新，对你有帮助的话收藏起来吧！