Oracle-12c数据库基础教程-Oracle-12c数据库游标、存储过程和触发器.pptx

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,11/7/2009,#,Oracle12c数据库基础教程（第3版）,授课教师：,职务：,第10章游标,存储过程与触发器,学前提示,本章将介绍Oracle数据库,程序设计中经常会用到地3,个概念,即游标,存储过程,与触发器。,知识要点,10.1游,10.2存储过程,10.3触,标,发,器,10.1游,标,10.3.1触发器地基本概念,10.3.2创建触发器,10.1.3游标属性,10.1.4游标FOR循环,10.1.5引用游标,10.1.6管理游标结果集,10.1.1游标地基本概念,Oracle游标可以分为两种类型,显式游标与隐式游标,使用显式游标需求以下4个步骤,（1）声明游标。定义游标名和游标中使用地SELECT语句,。,（2）打开游标。执行声明游标时定义地SELECT语句,把查,询结果装入内存,游标位于结果集地第1条记录位置。,（3）读取数据。从结果集地游标当前位置处读取数据,执,行完成后游标后移一行。,（4）关闭游标。释放结果集与游标占用地内存空间。,【例10.1】,使用SELECT语句声明隐式游标,从C#HRSYS.Departments表中读取,Dep_name字段地值到变量DepName,代码如下：,SETServerOutputON;,DECLAREDepNameC#HRSYS.Departments.Dep_Name%Type;,BEGIN,SELECTDep_nameINTODepName,FROMC#HRSYS.Departments,WHEREDep_ID=1;,dbms_output.put_line(DepName);,END;,/,运行结果为：,人事部,PL/SQL过程已成功完成。,10.1.2游标控制语句,1声明游标语句CURSOR,2打开游标语句OPEN,3游标取值语句FETCH,4关闭游标语句CLOSE,1声明游标语句CURSOR,声明一个游标MyCur,读取指定部门地员工信息,代码如,下：,DECLARECURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,1声明常量,OPEN游标名(参数列表);,【例10.3】,户信息,代码如下：,打开游标MyCur,读取类型为1地用,OPENMyCur(1);,3游标取值语句FETCH,游标取值语句FETCH地基本语法结构如下：,FETCH游标名INTO变量列表;,【例10.4】,在打开地游标MyCur地当前位置读取数据,代码如下：,FETCHMyCurINTOvarId,varName;,显式游标必须事先打开,才能使用FETCH语句取值,否则,会出现错误。,4关闭游标语句CLOSE,关闭游标语句CLOSE地基本语法结构如下：,CLOSE游标名;,【例10.5】,关闭游标MyCur,代码如下：,CLOSEMyCur;,显式游标使用完后,应该和时关闭,从而释放存,储空间。,【例10.6】,下面介绍一个完整地游标应,用实例,/*打开显示模式*/,SETServerOutputON;,DECLARE-开始声明部分,varIdNUMBER;-声明变量,用来保存游标中地用户编号,varNameVARCHAR2(50);-声明变量,用来保存游标中地用户名,-定义游标,varDepid为参数,指定员工部门编号,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,OPENMyCur(1);-打开游标,参数为1,表示读取部门编号为1地记录,FETCHMyCurINTOvarId,varName;-读取当前游标位置地数据,CLOSEMyCur;-关闭游标,dbms_output.put_line(员工编号:|varId|,姓名:|varName);-显,示读取地数据,END;-结束程序体,/,运行结果为,员工编号:1,姓名:张老三,PL/SQL过程已成功完成。,10.1.3游标属性,1,2,3,%ISOPEN属性,%FOUND属性与%NOTFOUND属性,%ROWCOUNT属性,1,%ISOPEN属性,%ISOPEN属性判断游标是否被打开,如果游,标被打开,则%ISOPEN于TRUE,否则%ISOPEN,于FALSE。,【例10.7】,下面地代码演示当使用未打开地游标时,将会出现错误。,/*打开显示模式*/,SETServerOutputON;,DECLARE-开始声明部分,varNameVARCHAR2(50);-声明变量,用来保存游标,中地用户名,varIdNUMBER;-声明变量,用来保存游标中地用户,编号,3分支语句CASE,-定义游标,varDepid为参数,指定部门编号,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,FETCHMyCurINTOvarId,varName;-读取当前游标位置地数据,CLOSEMyCur;-关闭游标,dbms_output.put_line(员工编号:|varId|,姓名:|,varName);-显示读取地数据,END;-结束程序体,/,运行结果为：,ORA-01001:无效地游标,ORA-06512:在line9,【例10.8】,修改上面地程序,在使用游标之前,调用%ISOPEN属性判断游标是,否打开。,/*打开显示模式*/,SETServerOutputON;,DECLARE-开始声明部分,varNameVARCHAR2(50);-声明变量,用来保存游标中地用,户名,varIdNUMBER;-声明变量,用来保存游标中地用户编号,-定义游标,varDepid为参数,指定部门编号,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,IFMyCur%ISOPEN=FALSEThen,OPENMyCur(2);,ENDIF;,FETCHMyCurINTOvarId,varName;-读取,当前游标位置地数据,CLOSEMyCur;-关闭游标,dbms_output.put_line(用户编号:|,varId|,用户名:|varName);-显示,读取地数据,END;-结束程序体,2,%FOUND属性与%NOTFOUND属性,%FOUND属性判断游标所在地行是否有效,如,果有效,则%FOUND于TRUE,否则%FOUND于,FALSE。,【例10.9】,使用%FOUND属性可以循环执行游标读取数据,请看下面地程序。,/*打开显示模式*/,SETServerOutputON;,DECLARE-开始声明部分,varNameVARCHAR2(50);-声明变量,用来保存游标中地用户,名,varIdNUMBER;-声明变量,用来保存游标中地用户编号,-定义游标,varDepid为参数,指定部门编号,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,IFMyCur%ISOPEN=FALSEThen,OPENMyCur(1);-读取部门编号为1地员工记录,ENDIF;,FETCHMyCurINTOvarId,varName;-读取当前游标位置地数,据,WHILEMyCur%FOUND-如果当前游标有效,则执行循环,LOOP,dbms_output.put_line(员工编号:|varId|,姓名:,|varName);-显示读取地数据,FETCHMyCurINTOvarId,varName;-读取当前游标位置地,数据,ENDLOOP;,CLOSEMyCur;-关闭游标,END;-结束程序体,运行结果,员工编号:1,姓名:张老三,员工编号:2,姓名:李四,员工编号:3,姓名:王五,3,%ROWCOUNT属性,【例10.10】,代码如下。,/*打开显示模式*/,SETServerOutputON;,DECLARE-开始声明部分,在例10.6程序中,只读取前2行记录,程序,varNameVARCHAR2(50);-声明变量,用来保存游标,中地用户名,varIdNUMBER;-声明变量,用来保存游标中地用户,编号,-定义游标,varDepid为参数,指定部门编号,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,IFMyCur%ISOPEN=FALSEThen,OPENMyCur(1);,ENDIF;,FETCHMyCurINTOvarId,varName;-读取当前游标位置地数据,WHILEMyCur%FOUND-如果当前游标有效,则执行循环,LOOP,dbms_output.put_line(员工编号:|varId|,姓名:|varName);-,显示读取地数据,IFMyCur%ROWCOUNT=2THEN,EXIT;,ENDIF;,FETCHMyCurINTOvarId,varName;-读取当前游标位置地数据,ENDLOOP;,CLOSEMyCur;-关闭游标,END;-结束程序体,/,运行结果,员工编号:1,姓名:张老三,员工编号:2,姓名:李四,PL/SQL过程已成功完成。,10.1.4游标FOR循环,TYPE记录类型名ISRECORD,(字段声明,字段声明);,定义记录变量地方法与定义普通变量地方法相同,记录变量名记录变量类型,【例10.11】,录变量var_UserRecord,代码如下：,声明记录类型User_Record_Type与定义记,TYPEUser_Record_TypeISRECORD,(UserId,Users.UserId%Type,UserName,Users.UserName%Type);,var_UserRecordUser_Record_Type;,可以使用下面地方法访问记录中地字段,记录名.字段名,例如var_UserRecord.UserId与,var_UserRecord.UserName。,如果要声明地记录类型与某个表或视图地结构完,全相同,则可以直接使用%ROWTYPE属性来定义记,录变量,语法如下：,变量名,表名.%ROWTYPE;,【例10.12】,定义一个与表User结构完全相同地记录变量,var_UserRecord1,代码如下：,var_UserRecord1User%ROWTYPE;,使用这种方法定义记录变量,不需求声明记录类,型。记录变量地字段名与对应表或视图中地列名,一致,而且拥有相同地数据类型。,【例10.13】,/*打开显示模式*/,SETServerOutputON;,DECLARE-开始声明部分,/*声明记录类型*/,TYPEEmployees_Record_TypeISRECORD,(EmpId,C#HRSYS.Employees.Emp_Id%Type,EmpName,C#HRSYS.Employees.Emp_Name%Type);,/*定义记录变量*/,var_EmpRecordEmployees_Record_Type;,-定义游标,varDepid为参数,指定部门编号,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,IFMyCur%ISOPEN=FALSEThen,OPENMyCur(1);,ENDIF;,LOOP,FETCHMyCurINTOvar_EmpRecord;-读取当前游标位置地数据到,记录变量var_EmpRecord,EXITWHENMyCur%NOTFOUND;-当游标指向结果集结尾时退出循环,/*显示保存在记录变量var_EmpRecord中地数据*/,dbms_output.put_line(员工编号:|var_EmpRecord.EmpId,|,用户名:|var_EmpRecord.EmpName);,ENDLOOP;,CLOSEMyCur;-关闭游标,END;-结束程序体,/,运行结果,员工编号:1,用户名:张老三,员工编号:2,用户名:李四,员工编号:3,用户名:王五,PL/SQL过程已成功完成。,典型游标FOR循环地语法,FORINLOOP,语句1;,语句2;,语句n;,ENDLOOP;,【例10.14】,下面是一个典型游标FOR,循环地例子,/*打开显示模式*/,SETServerOutputON;,DECLARE,CURSORMyCur(varDepidNUMBER)IS,SELECTEmp_Id,Emp_NameFROMC#HRSYS.Employees,WHEREDep_id=varDepid;,BEGIN-开始程序体,FORvar_EmpRecordINMyCur(1)LOOP,/*显示保存在记录变量var_EmpRecord中地数据*/,dbms_output.put_line(员工编号:|var_EmpRecord.Emp_Id,|,用户名:|var_EmpRecord.Emp_Name);,ENDLOOP;,END;-结束程序体,/,运行结果,员工编号:2,用户名:李四,员工编号:3,用户名:王五,PL/SQL过程已成功完成。,带子查询地游标FOR循环地语法,FORINLOOP,语句1;,语句2;,语句n;,ENDLOOP;,【例10.15】,/*打开显示模式*/,SETServerOutputON;,BEGIN-开始程序体,FORvar_EmpRecordIN(SELECTEmp_Id,Emp_Name,FROMC#HRSYS.Employees,WHEREDep_id=1),LOOP,/*显示保存在记录变量var_EmpRecord中地数据*/,dbms_output.put_line(部门编号:|,var_EmpRecord.Emp_Id|,姓名:|,var_EmpRecord.Emp_Name);,ENDLOOP;,END;-结束程序体,10.1.5引用游标,引用游标（REF游标）是一种动态游标,它比普,通地静态游标更加灵活,因为它不依赖指定地,查询语句。换言之,引用游标在运行时可以与,不同地查询语句相关联,也可以使用游标变量,。,引用游标有两种类型,即强型游标与弱型游标,。强型游标返回指定格式地结果集,而弱型游,标则没有返回类型。,提示,除非有特殊地需求,建议在声明引,用游标时指定返回类型,因为这样,减小出现错误地概率。,使用引用游标地基本方法,DECLARE,TYPEcursor_typeISREFCURSORRETURNreturn_type;,cursor_variablecursor_type;,single_recordreturn_type;,OPENcursor_variableFORSELECT语句;,LOOP,FETCHcursor_variable;,EXITWHENcursor_variable%NOTFOUND;,.;-处理得到地行,ENDLOOP;,CLOSEcursor_variable;,具体说明如下,cursor_type指定引用游标地名称。,return_type指定返回结果地类型。,cursor_variable是定义地引用游标变量,single_record是返回结果变量。,使用OPEN语句打开并查询游标变量。,使用FETCH语句获取游标中地数据。,使用%NOTFOUND属性判断游标中结果集地当前,行是否有效。,使用CLOSE语句关闭游标。,【例10.16】,下面是一个弱型游标地实例,SETServerOutputON;,DECLARE,TYPERefCurISREFCURSOR;-声明引用游标类型游标返回地类型没有限制,EmpCurRefCur;-定义游标变量,EmpRowC#HRSYS.Employees%ROWTYPE;-存储游标查询得到地员工结果集,DepRowC#HRSYS.Departments%ROWTYPE;-存储游标查询得到地部门结果集,flagint:=2;-根据flag变量值地不同,与引用游标相关联地SQL语句也不同,BEGIN,IFflag=0THEN,OPENEmpCurFORSELECT*FROMC#HRSYS.EmployeesWHEREWage,=3000;,ELSIFflag=2THEN,-因为弱类型游标对目标表没有限制,数据可以,使来自任何表,所以这里绑定到表Departments,OPENEmpCurFORSELECT*FROM,C#HRSYS.Departments;,ELSE,OPENEmpCurFORSELECT*FROM,C#HRSYS.Employees;,ENDIF;,LOOP,IFflag=2THEN,FETCHEmpCurINTODepRow;,EXITWHENEmpCur%NOTFOUND;-如果没有查询到数据就退,出,DBMS_output.put_line(Dep_name=|DepRow.Dep_name);,ELSE,FETCHEmpCurINTOEmpRow;,EXITWHENEmpCur%NOTFOUND;-如果没有查询到数据就退,出,DBMS_output.put_line(name=|EmpRow.Emp_name|,Wage=|EmpRow.Wage);,ENDIF;,ENDLOOP;,CLOSEEmpCur;,END;,/,将flag设置为0时地运行结果,将flag设置为1时地运行结果,将flag设置为2时地运行结果,【例10.17】,SETServerOutputON;,DECLARE,-声明引用游标类型,只能返回C#HRSYS.Employees类型地记,录,TYPERefEmpCurISREFCURSORRETURN,C#HRSYS.Employees%ROWTYPE;,EmpCurRefEmpCur;-定义游标变量,EmpRowC#HRSYS.Employees%ROWTYPE;-存储游标查询得到,地员工结果集,flagint:=0;-根据flag变量值地不同,与引用游标相关,联地SQL语句也不同,BEGIN,IFflag=0THEN,OPENEmpCurFORSELECT*FROMC#HRSYS.Employees,WHEREWage=3000;,ELSE,OPENEmpCurFORSELECT*FROMC#HRSYS.Employees;,ENDIF;,LOOP,FETCHEmpCurINTOEmpRow;,EXITWHENEmpCur%NOTFOUND;-如,果没有查询到数据就退出,DBMS_output.put_line(name=|EmpRow.Emp_,name|Wage=|EmpRow.Wage);,ENDLOOP;,CLOSEEmpCur;,END;,10.1.6管理游标结果集,1修改游标结果集中地行,2删除游标结果集中地行,1修改游标结果集中地行,DECLARECURSOR游标名,(参数列表),IS,SELECT语句,FORUPDATEOF可以更新地列名;,【例10.18】,下面定义了一个游标MyEmpCursor,可以访问,指定部门中地员工记录,并可以更新员工工资列,DECLARECURSORMyEmpCursor(varEmpId,NUMBER)IS,SELECTEmp_name,WageFROM,C#HRSYS.Employees,WHEREDep_id=varDepId,FORUPDATEOFWage;,/,使用WHERECURRENTOF子句指定要,更新地游标,UPDATE表名SET子句,WHERECURRENTOF游标名,【例10.19】,DECLARECURSORMyEmpCursor(varDepIdNUMBER)IS,SELECTEmp_name,WageFROMC#HRSYS.Employees,WHEREDep_id=varDepId,FORUPDATEOFWage;,BEGIN,FORrec1INMyEmpCursor(1)LOOP,UPDATEC#HRSYS.EmployeesSETWage=Wage+,100,WHERECURRENTOFMyEmpCursor;,ENDLOOP;,END;,/,脚本地运行过程如下：,（1）首先使用DECLARECURSOR语句声明一个,游标MyEmpCursor,查询表C#HRSYS.Employees,中部门编号于参数varDepId地员工记录。,（2）使用FORLOOP语句遍历表,MyEmpCursor(1)中地所有记录。,（3）在UPDATE语句中使用WHERECURRENTOF,子句,修改当前游标中地记录。,执行上面地脚本后,查询表Employees中地数据,确认部门编号为1地员工工资增加了100。,2删除游标结果集中地行,DELETEFROM表名,WHERECURRENTOF游标名,【例10.20】,下面地脚本中可以删除所有部门编号为1地员工记录。,DECLARECURSORMyEmpCursor(varDepIdNUMBER)IS,SELECTEmp_name,WageFROMC#HRSYS.Employees,WHEREDep_id=varDepId,FORUPDATEOFWage;,BEGIN,FORrec1INMyEmpCursor(1)LOOP,DELETEFROMC#HRSYS.Employees,WHERECURRENTOFMyEmpCursor;,ENDLOOP;,END;,/,10.2存储过程,PL/SQL有以下3种存储过程。,过程：一种基本地存储过程,由过,程名,参数与程序体组成。,函数：与过程类似,只是函数有返,回值。,程序包：一组相关地PL/SQL过程,与函数,由包名,说明部分与包体组,成。,10.2.1过程,CREATEORREPLACEPROCEDURE过程名,参数列表IS|AS,局部变量声明,BEGIN,END过程名;,以下几点需求特别说明,使用REPLACE关键字表示如果要创建地过程,已经存在,则将其替换为当前定义地过程。,参数名IN|OUT|INOUT数据类型:=,参数声明地格式如下：,初始值,IN参数类型表示此参数接受过程外传递来地值,;OUT参数类型表示此参数将在过程中被赋值,并传递到过程体外;INOUT参数类型表示此参,数同时具备IN与OUT参数型地特性。,在局部变量声明块中定义地变量只在过程体,内有效。,【例10.21】,CREATEORREPLACEPROCEDURE,C#HRSYS.ResetWage,(v_EmpIdINNUMBER),AS,BEGIN,UPDATEC#HRSYS.EmployeesSETWage=,8000WHEREEmp_id=v_EmpId;,END;,/,要将编号为2（员工李四）地用户工资重置,可,以使用如下代码,EXECUTEC#HRSYS.ResetWage(2);,SELECTEmp_name,WageFROMC#HRSYS.EmployeesWHEREEmp_Id,=2;,运行结果为：,EMP_NAME,-,李四,WAGE,-,使用DROPPROCEDURE命令删除过程,DROPPROCEDURE方案名.过程名,【例10.22】,ResetWage。,使用下面地语句可以删除示例过程,DROPPROCEDUREC#HRSYS.ResetWage;,10.2.2函数,CREATEORREPLACEFUNCTION函数名,参数列表,RETURN函数数据类型IS|AS,局部变量声明,BEGIN,过程体,RETURN函数值,END过程名;,以下两点需求特别说明,RETURN函数数据类型子句指定了函数返回值地数据,类型。,RETURN函数值子句将函数值作为函数地结果返回。,【例10.23】,CREATEFUNCTIONC#HRSYS.GetWage,(v_nameINEmployees.Emp_Name%Type),RETURNEmployees.Wage%Type,AS,outwageEmployees.Wage%Type;,BEGIN,SELECTWageINTOoutwageFROMEmployees,WHEREEmp_name=|v_name|;,RETURNoutwage;,END;,/,调用函数,SETServerOutputON;,DECLARE,varWageC#HRSYS.Employees.Wage%Type;,BEGIN,varWage:=C#HRSYS.GetWage(李四);,dbms_output.put_line(varWage);,END;,/,【例10.24】,删除函数C#HRSYS.GetWage地语句如下：,DROPFUNCTIONC#HRSYS.GetWage;,10.2.3程序包,CREATEORREPLACEPACKAGE程序包名,IS|AS,声明部分,END程序包名;,【例10.25】,下面介绍一个示例创建程序包C#HRSYS.MyPack,它包含,前面小节中地过程ResetWage与函数GetWage。代码如下,：,CREATEORREPLACEPACKAGEC#HRSYS.MyPack,IS,PROCEDUREResetWage,(v_EmpIdINNUMBER);,FUNCTIONGetWage,(v_nameINEmployees.Emp_Name%Type),RETURNEmployees.Wage%Type;,ENDMyPack;,/,使用CREATEPACKAGEBODY语句来,创建包体部分,CREATEPACKAGEBODY程序包名,IS|AS,声明部分,过程体,函数体,初始化部分,END程序包名;,【例10.26】,下面创建程序包MyPack地包体部分,CREATEPACKAGEBODYC#HRSYS.MyPack,IS,PROCEDUREResetWage,(v_EmpIdINNUMBER),AS,BEGIN,UPDATEC#HRSYS.EmployeesSETWage=8000WHERE,Emp_id=v_EmpId;,END;,FUNCTIONGetWage,(v_nameINEmployees.Emp_Name%Type),RETURNEmployees.Wage%Type,AS,outwageEmployees.Wage%Type;,BEGIN,SELECTWageINTOoutwageFROMEmployees,WHEREEmp_name=|v_name|;,RETURNoutwage;,END;,ENDMyPack;,/,可以使用下面地方法调用程序包中地过,程。,方案名.程序包名.过程名,可以使用下面地方法调用程序包中地函,数。,方案名.程序包名.函数名,【例10.27】,SETServerOutputON;,DECLARE,varWage,C#HRSYS.Employees.Wage%Type;,BEGIN,varWage:=C#HRSYS.MyPack.GetWage(,李四);,dbms_output.put_line(varWage);,【例10.28】,删除程序包体C#HRSYS.MyPack地语句如下：,DROPPACKAGEBODYC#HRSYS.MyPack;,使用DROPPACKAGE命令删除程序包地说,明部分,【例10.29】,删除程序包C#HRSYS.MyPack地语句如,下：,DROPPACKAGEC#HRSYS.MyPack;,10.3触发器,触发器是一种特殊地存储过程,当指定地,事件发生时自动运行。,10.3.1触发器地基本概念,根据以下3个因素区分不同地触发器,1触发事件,2触发时间,3触发级别,1触发事件,DML语句事件：执行,INSERT,UPDATE,DELETE语句时触发地事,件。,DDL语句事件：执行,CREATE,ALTER,DROP语句语句时触发地事,件。,数据库事件：执行,STARTUP,SHUTDOWN,LOGON,LOGOFF操作时,触发地事件。,系统错误：当Oracle数据库系统出现,错误时触发地事件。,2触发时间,BEFORE：在指定地事件发生之前执行,触发器。,AFTER：在指定地事件发生之后执行触,发器。,3触发级别,行触发：对触发事件影响地每一行（例如,一条语句,更新或删除多条记录）都执行触发器。,语句触发：对于触发事件只能执行触发器一次。,10.3.2创建触发器,1创建语句触发器,2创建行触发器,3创建INSTEADOF触发器,4创建LOGON与LOGOFF触发器,1创建语句触发器,CREATEORREPLACETRIGGER触发器,名,BEFORE|AFTER触发事件ON表名|,视图名,PL/SQL程序体,以下几点需求特别说明,触发事件可以是INSERT,UPDATE与DELETE。,表名|视图名定义了与触发器相关地表或视图地名,字。,PL/SQL程序体是触发器触发时要执行地程序块。,【例10.30】,CREATETABLEC#HRSYS.Test,(,id,name,INTEGER,VARCHAR2(50),);,CREATETABLEC#HRSYS.LogTable,(,log_date,action,DATE,VARCHAR2(50),);,创建语句触发器LogUpdateTrigger,CREATEORREPLACETRIGGERC#HRSYS.LogUpdateTrigger,AFTERINSERTORUPDATEORDELETE,ONC#HRSYS.Test,DECLARElog_actionVARCHAR2(50);,BEGIN,IFINSERTINGTHENlog_action:=Insert;,ELSIFUPDATINGTHENlog_action:=Update;,ELSIFDELETINGTHENlog_action:=Delete;,ELSEDBMS_OUTPUT.PUT_LINE(.);,ENDIF;,INSERTINTOC#HRSYS.LogTable(log_date,action),VALUES(SYSDATE,log_action);,END;,/,在表Test上执行INSERT,UPDATE与,DELETE操作,INSERTINTOC#HRSYS.TestVALUES(1,Insert);,UPDATEHRSYS.TestSETname=Update;,DELETEFROMC#HRSYS.TestWHEREid=1;,COMMIT;,查看表LogTable地内容,2创建行触发器,CREATEORREPLACETRIGGER触发器名,BEFORE|AFTER触发事件ON表名|视图名,FOREACHROW,【例10.31】,CREATEORREPLACETRIGGERC#HRSYS.MyTrigger,AFTERUPDATEONC#HRSYS.Departments,FOREACHROW,BEGIN,UPDATEC#HRSYS.EmployeesSETDep_Id=:new.Dep_Id,WHEREDep_Id=:old.Dep_Id;,END;,/,为了验证触发器地功能,可以在SQLPlus中运行以下命令：,UPDATEC#HRSYS.DepartmentsSETDep_Id=101WHERE,Dep_Id=1;,COMMIT;,3创建INSTEADOF触发器,CREATEORREPLACETRIGGER触发器名,INSTEADOF触发事件ON表名|视图名,PL/SQL程序体,【例10.32】,CREATEVIEWC#HRSYS.V_EMP_DEPT,AS,SELECTC#HRSYS.EMPLOYEES.Emp_id,C#HRSYS.EMPLOYEES.Emp_name,C#HRSYS.EMPLOYEES.Sex,C#HRSYS.EMPLOYEES.Title,C#HRSYS.DEPARTMENTS.DEP_NAME,C#HRSYS.DEPARTMENTS.DEP_IDFROM,C#HRSYS.EMPLOYEES,C#HRSYS.DEPARTMENTSWHERE,C#HRSYS.EMPLOYEES.DEP_ID=,C#HRSYS.DEPARTMENTS.DEP_ID;,创建一个触发器MyInsteadofTrigger,CREATEORREPLACETRIGGER,C#HRSYS.MyInsteadofTrigger,INSTEADOFUPDATEONC#HRSYS.V_EMP_DEPT,BEGIN,UPDATEC#HRSYS.EmployeesSETEmp_name=,:new.Emp_name,Sex=:new.Sex,Title=:new.Title,WHEREEmp_id=:old.Emp_id;,END;,/,创建触发器MyInsteadofTrigger后,验证它地功能,UPDATEC#HRSYS.V_EMP_DEPTSETEmp_name=李红,Title=经理,Sex=女,WHEREEmp_id=2;,COLEmp_nameFORMATA20,COLTitleFORMATA20,SELECTEmp_name,Title,SexFROM,C#HRSYS.Employees,WHEREEmp_id=2;,查看INSTEADOF触发器地效果,4创建LOGON与LOGOFF触发器,LOGON触发器在用户登录数据库时被,触发,LOGOFF触发器则在用户注销时被触,发。,使用这两个触发器可以将用户访问,数据库地情况记录在一个日志表中,。,【例10.33】,CREATETABLEC#HRSYS.Users_Log,(,UserName,Activity,EventDate,VARCHAR2(50),VARCHAR2(20),DATE,);,创建LOGON触发器,C#HRSYS.MyLogonTrigger,CREATEORREPLACETRIGGER,C#HRSYS.MyLogonTrigger,AFTERLOGON,ONHRSYS.SCHEMA,BEGIN,INSERTINTOC#HRSYS.Users_Log,VALUES(USER,LOGON,SYSDATE);,END;,/,创建LOGOFF触发器C#HRSYS.MyLogoffTrigger,CREATEORREPLACETRIGGER,C#HRSYS.MyLogoffTrigger,BEFORELOGOFF,ONHRSYS.SCHEMA,BEGIN,INSERTINTOC#HRSYS.Users_LogVALUES,(USER,LOGOFF,SYSDATE);,END;,/,查看LOGON与LOGOFF触发器地效果,10.3.3启用与禁用触发器,可以使用ALTERTRIGGERDISABLE语句禁用触发,器。,【例10.34】,C#HRSYS.MyLogoffTrigg