Oracle数据库基础教程第10章人民邮电出版社.ppt

Oracle10g数据库应用教程 授课教师 职务 第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 IS 例 声明一个游标MyCur 读取指定类型的用户信息 DECLARECURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType 游标控制语句 2 打开游标语句OPEN OPEN 例 打开游标MyCur 读取类型为1的用户信息 OPENMyCur 1 游标控制语句 3 游标取值语句FETCH 游标取值语句FETCH的基本语法结构如下 FETCHINTO 例 在打开的游标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存储过程管理 过程函数程序包 过程 CREATEPROCEDURE语句来创建过程 CREATE ORREPLACE PROCEDURE IS AS BEGINEND 过程 例 创建示例过程ResetPwd 此过程的功能是将表Users中指定用户的密码重置为111111 CREATEORREPLACEPROCEDUREUserMan ResetPwd UserIdINNUMBER ASBEGINUPDATEUsersSETUserPwd 111111 WHEREUserId UserId END 过程 存储过程管理 过程 添加存储过程 过程 修改存储过程 函数 CREATEFUNCTION语句来创建函数 CREATE ORREPLACE FUNCTION RETURN IS AS BEGINRETURNEND 函数 例 下面介绍一个示例函数GetPwd 此函数的功能是在表Users中根据指定的用户名返回该用户的密码信息 CREATEFUNCTIONUserMan GetPwd nameINUsers UserName Type RETURNUsers UserPwd TypeASoutpwdUsers UserPwd Type BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName name RETURNoutpwd END 函数 函数管理 函数 添加函数 函数 修改函数 程序包 CREATEPACKAGE语句来创建包的说明部分 CREATE ORREPLACE PACKAGEIS AS END 程序包 例 下面介绍一个示例创建程序包MyPack 它包含前面2小节中的过程ResetPwd和函数GetPwd CREATEORREPLACEPACKAGEUserMan MyPackISPROCEDUREResetPwd UserIdINNUMBER FUNCTIONGetPwd nameINUsers UserName Type RETURNUsers UserPwd Type ENDMyPack 程序包 程序包管理 程序包 添加程序包 程序包 CREATEPACKAGEBODY语句来创建包体部分 CREATEPACKAGEBODYIS AS END 程序包 例 下面创建程序包MyPack的包体体部分 CREATEPACKAGEBODYUserMan MyPackISPROCEDUREResetPwd UserIdINNUMBER ASBEGINUPDATEUsersSETUserPwd 111111 WHEREUserId UserId 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 MyPack DROPPACKAGE命令删除程序包的说明部分 DROPPACKAGEUserMan MyPack 10 3触发器管理 触发器的基本概念创建及使用触发器 触发器的基本概念 触发事件 INSERT 当指定的表发生插入 INSERT 操作时执行触发器 UPDATE 当指定的表发生修改 UPDATE 操作时执行触发器 DELETE 当指定的表发生删除 DELETE 操作时执行触发器 触发器的基本概念 触发时间 BEFORE 在指定的事件发生之前执行触发器 AFTER 在指定的事件发生之后执行触发器 触发级别 行触发 对触发事件影响的每一行执行触发器 语句触发 对于触发事件只能触发一次 而且不能访问受触发器影响的每一行的值 创建及使用触发器 CREATETRIGGER语句来创建触发器 CREATE ORREPLACE TRIGGER BEFORE AFTER ON FOREACHROW WHEN 创建及使用触发器 例 创建一个触发器MyTrigger 它的作用是当表USERMAN UserType中TypeId列的值发生变化时 自动更新表USERMAN Users中的UserType列的值 从而保证数据的完整性 CREATEORREPLACETRIGGERUSERMAN MyTriggerAFTERUPDATEONUSERMAN UserTypeFOREACHROWBEGINUPDATEUSERMAN UsersSETUserType new TypeIdWHEREUserType old TypeId END 创建及使用触发器 触发器管理 创建及使用触发器 添加触发器