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

Oracle10g数据库应用教程

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

（1）说明游标。（2）打开游标。（3）读取数据。（4）关闭游标。游标的基本概念隐式游标【例】使用SELECT语句声明隐式游标，从HR.Departments表中读取Department_name字段的值到变量DepName：SETServerOutputON;DECLAREDepName

HR.Departments.Department_Name%Type;BEGINSELECTDepartment_nameINTODepNameFROMHR.DepartmentsWHEREDepartment_ID=10;dbms_output.put_line(DepName);END;游标控制语句（1）声明游标语句CURSOR：DECLARECURSOR<游标名>[(<参数列表>)]IS<SELECT语句>;【例】声明一个游标MyCur，读取指定类型的用户信息：DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;游标控制语句（2）打开游标语句OPEN：OPEN<游标名>[(<参数列表>)];【例】打开游标MyCur，读取类型为1的用户信息：OPENMyCur(1);游标控制语句（3）游标取值语句FETCH。游标取值语句FETCH的基本语法结构如下：FETCH<游标名>INTO<变量列表>;【例】在打开的游标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属性【例】下面的代码演示当使用未打开的游标时，将会出现错误：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分

varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名

varIdNUMBER;--声明变量,用来保存游标中的用户编号

--定义游标,varType为参数,指定用户类型编号

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体

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属性可以循环执行游标读取数据：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分

varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名

varIdNUMBER;--声明变量,用来保存游标中的用户编号

--定义游标,varType为参数,指定用户类型编号

CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体

IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据

WHILEMyCur%FOUND--如果当前游标有效，则执行循环

LOOP

dbms_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--如果当前游标有效，则执行循环

LOOP

dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据

IFMyCur%ROWCOUNT=2THENEXIT;ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据

ENDLOOP;CLOSEMyCur;--关闭游标END;--结束程序体游标FOR循环【例】声明记录类型User_Record_Type和定义记录变量var_UserRecord：TYPEUser_Record_TypeISRECORD(UserId

Users.UserId%Type,

UserName

Users.UserName%Type);var_UserRecord

User_Record_Type;游标FOR循环【例】PL/SQL记录可以与游标结合使用：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分/*声明记录类型*/TYPEUser_Record_TypeISRECORD(UserId

Users.UserId%Type,

UserName

Users.UserName%Type);/*定义记录变量*/var_UserRecord

