西安邮电大学Oracle大型数据库第章课件

第10章游标、存储过程和触发器课程描述介绍Oracle数据库程序设计中经常会用到的3个概念，即游标、存储过程和触发器。本章知识点游标存储过程管理触发器管理游标游标的基本概念游标控制语句游标属性游标FOR循环游标的基本概念游标示意图游标的基本概念使用显式游标

（1）声明游标。（2）打开游标。（3）读取数据。（4）关闭游标。游标的基本概念隐式游标【例】使用SELECT语句声明隐式游标，从HR.Departments表中读取Department_name字段的值到变量DepName：SETServerOutputON;%Type;BEGINSELECTDepartment_nameINTODepNameFROMHR.DepartmentsWHEREDepartment_ID=10;dbms_output.put_line(DepName);END;类型描述符游标控制语句（1）声明游标语句CURSOR：DECLARECURSOR<游标名>[(<参数列表>)]IS<SELECT语句>;【例】声明一个游标MyCur，读取指定类型的用户信息：DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游标控制语句（2）打开游标语句OPEN：OPEN<游标名>[(<参数列表>)];【例】打开游标MyCur，读取类型为1的用户信息：OPENMyCur(1);游标控制语句（3）游标取值语句FETCH。游标取值语句FETCH的基本语法结构如下：FETCH<游标名>INTO<变量列表>;【例】在打开的游标MyCur的当前位置读取数据：FETCHMyCurINTOvarI,dvarName;（4）关闭游标语句CLOSE：CLOSE<游标名>;【例】关闭游标MyCur：CLOSEMyCur;游标控制语句【例】下面介绍一个完整的游标应用实例：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分varIdNUMBER;--声明变量,用来保存游标中的用户编号varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体

OPENMyCur(1);--打开游标,参数为1,表示读取用户类型编号为1的记录

FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据

