这篇文章主要介绍了mysql存储过程与存储函数实例分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇mysql存储过程与存储函数实例分析文章都会有所收获，下面我们一起来看看吧。

简介

是一组经过预先编译的SQL语句的封装存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行

分类

1、没有参数（无参数无返回）2、仅仅带IN类型（有参数无返回）3、仅仅带OUT类型（无参数有返回）4、既带IN又带OUT（有参数有返回）5、带INOUT（有参数有返回）

格式

DELIMITER$CREATEPROCEDURE存储过程名(IN|OUT|INOUT参数名参数类型,...)[characteristics...]BEGINsql语句1;sql语句2;END$IN：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是IN，表示输入参数OUT：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了INOUT：当前参数既可以为输入参数，也可以为输出参数形参类型可以是MySQL数据库中的任意类型characteristics表示创建存储过程时指定的对存储过程的约束条件1.BEGIN…END：BEGIN…END中间包含了多个语句，每个语句都以（;）号为结束符2.DECLARE：DECLARE用来声明变量，使用的位置在于BEGIN…END语句中间，而且需要在其他语句使用之前进行变量的声明3.SET：赋值语句，用于对变量进行赋值4.SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值存储过程体中可以有多条SQL语句，如果仅仅一条SQL语句，则可以省略BEGIN和ENDDELIMITER新的结束标记DELIMITER//”语句的作用是将MySQL的结束符设置为//，并以“END//”结束存储过程。存储过程定义完毕之后再使用“DELIMITER;”恢复默认结束符当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符

代码案例

#类型1：无参数无返回值#举例1：创建存储过程select_all_data()，查看employees表的所有数据DELIMITER$#开始CREATEPROCEDUREselect_all_data()BEGINSELECT*FROMemployees;END$DELIMITER;#结束#存储过程的调用CALLselect_all_data();#举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资DELIMITER//CREATEPROCEDUREavg_employee_salary()BEGINSELECTAVG(salary)FROMemployees;END//DELIMITER;#调用CALLavg_employee_salary();#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值DELIMITER//CREATEPROCEDUREshow_max_salary()BEGINSELECTMAX(salary)FROMemployees;END//DELIMITER;#调用CALLshow_max_salary();#类型2：带OUT#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出DELIMITER//CREATEPROCEDUREshow_min_salary(OUTmsDOUBLE)#输出ms，类型是DOUBLEBEGINSELECTMIN(salary)INTOms#将min赋值给msFROMemployees;END//DELIMITER#调用CALLshow_min_salary(@ms);#查看变量值SELECT@ms;#类型3：带IN#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名DELIMITER//CREATEPROCEDUREshow_someone_salary(INempnameVARCHAR(20))#传入empname，类型为VARCHARBEGINSELECTsalaryFROMemployeesWHERElast_name=empname;#使用empnameEND//DELIMITER;#调用方式1CALLshow_someone_salary('Abel');#调用方式2SET@empname:='Abel';CALLshow_someone_salary(@empname);#类型4：带IN和OUT#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资DELIMITER//CREATEPROCEDUREshow_someone_salary2(INempnameVARCHAR(20),OUTempsalaryDECIMAL(10,2))BEGINSELECTsalaryINTOempsalary#赋值FROMemployeesWHERElast_name=empname;#使用参数END//DELIMITER;#调用SET@empname='Abel';CALLshow_someone_salary2(@empname,@empsalary);#查看SELECT@empnameSELECT@empsalary;#类型5：带INOUT#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名DELIMITER$CREATEPROCEDUREshow_mgr_name(INOUTempnameVARCHAR(25))BEGINSELECTlast_nameINTOempnameFROMemployeesWHEREemployee_id=(SELECTmanager_idFROMemployeesWHERElast_name=empname);END$DELIMITER;#调用SET@empname:='Abel';CALLshow_mgr_name(@empname);#查看SELECT@empname;存储函数

格式

CREATEFUNCTION函数名(参数名参数类型,...)RETURNS返回值类型[characteristics...]BEGIN函数体#函数体中肯定有RETURN语句END1、RETURNStype语句表示函数返回数据的类型2、RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURNvalue语句3、characteristic创建函数时指定的对函数的约束。取值与创建存储过程时相同4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END5、调用存储函数SELECT函数名(实参列表)

characteristics

LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string1、LANGUAGESQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL2、[NOT]DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOTDETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOTDETERMINISTIC3、{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指明子程序使用SQL语句的限制CONTAINSSQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句NOSQL表示当前存储过程的子程序中不包含任何SQL语句READSSQLDATA表示当前存储过程的子程序中包含读数据的SQL语句MODIFIESSQLDATA表示当前存储过程的子程序中包含写数据的SQL语句默认情况下，系统会指定为CONTAINSSQL4、SQLSECURITY{DEFINER|INVOKER}：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。如果没有设置相关的值，则MySQL默认指定值为DEFINER5、COMMENT'string'：注释信息，可以用来描述存储过程

代码案例

#举例1：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型DELIMITER//#开始CREATEFUNCTIONemail_by_name()RETURNSVARCHAR(25)#返回值DETERMINISTIC#表示结果确定CONTAINSSQL#表示包含sql语句READSSQLDATA#表示包含读数据的sqlBEGINRETURN(SELECTemailFROMemployeesWHERElast_name='Abel');END//DELIMITER;#结束#调用SELECTemail_by_name();#举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型#创建函数前执行此语句，保证函数的创建会成功；则不需要写characteristicsSETGLOBALlog_bin_trust_function_creators=1;#声明函数DELIMITER//#开始CREATEFUNCTIONemail_by_id(emp_idINT)#传入参数RETURNSVARCHAR(25)#返回值BEGINRETURN(SELECTemailFROMemployeesWHEREemployee_id=emp_id);#使用emp_idEND//DELIMITER;#调用SELECTemail_by_id(101);#调用方式2SET@emp_id:=102;SELECTemail_by_id(@emp_id);#举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。DELIMITER//CREATEFUNCTIONcount_by_id(dept_idINT)RETURNSINTBEGINRETURN(SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=dept_id);END//DELIMITER;#调用SET@dept_id:=50;SELECTcount_by_id(@dept_id);

存储过程、存储函数的查看

#方式1：使用SHOWCREATE语句查看存储过程和函数的创建信息#查看存储过程SHOWCREATEPROCEDUREshow_mgr_name;#查看存储函数SHOWCREATEFUNCTIONcount_by_id;#方式2：使用SHOWSTATUS语句查看存储过程和函数的状态信息#查看存储过程SHOWPROCEDURESTATUS;#查看指定存储过程SHOWPROCEDURESTATUSLIKE'show_max_salary';#查看指定存储函数SHOWFUNCTIONSTATUSLIKE'email_by_id';#方式3：从information_schema.Routines表中查看存储过程和函数的信息SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='email_by_id'ANDROUTINE_TYPE='FUNCTION';SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='show_min_salary'ANDROUTINE_TYPE='PROCEDURE';

存储过程、函数的修改

ALTERPROCEDUREshow_max_salarySQLSECURITYINVOKERCOMMENT'查询最高工资';

存储过程、函数的删除

DROPFUNCTIONIFEXISTScount_by_id;DROPPROCEDUREIFEXISTSshow_min_salary;

关于“mysql存储过程与存储函数实例分析”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“mysql存储过程与存储函数实例分析”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注亿速云行业资讯频道。