oracle数据库 游标、存储过程和触发器

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

2、语句声明隐式游标，从语句声明隐式游标，从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(DepName);E

3、ND;游标控制语句游标控制语句 （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 () ;【例】打开游标【例】打开游标MyCurMyCur，读取类型为

4、，读取类型为1 1的用户信的用户信息：息：OPEN MyCur(1);游标控制语句游标控制语句（3 3）游标取值语句）游标取值语句FETCHFETCH。游标取值语句。游标取值语句FETCHFETCH的基本语法结构如下：的基本语法结构如下：FETCH INTO ;【例】在打开的游标MyCur的当前位置读取数据：FETCH MyCur INTO varId, 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的记录 FETCH

6、 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 |, 用户名: | varName); -显

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

9、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%NOTFOUND属性

10、属性【例】%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%ISOPEN = F

11、ALSE 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%ROWCOUNT属性

12、属性 【例】只读取前【例】只读取前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 MyCur%

13、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; CLOSE MyCur

14、; -关闭游标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 WHERE UserT

16、ype = 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 |, 用户名: | v

17、ar_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 ;过程过程【例】创建示例过程【例】创建示例过程ResetPw

19、dResetPwd，此过程的功能是，此过程的功能是将表将表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 FUNCTION语句来创建

20、函数：语句来创建函数：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%Type;BE

21、GIN 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.UserPwd%Typ

24、eASoutpwd 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 PA

26、CKAGE UserMan.MyPack;综合训练综合训练- - 针对表针对表scott.t_student,scott.t_student,按下列要求创建并执行子程按下列要求创建并执行子程序序1 1、创建程序包、创建程序包user_manageuser_manage2 2、在、在user_manageuser_manage程序包中创建下列子程序：程序包中创建下列子程序： （1 1）创建过程）创建过程query_utbs,query_utbs,实现实现scott.t_studentscott.t_student表中表中所有记录的查询输出功能；所有记录的查询输出功能； （2 2）分别创建过程）分

27、别创建过程ins_utbs,del_utbs,ins_utbs,del_utbs,实现实现scott.t_studentscott.t_student表中单个记录的添加、删除操作；表中单个记录的添加、删除操作； （3 3）创建函数）创建函数user_logon,user_logon,用于判断学号和姓名是否用于判断学号和姓名是否正确。正确。 10.3触发器管理触发器管理 p 触发器的基本概念触发器的基本概念 p 创建及使用触发器创建及使用触发器 触发器的基本概念触发器的基本概念 触发器触发器(trigger)(trigger)是一些过程，与表关系密是一些过程，与表关系密切，用于保护表中的数据，当

28、一个基表被修改切，用于保护表中的数据，当一个基表被修改(INSERT(INSERT、UPDATEUPDATE或或DELETE)DELETE)时，触发器自动执行，时，触发器自动执行，例如通过触发器可实现多个表间数据的一致性和完例如通过触发器可实现多个表间数据的一致性和完整性。整性。 触发器和应用程序无关。触发器和应用程序无关。触发器的基本概念触发器的基本概念触发器的类型有三种：触发器的类型有三种： (1)(1) DMLDML触发器。触发器。OracleOracle可以在可以在DML(DML(数据操纵语句数据操纵语句) )语句进语句进行触发，可以在行触发，可以在DMLDML操作前或操作后进行触发，

29、并且可操作前或操作后进行触发，并且可以在每个行或该语句操作上进行触发。以在每个行或该语句操作上进行触发。(2)(2) 替代触发器。由于在替代触发器。由于在OracleOracle中不能直接对有两个以上中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它的表建立的视图进行操作，所以给出了替代触发器。它是是OracleOracle专门为进行视图操作的一种处理方法。专门为进行视图操作的一种处理方法。(3)(3) 系统触发器。在系统触发器。在Oracle8iOracle8i时，提供了第三种类型的触时，提供了第三种类型的触发器叫系统触发器。它可以在发器叫系统触发器。它可以在Oracle