CLOSEMyCur;--关闭游标dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据END;--结束程序体游标属性性（1）%ISOPEN属性【例】下下面的代代码演示示当使用用未打开开的游标标时，将将会出现现错误：：/*打打开显示示模式*/SETServerOutputON;DECLARE--开始始声明部部分varNameVARCHAR2(50);--声明变变量,用用来保存存游标中中的用户户名varIdNUMBER;--声声明变量量,用来来保存游游标中的的用户编编号--定义义游标,varType为为参数,指定定用户类类型编号号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程程序体FETCHMyCurINTOvarId,varName;--读读取当前前游标位位置的数数据CLOSEMyCur;--关闭闭游标dbms_output.put_line('用户编编号:'||varId||',用用户名名:'||varName);--显示读读取的数数据END;--结结束程序序体游标属性性【例】修修改上面面的程序序，在使使用游标标之前，，调用%ISOPEN属性判判断游标标是否打打开。/*打打开显示示模式*/SETServerOutputON;DECLARE--开始始声明部部分varNameVARCHAR2(50);--声明变变量,用用来保存存游标中中的用户户名varIdNUMBER;--声声明变量量,用来来保存游游标中的的用户编编号--定义义游标,varType为为参数,指定定用户类类型编号号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(2);ENDIF;FETCHMyCurINTOvarId,varName;--读读取当前前游标位位置的数数据CLOSEMyCur;--关闭闭游标dbms_output.put_line('用户编编号:'||varId||',用用户名名:'||varName);--显示读读取的数数据END;--结结束程序序体游标属性性（2）%FOUND属属性和%NOTFOUND属属性【例】%FOUND属属性可以以循环执执行游标标读取数数据：/*打打开显示示模式*/SETServerOutputON;DECLARE--开始始声明部部分varNameVARCHAR2(50);--声明变变量,用用来保存存游标中中的用户户名varIdNUMBER;--声声明变量量,用来来保存游游标中的的用户编编号--定义义游标,varType为为参数,指定定用户类类型编号号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--读读取当前前游标位位置的数数据WHILEMyCur%FOUND--如果果当前游游标有效效，则执执行循环环LOOPdbms_output.put_line('用户编编号:'||varId||',用用户名名:'||varName);--显示读读取的数数据FETCHMyCurINTOvarId,varName;--读取当前游游标位置的数数据ENDLOOP;CLOSEMyCur;--关闭游标标END;--结束束程序体游标属性（3）%ROWCOUNT属性【例】只读取取前2行记录录：/*打开显显示模式*/SETServerOutputON;DECLARE--开始声明部部分varNameVARCHAR2(50);--声声明变量,用用来保存游标标中的用户名名varIdNUMBER;--声明变变量,用来保保存游标中的的用户编号--定义游标标,varType为为参数,指指定用户类型型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游标属性BEGIN--开开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游游标位置的数数据WHILEMyCur%FOUND--如如果当前游标标有效，则执执行循环LOOPdbms_output.put_line('用户编号号:'||varId||',用户名名:'||varName);--显示示读取的数据据IFMyCur%ROWCOUNT=2THENEXIT;ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游游标位置的数数据ENDLOOP;CLOSEMyCur;--关闭游标标END;--结束束程序体游标FOR循循环【例】声明记记录类型User_Record_Type和和定义记录变变量var_UserRecord：TYPEUser_Record_TypeISRECORD(UserIdUsers.UserId%Type,UserNameUsers.UserName%Type);var_UserRecordUser_Record_Type;游标FOR循循环【例】PL/SQL记录录可以与游标标结合使用：：/*打开显显示模式*/SETServerOutputON;DECLARE--开始声明部部分/*声明记记录类型*/TYPEUser_Record_TypeISRECORD(UserIdUsers.UserId%Type,UserNameUsers.UserName%Type);/*定义记记录变量*/var_UserRecordUser_Record_Type;--定义游标标,varType为为参数,指指定用户类型型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游标FOR循循环BEGIN--开开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;LOOPFETCHMyCurINTOvar_UserRecord;--读取当前游游标位置的数数据到记录变变量var_UserRecordEXITWHENMyCur%NOTFOUND;--当游游标指向结果果集结尾时退退出循环/*显示保保存在记录变变量var_UserRecord中的数据*/dbms_output.put_line('用户编号号:'||var_UserRecord.UserId||',用户户名::'||var_UserRecord.UserName);ENDLOOP;CLOSEMyCur;--关闭游标标END;--结束束程序体游标FOR循循环【例】典型游游标FOR循循环的例子：：/*打开显显示模式*/SETServerOutputON;DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开开始程序体FORvar_UserRecordINMyCur(1)LOOP/*显示保保存在记录变变量var_UserRecord中的数据*/dbms_output.put_line('用户编号号:'||var_UserRecord.UserId||',用户户名::'||var_UserRecord.UserName);ENDLOOP;END;--结束束程序体10.2存存储过程管理理过程一种基本的存存储过程，由由过程名、参参数和程序体体组成。函数与过程类似，，只是函数有有返回值。程序包一组相关的PL/SQL过程和函数数，由包名、、说明部分和和包体组成。。FUNCTION,PROCEDURE和PACKAGE区别function和procedure是PL/SQL代码的集合合，通常为了了完成一个任任务。procedure不需要要返回任何值值而function将将返回一个值值，Package是为为了完成一个个商业功能的的一组function和proceudre的集合过程CREATEPROCEDURE语句来创建建过程：CREATE[ORREPLACE]PROCEDURE<过程名>[<参数列列表>]IS|AS[<局部变变量声明>]BEGIN<过程体>END[<过程名名>];参数声明格格式：<参数名>[IN|OUT|INOUT]<数据据类型>[:=<初始值>]过程【例】创建建示例过程程ResetPwd，此过程程的功能是是将表Users中中指定用户户的密码重重置为111111：CREATEORREPLACEPROCEDUREUserMan.ResetPwd(varUserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd='111111'WHEREUserId=varUserId;END;过程存储过程管管理过程添加存储过过程过程修改存储过过程函数CREATEFUNCTION语句句来创建函函数：CREATE[ORREPLACE]FUNCTION<函数名>[<参数数列表>][RETURN<函数数数据类型>]IS|AS[<局部部变量声明明>]BEGIN<函数体>RETURN<函函数值>END[<函数数名>];CREATE[ORREPLACE]PROCEDURE<过程名>[<参数数列表>]IS|AS[<局部部变量声明明>]BEGIN<过过程程体体>END[<过过程程名名>];函数数【例例】】下下面面介介绍绍一一个个示示例例函函数数GetPwd，，此此函函数数的的功功能能是是在在表表Users中中根根据据指指定定的的用用户户名名返返回回该该用用户户的的密密码码信信息息：：CREATEFUNCTIONUserMan.GetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwdUsers.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=name;RETURNoutpwd;END;函数数函数数管管理理函数数添加加函函数数函数数修改改函函数数程序序包包CREATEPACKAGE语语句句来来创建建包包的的说说明明部部分分：CREATE[ORREPLACE]PACKAGE<程程序序包包名名>IS|AS[<声声明明部部分分>]END[<程程序序包包名名>];--<声声明明部部分分>可可以以包包括括类类型型、、变变量量、、过过程程、、函函数数和和游游标标的的说说明明程序序包包【例例】】下下面面介介绍绍一一个个示示例例创创建建程程序序包包MyPack，，它它包包含含前前面面2小小节节中中的的过过程程ResetPwd和和函函数数GetPwd：：CREATEORREPLACEPACKAGEUserMan.MyPackISPROCEDUREResetPwd(UserIdINNUMBER);FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%Type;ENDMyPack;程序序包包程序序包包管管理理程序序包包添加加程程序序包包程序序包包CREATEPACKAGEBODY语语句句来来创建包体部分：CREATEPACKAGEBODY<程序包名>IS|AS[<声明部部分>][<过程体体>][<函数体体>][<初始化化部分>]END[<程序包包名>];程序包【例】下面创创建程序包MyPack的包体体部部分：CREATEPACKAGEBODYUserMan.MyPackISPROCEDUREResetPwd(varUserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd='111111'WHEREUserId=varUserId;END;FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwdUsers.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=''||name||'';RETURNoutpwd;END;ENDMyPack;程序包程序包管理查查看MyPack程序包包体程序包添加程序包程序包调用程序包中中的过程<方案名>.<程序包名名>.<过程程名>调用程序包中中的函数：<方案名>.<程序包名名>.<函数数名>【例】调用UserMan.MyPack.GetPwd函数，返回回指定用户的的密码信息：：SETServerOutputON;DECLAREvarPwdUsers.UserPwd%Type;BEGINvarPwd:=UserMan.MyPack.GetPwd('Admin');dbms_output.put_line(varPwd);END;程序包DROPPACKAGEBODY命令删除除程序包体：：DROPPACKAGEBODYUserMan.MyP