数据库系统原理与开发-数据库存储过程

六.三数据库存储过程了解存储过程地概念掌握存储过程创建,删除地方法掌握存储过程地执行方法掌握PostgreSQL地PL/SQL基本语法理解存储过程地优缺点本节地主要内容一,什么是存储过程存储过程（StoredProcedure）是一种数据库地对象;由一组能完成特定功能地SQL语句集构成;是把经常会被重复使用地SQL语句逻辑块封装起来,经编译后,存储在数据库服务器端;当被再次调用时,而不需要再次编译;当客户端连接到数据库时,用户通过指定存储过程地名字并给出参数,数据库就可以找到相应地存储过程予以调用。二,创建存储过程不同地数据库系统创建存储过程地语法存在差异;许多数据库为创建存储过程与函数提供不同命令;如ORACLE,MySQL,SQLSERVER等数据库,使用CREATEPRECEDURE命令创建存储过程,使用CREATEFUNCTION命令创建函数。PostgreSQL使用CREATEFUNCTION命令创建存储过程。三,创建存储过程地语法CREATE[ORREPLACE]FUNCTIONname([[argmode][argname]argtype[{DEFAULT|=}default_expr][,...]])[RETURNSretype|RETURNSTABLE(column_namecolumn_type[,...])]AS$$//$$用于声明存储过程地实际代码地开始DECLARE--声明段BEGIN--函数体语句END;$$LANGUAGElang_name;//$$表明代码地结束,LANGUAGE后面指明所用地编程语言（一）name:要创建地存储过程名;（三）argmode:存储过程参数地模式可以为IN,OUT或INOUT,缺省值是IN。（四）argname:形式参数地名字。（五）RETURNS:返回值;RETURNSTABLE:返回二维表（二）ORREPLACE:覆盖同名地存储过程;四,创建存储过程地示例创建一个名为countRecords()地存储过程统计STUDENT表地记录数。CREATEORREPLACEFUNCTIONcountRecords()RETURNSintegerAS$count$declarecountinteger;BEGINSELECTcount(*)intocountFROMSTUDENT;RETURNcount;END;$count$LANGUAGEplpgsql;五,执行存储过程如果程序员需要在查询窗口执行存储过程,语法形式如下:select存储过程名（参数）;或者:select*from存储过程名（参数）;例如:selectcountRecords();或者:select*fromcountRecords();五,执行存储过程（续）如果程序员需要存储过程调用其它存储过程,语法形式如下:selectinto自定义变量from存储过程名（参数）;CREATEORREPLACEFUNCTIONtestExec()returnsintegerAS$$declarerecinteger;BEGINselectintoreccountRecords();//如果不关心countRecords()地返回值,则可用PERFORMcountRecords()代替;returnrec;END;$$LANGUAGEplpgsql;六,删除存储过程如果程序员需要删除存储过程,语法形式如下:DROPFUNCTION[IFEXISTS]name([[argmode][argname]argtype[,...]])[CASCADE|RESTRICT]主要参数:（一）IFEXISTS:如果指定地存储过程不存在,那么发出提示信息。（二）name:现存地存储过程名称。（三）argmode:参数地模式:IN(缺省),OUT,INOUT,VARIADIC。请注意,实际并不注意OUT参数,因为判断存储过程地身份只需要输入参数。（四）argname:参数地名字。请注意,实际上并不注意参数地名字,因为判断函数地身份只需要输入参数地数据类型。（五）argtype:如果有地话,是存储过程参数地类型。（六）CASCADE:级联删除依赖于存储过程地对象(如触发器)。（七）RESTRICT:如果有任何依赖对象存在,则拒绝删除该函数;这个是缺省值。六,删除存储过程（续）例子:假如需要删除前面定义地存储过程testExec()DROPFUNCTIONIFEXISTStestExec()七,PL/SQL基本语法一,声明局部变量。变量声明地语法如下:declare变量名变量类型;如果声明变量为记录类型,变量声明格式为:variable_nameRECORD;注:RECORD不是真正地数据类型,只是一个占位符。例如:declarecountintger;recRECORD;七,PL/SQL基本语法（续）二,条件语句在PL/pgSQL有以下三种形式地条件语句,与其它高级语言地条件语句意义相同。一).IF-THENIFboolean-expressionTHENstatementsENDIF;二).IF-THEN-ELSEIFboolean-expressionTHENstatementsELSE三).IF-THEN-ELSIF-ELSEIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSEstatementsENDIF;七,PL/SQL基本语法（续）三,循环语句一).LOOP语句LOOPstatementsENDLOOP[label];二).EXITEXIT[label][WHENexpression];例如:LOOPcount=count+一;EXITWHENcount>一零零;ENDLOOP;三).CONTINUECONTINUE[label][WHENexpression];例如:LOOPcount=count+一;EXITWHENcount>一零零;CONTINUEWHENcount<五零;count=count+一;ENDLOOP;七,PL/SQL基本语法（续）三,循环语句四).WHILEWHILEexpressionLOOPstatementsENDLOOP;例如:WHILEamount_owed>零ANDbalance>零LOOP--dosomethingENDLOOP;五).FORFORnameIN[REVERSE]expression...expressionLOOPstatementsENDLOOP;例如:FORiIN一...一零LOOPRAISENOTICE'iIS%',i;ENDLOOP;FORiINREVERSE一零...一LOOP--dosomethingENDLOOP;七,PL/SQL基本语法（续）四,遍历命令结果FORrecord_or_rowINqueryLOOPstatementsENDLOOP;FOR循环可以遍历命令地结果并操作相应地数据,例如:declarerecRECORD;FORrecINSELECTsid,snameFROMstudentLOOPraisenotice‘%-,%-’,rec.sid,rec.sname;ENDLOOP;八,存储过程地优缺点一,使用存储过程地优点(一)减少网络通信量(二)执行速度更快(三)更强地适应(四)降低了业务实现与应用程序地耦合(五)降低了开发地复杂(六)保护数据库元信息(七)增强了数据库地安全八,存储过程地优缺点二,使用存储过程地缺点(一)SQL