《MySQL数据库应用与管理项目化教程》课件-项目7-3 存储过程

项目七-3

存储过程和函数任务目标任务一创建存储过程和函数任务二查看存储过程和函数任务三 编程语法与流程控制任务四 修改存储过程和函数任务六删除存储过程和函数

创建存储过程和函数

主要内容:创建存储过程创建存储函数重点难点:设计和应用；存储过程和函数应用引入：db_shop系统中要完成一个购买商品的订单的处理，一般需要考虑以下几步：（1）在生成订单之前，首先需要查看商品库存中是否有相应商品；（2）如果商品库存中不存在相应商品，需要向供应商订货；（3）如果商品库存中存在相应商品，需要预定商品，并修改库存数量。针对表的一个完整事务操作往往不是单条SQL语句就能实现的，而是需要一组SQL语句来实现。可将一个完整事务操作中所包含的多条SQL语句创建为存储过程或函数，以方便应用。存储过程和函数作用存储过程和函数可以简单地理解为一组经过编译并保存在数据库中的SQL语句的集合，可以随时被调用。作用模块化使用。将复杂的工作程序写成存储过程和函数，以后可通过带入不同的参数或不带参重复调用，大大方便了用户的使用。执行效率高。存储过程和函数，执行多次时速度快。降低网络流量。存储过程是存在服务器端、并在服务器端执行，调用执行时用一个EXECUTE指令来代替存储过程内所包含的大量SQL语句的传输，大大降低了网络流量。安全性。当数据表需要保密时，可以利用存储过程来作为数据存取的管道，来控制用户对数据库信息访问的权限。存储过程创建查阅语法：官网文档：/doc/refman/8.0/en/create-procedure.htmlCREATE[DEFINER=user]PROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_bodycharacteristic:COMMENT'string'|LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}routine_body:ValidSQLroutinestatement存储过程创建创建基本语法：CREATEPROCEDUREproc_name([IN|OUT|INOUT]param_nametype[,…])BEGIN语句体（包括变量声明、控制语句、SQL语句）END说明：IN|OUT|INOUT表示：输入参数、输出参数、输入输出参数。语句体以BEGIN......END括住，每一个语句都要用分号“；”结尾。由于存储过程内部语句要以分号结束，需要在定义存储过程前，用delimiter关键字定义其他字符作为结束标志。存储过程调用调用基本语法：CALLsp_name([parameter[,...]])CALLsp_name[()]存储过程应用使用不带参数的存储过程mysql>delimiter$$

--改变MySQLdelimiter为：“//”改变命令提交执行的标志符号，默认情况下，delimiter是分号;

mysql>CREATEPROCEDUREp_search()BEGIN SELECT*FROMgoods;END$$mysql>delimiter;--改回默认的MySQLdelimiter：“;”mysql>CALLp_Search();存储过程应用使用带输入（IN）参数的存储过程mysql>delimiter//--改变MySQLdelimiter为：“//”

mysql>CREATEPROCEDUREp_staferSearch(INsidCHAR(5))BEGINSELECT*FROMstafferWHEREstaff_id=sid;END//mysql>delimiter;--改回默认的MySQLdelimiter：“;”mysql>CALLp_staferSearch('10503');存储过程应用使用带输入（IN）参数和输出（OUT

）参数的存储过程mysql>DELIMITER//--改变MySQLdelimiter为：“//”mysql>CREATEPROCEDUREp_count(INdeptidCHAR(5),OUTnINT)BEGINSELECTcount(*)INTOnFROMstafferWHEREdept_id=deptid;END//mysql>DELIMITER;--改回默认的MySQLdelimiter：“;”mysql>CALLp_count(1,@a);mysql>SELECT@a;mysql>CALLp_count(2,@a);mysql>SELECT@a;存储过程应用使用带INOUT

参数的存储过程mysql>DELIMITER//--改变MySQLdelimiter为：“//”mysql>CREATEPROCEDUREitem_count(INidINT,INOUTnINT)

BEGIN

DECLAREcount1INT;

SELECTcount(*)INTOcount1FROMitemWHEREorder_id=id;

SETn=count1*n;

END//mysql>DELIMITER;--改回默认的MySQLdelimiter：“;”mysql>SET@n=10;mysql>CALLitem_count(1,@n);mysql>SELECT@n;函数创建和调用查阅语法：官网文档：/doc/refman/8.0/en/create-function.html创建语法：CREATE[DEFINER=user]FUNCTIONfunc_name[func_parameter[,...]])RETURNStype[characteristic...]routine_body调用语法：SET@var_name=func_name(parameter[,......]);SELECTfunc_name(parameter[,......]);函数创建mysql>showvariableslike'log_bin_trust_function_creators';#查看设置，可以在f配置文件中添加：log_bin_trust_function_creators=1mysql>setgloballog_bin_trust_function_creators=1;#如果出现ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled例1：建立一个2个整数相乘的函数。mysql>DROPFUNCTIONIFEXISTSSimpleCompare;mysql>DELIMITER//mysql>CREATEFUNCTIONSimpleCompare(nINT,mINT)RETURNSINT

#-----------------------------------注意是RETURNS,有个SBEGINDECLAREmul

INT;SETmul=n*m;RETURNmul;

