Oracle 10g数据库基础教程第10章--游标、存储过程和触发器

1、 第第10章章 游标、存储过程和触发器游标、存储过程和触发器 本章知识点本章知识点p 游标游标 p 存储过程管理存储过程管理 p 触发器管理触发器管理 游标游标 p 游标的基本概念游标的基本概念 p 游标控制语句游标控制语句 p 游标属性游标属性 p 游标游标FORFOR循环循环 游标的基本概念游标的基本概念 p 游标示意图游标示意图 游标的基本概念游标的基本概念p使用显式游标使用显式游标 （1 1）说明游标。）说明游标。（2 2）打开游标。）打开游标。（3 3）读取数据。）读取数据。（4 4）关闭游标。）关闭游标。游标的基本概念游标的基本概念p隐式游标隐式游标 【例】使用【例】使用SELEC

2、TSELECT语句声明隐式游标，从语句声明隐式游标，从HR.DepartmentsHR.Departments表中读取表中读取Department_nameDepartment_name字段的字段的值到变量值到变量DepNameDepName：SET ServerOutput ON;DECLARE DepName HR.Departments.Department_Name%Type;BEGINSELECT Department_name INTO DepNameFROM HR.DepartmentsWHERE Department_ID=10;dbms_output.put_line(Dep

3、Name);END;游标控制语句游标控制语句 （1 1）声明游标语句）声明游标语句CURSORCURSOR：DECLARE CURSOR () IS;【例】声明一个游标【例】声明一个游标MyCurMyCur，读取指定类型的，读取指定类型的用户信息：用户信息：DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游标控制语句游标控制语句（2 2）打开游标语句）打开游标语句OPENOPEN：OPEN () ;【例】打开游标【例】打开游标MyCurMyCu

4、r，读取类型为，读取类型为1 1的用户信的用户信息：息：OPEN MyCur(1);游标控制语句游标控制语句（3 3）游标取值语句）游标取值语句FETCHFETCH。游标取值语句。游标取值语句FETCHFETCH的基本语法结构如下：的基本语法结构如下：FETCH INTO ;【例】在打开的游标MyCur的当前位置读取数据：FETCH MyCur INTO varI,d varName;（4 4）关闭游标语句）关闭游标语句CLOSECLOSE：CLOSE ;【例】关闭游标MyCur：CLOSE MyCur;游标控制语句游标控制语句【例【例】下面介绍一个完整的游标应用实例：】下面介绍一个完整的游标

5、应用实例：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varId NUMBER; -声明变量,用来保存游标中的用户编号 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 OPEN MyCur(1); -打开游标,参数为1,表示读取用户类型编号为1的记

6、录 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体游标属性游标属性 （1 1）%ISOPEN%ISOPEN属性属性 【例】下面的代码演示当使用未打开的游标时，将会出现错误：【例】下面的代码演示当使用未打开的游标时，将会出现错误：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50);

7、 -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varNa

8、me); -显示读取的数据END; -结束程序体游标属性游标属性【例】修改上面的程序，在使用游标之前，调用【例】修改上面的程序，在使用游标之前，调用%ISOPEN%ISOPEN属性判断游标是否打开。属性判断游标是否打开。/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT U

9、serId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(2); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体游标属性游标属性（2 2）%FOUND%FOUND属性和属性和%NOTFOUND%NOT

10、FOUND属性属性【例】%FOUND属性可以循环执行游标读取数据：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 IF MyCur%ISO

11、PEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体游标属性游标属性（3 3）%ROWCOUNT%ROW

12、COUNT属性属性 【例】只读取前【例】只读取前2 2行记录：行记录：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;游标属性游标属性BEGIN -开始程序体 IF

13、 MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 WHILE MyCur%FOUND -如果当前游标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据 IF MyCur%ROWCOUNT = 2 THEN EXIT; END IF; FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 END LOOP; CLOS

14、E MyCur; -关闭游标END; -结束程序体游标游标FOR循环循环 【例】声明记录类型【例】声明记录类型User_Record_TypeUser_Record_Type和定义记和定义记录变量录变量var_UserRecordvar_UserRecord：TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);var_UserRecord User_Record_Type;游标游标FOR循环循环 【例】【例】PL/SQLPL/SQL记录可以与游标结合使用：记录可以与游

15、标结合使用：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分/* 声明记录类型 */TYPE User_Record_Type IS RECORD ( UserId Users.UserId%Type, UserName Users.UserName%Type);/* 定义记录变量 */var_UserRecord User_Record_Type; -定义游标, varType为参数, 指定用户类型编号 CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHER

