存储过程_MySQL中的存储过程和函数

水深无声 2023-01-05 12:16 137阅读 0赞

存储过程和函数

*  简单的说，存储过程就是一条或者多条SQL语句的集合。可以视为批文件，但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数，以及如何调用、查看、修改、删除存储过程和存储函数等。

创建存储过程和函数

存储程序分为存储过程和存储函数。Mysql创建存储过程和存储函数的语句分别为CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。存储函数可以从语句外调用(通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。废话少说，如下步入正文。

*  创建存储过程

创建存储过程的基本语法格式为：CREATE PROCEDURE sp\_name(\[proc\_parameter\]) \[characteristics\] routine\_body

其中CREATE PROCEDURE为创建存储过程的关键字，sp\_name为存储过程的名称，pro\_parameter为指定存储过程的参数列表，其中参数列表如下：

1.  \[IN|OUT|INOUT\] param\_name type 其中，IN表述输入参数，OUT表示输出参数，INOUT表示即可输入也可输出；param\_name表述参数名称；type表示参数类型，该类型可以是MySQL数据库中的任意类型。
2.  characteristics指定存储过程的特性，有以下取值：

*  LANGUAGE SQL:说明routine\_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
 *  \[NOT\] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出，NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出，如果没有指定任意一个值，默认为NOT DETERMINISTIC。
 *  \[CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA\]：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但不包含读写数据语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表名子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
 *  SQL SECURITY\[DEFINER|INVOKER\]：指明谁有权限来执行。DEFINER表示只有定义着才能执行。INVOKER表示用友权限的调用者可以执行。默认情况下，系统指定为DEFINER。
 *  COMMENT 'string'：注释信息，用来描述存储过程或函数。

1.  routine\_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

我们从最简单的存储过程开始说起，如下是不包含任何参数的存储过程，代码为：CREATE PROCEDURE Proc() BEGIN SELECT \* FROM tb\_score; END; 我们定义了一个名称为Proc的存储过程，该过程是用来查询tb\_score(该表接上篇博客，已存在)数据库表中的所有数据。

![03ab8c8dfb3e1f618b19d767edef25b8.png][]

![b5ca3d430b31543bc62e25eb4b4b028a.png][]

![8273e5081d57c58a5ac5fdf5f0484966.png][]

第一张图是通过执行sql语句查询到tb\_score表中的数据。第二张图是创建存储过程，其中第一句DELIMITER //是将MySQL的结束符设置为//，因为MySQL默认的结束符为分号，为了避免与存储过程中SQL语句结束符相冲突，需要DELIMITER改变存储过程的结束符，并以“END //”结束存储过程。第三张图是调用存储过程，在调用存储过程之前先将MySQL结束符恢复为默认的分号(DELIMITER ;)然后通过CALL Proc()调用。

接下来我们讲解MySQL存储过程中的参数IN、OUT、INOUT，IN作为输入，将输入作为参数传输到存储过程的执行当中去；OUT作为输出，将存储过程的输出通过参数传出来，而INOUT参数可以同时作为输入和输出。

还是通过存储过程查询tb\_score表，不过这次我们要查询课程号为1(cID=1)的所有学生的成绩，存储过程定义为：CREATE PROCEDURE Proc\_cID(IN classID INT) BEGIN SELECT \* FROM tb\_score WHERE cID=classID; END;

![536b208661317289a5f641a717e9816c.png][]

![05bdcb832eb23c237a358896a8451965.png][]

如我们需要查询课程号为1的学生的人数和平均成绩，则存储过程定义如下：CREATE PROCEDURE Proc\_AVG(IN classID INT,OUT total INT,OUT a\_s FLOAT) BEGIN SELECT COUNT(\*),AVG(grade) INTO total,a\_s FROM tb\_score WHERE cID=classID; END;

![f9f66ae87b89c7147677a5404b2c520b.png][]

*  创建存储函数

创建存储函数需要使用CREATE FUNCTION语句，基本语法格式为：CREATE FUNCTION func\_name(\[func\_parameter\]) returns type \[characteristic\] routine\_body CREATE FUNCTION为用来创建存储函数的关键字，func\_name表示存储函数的名称，func\_parameter为存储过程的参数列表如下：

*  \[IN|OUT|INOUT\] param\_name type 其参数含义同存储过程(PROCEDURE)相同，其默认为IN参数。

RETURNS type语句表示函数返回数据的类型，characteristic指定存储函数的特性，取值与创建存储过程时相同。

查询某个学生某门课程的成绩函数代码为：CREATE FUNCTION Query\_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb\_score WHERE cID=classID AND sID=studentID); 通过SELECT Query\_score(1,1)查询1号学生1号课程的成绩。

