Oracle数据库的游标与触发器

一、游标

1.什么是游标:

Oracle游标是通过关键字CURSOR的来定义一组Oracle查询出来的数据集，类似数组/容器一样，把查询的数据集存储在内存当中，然后通过游标指向其中一条记录，通过循环游标达到循环数据集的目的。

即游标就是查询所获得的结果集

2.游标的种类

oracle的游标分为显示游标和隐式游标

显式游标：

指的是游标使用之前必须得先声明定义，一般是对查询语句的结果集进行定义游标，然后通过打开游标循环获取结果集内的记录，或者可以根据业务需求跳出循环结束游标的获取。循环完成后，可以通过关闭游标，结果集就不能再获取了。全部操作完全由开发者自己编写完成，自己控制。

隐式游标：

指的是PL/SQL自己管理的游标，开发者不能自己控制操作，只能获得它的属性信息。

3.显示游标的语法

1）声明游标：声明游标是给游标命名并给游标关联一个查询结果集，具体声明语法如下:
	declare cursor cursor_name(游标名)
	is select＿statement(查询语句);

2）打开游标：游标声明完，可以通过打开游标打开命令，初始化游标指针，游标一旦打
开后，游标对应的结果集就是静态不会再变了，不管查询的表的基础数据发生了变化。
打开游标的命令如下:
	open cursor_name;


3）读取游标中数据:读取游标中的数据是通过fetch into语句完成，把当前游标指针指
向的数据行读取到对应的变量中（record 变量）。游标读取一般和循环LOOP一起使用，
用于循环获取数据集中的记录。
	fetch cursor_name into record变量

4）关闭游标：游标使用完，一定要关闭游标释放资源。关闭后，该游标关联的结果集
就释放了，不能够再操作了，命令如下：
	close cursor_name;

4.显式游标的属性

利用显式游标的属性值来获取游标所处的状态，然后对应做相应的处理

常用的属性有四个：

	1)%NOTFOUND : 表示游标获取数据的时候是否有数据提取出来，没有数据返回TRUE，
	有数据返回false。经常用来判断游标是否全部循环完毕。

	2)%FOUND : 正好和%NOTFOUND相反，当游标提取数据值时有值，返回TRUE，否则返回FALSE。

	3)%ISOPEN : 用来判断游标是否打开。

	4)%ROWCOUNT : 表示当前游标FETCH INTO获取了多少行的记录值，用来做计数用的。

	5)%TYPE : 为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）
	的数据类型相一致，Oracle提供了%TYPE定义方式。

	6)%ROWTYPE : 如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量，
	比分别使用%TYPE来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会
	增加程序的可维护性。

案例展示

declare
  --定义游标
  cursor cur_xsjbxx is
    select * from student order by sno;
  --定义记录变量  
  ls_curinfo   cur_xsjbxx%rowtype;
begin
  open cur_xsjbxx;--打开游标
  loop
      FETCH cur_xsjbxx
      INTO ls_curinfo;--获取记录值
     EXIT WHEN cur_xsjbxx%NOTFOUND;--循环结束的条件：判断是否还有记录
   
    dbms_output.put_line('学号：' || ls_curinfo.sno|| ',姓名：' || ls_curinfo.SNAME);
  end loop;
  close cur_xsjbxx;--关闭游标
end;


运行结果:

学号：s001,姓名：张三
学号：s002,姓名：李四
学号：s003,姓名：吴鹏
学号：s004,姓名：琴沁
学号：s005,姓名：王丽
学号：s006,姓名：李波
学号：s007,姓名：刘玉
学号：s008,姓名：萧蓉
学号：s009,姓名：陈萧晓
学号：s010,姓名：陈美
学号：s011,姓名：张三
学号：s012,姓名：即墨寒霜
学号：s013,姓名：张德坤

--%ROWCOUNT（游标计数器）
declare
  --定义游标
  cursor cur_xsjbcount  is   select *  from student order by sno;
  --定义记录变量  
  ls_curinfo cur_xsjbcount%rowtype;
begin
  open cur_xsjbcount;--打开游标
  loop
    FETCH cur_xsjbcount
      INTO ls_curinfo;--获取记录值
    EXIT WHEN cur_xsjbcount%NOTFOUND;
    --利用游标计数器打印学生个数
  end loop;
   dbms_output.put_line('表中一共有：'||cur_xsjbcount%ROWCOUNT||'个学生');
  close cur_xsjbcount;--关闭游标
end;

