游标触发器PPT课件.ppt

游标、触发器游标、触发器第第 六六 章章本章目标本章目标理解游标的作用学会创建游标学会创建触发器什么是游标什么是游标游标是指向查询结果集的一个指针，它是一个通过定义语句与一条Select语句相关联的一组Sql语句。游标包含两方面的内容：游标结果集：执行其中的游标结果集：执行其中的SelectSelect语句所得到的结果集语句所得到的结果集 游标位置：一个指向游标结果集内的某一条记录的指针游标位置：一个指向游标结果集内的某一条记录的指针 游标的作用：利用游标可以单独操纵结果集中的每一行。游标在定义利用游标可以单独操纵结果集中的每一行。游标在定义以后存在两种状态：关闭和打开。以后存在两种状态：关闭和打开。当游标关闭时，其查询结果集不存在；当游标关闭时，其查询结果集不存在；当游标打开时，能按行读取或修改结果集中的数据。当游标打开时，能按行读取或修改结果集中的数据。创建游标（一）创建游标（一）游标能帮助DBA对查询的结果逐行进行处理。使用游标分为以下几步：1 1、定义游标、定义游标2 2、打开游标、打开游标3 3、从游标中操作数据、从游标中操作数据4 4、关闭游标、关闭游标示例如下：Declare Cursor 游标名称游标名称 Is 查询语句查询语句;Begin Open 游标名称游标名称;-提取数据，操作记录提取数据，操作记录 Close 游标名称游标名称;End;取出记录取出记录当游标打开后，就可以从结果集中提取数据了。提取游标数据有三种方式：1 1、提取单行数据：、提取单行数据：Fetch Fetch 游标名游标名 Into Into 存放结果的存放结果的 记录类型记录类型|变量变量2 2、提取全部数据：、提取全部数据：Fetch Fetch 游标名游标名 Bulk Collect Into Bulk Collect Into 集合类型集合类型3 3、提取指定行数的记录：、提取指定行数的记录：Fetch Fetch 游标名称游标名称 Bulk Collect Into Bulk Collect Into 集合类型集合类型 Limit Limit 提取的数量提取的数量游标实例（一）游标实例（一）开发一个游标，要求如下：1 1、创建一个查询、创建一个查询scott.empscott.emp表所有数据的游标表所有数据的游标2 2、每次取出、每次取出5 5条记录条记录3 3、如果记录、如果记录IDID能被能被2 2整除，则打印该记录的信息。整除，则打印该记录的信息。4 4、打印格式如下：、打印格式如下：XX XX 雇员的工资是：雇员的工资是：YY,YY,对应的编号是：对应的编号是：ZZZZ游标实例（二）游标实例（二）Declare Cursor emp_Cursor is Select*from Scott.emp;-定义游标 Type tabResult is Table of Scott.emp%rowType;-定义嵌套表类型 v_result tabResult;-声明变量Begin Open emp_Cursor;-打开游标 Loop -循环取出记录 Fetch emp_Cursor Bulk Collect Into v_result Limit 10;-提取数据 Exit When emp_Cursor%NOTFOUND;-如果提取的记录为空就退出 For i in 1.v_result.COUNT Loop -循环取出的10条记录 If mod(v_result(i).empno,2)=0 Then dbms_output.put_line(v_result(i).ename|雇员的工资是：|v_result(i).sal|,对应的编号是：|v_result(i).empno);End If;End Loop;End Loop;Close emp_Cursor;-关闭游标End;游标属性游标属性游标提供了一些属性帮助编写pl/sql程序，属性的例用方法：游标名称游标名称%属性名属性名游标的属性有：1 1、%ISOPEN%ISOPEN：确定游标是否已经打开：确定游标是否已经打开2 2、%FOUND%FOUND：检查是否从结果集中提取到了记录，如果提：检查是否从结果集中提取到了记录，如果提取到数据返回取到数据返回truetrue；否则返回；否则返回falsefalse。3 3、%NOTFOUND%NOTFOUND：检查是否没有从结果集中提取到记录，：检查是否没有从结果集中提取到记录，如果没有取到数据返回如果没有取到数据返回truetrue；否则返回；否则返回falsefalse。4 4、%ROWCOUNT%ROWCOUNT：返回到当前行为止，已经提取了多少数：返回到当前行为止，已经提取了多少数据。据。更新或删除游标更新或删除游标普通游标只能完成查询的操作，称为：查询游标。有时需要修改/删除记录，这时就要使用更新/删除游标。更新/删除游标与查询游标的区别：1 1、Select Select 查询语句后添加查询语句后添加 For UpdateFor Update2 2、在删除、在删除/更新语句后添加更新语句后添加 where Current Of where Current Of 游标名游标名示例如下：示例如下：Declare Cursor 游标名称游标名称 Is 查询语句查询语句 For UpdateFor Update;Begin Open 游标名称游标名称;Delete from 表名表名 where Current Of 游标名游标名 Close 游标名称游标名称;End;小结小结什么是游标？如何声明游标？创建一个基于scott.emp表的游标，实现对工资小于3000的雇员信息进行打印，并且统计工资大于等于3000的雇员数量。什么是触发器什么是触发器触发器：指存储在数据库中，当对表执行insert、update、delete时，被隐式执行的存储过程。触发器的作用：1 1、安全性。可以基于数据库的值，使用户具有操作数据、安全性。可以基于数据库的值，使用户具有操作数据库的某种权利。库的某种权利。2 2、审计。可以跟踪用户对数据库的操作。、审计。可以跟踪用户对数据库的操作。3 3、实现复杂的非标准的数据库相关完整性规则。触发器、实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。可以对数据库中相关的表进行连环更新。4 4、自动计算数据值，如果数据的值达到了一定的要求，、自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。则进行特定的处理。触发器触发器在创建触发器时，需要指定：1 1、触发时机：指定触发时机。取值：、触发时机：指定触发时机。取值：BeforeBefore、AfterAfter以以及及Instead OfInstead Of。2 2、触发事件：指定导致触发器执行的、触发事件：指定导致触发器执行的DMLDML操作。即：操作。即：insertinsert、updateupdate、deletedelete。可以使用单个触发事件，也。可以使用单个触发事件，也可以组合多个触发事件。可以组合多个触发事件。3 3、表名：指定、表名：指定DMLDML操作对应的表。操作对应的表。4 4、触发类型：指定触发事件发生后，需要执行几次触发、触发类型：指定触发事件发生后，需要执行几次触发操作。操作。5 5、触发操作：指定触发器执行的代码。、触发操作：指定触发器执行的代码。THANK YOUSUCCESS2024/2/27 周二13可编辑条件谓语条件谓语当在触发器中同时包括多个触发事件时，为了在触发器代码中进行区分，可以使用以下谓语：1 1、INSERTINGINSERTING：当触发事件是当触发事件是InsertInsert操作时，该条件谓语返回操作时，该条件谓语返回true,true,否则否则返回返回falsefalse。2 2、UPDATINGUPDATING：当触发事件是当触发事件是updateupdate操作时，该条件谓语返回操作时，该条件谓语返回true,true,否则否则返回返回falsefalse。3 3、DELETINGDELETING：当触发事件是当触发事件是deletedelete操作时，该条件谓语返回操作时，该条件谓语返回true,true,否则否则返回返回falsefalse。创建触发器（一）创建触发器（一）一个完整的触发器由五部分组成，所以创建触发器也必须按照如下格式：语法格式：语法格式：Create or Replace TriggerCreate or Replace Trigger 触发器名称触发器名称 触发时机触发时机 触发事件触发事件 OnOn 表名表名 For Each RowFor Each Row DeclareDeclare-声明变量声明变量BeginBegin .-.-触发操作触发操作End End 触发器名称触发器名称;创建触发器（二）创建触发器（二）创建表级触发器，示例如下：注意：例外的自定义编码从注意：例外的自定义编码从-20000-20000 到到 -20999-20999Create Or Replace Trigger myTrigger Before insert or update on TempDeclare myException Exception;Begin If nvl(:New.id,-1)1 Then Case When Inserting Then raise_application_error(-20005,添加失败，ID不能为空!);When Updating Then raise_application_error(-20006,修改失败,id不能为空!);End Case;End If;End myTrigger;创建触发器（三）创建触发器（三）创建行级触发器。示例如下：示例如下：Create Or Replace Trigger myTrigger Before insert or update on Temp For Each RowDeclareBegin If nvl(:New.id,-1)SavePoint a;SQL Insert into Temp values(11,aa);SQL SavePoint b;SQL Delete from Temp;SQL Insert into Temp value(12,bb);SQL Rollback b;SQL Insert into Temp value(13,cc);SQL Commit;谢谢 谢！谢！THANK YOUSUCCESS2024/2/27 周二26可编辑