![d27850a5432c17a212b92a6f694051ac.png][]

![3150003247465bee0a911c6861e660ff.png][]

读者可能会发现存储过程的查询结果可能为多个值，而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用CALL而存储函数是SELECT。那么存储过程和函数具体的区别又是什么呢？

1.  存储过程的功能更加复杂，而函数的功能针对性更强；
2.  存储过程可以返回参数(通过OUT|INOUT)，而函数只能返回单一值或者表对象；
3.  存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字之后；
4.  存储过程是通过关键字CALL来调用，作为一个独立的执行部分。而存储函数则可作为SELECT语句的一部分调用，嵌入到SQL语句中；
5.  当存储过程和函数被执行的时候，SQLManager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQLManager就会对存储过程和函数进行编译。

*  变量的使用

变量可以在子程序中声明并使用，作用范围是在BEGIN...END程序中，如下将主要介绍如何定义变量和为变量赋值。

1.  定义变量。语法格式为：DECLARE var\_name\[,var\_name\]...data\_type\[DEFAULT value\]; var\_name为局部变量名称，DEFAULT value给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果缺少DEFAULT子句，初始值为NULL。
2.  为变量赋值。MySQL中使用SET语句为变量赋值，语法格式为：SET var\_name=expr\[,var\_name=expr\]...

*  流程控制的使用

流程控制与用来根据条件控制语句的执行。MySQL中的用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。各语句介绍如下：

1.  IF语句。包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式为 IF expr\_condition THEN statement\_list ESLEIF expr\_condition THEN statement\_list ESLE statement\_list END IF 如下我们定义一个学生成绩等级评定函数，将学生成绩以参数的形式传输函数，输出学生成绩等级A(90~100)、B(75~90)、C(60~75)、D(60以下)。代码编写中需要注意，IF中如果有多个限制条件，限制条件间用AND连接，DECLARE的变量声明必须在BEGIN内，以及字符串之间拼接用CONCAT。该功能代码如下图所示：

![da110399afe262f7f565377d402b6f93.png][]

1.  
2.  CASE语句。另外一个进行条件判断的语句，该语句有2种语句格式。
3.  第一种格式为：CASE case\_expr WHEN value THEN statement\_list \[WHEN value THEN statement\_list\] \[ELSE statement\_list\] END CASE 其中case\_expr参数表示条件判断的表达式，决定哪个WHEN子句会被执行，value表示表达式可能的值，如果case\_expr等于某个value，则执行相应value后的statement\_list。
4.  第二种格式为：CASE WHEN expr\_condition THEN statement\_list \[WHEN expr\_condition THEN statement\_list\] \[ELSE statement\_list\] END CASE 其中expr\_condition参数表示条件判断语句，该格式下，WHEN语句将被逐个执行，直到某个expr\_condition表达式为真，则这行对应THEN关键字后面的statement\_list语句。如果没有匹配，ELSE子句里的语句被执行。
5.  LOOP语句。循环语句用来重复执行某些语句，与IF和CASE相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出。跳出循环过程使用LEAVE子句，LOOP语句基本格式为：\[label\] LOOP statement\_list END LOOP\[label\] label表示LOOP语句的标注名称，该参数可以省略，statement\_list表示需要执行的语句。

