游标存储过程触发器

Oracle10g数据库应用教程 授课教师 职务 第6章游标 存储过程和触发器 课程描述介绍Oracle数据库程序设计中经常会用到的3个概念 即游标 存储过程和触发器 本章知识点 游标存储过程管理触发器管理 游标 游标的基本概念游标控制语句游标属性游标FOR循环 游标的基本概念 游标就是一种定位并控制结果集的机制将游标放置到某行后 即可对该行数据进行操作 提取当前行数据 游标示意图 游标的基本概念 ORACLE游标可分为两种 显示游标和隐式游标隐式游标不需要声明 使用时也不需要执行打开和关闭操作实际上 隐式游标是在SELECT语句中增加INTO子句 把结果集自动读取到指定的变量中 例 使用SELECT语句声明隐式游标 从users表中读取username字段的值到变量DepName SETServerOutputON DECLAREDepNamevarchar2 40 BEGINSELECTusernameINTODepNameFROMusersWHEREuserid 1 dbms output put line DepName END 游标的基本概念 显示游标需要声明 在使用之前需要打开游标 使用完成后要关闭游标 使用显式游标 1 说明游标 声明游标名及游标中使用的SELECT语句 2 打开游标 执行说明游标时定义的SELECT语句 把查询结果装入内存 游标位于结果集的第1条记录位置 3 读取数据 从结果集的游标当前位置读取数据 执行完成后游标后移一行 4 关闭游标 释放结果集和游标占用的内存空间 游标控制语句 1 声明游标语句CURSOR DECLARECURSOR IS 例 声明一个游标MyCur 读取指定类型的用户信息 DECLARECURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType 游标控制语句 2 打开游标语句OPEN OPEN 例 打开游标MyCur 读取类型为1的用户信息 OPENMyCur 1 游标控制语句 3 游标取值语句FETCH 游标取值语句FETCH的基本语法结构如下 FETCHINTO 例 在打开的游标MyCur的当前位置读取数据 FETCHMyCurINTOvarId varName 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属性判断游标是否被打开 如果游标被打开 则 ISOPEN等于TRUE 否则 ISOPEN等于FALSE 例 下面的代码演示当使用未打开的游标时 将会出现错误 打开显示模式 SETServerOutputON DECLARE 开始声明部分varNameVARCHAR2 50 声明变量 用来保存游标中的用户名varIdNUMBER 声明变量 用来保存游标中的用户编号 定义游标 varType为参数 指定用户类型编号CURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType BEGIN 开始程序体 缺少OPEN语句 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属性判断游标所在的行是否有效 如果有效 则 FOUND属性等于TRUE 否则等于FALSE NOTFOUND属性正相反 例 FOUND属性可以循环执行游标读取数据 打开显示模式 SETServerOutputON DECLARE 开始声明部分varNameVARCHAR2 50 声明变量 用来保存游标中的用户名varIdNUMBER 声明变量 用来保存游标中的用户编号 定义游标 varType为参数 指定用户类型编号CURSORMyCur varTypeNUMBER ISSELECTUserId UserNameFROMUsersWHEREUserType varType 游标属性 2 FOUND属性和 NOTFOUND属性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循环 使用FOR循环依次读取结果集中的行数据 当FOR循环开始时 游标被自动打开 不需要使用OPEN语句 每循环一次 系统自动读取游标当前行的数据 不需要使用FETCH语句 当退出FOR循环时 游标自动关闭 不需要使用CLOSE语句 游标FOR循环通常与PL SQL记录一起使用PL SQL记录 record 与C语言中的结构体相似 由一组数据项构成的逻辑单元 例如描述一个用户信息 可以使用由用户编号userid 用户名username和用户类型usertype等数据项组成的记录 游标FOR循环 PL SQL记录并不保存在数据库中 它与变量一样 保存在内存空间中 在使用记录时 需要先定义记录的结构 然后声明记录变量 可以把pl sql记录看作是一个用户自定义的数据类型 定义记录类型的基本语法如下 TYPEISRECORD 字段声明 字段声明 在 字段声明 中 需要定义PL SQL记录中的字段项及其数据类型 数据类型可以是PL SQL中定义的数据类型 也可以使ORACLE数据库中列的数据类型 在声明记录类型时 使用 TYPE来表示表中列的数据类型 可以使用下面的方法访问记录中的字段 如果要声明的记录类型与某个表或视图的结构完全相同 则可以直接使用 ROWTYPE属性来定义记录变量 语法如下 ROWTYPE 使用此方法定义的记录变量 不需要声明记录类型 记录变量的字段名与对应表或视图中的列名一致 而且拥有相同的数据类型 例 定义一个与表USERs结构完全相同的记录变量var UserRecord1 var userrecord1users ROWTYPE 游标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 DECLARE 声明记录类型 TYPEUser Record TypeISRECORD UserIdUsers UserId Type UserNameUsers UserName Type 定义记录变量 var UserRecordUser Record Type CURSORMyCur 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语言也可以把用户自己编写的程序存储起来 在需要的时候调用执行 这样可以实现工作的积累 提高代码的重用性和共享性 存储过程就是以一种形式存储的用户程序 它需要人为执行调用语句运行 PL SQL有3种存储过程 过程 一种基本的存储过程 由过程名 参数和程序体组成 函数 与过程类似 只是函数有返回值 程序包 一组相关的PL SQL过程和函数 由包名 说明部分和包体组成 过程 从本质上看 过程就是命名的PL SQL程序块 它可以被赋予参数并存储在数据库中 然后由另外一个应用或PL SQL历程调用 CREATEPROCEDURE语句来创建过程 CREATE ORREPLACE PROCEDURE IS AS BEGINEND 说明 使用REPLACE表示如果创建的过程已经存在 则将其替换为当前定义的过程 在局部变量声明块中定义的变量只在过程体内有效 参数声明格式如下 IN OUT INOUT IN参数类型表示此参数接受过程外传递来的值 OUT参数类型表示此参数将在过程中被赋值 并传递到过程体外 INOUT参数类型表示此参数同时具备IN和OUT参数类型的特性 过程 例 创建示例过程ResetPwd 此过程的功能是将表Users中指定用户的密码重置为111111 CREATEORREPLACEPROCEDUREResetPwd v useridINNUMBER ASBEGINUPDATEUsersSETUserPwd 111111 WHEREUserId v userid END 过程 2 调用过程使用EXECUTE命令可以调用过程 execute函数可以简写为exec 例 将表Users中编号为1的用户的密码重置为111111 execresetpwd 1 selectusername userpwdfromuserswhereuserid 1 过程 也可以从匿名PL SQL程序块中直接调用SQL createproceduremy proc2as3begin4dbms output put line 欢迎 5endmy proc 6 过程已创建 过程 SQL setserveroutonSQL begin2my proc 3end 4 欢迎 PL SQL过程已成功完成 过程 3 删除过程SQL dropprocedureresetpwd 过程 使用IN参数过程SQL createtablet2 3nnumber4 表已创建 SQL createorreplace2procedureinsert into t p parminnumber 3as4begin5insertintotvalues p parm 6endinsert into t 7 过程已创建 SQL select fromt 未选定行 过程 使用IN参数过程SQL execinsert into t p parm 100 PL SQL过程已成功完成 SQL select fromt N 100 参数传递方式 名称表示法 位置表示法 混合表示法SQL createorreplace2procedurethree parms 3p p1number 4p p2number 5p p3number 6as7begin8dbms output put line p p1 p p1 9dbms output put line p p2 p p2 10dbms output put line p p3 p p3 11endthree parms 12 过程已创建 名称表示法SQL execthree parms p p2 23 p p1 89 p p3 798 结果为 p p1 89p p2 23p p3 798PL SQL过程已成功完成 位置表示法SQL execthree parms 12 23 432 结果为 p p1 12p p2 23p p3 432PL SQL过程已成功完成 混合表示法SQL execthree parms 123 3 p p3 789 结果为 p p1 123p p2 3p p3 789PL SQL过程已成功完成 过程 out参数 创建过程SQL createorreplace2procedureuser sal3 4i usernoinnumber 5o usernameoutusers username type 6o saloutusers salary type 7as 过程 8begin9selectusername salary10intoo username o sal11fromusers12whereuserid i userno 13exception14whenno data foundthen15o username null 16o sal 1 17enduser sal 18 过程已创建 过程 执行过程SQL variablenamevarchar2 20 SQL variablesalnumber SQL execuser sal 1 name sal PL SQL过程已成功完成 SQL printname SQL printsal 匿名块调用过程SQL setserveroutonSQL declare2nameusers username type 3salusers salary type 4begin5user sal 1 name sal 6dbms output put line name name 7dbms output put line sal sal 8end 9 name Adminsal 2000PL SQL过程已成功完成 过程 inout参数inout参数能够用于传入参数值 并且从过程中返回值 该参数适用于用户需要向过程传递可以由过程本身修改的input变量的情况 常用于交互历程 SQL createorreplace2procedureswap3 4p parm1inoutnumber 5p parm2inoutnumber6 7as8l tempnumber 过程 9begin10l temp p parm1 11p parm1 p parm2 12p parm2 l temp 13endswap 14 过程已创建 过程 SQL declare2l num1number 111 3l num2number 222 4begin5dbms output put line 交换前的变量值 6dbms output put line l num1 l num1 7dbms output put line l num2 l num2 8swap l num1 l num2 9dbms output put line 交换后的变量值 10dbms output put line l num1 l num1 11dbms output put line l num2 l num2 12end 13 过程 存储过程管理 过程 添加存储过程 过程 修改存储过程 函数 函数与过程非常类似 也是数据库中存储的命名PL SQL程序块 创建函数与创建过程 都遵循同样的规则 函数与过程的安全方式和参数传递也相同 函数的主要特征是它必须返回一个值 定义过程和定义函数的主要区别之一是 定义函数必须使用return子句返回数据 在定义函数的函数体中的任何地方 用户都可以return 子句 注意 这里的expression表达式要等于return数据类型 函数 CREATEFUNCTION语句来创建函数 CREATE ORREPLACE FUNCTION RETURN IS AS BEGINRETURNEND 其中 RETURN子句指定了函数返回值的数据类型RETURN子句将作为函数的结果返回 函数 例 下面介绍一个示例函数GetPwd 此函数的功能是在表Users中根据指定的用户名返回该用户的密码信息 CREATEORREPLACEFUNCTIONUserMan GetPwd nameINUsers UserName Type RETURNUsers UserPwd TypeASoutpwdUsers UserPwd Type BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName name RETURNoutpwd END 函数调用SQL setserverouton SQL declare2varpwdusers userpwd type 3begin4varpwd getpwd Admin 5dbms output put line varpwd 6end 7 结果为 111111PL SQL过程已成功完成 删除函数SQL dropfunctiongetpwd 函数 函数管理 函数 添加函数 函数 修改函数 程序包 程序包可以允许用户从逻辑上组织过程 函数 对象类型等各种内容 程序包是由包的说明部分和包体组成的 CREATEPACKAGE语句来创建包的说明部分 CREATE ORREPLACE PACKAGEIS AS END 声明部分可以包括类型 变量 过程 函数和游标的说明 程序包 例 下面介绍一个示例创建程序包MyPack 它包含前面2小节中的过程ResetPwd和函数GetPwd CREATEORREPLACEPACKAGEMyPackISPROCEDUREResetPwd UserIdINNUMBER FUNCTIONGetPwd nameINUsers UserName Type RETURNUsers UserPwd Type ENDMyPack 程序包 CREATEPACKAGEBODY语句来创建包体部分 CREATEPACKAGEBODYIS AS END 程序包 例 下面创建程序包MyPack的包体体部分 CREATEPACKAGEBODYMyPackISPROCEDUREResetPwd UserIdINNUMBER ASBEGINUPDATEUsersSETUserPwd 111111 WHEREUserId UserId END FUNCTIONGetPwd nameINUsers UserName Type RETURNUsers UserPwd TypeASoutpwdUsers UserPwd Type BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName name RETURNoutpwd END ENDMyPack 程序包 调用程序包中的过程 调用程序包中的函数 例 调用UserMan MyPack GetPwd函数 返回指定用户的密码信息 SETServerOutputON DECLAREvarPwdUsers UserPwd Type BEGINvarPwd MyPack GetPwd Admin dbms output put line varPwd END 程序包 DROPPACKAGEBODY命令删除程序包体 DROPPACKAGEBODYMyPack DROPPACKAGE命令删除程序包的说明部分 DROPPACKAGEMyPack 10 3触发器管理 触发器的基本概念创建及使用触发器 触发器的基本概念 触发器是一类特殊的过程 但是用户不能直接调用触发器 触发器是当特定事件出现时自动执行的代码块 在Oracle系统中 触发器有4个组成部分 触发器名称触发语句触发器限制触发操作 触发器组成部分 触发器名称 触发器名称就是触发器的名称 一般地 应该采用描述性名称来定义触发器名称 在触发器名称中 应该包括触发器执行的时间 执行的操作 涉及的表 涉及的列等 例如 biufer users userid其中 biufer是各行的插入或更新之前 beforeinsertorupdateforeachrow 的英文缩写 adfer是一个每行删除之后激活的触发器 afterdeleteforeachrow users 表名 userid 字段名 触发器组成部分 触发语句 触发语句就是那些导致Oracle执行触发器的事件 触发器事件可以是下面这些内容 INSERT 当指定的表发生插入 INSERT 操作时执行触发器 UPDATE 当指定的表发生修改 UPDATE 操作时执行触发器 DELETE 当指定的表发生删除 DELETE 操作时执行触发器 触发器组成部分 触发器限制 即使导致触发器激活的事件发生了 但是也不意味着Oracle就会执行它 激活事件发生之后 触发器首先判断在触发器上是否存在限制 然后才会在执行触发器之间评估所有的限制 触发器组成部分 触发操作 触发操作是触发器的主体 Oracle会在发生如下两件事情时 才会执行代码块 使用了合适的语句 如果提供了触发器限制 则评估为真 触发器类型 在Oracle系统中 包括了5种类型的触发器 每一种类型的触发器都可以完成不同的任务 这5种类型的触发器是 语句触发器 行触发器 insteadof触发器 系统事件触发器 用户事件触发器 触发器的基本概念 触发时间BEFORE 在指定的事件发生之前执行触发器 AFTER 在指定的事件发生之后执行触发器 触发级别行触发 对触发事件影响的每一行执行触发器 语句触发 对于触发事件只能触发一次 而且不能访问受触发器影响的每一行的值 创建及使用触发器 CREATETRIGGER语句来创建触发器 CREATE ORREPLACE TRIGGER BEFORE AFTER ON FOREACHROW WHEN 语句触发器 例 创建一个对修改表的时间进行日志记录的语句触发器 创建基表createtableusers copyasselectuserid username salaryfromusers 表已创建 语句触发器 创建日志记录表createtableusers copy log whovarchar 20 whendate 表已创建 语句触发器 创建触发器createtriggerbiudfer users copybeforeinsertorupdateordeleteonusers copybegininsertintousers copy logvalues user sysdate end 触发器已创建 语句触发器 触发updateusers copysetsalary salary 1 2 已更新5行 select fromusers copy log Insteadof触发器 insteadof触发器是Oracle系统用来替换所使用的实际语句而执行的触发器 与导致触发器激活的语句执行之前或执行之后执行的语句触发器和行触发器不同 insteadof触发器只适用于视图如果在视图上执行insert操作的用户 且该视图上有insteadof插入触发器 那么Oracle系统就不会执行用户的触发器 然后 用户的触发器就可以执行编码所要执行的所有操作 它可以向基本表或另外的表中插入用户数据 Insteadof触发器 例 在视图上创建一个触发器 使得对视图的数据更新转变为对基表的数据更新 创建视图创建触发器更新视图数据 基表也发生变化 Insteadof触发器 创建视图createorreplaceviewusers viewasselectuserid username salaryfromusers Insteadof触发器 创建触发器createtriggerupdate users viewinsteadofupdateonusers viewbeginupdateuserssetuserid new userid username new username salary new salarywhereuserid old userid end Insteadof触发器 更新视图数据 基表也发生变化updateusers viewsetsalary 3000whereuserid 1 Select fromusers 行触发器 不仅可以建立为表上各个insert或update或delete语句激活的触发器 而且还可以定义受到影响的各个行激活的触发器 这些触发器称为行触发器 行触发器的定义方式与语句触发器类似 但是有两个例外 第一 行触发器要在触发器定义的触发语句中包含foreachrow子句 第二 在before foreachrow触发器中 可以引用受到影响的行值 甚至可以在触发器中设置这些值 行触发器 在行触发器中 用户能够访问受到影响的行值 通过应用列名称的相关名称来实现 前映像的默认相关名称是 old后映像的默认相关名称是 newinsert触发器只有后映像 只可以使用 new值after触发器只有前映像 只可以使用 old值update触发器既有前映像 又有后映像 可以同时使用 new值和 old值 行触发器 创建行触发器 为主键提供默认设置创建表connuserman userman createtableperson personidnumberprimarykey personnamevarchar2 20 行触发器 createsequenceperson seq 序列已创建 createtriggerbifer person personid pkbeforeinsertonpersonforeachrowbeginselectperson seq nextvalinto new personidfromdual end 触发器已创建 行触发器 不提供personid值 该列有值insertintoperson personname values tom 已创建1行 不使用插入值 而使用默认的person seq nextval值 insertintopersonvalues 234 peter 已创建1行 select fromperson 系统事件触发器 Oracle系统还允许开发人员建立与特定系统事件相关的触发器 这些特定事件是 数据库启动数据库关闭服务器错误这些事件都是实例范围的 这些触发器不像