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

第10章游标、存储过程和触发器课程描述介绍Oracle数据库程序设计中经常会用到的3个概念，即游标、存储过程和触发器。本章知识点游标存储过程管理触发器管理游标游标的基本概念游标控制语句游标属性游标FOR循环游标的基本概念游标示意图游标的基本概念使用显式游标（1）声明游标。（2）打开游标。（3）读取数据。（4）关闭游标。游标的基本概念隐式游标【例】使用SELECT语句声明隐式游标，从HR.Departments表中读取Department_name字段的值到变量DepName：SETServerOutputON;DECLAREDepNameHR.Departments.Department_Name%Type;BEGINSELECTDepartment_nameINTODepNameFROMHR.DepartmentsWHEREDepartment_ID=10;dbms_output.put_line(DepName);END;类型描述符游标控制语句（1）声明游标语句CURSOR：DECLARECURSOR游标名[(参数列表)]ISSELECT语句;【例】声明一个游标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函数名[参数列