游标、存储过程和触发器

1、第第10章章 游标、存储过程和触发器游标、存储过程和触发器 课程描述课程描述介绍介绍OracleOracle数据库程数据库程序设计中经常会用到序设计中经常会用到的的3 3个概念，即游标、个概念，即游标、存储过程和触发器。存储过程和触发器。本章知识点本章知识点p 游标游标 p 存储过程管理存储过程管理 p 触发器管理触发器管理 游标游标 p 游标的基本概念游标的基本概念 p 游标控制语句游标控制语句 p 游标属性游标属性 p 游标游标FORFOR循环循环 游标的基本概念游标的基本概念 p 游标示意图游标示意图 游标的基本概念游标的基本概念p使用显式游标使用显式游标 （1 1）声明游标。）声明游标

2、。（2 2）打开游标。）打开游标。（3 3）读取数据。）读取数据。（4 4）关闭游标。）关闭游标。游标的基本概念游标的基本概念p隐式游标隐式游标 【例】使用【例】使用SELECTSELECT语句声明隐式游标，从语句声明隐式游标，从HR.DepartmentsHR.Departments表中读取表中读取Department_nameDepartment_name字段的值到变量字段的值到变量DepNameDepName：SET ServerOutput ON;DECLARE DepName HR.Departments.Department_Name%Type;BEGINSELECT Depart

3、ment_name INTO DepNameFROM HR.DepartmentsWHERE Department_ID=10;dbms_output.put_line(DepName);END;类型描述符游标控制语句游标控制语句 （1 1）声明游标语句）声明游标语句CURSORCURSOR：DECLARE CURSOR () IS;【例】声明一个游标【例】声明一个游标MyCurMyCur，读取指定类型的用户，读取指定类型的用户信息：信息：DECLARE CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHE

4、RE UserType = varType;游标控制语句游标控制语句（2 2）打开游标语句）打开游标语句OPENOPEN：OPEN () ;【例】打开游标【例】打开游标MyCurMyCur，读取类型为，读取类型为1 1的用户信的用户信息：息：OPEN MyCur(1);游标控制语句游标控制语句（3 3）游标取值语句）游标取值语句FETCHFETCH。游标取值语句。游标取值语句FETCHFETCH的基本语法结构如下：的基本语法结构如下：FETCH INTO ;【例】在打开的游标MyCur的当前位置读取数据：FETCH MyCur INTO varI,d varName;（4 4）关闭游标语句）关

5、闭游标语句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 |,

7、 用户名用户名: | varName); -显示读取的数据显示读取的数据END; -结束程序体结束程序体游标属性游标属性 （1 1）%ISOPEN%ISOPEN属性属性 【例】下面的代码演示当使用未打开的游标时，将会出现错误：【例】下面的代码演示当使用未打开的游标时，将会出现错误：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, varType为参数, 指定用户类型编号 CURSOR My

8、Cur(varType NUMBER) IS SELECT UserId, UserName FROM Users WHERE UserType = varType;BEGIN -开始程序体 FETCH MyCur INTO varId, varName; -读取当前游标位置的数据 CLOSE MyCur; -关闭游标 dbms_output.put_line(用户编号: | varId |, 用户名: | varName); -显示读取的数据END; -结束程序体游标属性游标属性【例】修改上面的程序，在使用游标之前，调用【例】修改上面的程序，在使用游标之前，调用%ISOPEN%ISOPEN属

9、性判断游标是否打开。属性判断游标是否打开。/* 打开显示模式 */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%ISOPEN = FALSE

10、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%NOTFOUND属性属性【例】【例】%FOUND%FOUND属性可以循环执行游标读取数据：属性可以循环执行游标读取数据：/ /* * 打开显示模式打开显示模式 * */ /SET Se

11、rverOutput ON; SET ServerOutput ON; DECLARE -DECLARE -开始声明部分开始声明部分 varName VARCHAR2(50); - varName VARCHAR2(50); -声明变量声明变量, ,用来保存游标中的用户名用来保存游标中的用户名 varId NUMBER; - varId NUMBER; -声明变量声明变量, ,用来保存游标中的用户编号用来保存游标中的用户编号 - -定义游标定义游标, varType, varType为参数为参数, , 指定用户类型编号指定用户类型编号 CURSOR MyCur(varType NUMBER)

12、IS CURSOR MyCur(varType NUMBER) IS SELECT UserId, UserName FROM Users SELECT UserId, UserName FROM Users WHERE UserType = varType; WHERE UserType = varType;BEGIN -BEGIN -开始程序体开始程序体 IF MyCur%ISOPEN = FALSE Then IF MyCur%ISOPEN = FALSE Then OPEN MyCur(1); OPEN MyCur(1); END IF; END IF; FETCH MyCur INT

13、O varId, varName; - FETCH MyCur INTO varId, varName; -读取当前游标位置的数据读取当前游标位置的数据 WHILE MyCur%FOUND - WHILE MyCur%FOUND -如果当前游标有效，则执行循环如果当前游标有效，则执行循环 LOOP LOOP dbms_output.put_line( dbms_output.put_line(用户编号用户编号: | varId |, : | varId |, 用户名用户名: | varName); -: | varName); -显示读取的数据显示读取的数据 FETCH MyCur INTO