16、E UserType = varType;游标游标FOR循环循环 BEGIN -开始程序体 IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); END IF; LOOP FETCH MyCur INTO var_UserRecord; -读取当前游标位置的数据到记录变量var_UserRecord EXIT WHEN MyCur%NOTFOUND; -当游标指向结果集结尾时退出循环 /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用

17、户名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体游标游标FOR循环循环 【例】典型游标【例】典型游标FORFOR循环的例子：循环的例子：/* 打开显示模式 */SET ServerOutput ON; DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 FOR var_UserRecord IN MyCur(1) LOOP /*

18、显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP;END; -结束程序体10.2存储过程管理存储过程管理 p 过程过程 p 函数函数 p 程序包程序包 过程过程pCREATE PROCEDURECREATE PROCEDURE语句来创建过程：语句来创建过程：CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;过程过程【例】创建示例过程【例】创建示例过程

19、ResetPwdResetPwd，此过程的功能是，此过程的功能是将表将表UsersUsers中指定用户的密码重置为中指定用户的密码重置为111111111111：CREATE OR REPLACE PROCEDURE UserMan.ResetPwd( UserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId;END;过程过程p存储过程管理存储过程管理 过程过程p添加存储过程添加存储过程 过程过程p修改存储过程修改存储过程 函数函数 pCREATE FUNCTIONCREATE FUNCTI

20、ON语句来创建函数：语句来创建函数：CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;函数函数 【例】下面介绍一个示例函数【例】下面介绍一个示例函数GetPwdGetPwd，此函数的，此函数的功能是在表功能是在表UsersUsers中根据指定的用户名返回该中根据指定的用户名返回该用户的密码信息：用户的密码信息：CREATE FUNCTION UserMan.GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%

21、Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=|name|; RETURN outpwd;END;函数函数 p函数管理函数管理 函数函数 p添加函数添加函数 函数函数 p修改函数修改函数 程序包程序包 pCREATE PACKAGECREATE PACKAGE语句来创建包的说明部分：语句来创建包的说明部分：CREATE OR REPLACE PACKAGE IS | AS END ;程序包程序包【例】下面介绍一个示例创建程序包【例】下面介绍一个示例创建程序包MyPackMyPack，它，它包含前面包含前面2 2小

22、节中的过程小节中的过程ResetPwdResetPwd和函数和函数GetPwdGetPwd：CREATE OR REPLACE PACKAGE UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Type )RETURN Users.UserPwd%Type;END MyPack;程序包程序包p程序包管理程序包管理 程序包程序包p添加程序包添加程序包 程序包程序包pCREATE PACKAGE BODYCREATE PACKAGE BODY语句来创建包体部分

23、：语句来创建包体部分：CREATE PACKAGE BODY IS | AS END ;程序包程序包【例】下面创建程序包【例】下面创建程序包MyPackMyPack的包体体部分：的包体体部分：CREATE PACKAGE BODY UserMan.MyPackISPROCEDURE ResetPwd( UserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = UserId;END;FUNCTION GetPwd( name IN Users.UserName%Type )RETURN Users.User

24、Pwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName=|name|; RETURN outpwd;END;END MyPack;程序包程序包p 程序包管理查看程序包管理查看MyPackMyPack程序包体程序包体 程序包程序包p添加程序包添加程序包 程序包程序包p调用程序包中的过程调用程序包中的过程.p调用程序包中的函数：调用程序包中的函数：. p【例】调用【例】调用UserMan.MyPack.GetPwdUserMan.MyPack.GetPwd函数，返回

25、指定用户函数，返回指定用户的密码信息：的密码信息：SET ServerOutput ON;DECLARE varPwd Users.UserPwd%Type;BEGIN varPwd:= UserMan.MyPack.GetPwd(Admin); dbms_output.put_line(varPwd);END;程序包程序包pDROP PACKAGE BODYDROP PACKAGE BODY命令删除程序包体：命令删除程序包体：DROP PACKAGE BODY UserMan.MyPack;pDROP PACKAGEDROP PACKAGE命令删除程序包的说明部分：命令删除程序包的说明部分：DROP PACKAGE UserMan.MyPack;10.3触发器管理触发器管理 p 触发器的基本概念触发器的基本概念 p 创建及使用触发器创建及使用触发器 触发器的基本概念触发器的基本概念p 触发事件。触