运行结果:

表中一共有：13个学生

二、触发器

1.什么是触发器:

是数据库提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程,它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

2.定义剖析

触发器的本质是一个存储过程，顾名思义发生特定事件时Oracle会执行触发器中的代码。

细分它的组成可以分为3个部分：

• 第一部分在什么条件下触发器会执行，即触发器被触发的事件。

• 第二部分在什么时间点执行触发器即触发器的发生事件例如before,after。

• 第三部分触发器自身所要做的事情，就是触发器被触发以后具体想表达的事件，在begin和end之间的sql

3.触发器类型

• DML触发器：基于dml操作的触发器，细分又可以分为行触发器和语句触发器

• ddl触发器：即执行ddl操作后所触发的事件。

• 替代触发器：由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法

• 系统触发器：顾名思义，由系统触发器所触发的事件，常用的系统事件startup,shutdown,db_roll_change,server error等

ps:后三种要求的权限比较高，且专业性比较强，故此是DBA的工作

4.触发器的作用

• 数据确认；

• 实施复杂的安全性检查；

• 做日志记录，跟踪表上所做的数据操作；

• 数据的备份和同步；

• 允许/限制对表的修改；

• 自动生成派生列，比如自增字段；

• 强制数据一致性；

• 防止无效的事务处理；

• 启用复杂的业务逻辑等。

5.触发器的基本语法：

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

解释:

	触发器名 : 触发器对象的名称。由于触发器是数据库自动执行
的，因此该名称只是一个名称，没有实质的用途。
	触发时间 : 指明触发器何时执行，该值可取。
	before : 表示在数据库动作之前触发器执行。
	after : 表示在数据库动作之后触发器执行。
	触发事件 : 指明哪些数据库动作会触发此触发器。
	insert : 数据库插入会触发此触发器;
	update : 数据库修改会触发此触发器;
	delete : 数据库删除会触发此触发器。
	表 名 : 数据库触发器所在的表。
	for each row：对表的每一行触发器执行一次。如果没有这
一选项，则只对整个表执行一次。

代码案例:

CREATE OR REPLACE TRIGGER ordeinfo_trigger 
BEFORE UPDATE OR DELETE OR INSERT
ON orderinfo
BEGIN
--sysdate代指当前系统时间，为星期一，sysdate+5代指星期六
 IF to_char(sysdate+5,'day') IN ('星期六','星期日') THEN 
 RAISE_APPLICATION_ERROR(-20008,'不允许在周六周日修改orderinfo表');   
 --打印错误信息：第一个参数为设定不同的错误信息标识代码
 --（可以是20001-29999之间），第二个参数是提示出来的错误信息，
 --大小可以是2k，但是超过2k按2k保留
 END IF;
END;

--测试自定义触发器orderinfo_trigger时候起作用了
update orderinfo set message='new message ' where id='001';

CREATE OR REPLACE TRIGGER SAL_EMP
 BEFORE UPDATE ON tg_user
 FOR EACH ROW
 BEGIN
 IF :OLD.score > :NEW.score THEN 
  --DBMS_OUTPUT.PUT_LINE('积分减少');
  RAISE_APPLICATION_ERROR(-20008,'积分不能减少');   
 ELSIF :OLD.score < :NEW.score THEN
  DBMS_OUTPUT.PUT_LINE('积分增加');
 ELSE
  DBMS_OUTPUT.PUT_LINE('积分未作任何变动');
 END IF;
 DBMS_OUTPUT.PUT_LINE('更新前积分 ：' || :OLD.score);
 DBMS_OUTPUT.PUT_LINE('更新后积分 ：' || :NEW.score);
END;

-- 测试一下 我们减一下积分，就会触发 我们写的触发器，就会报错
UPDATE tg_user SET score = -3000;
--测试一下，我们增加积分，触发器正常通过，就不会报错
UPDATE tg_user SET score = 60000 ;

6.触发器的增删改查

--【禁用某个触发器】ALTER TRIGGER <触发器名> DISABLE
ALTER TRIGGER SAL_EMP DISABLE

--【重新启用触发器】ALTER TRIGGER <触发器名> ENABLE

ALTER TRIGGER SAL_EMP ENABLE

--【启用表的所有触发器】ALTER TABLE <表名> ENABLE ALL TRIGGERS;
ALTER TABLE tg_user ENABLE ALL TRIGGERS;

--【删除触发器】DROP TRIGGER <触发器名>;
DROP TRIGGER SAL_EMP;