理解MySQL存储过程和函数

1、理解mysQL者过程和函数一、概述一提到存储过程可能就会引出另一个话题就是存储过程的优缺点，这里也不做讨论，一般别人问我我就这样回答你觉得它好你就用它。因为y中存储过程和函数的语法非常接近所以就放在一起，主要区别就是函数必须有返回值（），并且函数的参数只有类型而存储过程有、这三种类型。二、语法创建存储过程和函数语法Stypecteristic.routine_bodyOU、|I、OU、param_nametytypeype:AnyvalidMySQLdtypeLA、GUAGESQLRMI、IS、I、AI、SSQLOSQL|EADSSQL|SQLSE|COMMEbody:SQLproMODIFI

2、ESSQL语法来自官方自带的参考手册,例子：语法块是需要注意的地方，先用一个例子来介绍。ployeeLLM主键YCOMM人名LLOMME、咅部门MMS、SEmploy插入测试数据I、SER、I#创建存储过程DELIMI、EREADSSQLSQLSECURyee陈王张L李S40,2郭SSloyeeo_Employee(I、SELMEmployLIM创建数据库ABASEA、ABASRSE、SEDpro;创建部门表ROP、ABLEREA、E、ABL#执行存储过程CALLPro_Employee(101,pcount);SELECTpcount;语法解释：在创建存储过程的时候一般都会用DELIMITE

3、R$END$DELIMITER放在开头和结束，目的就是避免mysqI把存储过程内部的”;解释成结束符号，最后通过“DELIMITER;”来告知存储过程结束。主要解释characteristic部分：LANGUAGESQL:用来说明语句部分是SQL语句，未来可能会支持其它类型的语句。NOTDETERMINISTIC:如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是,确定的”，否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOTDETERMINISTIC，默认的就是NOTDETERMINISTIC(非确定的)CONTAINSSQL:表示子程序不包含读或写数据

4、的语句。NOSQL:表示子程序不包含SQL语句。READSSQLDATA:表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIESSQLDATA:表示子程序包含写数据的语句。SQLSECURITYDEFINER:表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。SQLSECURITYINVOKER:表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。(例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行，当前存储过程是用来查询Employee表，如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误，如果换成DEFINER

5、如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行，因为执行存储过程的权限变成了root)COMMENTstring:备注，和创建表的字段备注一样。注意：在编写存储过程和函数时建议明确指定上面characteristic部分的状态，特别是存在复制的环境中，如果创建函数不明确指定这些状态会报错，从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC,NOSQL,orREADSSQLDATA该三个状态也会报错。报错示例ErrorCode:1418.ThisfunctionhasnoneofDETERMINISTIC,NOSQL,or

6、READSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable)这个报错就是上面注意部分说的问题。原来是因为在主从复制的两台MySQL服务器中开启了二进制日志选项og-bin,slave会从master复制数据，而一些操作，比如function所得的结果在master和slave上可能不同，所以存在潜在的安全隐患。因此，在默认情况下回阻止function的创建。解决办法有两种:将log_bin_trust_fun

7、ction_creator参数设置为ON，这样一来开启了log-bin的MySQLServe便可以随意创建function。这里存在潜在的数据安全问题，除非明确的知道创建的function在master和slave上的行为完全一致。设置该参数可以用动态的方式或者指定该参数来启动数据库服务器或者修改配置文件后重启服务器。需注意的是，动态设置的方式会在服务器重启后失效。mysqlshowvariableslikelog_bin_trust_function_creators;mysqlsetgloballog_bin_trust_function_creators=1;另外如果是在master上创

8、建函数，想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON(变量的设置不会从master复制到slave上,这点需要注意)，否则主从复制会报错。明确指明函数的类型)、DETERMINISTIC不确定的)、NOSQL没有SQl语句，当然也不会修改数据)、READSSQLDATA只是读取数据，当然也不会修改数据比如：CREATEDEFINER=username%READSSQLDATAFUNCTIONfn_getitemclock(i_itemidbigint,i_clockint,i_posint)RETURNSint(11).这扌样一来

9、相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。修改存储过程函数语法ALTERPROCEDURE|FUNCTIONsp_namecharacteristic.characteristic:CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENTstring删除存储过程函数语法DROPPROCEDURE|FUNCTIONIFEXISTSsp_name查看存储过程和函数查看存储过程状态SHOWPROCEDURE|FUNCTIONSTATUSLIKEpatternshowprocedurestatuslikePro_EmployeeG查看存储过程和函数的创建语法SHOWCREATEPROCEDURE|FUNCTIONsp_nameSHOWCREATEPROCEDUREPro_EmployeeG;查看存储过程和函数详细信息复制代码代码如下:SELECT*FROMinformation_s