《Oracle数据库基础教程第10章人民邮电出版社.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库基础教程第10章人民邮电出版社.ppt（48页珍藏版）》请在金锄头文库上搜索。
1、Oracle 10gOracle 10g数据库应用教程数据库应用教程数据库应用教程数据库应用教程 授课教师：授课教师：职务：职务：第第10章章 游标、存储过程和触发器游标、存储过程和触发器 课程描述课程描述课程描述课程描述介绍介绍OracleOracle数据数据库程序设计中经库程序设计中经常会用到的常会用到的3 3个个概念，即游标、概念，即游标、存储过程和触发存储过程和触发器。器。本章知识点本章知识点p 游标游标 p 存储过程管理存储过程管理 p 触发器管理触发器管理 游标游标 p游标的基本概念游标的基本概念 p游标控制语句游标控制语句 p游标属性游标属性 p游标游标FORFOR循环循环 游标
2、的基本概念游标的基本概念 p游标示意图游标示意图 游标的基本概念游标的基本概念p使用显式游标使用显式游标 （1 1）说明游标。）说明游标。（2 2）打开游标。）打开游标。（3 3）读取数据。）读取数据。（4 4）关闭游标。）关闭游标。游标的基本概念游标的基本概念p隐式游标隐式游标 【例例】使用使用SELECTSELECT语句声明隐式游标，从语句声明隐式游标，从HR.DepartmentsHR.Departments表中读取表中读取Department_nameDepartment_name字段的字段的值到变量值到变量DepNameDepName：SET ServerOutput ON;DECL
3、ARE DepName HR.Departments.Department_Name%Type;BEGINSELECT Department_name INTO DepNameFROM HR.DepartmentsWHERE Department_ID=10;dbms_output.put_line(DepName);END;游标控制语句游标控制语句 （1 1）声明游标语句）声明游标语句CURSORCURSOR：DECLARE CURSOR () IS;【例例】声明一个游标声明一个游标MyCurMyCur，读取指定类型的用，读取指定类型的用户信息：户信息：DECLARE CURSOR MyCu
4、r(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游标控制语句游标控制语句（2 2）打开游标语句）打开游标语句OPENOPEN：OPEN () ;【例例】打开游标打开游标MyCurMyCur，读取类型为，读取类型为1 1的用户信的用户信息：息：OPEN MyCur(1);游标控制语句游标控制语句（3 3）游标取值语句）游标取值语句FETCHFETCH。游标取值语句。游标取值语句FETCHFETCH的基本语法结构如下：的基本语法结构如下：FETCH INTO ;【例】在打开的游标MyC
5、ur的当前位置读取数据：FETCH MyCur INTO varI,d varName;（4 4）关闭游标语句）关闭游标语句CLOSECLOSE：CLOSE ;【例】关闭游标MyCur：CLOSE MyCur;游标控制语句游标控制语句【例【例】下面介绍一个完整的游标应用实例：下面介绍一个完整的游标应用实例：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varId NUMBER; -声明变量,用来保存游标中的用户编号 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 -定义游标, varType为参数, 指定用户类
6、型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 OPEN MyCur(1); -打开游标,参数为1,表示读取用户类型编号为1的记录 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体游标属性游标属性 （
7、1 1）%ISOPEN%ISOPEN属性属性 【例例】下面的代码演示当使用未打开的游标时，将会出现错误：下面的代码演示当使用未打开的游标时，将会出现错误：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE
8、UserType = varType;BEGIN -开始程序体 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体游标属性游标属性【例例】修改上面的程序，在使用游标之前，调用修改上面的程序，在使用游标之前，调用%ISOPEN%ISOPEN属性判断游标是否打开。属性判断游标是否打开。/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声
9、明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(2); END IF; FETCH MyCur INTO varId, varName; -读取当前
10、游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体游标属性游标属性（2 2）%FOUND%FOUND属性和属性和%NOTFOUND%NOTFOUND属性属性【例】%FOUND属性可以循环执行游标读取数据：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号
11、-定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | v
12、arId |, 用户名: | varName); -显示读取的数据 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体游标属性游标属性（3 3）%ROWCOUNT%ROWCOUNT属性属性 【例例】只读取前只读取前2 2行记录：行记录：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号
13、-定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游标属性游标属性BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用
14、户编号: | varId |, 用户名: | varName); -显示读取的数据 IF MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体游标游标FOR循环循环 【例例】声明记录类型声明记录类型User_Record_TypeUser_Record_Type和定义记和定义记录变量录变量var_UserRecordvar_UserRecord：TYPE User_Record_Type IS RECO
15、RD ( UserId Users.UserId%Type, UserName Users.UserName%Type);var_UserRecord User_Record_Type;游标游标FOR循环循环 【例例】PL/SQLPL/SQL记录可以与游标结合使用：记录可以与游标结合使用：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分/* 声明记录类型 */TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);/* 定义
16、记录变量 */var_UserRecord User_Record_Type; -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游标游标FOR循环循环 BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO var_UserRecord; -读取当前游标位置的数据到记录变量var_User
17、Record EXIT WHEN MyCur%NOTFOUND; -当游标指向结果集结尾时退出循环 /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体游标游标FOR循环循环 【例例】典型游标典型游标FORFOR循环的例子：循环的例子：/* 打开显示模式 */SET ServerOutput ON; DECLARE CURSOR
18、MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 FOR var_UserRecord IN MyCur(1) LOOP /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP;END; -结束程序体10.2存储过程管理存储过程管理 p 过程过程 p
19、 函数函数 p 程序包程序包 过程过程pCREATE PROCEDURECREATE PROCEDURE语句来创建过程：语句来创建过程：CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;过程过程【例例】创建示例过程创建示例过程ResetPwdResetPwd，此过程的功能是，此过程的功能是将表将表UsersUsers中指定用户的密码重置为中指定用户的密码重置为111111111111：CREATE OR REPLACE PROCEDURE UserMan.ResetPwd( UserId IN NUMBER)ASBEGIN UPDATE Users
20、SET UserPwd = 111111 WHERE UserId = UserId;END;过程过程p存储过程管理存储过程管理 过程过程p添加存储过程添加存储过程 过程过程p修改存储过程修改存储过程 函数函数 pCREATE FUNCTIONCREATE FUNCTION语句来创建函数：语句来创建函数：CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;函数函数 【例例】下面介绍一个示例函数下面介绍一个示例函数GetPwdGetPwd，此函数的，此函数的功能是在表功能是在表UsersUsers中根据指定的用户名返回该中根据指
21、定的用户名返回该用户的密码信息：用户的密码信息：CREATE FUNCTION UserMan.GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=|name|; RETURN outpwd;END;函数函数 p函数管理函数管理 函数函数 p添加函数添加函数 函数函数 p修改函数修改函数 程序包程序包 pCREATE PACKAGECREATE PAC
22、KAGE语句来创建包的说明部分：语句来创建包的说明部分：CREATE OR REPLACE PACKAGE IS | AS END ;程序包程序包【例例】下面介绍一个示例创建程序包下面介绍一个示例创建程序包MyPackMyPack，它，它包含前面包含前面2 2小节中的过程小节中的过程ResetPwdResetPwd和函数和函数GetPwdGetPwd：CREATE OR REPLACE PACKAGE UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Ty
23、pe )RETURN Users.UserPwd%Type;END MyPack;程序包程序包p程序包管理程序包管理 程序包程序包p添加程序包添加程序包 程序包程序包pCREATE PACKAGE BODYCREATE PACKAGE BODY语句来创建包体部分：语句来创建包体部分：CREATE PACKAGE BODY IS | AS END ;程序包程序包【例例】下面创建程序包下面创建程序包MyPackMyPack的包体体部分：的包体体部分：CREATE PACKAGE BODY UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER)AS
24、BEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId;END;FUNCTION GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=|name|; RETURN outpwd;END;END MyPack;程序包程序包p 程序包管理查看程序包管理查看MyPackMyPack程序包体程
25、序包体 程序包程序包p添加程序包添加程序包 程序包程序包p调用程序包中的过程调用程序包中的过程.p调用程序包中的函数：调用程序包中的函数：. p【例例】调用调用UserMan.MyPack.GetPwdUserMan.MyPack.GetPwd函数，返回指定用户函数，返回指定用户的密码信息：的密码信息：SET ServerOutput ON;DECLARE varPwd Users.UserPwd%Type;BEGIN varPwd:= UserMan.MyPack.GetPwd(Admin); dbms_output.put_line(varPwd);END;程序包程序包pDROP PACK
26、AGE BODYDROP PACKAGE BODY命令删除程序包体：命令删除程序包体：DROP PACKAGE BODY UserMan.MyPack;pDROP PACKAGEDROP PACKAGE命令删除程序包的说明部分：命令删除程序包的说明部分：DROP PACKAGE UserMan.MyPack;10.3触发器管理触发器管理 p 触发器的基本概念触发器的基本概念 p 创建及使用触发器创建及使用触发器 触发器的基本概念触发器的基本概念p触发事件。触发事件。1.INSERT。当指定的表发生插入（INSERT）操作时执行触发器。2.UPDATE。当指定的表发生修改（UPDATE）操作时执
27、行触发器。3.DELETE。当指定的表发生删除（DELETE）操作时执行触发器。触发器的基本概念触发器的基本概念p 触发时间。触发时间。1.BEFORE。在指定的事件发生之前执行触发器。2.AFTER。在指定的事件发生之后执行触发器。p 触发级别。触发级别。1.行触发。对触发事件影响的每一行执行触发器。2.语句触发。对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值。创建及使用触发器创建及使用触发器 pCREATE TRIGGERCREATE TRIGGER语句来创建触发器：语句来创建触发器：CREATE OR REPLACE TRIGGER BEFORE | AFTER ON F
28、OR EACH ROW WHEN 创建及使用触发器创建及使用触发器【例例】创建一个触发器创建一个触发器MyTriggerMyTrigger，它的作用是当表，它的作用是当表USERMAN.UserTypeUSERMAN.UserType中中TypeIdTypeId列的值发生变化时，自动列的值发生变化时，自动更新表更新表USERMAN.UsersUSERMAN.Users中的中的UserTypeUserType列的值，从而保列的值，从而保证数据的完整性：证数据的完整性：CREATE OR REPLACE TRIGGER USERMAN.MyTriggerAFTER UPDATE ON USERMAN.UserTypeFOR EACH ROWBEGIN UPDATE USERMAN.Users SET UserType = :new.TypeId WHERE UserType = :old.TypeId;END;创建及使用触发器创建及使用触发器p触发器管理触发器管理 创建及使用触发器创建及使用触发器p添加触发器添加触发器
