第10章数据库之存储过程

第10章存储程序1存储程序：是保存在服务器里的一组sql语句的集合。有两种类型：存储过程、存储函数。存储过程的优点：（1）存储过程在服务器端执行，执行速度快。（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需要从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。10.1存储过程2（3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数据库的信息访问的权限。10.1存储过程310.1.1创建存储过程例10.2创建存储过程，功能是删除一个特定供应商的信息。10.1存储过程410.1存储过程5Delimiter$$Createproceduredelete_gys(ingyshchar(6))Begindeletefromswheresno=gysh;End$$Delimiter;P147存储过程体10.1存储过程610.1.2调用存储过程P158语法：callsp_name([存储过程参数[,…]])例10.15：P15810.1存储过程7Useprojiect;Createproceduredo_query()selectcount(*)froms;Calldo_query();这个是不带参数的存储过程的调用。10.1存储过程8又例：带参数的存储过程的调用。调用例10.2创建的存储过程。Delimiter$$Createproceduredelete_gys(ingyshchar(6))Begindeletefromswheresno=gysh;End$$Delimiter;10.1存储过程9Calldelete_gys(‘s12007’);例10.17P15910Delimiter$$Createproceduredo_update(ingyshvarchar(6),inljmvarchar(6))BeginDeclareljhvarchar(6);Declareysvarchar(8);Declaresltinyint;selectjno,jcolorintoljh,ysfromjwherejname=ljm;selectjcountintoslfromspjwherepno=‘p12001’andsno=gyshandjno=ljh;11ifsl160thenupdatejsetjcolor=‘无’wherejno=ljh;elseupdatejsetjcolor=yswherejno=ljh;endif;End$$Delimiter;调用存储过程：Calldo_update(‘s12001’,’马达’);10.1.3删除存储过程P162Dropprocedure[ifexists]sp_name10.1.4修改存储过程P162一般采用先删除再重新建立的方式。10.1存储过程1210.1.4存储过程体1.局部变量：在存储过程中可以声明局部变量，用来存储临时结果。（1）声明局部变量例10.3声明一个整型变量和两个字符变量。Declarenumint（8）；Declarestr1，str2varchar(6);10.1存储过程13说明：局部变量只能在begin…end语句块中声明，且必须在存储过程的开头就声明，而且只能在begin…end语句块中使用，其他语句块中不能使用。10.1存储过程14（2）使用set语句赋值例10.4给上面声明的局部变量赋值。Setnum=1，str1=‘hello’；说明：上面这条赋值语句无法单独执行，只能在存储过程和存储函数中使用。10.1存储过程15（3）使用select…into语句赋值使用select…into可以把选定的列值直接存储到变量中。例题10.5在存储过程体中将S表中的供应商号为s12008的供应商和所在城市的值分别赋给变量name和city。10.1存储过程16Selectsno，scityintoname，cityFromsWhere供应商号=‘s12008’；10.1存储过程17Selectsno，scityintoname，cityFromsWhere供应商号=‘s12008’；10.1存储过程182.控制流语句（1）if语句（if-then-else）例题10.6创建存储过程，判断两个输入的参数哪一个更大。10.1存储过程19Delimiter$$Createprocedurecompar(inK1integer,inK2integer,outK3char(6))BeginifK1K2thensetK3=‘大于’;elseifK1=K2thensetK3=‘等于’;elsesetK3=‘小于’;Endif;End$$Delimiter;说明：K1、K2是输入参数，K3是输出参数2010.1存储过程21Callcompare(1,3,@k);Select@k;这个是既有输入参数，又有输出参数的例子。2.控制流语句（2）case语句例题10.7创建存储过程，针对参数的不同，返回不同的结果。10.1存储过程22第一种格式：Delimiter$$Createprocedureresult(instrvarchar(6),outsexvarchar(6))Begincasestrwhen‘M’thensetsex=‘男’；when‘F’thensetsex=‘女’；elsesetsex=‘无’；endcase；End$$Delimiter;23第二种格式：Delimiter$$Createprocedureresult(instrvarchar(6),outsexvarchar(6))Begincasewhenstr=‘M’thensetsex=‘男’；whenstr=‘F’thensetsex=‘女’；elsesetsex=‘无’；endcase；End$$Delimiter;242.控制流语句（3）循环语句MYSQL支持3条用来创建循环的语句：while、repeat、loop例题10.910.1存储过程25用while语句实现：Delimiter$$CreateproceduredowhileBegindeclarev1intdefault5;whilev10dosetv1=v1-1;endwhile;End$$Delimiter;说明：调用此存储过程时，首先判断V1是否大于0，如果大于0则执行循环，否则循环结束。26用repeat语句实现：Delimiter$$CreateproceduredowhileBegindeclarev1intdefault5;repeatsetv1=v1-1;untilv11endrepeat;End$$Delimiter;说明：先执行，再判断。272.控制流语句例题10.11loopP15210.1存储过程283.处理程序和条件P153在存储过程中执行sql语句可能导致一条错误消息。例如，向一个表中插入新的行而主键值已经存在，这条insert语句会导致一个出错消息，并且mysql立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个sqlstate代码。10.1存储过程293.处理程序和条件P153定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。10.1存储过程303.处理程序和条件P153为了防止Mysql在一条错误消息产生时就停止处理，需要使用declarehandler语句。语法格式件P154例如10.12P15410.1存储过程313.处理程序和条件P153注意：不能为同一个出错消息在同一个Begin-end语句块中定义两个或更多的处理程序。另：为了提高可读性，可以为出错代码定义一个名字。例10.13P15610.1存储过程324.游标P156游标用来处理select语句产生的结果集。Mysql支持简单的游标。在Mysql中，游标要在存储过程或函数中使用，不能单独在查询中使用。10.1存储过程334.游标P156使用游标需要用到如下4条语句：Declarecursor:声明游标Opencursor:打开游标Fetchcursor:读取游标Closecursor:关闭游标例10.14P15710.1存储过程34存储函数也是过程式对象之一，与存储过程很相似。它们都是由sql和过程式语句组成的代码段，并可以在应用程序和sql中调用。10.2存储函数3510.2.1存储函数与存储过程的区别（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；（2）不能用CALL语句来调用存储函数；（3）存储函数必须包含一条return语句，而这条sql语句不能包含在存储过程中。创建存储函数10.2存储函数3610.2.2创建存储函数P163例10.22：创建存储函数，功能是返回s表记录数目。10.2存储函数3710.2.3调用存储函数P164例10.25：调用例10.22中的存储函数selectnum_of_s();例10.26：调用例10.23中的存储函数selectname_of_s(‘s12006’);说明：在存储函数中可以调用另外一个存储函数或存储过程。10.2存储函数3810.2.4删除存储函数P16610.2.5修改存储函数P16610.2存储函数39