MySQL数据库技术与应用第10章 存储过程

第十章存储过程与

存储函数主要内容查看存储过程与存储函数创建存储过程与存储函数存储过程与存储函数概述

存储过程与存储函数的修改与删除第一节存储过程和存储函数概述什么是存储过程和存储函数？为什么要使用存储过程和存储函数？存储过程和存储函数的优缺点第一节存储过程和存储函数是什么

MySQL存储过程（StoredProcedure）和存储函数（StoredFunction）是在数据库中定义并保存的一段可执行的SQL代码块。

存储过程是一组预定义的SQL语句集合，它们根据需要可以被多次调用。存储过程可以接受参数，并且可以有输入参数、输出参数和返回结果。存储过程通常用于封装复杂的业务逻辑，提高数据库的性能和安全性。存储过程可以在应用程序中通过调用存储过程的名称来执行。

存储函数是一个具有返回值的SQL代码块。存储函数和存储过程类似，但存储函数必须返回一个值，并且可以在其他SQL语句中使用。存储函数通常用于对数据进行计算或处理，并将计算结果返回给调用者。存储函数可以接受参数，这些参数可以用于计算函数的返回值。第一节存储过程和存储函数是什么此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。存储过程和存储函数的区别：第一节为什么用存储过程和存储函数代码重用和模块化：将常用的业务逻辑封装起来,提高代码的可维护性和重用性。提高性能：在数据库服务器上预编译、缓存并执行，减少解析和编译时间，以及网络传输的数据量。安全性和权限控制：只能通过已定义的接口操作限制，用户对数据库的直接访问。数据库一致性和完整性：可以用于实现复杂的数据操作和业务规则，确保数据的一致性和完整性。简化客户端开发：可以将复杂的业务逻辑和数据操作逻辑移至数据库服务器端执行，从而简化客户端应用程序的开发和维护。二、存储过程和存储函数的缺点缺点：学习成本：存储过程和存储函数有一定的学习曲线。使用它们可能需要掌握特定的语法和接口，以及了解数据库的内部运作原理。可移植性限制：存储过程和存储函数的语法和实现可能会有一些差异，这可能导致在不同的数据库系统之间迁移代码时需要进行修改和调整。难以调试：与在应用程序中调试逻辑相比，存储过程和存储函数的调试可能会更加困难。对于复杂的存储过程和存储函数，定位和处理错误可能会变得具有挑战性。限制性：存储过程和存储函数通常运行在数据库服务器上，受限于数据库系统的资源和功能。某些高级编程概念和操作可能无法在存储过程和存储函数中使用。第二节创建存储过程和存储函数创建存储过程：CREATEPROCEDURE存储过程名(IN|OUT|INOUT参数名参数类型,...)[characteristics...]BEGIN存储过程体END调用存储过程：CALL存储过程或存储函数名(参数名);例子：CREATEPROCEDURECountProc(INsidINT,OUTnumINT)BEGINSELECTCOUNT(*)INTOnumFROMfruitsWHEREs_id=sid;END创建存储函数：CREATEFUNCTIONfunc_name([func_parameter])RETURNStype[characteristic...]BEGIN存储函数体END调用存储函数：SELECT存储函数名(参数名);例子：CREATEFUNCTIONemail_by_id(emp_idINT)RETURNSVARCHAR(25)CONTAINSSQLBEGINRETURN(SELECTemailFROMemployeesWHEREemployee_id=emp_id);END第二节创建存储过程和存储函数存储过程体和存储函数体语法：1.定义变量DECLARE变量名类型[default值];DECLAREmyparamINTDEFAULT100;2.变量赋值

方式一：一般用于赋简单的值SET变量名=值;SET变量名:=值;