30、Oracle数据库系统的时数据库系统的时间中进行触发，如间中进行触发，如OracleOracle数据库的关闭或打开等。数据库的关闭或打开等。触发器的基本概念触发器的基本概念p 触发事件。触发事件。p INSERT。当指定的表发生插入（INSERT）操作时执行触发器。p UPDATE。当指定的表发生修改（UPDATE）操作时执行触发器。1. DELETE。当指定的表发生删除（DELETE）操作时执行触发器。触发器的基本概念触发器的基本概念p 触发时间。触发时间。p BEFORE。在指定的事件发生之前执行触发器。p AFTER。在指定的事件发生之后执行触发器。p 触发级别。触发级别。行触发。对触发

31、事件影响的每一行执行触发器。1. 语句触发。对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值。创建及使用触发器创建及使用触发器 p CREATE TRIGGERCREATE TRIGGER语句来创建触发器：语句来创建触发器：CREATE OR REPLACE TRIGGER BEFORE | AFTER ON FOR EACH ROW WHEN 创建及使用触发器创建及使用触发器 p 创建触发器的限制创建触发器的限制创建触发器有以下限制：(1) 代码大小。触发器代码大小必须小于32K。(2) 触发器中有效语句可以包括DML语句，但不能包括DDL语句。ROLLBACK、COMMIT、

32、SAVEPOINT也不能使用。但是，对于系统触发器(system trigger)可以使用CREATE、ALTER、DROP TABLE和ALTERCOMPILE语句。创建及使用触发器创建及使用触发器 p 触发器触发次序触发器触发次序 Oracle对事件的触发有16种，它们按照一定次序执行：(1) 执行BEFORE语句的触发器；(2) 对于受语句影响的每一行：执行BEFORE语句行级触发器执行DML语句执行AFTER行级触发器。(3) 执行AFTER语句级触发器。创建及使用触发器创建及使用触发器 1、DML语句触发器语句触发器 DML语句触发器是指当执行DML语句时被隐含执行的触发器。 如果在

33、表上针对某种DML操作建立了语句触发器，那么当执行DML操作时会自动执行触发器的相应代码。语法如下：CREATE OR REPLACE TRIGGER trigger_name timing event1 OR event2 OR event3 ON table_name PL/SQL block;创建及使用触发器创建及使用触发器 例例1建立建立BEFOREBEFORE语句触发器，保证学生信息的修语句触发器，保证学生信息的修改只能在工作时间。改只能在工作时间。 CREATE OR REPLACE TRIGGER tr_sec_stuCREATE OR REPLACE TRIGGER tr_se

34、c_stu BEFORE INSERT OR UPDATE OR DELETE ON scott.t_student BEFORE INSERT OR UPDATE OR DELETE ON scott.t_student DECLARE DECLARE - - 可以在这里声明私有变量可以在这里声明私有变量 BEGIN BEGIN IF IF to_char(SYSDATE,DY,nls_date_language=AMERICAN) to_char(SYSDATE,DY,nls_date_language=AMERICAN) in (SAT,SUN) THENin (SAT,SUN) THE

35、N raise_application_error(-20001, raise_application_error(-20001,不能在休息日改变不能在休息日改变学生信息学生信息);); END IF; END IF; END tr_sec_stu; END tr_sec_stu; / / 创建及使用触发器创建及使用触发器 2、DML行触发器行触发器 DML行触发器是指当执行DML操作时，每作用一行被触发一次的触发器。语法如下：CREATE OR REPLACE TRIGGER trigger_name timing event1 OR event2 OR event3 ON table_na