![fbfc9be9e43bc3e32fd527eb01e01139.png][]

1.  
2.  LEAVE语句。从LOOP语句的例子中可知LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本格式为：LEAVE label
3.  ITERATE语句。将执行顺序转到语句段开头处，语句基本格式为：ITERATE lable ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为再次循环，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。例子中p1=0，如果p1的值小于10时，重复执行p1加1操作；p1大于等于10并且小于20时，打印消息'p1 is between 10 and 20';p1大于20时，退出循环。

![32f9ac2a64b0914b3b39274a39400beb.png][]

1.  
2.  REPEAT语句。创建一个带条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，表达式为真循环结束，否则重复执行循环中的语句。语句基本格式：\[label\]:REPEAT statement\_list UNTIL expr\_condition END REPEAT \[label\]
3.  WHILE语句。WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句时先对指定的表达式进行判断，为真则执行循环内的语句，否则退出循环。语句基本格式：\[label\] WHILE expr\_condition DO statement\_list END WHILE \[label\]

![32c49406adfe03ad0c0ef72c119ea78f.png][]

查看存储过程和函数

MySQL中，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看存储过程和函数，也可以直接从系统的information\_schema数据库中查询。本节将通过实例来介绍这3种方法。

*  SHOW STATUS语句查看存储过程和函数的状态，其基本语法为：SHOW\{PROCEDURE|FUNCTION\} STATUS \[LIKE 'pattern'\] 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数，LIKE语句表示匹配存储过程或函数的名称。

![87457ebed3dc2003f5393ec3bf7f94e0.png][]

*  
 *  SHOW CREATE查看存储过程和函数语句格式为：SHOW CREATE \{PROCEDURE|FUNCTION\} sp\_name 它返回一个可以来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数，同样也可是使用LIKE匹配。

![e53b4e02d09b8c55584daa9b2c4efa6d.png][]

*  
 *  从information\_schema.Routines表中查看存储过程和函数的信息。MySQL中存储过程和函数的信息存储在information\_schema数据库的Routines表中。通过查询该表的记录查询存储过程和函数的信息。基本语法格式为：SELECT \* FROM information\_schema.Routines WHERE ROUTINE\_NAME='sp\_name' 其中ROUTINE\_NAME字段存储的是存储过程和函数的名称，sp\_name参数表述存储过程或函数的名称。

![aac5c70157d2bfd5f8147a5ce64e1a12.png][]

修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性，本节将介绍如何通过ALTER语句修改存储过程和函数。语法格式为：ALTER \{PROCEDURE|FUNCTION\} sp\_name \[characteristic ...\] 其中，sp\_name参数表示存储过程或函数的名称，characteristic参数指定存储函数的特性，可能的取值有：

1.  CONTAINS SQL表示子程序包含SQL语句，但是不包含读或写数据的语句；
2.  NO SQL表示子程序中不包含SQL语句；
3.  READES SQL DATA表示子程序中包含读数据的语句；
4.  MODIFIES SQL DATA表示子程序中包含写数据的语句；
5.  SQL SECURITY\{DEFINER|INVOKER\}指明谁有权限来执行；
6.  DEFINER表示只有定义着自己才能够执行；
7.  INVOKER表示调用者可以执行；
8.  COMMENT 'string'表示注释信息。

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

![45dfdf02a8a26a8264b2c669df79ad47.png][]

![34b45f8e57a04c9b65347c13cdc90364.png][]

删除存储过程

删除存储过程和函数可以使用DROP语句，语法格式为：DROP \{PROCEDURE|FUNCTION\} \[IF EXISTS\] sp\_name 这个语句被用来移除一个存储过程或函数，sp\_name为要移除的存储过程或函数的名称。IF EXISTS子句是一个MySQL的扩展，如果存储过程或函数不存在，以防发生错误，产生一个用SHOW WARNINGS查看的警告。