14、varId, varName; - FETCH MyCur INTO varId, varName; -读取当前游标位置的数据读取当前游标位置的数据 END LOOP; END LOOP; CLOSE MyCur; - CLOSE MyCur; -关闭游标关闭游标END; -END; -结束程序体结束程序体游标属性游标属性（3 3）%ROWCOUNT%ROWCOUNT属性属性 【例】只读取前【例】只读取前2 2行记录：行记录：/* 打开显示模式 */SET ServerOutput ON; DECLARE -开始声明部分 varName VARCHAR2(50); -声明变量,用来保存游标中的

15、用户名 varId NUMBER; -声明变量,用来保存游标中的用户编号 -定义游标, 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 -如果当前游

16、标有效，则执行循环 LOOP dbms_output.put_line(用户编号: | 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_U

17、serRecord：TYPE User_Record_Type IS RECORD ( 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%

18、Type, UserName Users.UserName%Type);/* 定义记录变量 */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

19、var_UserRecord; -读取当前游标位置的数据到记录变量var_UserRecord EXIT WHEN MyCur%NOTFOUND; -当游标指向结果集结尾时退出循环 /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); END LOOP; CLOSE MyCur; -关闭游标END; -结束程序体游标游标FOR循环循环 【例】典型游标【例】典型游标FORFOR循环的例子：循环的例子：/* 打开

20、显示模式 */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 /* 显示保存在记录变量var_UserRecord中的数据 */ dbms_output.put_line(用户编号: | var_UserRecord.UserId |, 用户名: | var_UserRecord.UserName); EN

21、D LOOP;END; -结束程序体10.2存储过程管理存储过程管理 p 过程过程 一种基本的存储过程，由过程名、参数和程序体组成。p 函数函数 与过程类似，只是函数有返回值。p 程序包程序包 一组相关的PL/SQL过程和函数，由包名、说明部分和包体组成。p FUNCTION,PROCEDURE和和PACKAGE区别区别 function 和procedure是PL/SQL代码的集合，通常为了完成一个任务。procedure 不需要返回任何值而function将返回一个值，Package是为了完成一个商业功能的一组function和proceudre的集合过程过程pCREATE PROCEDU

22、RECREATE PROCEDURE语句来创建过程：语句来创建过程：CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;p参数声明格式：参数声明格式： IN | OUT | IN OUT :=过程过程【例】创建示例过程【例】创建示例过程ResetPwdResetPwd，此过程的功能是，此过程的功能是将表将表UsersUsers中指定用户的密码重置为中指定用户的密码重置为111111111111：CREATE OR REPLACE PROCEDURE UserMan.ResetPwd( varUserId IN NUMBER)ASBEGIN UPDATE

23、 Users SET UserPwd = 111111 WHERE UserId = varUserId;END;过程过程p存储过程管理存储过程管理 过程过程p添加存储过程添加存储过程 过程过程p修改存储过程修改存储过程 函数函数 pCREATE FUNCTIONCREATE FUNCTION语句来创建函数：语句来创建函数：CREATE OR REPLACE FUNCTION RETURN IS | AS BEGIN RETURN END ;CREATE OR REPLACE PROCEDURE IS | AS BEGIN END ;函数函数 【例】下面介绍一个示例函数【例】下面介绍一个示例函

24、数GetPwdGetPwd，此函数的功能是，此函数的功能是在表在表UsersUsers中根据指定的用户名返回该用户的密码信中根据指定的用户名返回该用户的密码信息：息：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函数管理函数管理 函数函

25、数 p添加函数添加函数 函数函数 p修改函数修改函数 程序包程序包 p CREATE PACKAGE语句来语句来创建包的说明部分创建包的说明部分：CREATE OR REPLACE PACKAGE IS | AS END ;-可以包括类型、变量、过程、函数和游标的说明程序包程序包【例】下面介绍一个示例创建程序包【例】下面介绍一个示例创建程序包MyPackMyPack，它包含，它包含前面前面2 2小节中的过程小节中的过程ResetPwdResetPwd和函数和函数GetPwdGetPwd：CREATE OR REPLACE PACKAGE UserMan.MyPackISPROCEDURE Re

26、setPwd( UserId IN NUMBER);FUNCTION GetPwd ( name IN Users.UserName%Type )RETURN Users.UserPwd%Type;END MyPack;程序包程序包p程序包管理程序包管理 程序包程序包p添加程序包添加程序包 程序包程序包pCREATE PACKAGE BODYCREATE PACKAGE BODY语句来语句来创建包体创建包体部分：部分：CREATE PACKAGE BODY IS | AS END ;程序包程序包【例】下面创建程序包【例】下面创建程序包MyPackMyPack的包体体部分：的包体体部分：CREA

27、TE PACKAGE BODY UserMan.MyPackISPROCEDURE ResetPwd( varUserId IN NUMBER)ASBEGIN UPDATE Users SET UserPwd = 111111 WHERE UserId = varUserId;END;FUNCTION GetPwd( name IN Users.UserName%Type )RETURN Users.UserPwd%TypeASoutpwd Users.UserPwd%Type;BEGIN SELECT UserPwd INTO outpwd FROM Users WHERE UserName

28、=|name|; RETURN outpwd;END;END MyPack;程序包程序包p 程序包管理查看程序包管理查看MyPackMyPack程序包体程序包体 程序包程序包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;程序包程序包p DROP PACKAGE BODY DROP PACKAGE BODY命令删除程序包体：命令删除程序包体：DROP PACKAGE BODY UserMan.MyPack;p DROP PACKAGE DROP PACKAGE命令删除程序包的说明部分：命令删除程序包的说明部分：DR