36、me REFERENCING OLD AS old | NEW AS new FOR EACH ROW WHEN condition PL/SQL block;创建及使用触发器创建及使用触发器 例例2假设数据库中增加一新表假设数据库中增加一新表student_HISstudent_HIS，表结构和，表结构和表表t_studentt_student相同，用来存放从相同，用来存放从t_studentt_student表中删除的记表中删除的记录。创建一个触发器，当录。创建一个触发器，当t_studnentt_studnent表被删除一行，把删表被删除一行，把删除的记录写到表除的记录写到表studen

37、t_HISstudent_HIS中。中。 CREATE OR REPLACE TRIGGER del_studentCREATE OR REPLACE TRIGGER del_studentBEFORE DELETE ON scott.t_student BEFORE DELETE ON scott.t_student FOR EACH ROW FOR EACH ROW BEGIN BEGIN INSERT INTO student_HIS VALUES INSERT INTO student_HIS VALUES (:OLD.stuno,:OLD.stuname,:OLD.stusex, (

38、:OLD.stuno,:OLD.stuname,:OLD.stusex, OLD.stubir);OLD.stubir); END del_student; END del_student;创建及使用触发器创建及使用触发器 p 使用使用DML触发器的注意事项触发器的注意事项 当编写当编写DMLDML触发器时，触发器代码不能从触触发器时，触发器代码不能从触发器所对应的基表中读取数据。发器所对应的基表中读取数据。 例如，如果要基于例如，如果要基于S S表建立触发器，该触发表建立触发器，该触发器的执行代码不能包含对器的执行代码不能包含对S S表的查询操作。表的查询操作。创建及使用触发器创建及使用触发

39、器 4、替代（、替代（Instead_of）触发器）触发器 Instead_ofInstead_of用于对视图的用于对视图的DMLDML触发。由于视触发。由于视图有可能由多个表进行关联而成，因而并非所有图有可能由多个表进行关联而成，因而并非所有的关联都是可更新的。的关联都是可更新的。创建及使用触发器创建及使用触发器 5、系统触发器、系统触发器 系统触发器可以在系统触发器可以在DDLDDL或数据库系统事件上或数据库系统事件上被触发。语法格式：被触发。语法格式： CREATE OR REPLACE TRIGGER trigger_nameCREATE OR REPLACE TRIGGER trig

40、ger_name timing event ddl_event OR timing event ddl_event OR database_eventdatabase_event ON DATABASE | SCHEMA ON DATABASE | SCHEMA WHEN condition WHEN condition PL/SQL block; PL/SQL block;创建及使用触发器创建及使用触发器 例例3当用户登录或退出是，将信息记录到表当用户登录或退出是，将信息记录到表log_tablelog_table中。中。 1 1、创建表、创建表log_tablelog_table CREA

41、TE TABLE log_table( CREATE TABLE log_table( username varchar2(20), - username varchar2(20), -记录用户名记录用户名 logon_time date, - logon_time date, - 记录用户登录时间记录用户登录时间 logoff_time date, - logoff_time date, -记录用户退出时间记录用户退出时间 address varchar2(20) - address varchar2(20) -用户登录的用户登录的IPIP地址地址 / /创建及使用触发器创建及使用触发器 例

42、例3当用户登录时，将信息记录到表当用户登录时，将信息记录到表log_tablelog_table中。中。 2 2、创建记录登录信息的触发器、创建记录登录信息的触发器 CREATE OR REPLACE TRIGGER tr_logon CREATE OR REPLACE TRIGGER tr_logon AFTER logon ON DATABASE AFTER logon ON DATABASE BEGIN BEGIN INSERT INTO log_table(username,logon_time,address) INSERT INTO log_table(username,logon

43、_time,address) VALUES(ora_login_user,sysdate,ora_client_ip_addres VALUES(ora_login_user,sysdate,ora_client_ip_address);s); END tr_logon; END tr_logon; / / /* *ora_login_user ora_login_user 返回登录用户名的事件属性函数返回登录用户名的事件属性函数ora_client_ip_address ora_client_ip_address 返回登录用户登录返回登录用户登录IPIP地址的事件属性函数地址的事件属性函数* */ /