#-------------------------这里才是RETURN，没有SEND//mysql>DELIMITER;mysql>SELECTSimpleCompare(2,3);函数创建例2：编写一个函数，统计指定商品编号的订单详细表的商品销售笔数mysql>DELIMITER//mysql>CREATEFUNCTIONitem_count(idINT)RETURNSINTBEGIN DECLAREnint; SELECTCOUNT(*)INTOnFROMitem WHEREgoods_id=id; RETURNn; END//mysql>DELIMITER;mysql>SET@n=item_count(1);mysql>SELECT@n,item_count(2);函数:应用（引出流程编程）例2：编写一个函数，通过职员编号查询职员姓名mysql>setgloballog_bin_trust_function_creators=1;#如果出现ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabledmysql>DROPFUNCTIONIFEXISTSstaffer_search;mysql>DELIMITER//--改变MySQLdelimiter为：“//”mysql>CREATEFUNCTIONstaffer_search(idCHAR(10))RETURNSVARCHAR(10)BEGINDECLAREsnameVARCHAR(10);SELECTstaff_nameINTOsnameFROMstafferWHEREstaff_id=id;IFISNULL(sname)THENRETURN'无人';ELSERETURNsname;ENDIF;END//mysql>DELIMITER;mysql>SET@sname=staffer_search('10501');mysql>SELECT@sname,staffer_search('10401');查看存储过程和函数查阅语法：官网文档：/doc/refman/8.0/en/show.html查看状态：SHOWSTATUS语句语法：SHOW{PROCEDURE|FUNCTION}STATUS

[LIKE'pattern'|WHEREexpr]mysql>SHOWPROCEDURESTATUSLIKE'%staffer%';mysql>SHOWFUNCTIONSTATUSLIKE'%staffer%';查看定义：SHOWCREATE语句语法：SHOWCREATE{PROCEDURE|FUNCTION}proc_or_func_namemysql>SHOWCREATEPROCEDUREp_searchStaffer;mysql>SHOWCREATEFUNCTIONstaffer_search;查看存储过程和函数查看详细信息：使用系统数据库的表information_schema.routinesmysql>SELECT*FROMinformation_schema.routines;mysql>SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAMELIKE'%search%';修改存储过程和函数查阅语法：官网文档：/doc/refman/8.0/en/alter-procedure.html语法：ALTER{PROCEDURE|FUNCTION}

proc_or_func_name

[characteristic...]characteristic:COMMENT'string'|LANGUAGESQL|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}作用：修改存储过程和函数的特性。修改存储过程和函数例如：将读写权限改为MODIFIESSQLDATA，并指明调用者可以执行。mysql>ALTERPROCEDUREp_searchStafferMODIFIESSQLDATASQLSECURITYINVOKER;例如：将读写权限改为READSSQLDATA，并加上注释信息'FINDNAME'。mysql>ALTERPROCEDUREp_searchStafferREADSSQLDATACOMMENT'FINDNAME';mysql>SELECT*FROMinformation_schema.routines#查看修改结果WHEREROUTINE_NAMELIKE'%search%';删除存储过程语法：DROP{PROCEDURE|FUNCTION}[IFEXISTS]sp_name例如：mysql>DROPFUNCTIONIFEXISTSstaffer_search;mysql>DROPPROCEDUREIFEXISTSitem_count;考考你对部门表，分别建立相应的插入、删除、更新存储过程对员工表，分别建立按职员号、按姓名的查询函数；任务实施按下列操作完成db_shopping数据库存储过程和函数的查看。1.选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;2.建立按部门名称查看部门信息的存储过程，并调用测试，执行语句如下：mysql>delimiter//--改变MySQLdelimiter为：“//”mysql>CREATEPROCEDUREp_dept(INdnameVARCHAR(20))BEGINSELECT*FROMdepartmentWHEREdept_name=dname;END//mysql>delimiter;--改回默认的MySQLdelimiter：“;”mysql>CALLp_dept(‘销售部’);3.建立一个存储过程，实现按部门编号修改所有部门信息，执行语句如下：mysql>USEdb_shop;mysql>delimiter//mysql>CREATEPROCEDUREp_dept_up(d_idint,d_namevarchar(20),d_phonechar(13),d_memovarchar(100))BEGINUPDATEdepartmentSETdept_name=d_name,dept_phone=d_phone,dept_memo=d_memoWHEREid=d_id;END//mysql>delimiter;mysql>CALLp_dept_up(6,'外联部',,'对外联系');4.建立一个存储过程，按订购号查看其订购额，把订购额通过输出参数输出，并调用，执行语句如下：mysql>DELIMITER//--改变MySQLdelimiter为：“//”mysql>CREATEPROCEDUREp_orders(INOrd_idINT,OUTxFLOAT) BEGIN SELECTamount_moneyINTOxFROMordersWHEREid=Ord_id;END//mysql>DELIMITER;--改回默认的MySQLdelimiter：“;”mysql>CALLitem_promotion(1,@x);mysql>SELECT@x;任务实施按下列操作完成db_shopping数据库存储过程和函数的查看。（1）选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;（2）建立一个函数，可按订购号查看其订购额，返回订购额，并调用测试，执行语句如下：mysql>DELIMITER//--改变MySQLdelimiter为：“//”mysql>CREA