2023学年完整公开课版MySQL存储过程

数据库技术与应用MySQL存储过程创建采集任务01存储过程01目录CONTENTS存储过程01使用存储过程的优点有：（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。（3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。存储过程创建存储过程可以使用CREATEPROCEDURE语句语法格式：CREATEPROCEDURE存储过程名([参数[,…]])存储过程体参数：存储过程的参数，格式如下：

[IN|OUT|INOUT]参数名类型

存储过程当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。

关键字分别是IN、OUT和INOUT分别为输入参数、输出参数和输入/输出参数，输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候，存储过程使用输出参数。输入/输出参数既可以充当输入参数，也可以充当输出参数。

存储过程也可以不加参数，但是名称后面的括号是不可省略的。

参数的名字不要等于列的名字，否则虽然不会返回出错消息。存储过程在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。存储过程DELIMITER语法格式为：DELIMITER$$说明：$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，两个“￥”等。【例】将MySQL结束符修改为两个斜杠“/”符号。

DELIMITER//要想恢复使用分号“;”作为结束符，运行下面命令即可：DELIMITER;存储过程【例】编写一个存储过程，实现的功能是删除一个指定用户姓名的用户信息。DELIMITER$$CREATEPROCEDUREdel_member(INxmCHAR(8))BEGIN DELETEFROMMembersWHERE姓名=xm;END$$DELIMITER;

存储过程在关键字BEGIN和END之间指定了存储过程体，因为在程序开始用DELIMITER语句转换了语句结束标志为“$$”，所以BEGIN和END被看成是一个整体，在END后用“$$”结束。当然，BEGIN-END复合语句还可以嵌套使用。要想查看数据库中有哪些存储过程，可以使用SHOWPROCEDURE命令。SHOWPROCEDURESTATUS要查看某个存储过程的具体信息，可使用SHOWCREATEPROCEDUREsp_name命令，其中sp_name是存储过程的名称。SHOWCREATEPROCEDUREDELETE_STUDENT存储过程局部变量在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。语法格式：DECLARE变量[,…]类型[DEFAULT值]说明：DEFAULT子句给变量指定一个默认值，如果不指定，默认为NULL。存储过程【例】声明一个整型变量和两个字符变量。DECLAREnumINT(4);DECLAREstr1,str2VARCHAR(6);说明：局部变量只能在BEGIN…END语句块中声明。局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGIN…END语句块中使用该变量，其他语句块中不可以使用它。存储过程使用SET语句赋值要给局部变量赋值可以使用SET语句。语法格式为：

SETvar_name=expr【例】在存储过程中给局部变量赋值。

SETnum=1,str1='hello';存储过程SELECT...INTO语句使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。语法格式为：

SELECT列名[,…]INTO变量名[,…]数据来源表达式【例】在存储过程体中将Book表中的书名为“计算机基础”的作者姓名和出版社的值分别赋给变量name和publish。

SELECT作者,出版社INTOname,publish FROMBook WHERE书名='计算机基础';存储过程存储过程创建完后，可以在程序、触发器或者存储过程中被调用，调用时都必须使用到CALL语句，

其语法格式：

CALL存储过程名([参数[,…]])说明：存储过程名为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。参数为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。

如调用del_member存储过程，删除“张三”的的用户信息。

CALLdel_member('张三');存储过程【例】创建存储过程实现查询Members表中会员人数的功能,并执行它。首先创建查询Members表中会员人数的存储过程：

CREATEPROCEDUREquery_members() SELECTCOUNT(*)FROMMembers;这是一个不带参数的非常简单的存储过程，通常SELECT语句不会被直接用在存储过程中。调用该存储过程：CALLquery_members();说明：通常SELECT语句不会被直接用在存储过程中。存储过程存储过程创建后需要删除时使用DROPPROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

语法格式为：

DROPPROCEDURE[IFEXISTS]存储过程名说明：存储过程名是要删除的存储过程的名称。IFEXISTS子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。【例】删除存储过程DO_QUERY()。

DROPPROCEDUREIFEXISTSDO_QUERY();存储过程存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到CALL语句。语法格式：CALL存储过程名([参数[,…]])参数为调用该存储过程使用的参数，这条语句中的参数个数必须总是等