第六章：游标和触发器
第一节：游标概念
-
游标的概念
游标实际上是一种能够从包括多条数据记录的结果集中每次提取的机制；主要意义就是遍历结果集；
SQL的游标是一种临时的数据库对象，既可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中数据行的指针。游标提供了在逐行的基础上操作表中数据的方法；
游标常见的用途是保存查询结果，以便以后使用。游标的结果集是由select语句产生的，如果处理过程需要重复使用一个记录，那么创建一次游标二重复使用若干次。
-
游标特点
- SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标；
- 当在PL/SQL块中执行查询语句（select）和数据操纵语言（DML）时，Oracle会为其分配一个上下文区（context area）；
- 又表示是至上下文区指针，他为应用提供了一种具有多行数据查询结果集中的每一行数据分贝进行单独处理的方法
-
游标的分类
游标分为显式游标和隐含游标两种
- 隐含游标用于处理select into 和 dml 语句；
- 显式游标用于处理select 语句返回的多行数据。（使用时自己声明）
第二节：显示游标
-
显示游标分为两种：静态游标和动态游标。
其中静态游标指的是声明同时对其指定SQL。而动态游标是指声明后，在打开时才指定；
显示游标适用于处理select语句返回的多行数据，为了处理select语句返回的多数据，我们开发人员可以使用显示游标。
使用显示游标可以分为四阶段
- 定义游标(DECLARE)
- 打开游标(OPEN)
- 提取游标、(FETCH)
- 关闭游标(CLOSE)
-
显示游标的属性
-
显示游标属性用于返回显示游标的执行信息
-
游标属性使用格式为：游标名 + 属性名。
-
%ISOPEN;用于确定游标是否已经打开，如果游标已经打开。则返回值为TRUE，否则返回FALSE；
-
%FOUND检查是否从结果集中提取到数据，如果提取到数据则返回值为TRUE否则返回FALSE;
-
%NOTFOUND与%FOUND属性恰好相反，如果提取到数据则返回FALSE，否则返回TRUE;
-
%ROWCOUNT返回到当前行为已经提取到的实际行数。
-
-
如何使用显示游标
- 定义游标
cursor cursor_name(参数) is select_statement;-
打开游标
open cursor_name; -
提取游标
fetch cursot_name into variable1,variable2...... -- 每次只能处理一行数据，为了处理结果集中的多行数据，必须使用循环语句进行处理； -
关闭游标
close cursor_name; -
游标实例一：
-- 循环打印出部门为10的人员信息编号和名称； declare -- 定义一个静态游标 cursor emps is select empno,ename from emp where deptno = 10; v_empno emp.empno%type; v_ename emp.ename%type; begin -- 打开游标 open emps; -- 每次读取读取一行 fetch emps into v_empno,v_ename; dbms_output.put_line(v_empno||v_ename); -- 循环读取值 loop fetch emps into v_empno,v_ename; exit when emps%notfound; dbms_output.put_line((v_empno||v_ename); end loop; -- 关闭游标 close emps end; -
游标实例2：用table结构提取游标数据（循环打印dept表中数据）游标内容可以使用 fetch … bulk collect into table 数据类型，一次性提取；
-- 循环打印dept表中的数据，使用table结构一次性读取 declare type tableinfo is table dept%rowtype index by binary_integer; depts tableinfo; -- 定义游标 cursor deptsc is select * from dept; begin -- 打开 open deptsc; -- 读取 fetch depts bulk collect into depts; -- 关闭游标 close deptsc; -- 循环遍历table结构内的数据 for i in depts.first..depts.last loop dbms_output.put_line(depts(i).dname); end loop; end;
-
带参游标
-
带参游标：在定义了参数之后，当时用了不同参数值多次打开游标时，可以生成不同的结果集，定义参数游标时，参数游标只能指定数据类型，而不能指定长度。
-
带参游标语法：
cursor cursor_name(param_name datatype) is select_statement; -
参数游标示例（打印指定部门编号的员工信息）；
-- 打印指定部门编号的员工信息 declare cursor emps(v_deptno number) is select empno,ename from emp where deptno = v_deptno; v_empno emp.empno%type; v_ename emp.ename%type; begin open emps(30); loop fetch emps into v_empno,v_ename; exit when emps%notfound; dbms_output.put_line(v_empno||v_ename); end loop; end;
-
-
游标for循环：
-
游标for循环简化游标处理
-
当时用游标for循环时 ，Oracle会隐含的打开游标，提取数据并关闭游标。示例：获取每个部门的名称可以省略声明，打开和关闭
declare cursor depts is select * from dept; v_dept dept%rowtype; begin for v_dept in depts loop dbms_output.put_line(v_dept.dname); end loop; end; -- 自己默认打开，默认关闭 -
当使用游标for循环时，可以直接使用子查询。示例：获取每个部门名称：
-- 使用子查询 declare v_dept dept%rowtype; begin fro v_dept in (select * from dept) loop dbms_output.put_line(v_dept.dname); end loop; end;
-
-
-
动态游标
当时用显示游标时，需要在定义部分指定其所对应的静态select语句，而当使动态游标时，开发人员可以在**打开游标变量时指定其所对应的select语句。
存储过程返回一个游标，都是动态游标；
-
游标变量语法
type ref_type_name is ref cursor; cursor_variable ref_type_name; -
使用游标变量，输出所有部门名称
declare -- 定义一个类型是动态游标 type cusortype is ref cursor; -- 声明变量 游标变量 mycursor cusortype; v_dept dept%rowtype; begin -- 打开是指定sql； open mycursor for select * from dept; loop fetch mycursor into v_dept; exit when mycursor%notfound; dbms_output.put_line(v_dept.dname); end loop; close mycursor; end; -
存储过程中返回游标对象都是动态游标创建一个存储过程；
-- 返回所有员工信息； create or replace procedure getEmpProc(emps out sys_refcursor)-- 出参类型是游标 is begin open emps for select* from emp; end;
-
第三节：隐含游标
隐含游标：
-
当执行一条DML语句或者SELECT…INTO 语句时，都会创建一个隐含游标。
-
当隐含游标的名称是SQL,不能对SQL游标显示的执行OPEN、FETCH、和CLOSE语句
-
Oracle隐式的打开、提起、并总是自动的关闭SQL游标。
-
隐含游标属性包含SQL%FOUND、SQL%NOTFOUND.
-
隐含游标实例判断删除修改是否成功
-- 隐含游标SQL用法 -- 删除指定编号员工是否存在sql%found； declare -- 声明一个变量接收员工编号 v_empno number(10):= &e; begin delete emp where empno = v_empno; -- sql隐式游标名指向的是紧邻的SQL语句 if sql%notfound then dbms_output.put_line('查无此人'); else commit; end if; end;
-
第四节：触发器
-
触发器概念：
触发器是在事件发生时隐式地自动运行的PL\SQL语句程序块，不能接收参数，不能被调用，触发器是指被隐含执行的存储过程，他可以使用PL\SQL进行执行
当发生特定事件（如修改表数据，登录到数据库）时，Oracle会自动执行触发器的相代码。
-
触发器类型：
- DML触发器：在对数据库进行DML操作进行时触发，并且可以对每次或者语句操作上进行触发。
- 替代触发器：是Oracle8专门为进行视图操作的一种触发器
- 系统触发器：对数据库事件进行触发，如启动、关闭等；
-
触发器的组成
- 触发事件：DML或DDL语句
- 触发时间：是在触发事件之前（before）还是之后（after）触发
- 触发操作：表、视图、模式、数据库
- 触发频率：触发器内定义的动作被执行的次数
-
触发器限制：
- 触发器代码的大小不不能超过32K。如果确实需要大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程
- 触发器代码只能包含DML(INSERT、UPDATE和SELECT)语句；
- 不能包含DDL语句(create、alterhe和drop)和事务控制语句（commit、rollback和savepoint）
-
创建触发器的一般语法：
-
语法：
create or replace trigger 触发器名称 {befor/after} {delete or update or insert }[of 列名]on 表名 [for each row] [when 条件] -- 代码块 begin end; -
其中：
BEFORE和AFTER指出触发器的触发时时序分别为前触发和后触发方式，前触发是在执行触发时间之前触发当前创建的触发器，后触发事件是指在执行触发事件之后触发当前所创建的触发器；
-
自定义错误：
RAISE_ARRLICATION_ERROP用法： RAISE_APPLICATION_ERROP(errorNumber,errorString) errorNumber 是数值在-20000到-20999之间，errorString为自定义异常的错误信息； -
实例（周1,5不能修改empty表）：
-- 周一周五不嫩他修改empty表 create or replace trigger tri_updateemp before update on emp begin -- 判断今天是周几 select to_char(sysdate,'day') in('星期一','星期五') then raise_application_error(-20001,'星期一或者星期五不能修改emp表'); end if; end; -
让触发失效
alter trigger 触发器名称 disable; -
条件谓词：（inserting,updatind,deleting）
实例：（周1.5不能修改，删除，新增emp表）提示不同；
-- 周一周五不能修改删除，添加emp表’ ； create or replace trigger tri_updeladdemp before update or insert or delete on emp begin if to_char(sysdate,'day') in ('星期五','星期一') then if updating then raise_applicating_error(-2001,'星期一或者星期五不能修改emp表'); elsif deleting then raise_applicating_error(-2001,'星期一或者星期五不能删除emp表'); else raise_applicating_error(-2001,'星期一或者星期五不能新增emp表'); end if; end;
-
-
行级触发器
-
FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在:行触发器要求当一个DML语句操作影响数据库中的多行数据时,对于其中的每个数据行只要它们符合触发器约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。当省略FOR EACH ROW选项时, BEFORE和AFTER触发器为语句触发器,而INSTEAD OF触发器则只能为行触发器
-
示例(部门30的人工资不能降低,奖金不能低,也不能删除).
create or replace trigger tri_salcomm before upfate or delete on emp for each row when(old.deptno = 30) begin if updating('sal') then if :new.sal<:old.sal then raise_application_error(-20000,'工资不能降低'); end if; elsif updating('comm') then if:new.comm<:old.comm then raise_application_error(-20000,'津贴不能降低'); end if; elsif deleting then raise_application_error(-20000,'不能删除'); end if; end; -- 失效代码 alter trigger tri_salcomm disable;1 -
创建after语句触发器
-
如果指定了after关键字，则表示在执行DML操作之后触发触发器
-
利用行触发器实现级联更新。在修改了主表dept中的的普通农之后after，级联的自动的更新字表emp中原来在该部门的deptno；
-
实例：在修改dept表中的deptno后，顺表把它级联的人员表中部门编号更新；
-- 可以做级联； create or replace tigger tri_updateempfordept after update on dept for each row begin update emp set deptno = :new.deptno where deptno = :old.deptno; end;
-
-
-
触发器里面的存储过程
-
有时业务逻辑过于复杂，触发器内容有限（32K），只能借助于存储过程
-
再删除dept表中记录时，将原有的记录保存到一个回收表delDept中。
-- 创建一个回收表 create table delDept( deptno number(10), dname varchar2(200), loc varchar2(200) ); -- 创建一个存储过程 create or replace procedure insertDelept(v_deptno number,v_dname varchar2,v_loc varchar2) is begin insert into deldept(deptno,dname,loc) values(v_deptno,v_dname,v_loc); end; -- after 触发器 create or replace trigger tri_delDept after delete on dept for each row begin insertDelEept(:old.deptno,:old.dname,:old.loc); end; -
恢复触发器
-- 恢复触发器 alter trigger 触发器名称 enable; -- 让触发器失效 alter trigger 触发器名称 disable; -
重新编译触发器
当时用alter table 命令修改表结构时，会使触发器变为无效状态，为了使触发器继续生效，需要重新编译触发器；
-
-
系统触发器(主要用于登录退出使用)
-- 创建一个登录日志表 create table loginlog( loguser varchar2(200), loginip varchar2(200), loginfate date ); -- 创建after触发器 create or replace trigger tri_logintest after logon on database begin insert into loginlog values(ora_login_user,ora_client_ip_address,sysdate); end;
198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