方式二：一般用于赋表中的字段值SELECT字段名或表达式INTO变量名FROM表;3.查看变量SELECT局部变量名;第二节创建存储过程和存储函数其他语法结构分支结构语法:IF表达式1THEN操作1[ELSEIF表达式2THEN操作2]…[ELSE操作N]ENDIF第二节创建存储过程和存储函数CASE语句是一种在编程语言中常见的条件语句，用于根据条件的匹配来执行不同的代码块。情况一：类似于switchCASE表达式WHEN值1THEN结果1或语句1(如果是语句，需要加分号)WHEN值2THEN结果2或语句2(如果是语句，需要加分号)...ELSE结果n或语句n(如果是语句，需要加分号)END[case]（如果是放在beginend中需要加上case，如果放在select后面不需要）第二节创建存储过程和存储函数情况二：类似于多重ifCASEWHEN条件1THEN结果1或语句1(如果是语句，需要加分号)WHEN条件2THEN结果2或语句2(如果是语句，需要加分号)...ELSE结果n或语句n(如果是语句，需要加分号)END[case]（如果是放在beginend中需要加上case，如果放在select后面不需要）第二节创建存储过程和存储函数循环结构LOOPLOOP循环体ENDLOOP循环结构WHILE[while_label:]WHILE循环条件DO循环体ENDWHILE[while_label];循环结构REPEAT[repeat_label:]REPEAT循环体UNTIL结束循环的条件表达式ENDREPEAT[repeat_label]第二节创建存储过程和存储函数例：创建存储过程，实现累加运算，计算1+2+…+n。DELIMITER//

CREATEPROCEDURE`add_num`(INnINT)

BEGIN

DECLAREiINT;

DECLAREsumINT;

SETi=1;

SETsum=0;

WHILEi<=nDO

SETsum=sum+i;

SETi=i+1;

ENDWHILE;

SELECTsum;

END//

DELIMITER;

第二节创建存储过程和存储函数例：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

DELIMITER//

CREATEFUNCTIONcount_by_id(dept_idINT)

RETURNSINT

LANGUAGESQL

NOTDETERMINISTIC

READSSQLDATA

SQLSECURITYDEFINER

COMMENT'查询部门平均工资'

BEGIN

RETURN(SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=dept_id);

END//

DELIMITER;

第二节创建存储过程和存储函数MySQL存储了存储过程和函数的状态信息，用户可以使用SHOWSTATUS语句或SHOWCREATE语句来查看，也可直接从系统的information_schema数据库中查询。使用SHOWCREATE语句查看存储过程和函数的创建信息SHOWCREATE{PROCEDURE|FUNCTION}存储过程名或函数名使用SHOWSTATUS语句查看存储过程和函数的状态信息SHOW{PROCEDURE|FUNCTION}STATUS[LIKE'pattern’]这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。第三节查看存储过程和存储函数从information_schema.Routines表中查看存储过程和函数信息MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='存储过程或函数的名'[ANDROUTINE_TYPE={PROCEDURE|FUNCTION}];第三节查看存储过程和存储函数修改存储过程或函数，不影响存储过程或函数功能，只修改相关特性。其中characteristic指定存储过程或函数的特性ALTER{PROCEDURE|FUNCTION}存储过程或函数的名[characteristic...]CONTAINSSQL，表示子程序包含SQL语句，但不包含读或写数据的语句。NOSQL，表示子程序中不包含SQL语句。READSSQLDATA，表示子程序中包含读数据的语句。MODIFIESSQLDATA，表示子程序中包含写数据的语句。COMMENT'string'，表示注释信息SQLSECURITY{DEFINER|INVOKER}，指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。第四节修改存储过程和存储函数例：修改存储过程CountProc的定义，将读写权限改为MODIFIESSQLDATA，并指明调用者可以执行：ALTERPROCEDURECountProcMODIFIESSQLDATASQLSECURITYINVOKER;

修改后，访问数据的权限（SQL_DATA_ACCESS）已经变成MODIFIESSQLDATA，安全类型（SECURITY_TYPE）已经变成INVOKER。

第四节修改存储过程和存储函数

删除存储过程和函数，可以使用DROP语句。其语法结构如下：DROP{PROCEDURE|FUNCTION}[IFEXISTS]存储过程或函数的名称第四节删除存储过程和存储函数MySQL中的存储过程和存储函数是一种可重复使用的数据库对象，它们允许将一系列SQL语句作为一个单元进行封装和执行。存储过程是一组预定义的SQL语句集合，可以通过调用名称来执行，而存储函数则类似于函数，可以接受参数并返回一个值。存储过程和存储函数的使用具有以下优势：提高性能：由于存储过程和存储函数在数据库中预编译和存储，执行速度比单独执行SQL语句快，可以减少网络通信开销。维护简便：存储过程和存储函数可以在数据库中进行集中管理，使得维护和更新更加方便，减少了重复代码的编写。安全性提升：存储过程和存储函数可以根据需要设定访问权限，可以控制用户对于数据库的操作权限，提升了安全性。第五节本章小结

创建存储过程和存储函数使用`CREATEPROCEDURE`和`CREATE