User_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;DECLARECURSORMyCur(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存储过程管理过程函数程序包过程CREATEPROCEDURE语句来创建过程：CREATE[ORREPLACE]PROCEDURE<过程名>

[<参数列表>

]IS|AS[<局部变量声明>]BEGIN<过程体>END[<过程名>];过程【例】创建示例过程ResetPwd，此过程的功能是将表Users中指定用户的密码重置为111111：CREATEORREPLACEPROCEDUREUserMan.ResetPwd(UserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd='111111'WHEREUserId=UserId;END;过程存储过程管理过程添加存储过程过程修改存储过程函数CREATEFUNCTION语句来创建函数：CREATE[ORREPLACE]FUNCTION<函数名>

[<参数列表>

][RETURN<函数数据类型>]IS|AS[<局部变量声明>]BEGIN<过程体>RETURN<函数值>END[<过程名>];函数【例】下面介绍一个示例函数GetPwd，此函数的功能是在表Users中根据指定的用户名返回该用户的密码信息：CREATEFUNCTIONUserMan.GetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwd

Users.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=''||name||'';RETURNoutpwd;END;函数函数管理函数添加函数函数修改函数程序包CREATEPACKAGE语句来创建包的说明部分：CREATE[ORREPLACE]PACKAGE<程序包名>

IS|AS[<声明部分>]END[<程序包名>];程序包【例】下面介绍一个示例创建程序包MyPack，它包含前面2小节中的过程ResetPwd和函数GetPwd：CREATEORREPLACEPACKAGEUserMan.MyPackISPROCEDUREResetPwd(UserIdINNUMBER);FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%Type;ENDMyPack;程序包程序包管理程序包添加程序包程序包CREATEPACKAGEBODY语句来创建包体部分：CREATEPACKAGEBODY<程序包名>

IS|AS[<声明部分>][<过程体>][<函数体>][<初始化部分>]END[<程序包名>];程序包【例】下面创建程序包MyPack的包体体部分：CREATEPACKAGEBODYUserMan.MyPackISPROCEDUREResetPwd(UserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd='111111'WHEREUserId=UserId;END;FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwd

Users.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=''||name||'';RETURNoutpwd;END;ENDMyPack;程序包程序包管理查看MyPack程序包体程序包添加程序包程序包调用程序包中的过程<方案名>.<程序包名>.<过程名>调用程序包中的函数：<方案名>.<程序包名>.<过程名>【例】调用UserMan.MyPack.GetPwd函数，返回指定用户的密码信息：SETServerOutputON;DECLARE

varPwd

Users.UserPwd%Type;BEGIN

varPwd:=UserMan.MyPack.GetPwd('Admin');

dbms_output.put_line(varPwd);END;程序包DROPPACKAGEBODY命令删除程序包体：DROPPACKAGEBODYUserMan.MyPack;DROPPACKAGE命令删除程序包的说明部分：DROPPACKAGEUserMan.MyPack;综合训练--针对表scott.t_student,按下列要求创建并执行子程序1、创建程序包user_manage2、在user_manage程序包中创建下列子程序：（1）创建过程query_utbs,实现scott.t_student表中所有记录的查询输出功能；（2）分别创建过程ins_utbs,del_utbs,实现scott.t_student表中单个记录的添加、删除操作；（3）创建函数user_logon,用于判断学号和姓名是否正确。10.3触发器管理触发器的基本概念创建及使用触发器触发器的基本概念触发器(trigger)是一些过程，与表关系密切，用于保护表中的数据，当一个基表被修改(INSERT、UPDATE或DELETE)时，触发器自动执行，例如通过触发器可实现多个表间数据的一致性和完整性。触发器和应用程序无关。触发器的基本概念触发器的类型有三种：(1) DML触发器。Oracle可以在DML(数据操纵语句)语句进行触发，可以在DML操作前或操作后进行触发，并且可以在每个行或该语句操作上进行触发。(2) 替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方法。(3) 系统触发器。在Oracle8i时，提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发，如Oracle数据库的关闭或打开等。触发器的基本概念触发事件。INSERT。当指定的表发生插入（INSERT）操作时执行触发器。UPDATE。当指定的表发生修改（UPDATE）操作时执行触发器。DELETE。当指定的表发生删除（DELETE）操作时执行触发器。触发器的基本概念触发时间。BEFORE。在指定的事件发生之前执行触发器。AFTER。在指定的事件发生之后执行触发器。触发级别。行触发。对触发事件影响的每一行执行触发器。语句触发。对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值。创建及使用触发器CREATETRIGGER语句来创建触发器：CREATE[ORREPLACE]TRIGGER<触发器名>[BEFORE|AFTER]<触发事件>ON<表名>[FOREACHROW][WHEN<条件表达式>]<PL/SQL程序体>创建及使用触发器创建触发器的限制创建触发器有以下限制：(1) 代码大小。触发器代码大小必须小于32K。(2) 触发器中有效语句可以包括DML语句，但不能包括DDL语句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。但是，对于系统触发器(systemtrigger)可以使用CREATE、ALTER、DROPTABLE和ALTER…COMPILE语句。创建及使用触发器触发器触发次序

Oracle对事件的触发有16种，它们按照一定次序执行：(1)执行BEFORE语句的触发器；(2)对于受语句影响的每一行：执行BEFORE语句行级触发器执行DML语句执行AFTER行级触发器。(3) 执行AFTER语句级触发器。创建及使用触发器1、DML语句触发器

DML语句触发器是指当执行DML语句时被隐含执行的触发器。如果在表上针对某种DML操作建立了语句触发器，那么当执行DML操作时会自动执行触发器的相应代码。语法如下：CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent1[ORevent2ORevent3]ONtable_namePL/SQLblock;创建及使用触发器[例1]建立BEFORE语句触发器，保证学生信息的修改只能在工作时间。

CREATEORREPLACETRIGGERtr_sec_stu

BEFOREINSERTORUPDATEORDELETEONscott.t_student

DECLARE

--可以在这里声明私有变量

BEGIN

IFto_char(SYSDATE,‘DY’,‘nls_date_language=AMERICAN’)in(‘SAT’,‘SUN’)THEN

raise_application_error(-20001,‘不能在休息日改变学生信息');

ENDIF;

ENDtr_sec_stu;

/创建及使用触发器2、DML行触发器

DML行触发器是指当执行DML操作时，每作用一行被触发一次的触发器。语法如下：CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent1[ORevent2ORevent3]ONtable_name[REFERENCINGOLDASold|NEWASnew]FOREACHROW[WHENcondition]PL/SQLblock;创建及使用触发器[例2]假设数据库中增加一新表student_HIS，表结构和表t_student相同，用来存放从t_student表中删除的记录。创建一个触发器，当t_studnent表被删除一行，把删除的记录写到表student_HIS中。

CREATEORREPLACETRIGGERdel_student BEFOREDELETEONscott.t_student

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

Instead_of用于对视图的DML触发。由于视图有可能由多个表进行关联而成，因而并非所有的关联都是可更新的。创建及使用触发器5、系统触发器

系统触发器可以在DDL或数据库系统事件上被触发。语法格式：

CREATE[ORREPLACE]TRIGGERtrigger_nametimingevent[ddl_eventORdatabase_event]ON[DATABASE|SCHEMA][WHENcondition]PL/SQLblock;创建及使用触发器[例3]当用户登录或退出是，将信息记录到表log_table中。

1、创建表log_table

CREATETABLElog_table(

usernamevarchar2(20),--记录用户名

logon_timedate,--记录用户登录时间

logoff_timedate,--记录用户退出时间