![44817edf5a2f62c83e9627fde2fa1e1f.png][]

最后再说几点值得大家注意的吧：

1.  目前MySQL不支持对已存在的存储过程代码进行修改，如果必须修改，则先使用DROP语句删除该存储过程，再重新创建新的存储过程；
2.  存储过程中包含用户定义的SQL语句集合，也可是使用CALL语句调用存储过程，但不能使用DROP删除其他存储过程；
3.  在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开，否则会报错。
4.  如果存储过程中需要传入中文参数，这时需要在定义存储过程的时候，在参数后加上character set gbk，不然调用存储过程使用中文参数会出错。如CREATE PROCEDURE userInfo(IN u\_name VARCHAR(50) character set gbk, OUT u\_age INT)。

[03ab8c8dfb3e1f618b19d767edef25b8.png]: /images/20221119/d2253b751cb0408e89d99814d708d339.png
[b5ca3d430b31543bc62e25eb4b4b028a.png]: https://img-blog.csdnimg.cn/img_convert/b5ca3d430b31543bc62e25eb4b4b028a.png
[8273e5081d57c58a5ac5fdf5f0484966.png]: https://img-blog.csdnimg.cn/img_convert/8273e5081d57c58a5ac5fdf5f0484966.png
[536b208661317289a5f641a717e9816c.png]: https://img-blog.csdnimg.cn/img_convert/536b208661317289a5f641a717e9816c.png
[05bdcb832eb23c237a358896a8451965.png]: https://img-blog.csdnimg.cn/img_convert/05bdcb832eb23c237a358896a8451965.png
[f9f66ae87b89c7147677a5404b2c520b.png]: https://img-blog.csdnimg.cn/img_convert/f9f66ae87b89c7147677a5404b2c520b.png
[d27850a5432c17a212b92a6f694051ac.png]: https://img-blog.csdnimg.cn/img_convert/d27850a5432c17a212b92a6f694051ac.png
[3150003247465bee0a911c6861e660ff.png]: https://img-blog.csdnimg.cn/img_convert/3150003247465bee0a911c6861e660ff.png
[da110399afe262f7f565377d402b6f93.png]: https://img-blog.csdnimg.cn/img_convert/da110399afe262f7f565377d402b6f93.png
[fbfc9be9e43bc3e32fd527eb01e01139.png]: https://img-blog.csdnimg.cn/img_convert/fbfc9be9e43bc3e32fd527eb01e01139.png
[32f9ac2a64b0914b3b39274a39400beb.png]: https://img-blog.csdnimg.cn/img_convert/32f9ac2a64b0914b3b39274a39400beb.png
[32c49406adfe03ad0c0ef72c119ea78f.png]: https://img-blog.csdnimg.cn/img_convert/32c49406adfe03ad0c0ef72c119ea78f.png
[87457ebed3dc2003f5393ec3bf7f94e0.png]: https://img-blog.csdnimg.cn/img_convert/87457ebed3dc2003f5393ec3bf7f94e0.png
[e53b4e02d09b8c55584daa9b2c4efa6d.png]: https://img-blog.csdnimg.cn/img_convert/e53b4e02d09b8c55584daa9b2c4efa6d.png
[aac5c70157d2bfd5f8147a5ce64e1a12.png]: https://img-blog.csdnimg.cn/img_convert/aac5c70157d2bfd5f8147a5ce64e1a12.png
[45dfdf02a8a26a8264b2c669df79ad47.png]: https://img-blog.csdnimg.cn/img_convert/45dfdf02a8a26a8264b2c669df79ad47.png
[34b45f8e57a04c9b65347c13cdc90364.png]: https://img-blog.csdnimg.cn/img_convert/34b45f8e57a04c9b65347c13cdc90364.png
[44817edf5a2f62c83e9627fde2fa1e1f.png]: https://img-blog.csdnimg.cn/img_convert/44817edf5a2f62c83e9627fde2fa1e1f.png