pl/sql学习(5): 触发器trigger/事务和锁
(一)触发器简单介绍
触发器是由数据库的特定时间来触发的, 特定事件主要包括以下几种类型:
(1)DML: insert, update,delete 增删改
(2)DDL: create, alter, drop;
(3) 数据库事件: logon,logoff,startup,shutdown,errors
触发器可以分为五种类型
(1)DML触发器: insert , update, delete
(2)DDL 触发器: create,alter,drop
(3)复合触发器 为Oracle11g的新特性,一个触发器包含了4中类型
(4)instead of 触发器: 通常作用在视图上,一般对具有多个表源的视图进行DML操作是不允许的,遇到这种情况就可以利用instead of 类型触发器解决问题,
由此可以对视图的DML操作转化为对多个源表的操作.
(5)用户与系统事件触发器: 登陆 注销等
简单实例: 每次执行删除操作之后，都会信息提示：“这是删除操作！”
CREATE TRIGGER first_trigger AFTER DELETE ON emp -- 作用在emp 表上 BEGIN DBMS_OUTPUT.put_line('这是删除操作！'); END;
查看触发器:
-- 查找触发器的名称 select object_name from user_objects where onject_type='TRIGGER'; -- 得到名称后, 查找触发器的内容 select * from user_source where name='first_trg' order by line;
(二)触发器的语法
1.DDL事件的触发器: create alter drop
CREATE [OR REPLACE] TRIGGER --触发器的名称 [BEFORE | AFTER | INTEAD OF ] [DDL事件] ON [DATABASE | SCHEMA] [WHEN 触发条件] [DECLARE] [程序的声明部分;] BEGIN 程序的代码部分 END;
DDL 事件描述与触发动机
2.DML触发器
DML触发器可用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能。
包括语句触发器和行触发器。
1、语句触发器：在指定操作语句之前或者之后执行一次，不管这条语句影响了多少行。（针对行）
2、行触发器：触发语句作用的每一条记录都被触发，在行级触发器中使用:old和:new伪记录变量，识别值的状态。（针对表）:old表示操作该行之前，这一行的值；:new 表示操作该行之后，这一行的值。
语法:
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER} {DELETE | INSERT | UPDATE [ OF 列名]} -- 触发条件具体到某一列 ON 表名 [FOR EACH ROW [WHEN (条件）]) -- 表示行级触发器, 省略则为语句触发器 PLSQL块
实例1: 禁止在休息日（周六、周日）改变emp表的数据。
--思路: 使用to_char(sysdate, 'day')函数; 采用语句触发器。 CREATE OR REPLACE TRIGGER emp_trigger BEFORE INSERT OR UPDATE OR DELETE ON emp BEGIN IF to_char(sysdate, 'day') IN ('星期六', '星期日') THEN RAISE_APPLICATION_ERROR(-20006, '不能在休息日改变员工信息！'); END IF; END;
注意：RAISE_APPLICAITON_ERROR是用来测试异常处理的存储过程，能够将应用程序专有的错误从服务器端转达到客户端应用程序中（其他机器上的SQLPlus或者其他前台开发语言），其存储过程有两个参数
RAISE_APPLICAITON_ERROR(error_number_in IN NUMBER, error_msg_in IN VARCHAR2); -- error_msg_in的长度不能超过2k，超过2k后会进行截取
实例2: 实现数据审计: 审计员工信息表数据的变化，审计删除时间，以及被删除的雇员名。
已知表emp_temp (name varchar2(10), delete_time date) 用于存放被删除的员工姓名与删除日期.
CREATE OR REPLACE TRIGGER del_emp_trigger AFTER DELETE ON emp FOR EACH ROW BEGIN INSERT INTO emp_temp VALUES(:old.ename, SYSDATE); --插入被删除员工的姓名和当前时间 END;
注意: SQL语句和PLSQL语句中，old和new伪记录变量需要加上冒号: , 而在when这样的限制性条件语句当中，则不需要加上冒号:。
实例3: 员工涨后工资不能低于原来的工资，且所涨的工资不能超过原工资的50%。
CREATE OR REPLACE TRIGGER tr_check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.5) BEGIN RAISE_APPLICATION_ERROR(-20028, '工资只升不降，并且升幅不能超过50%'); END;
3. instead of 触发器: 适用于视图上的触发器
为什么要用instead of 触发器?
在简单视图上往往可以执行INSET、UPDATE、DELETE操作。
但是在复杂视图上执行INSET、UPDATE、DELETE操作时有限制。所以为了在这些复杂的视图上执行DML操作，需要建立替代触发器。
限制:
(1)替代触发器只适用于视图。
(2)替代触发器不能指定BEFORE和AFTER选项。
(3)不能在具有WITH CHECK OPTION选项的视图上建立替代触发器。
(4)替代触发器必须包含FOR EACH ROW选项。
实例:
CREATE OR REPLACE VIEW emp_dept --创建一个视图 AS SELECT d.deptno, d.dname, e.empno, e.ename FROM dept d, emp e WHERE d.deptno = e.deptno;
当没有创建替代触发器时，对emp_dept视图插入数据出错：
INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE'); INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE'); -- 报错 --ORA-01779: 无法修改与非键值保存表对应的列
创建emp_dept视图（复杂视图）的替代触发器：
CREATE OR REPLACE TRIGGER instead_of_trigger INSTEAD OF INSERT ON emp_dept FOR EACH ROW DECLARE v_temp INT; BEGIN SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno = :new.deptno; IF v_temp = 0 THEN -- 没有这个部门则插入这个条新数据 INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname); END IF; SELECT COUNT(*) INTO v_temp FROM emp WHERE empno = :new.empno; IF v_temp = 0 THEN -- 没有这个员工信息就插入这条新数据 INSERT INTO emp(empno, ename, deptno) VALUES(:new.empno, :new.ename, :new.deptno); END IF; END;
4. 系统触发器
实例: 创建记录发生的数据库系统EVENT_TALBE事件表：
CREATE OR REPLACE TRIGGER startup_trigger AFTER STARTUP ON DATABASE BEGIN INSERT INTO event_table VALUES(ORA_SYSEVENT, SYSDATE); END;
(三)事务和锁
1.什么是事务?
事务可以看做是由对数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求。
事务和锁保证了数据库的一致性. 因为数据库是一个由多个用户共享的资源, 因此当多个用户并发的存储数据时, 为了保证数据库的准确性, 需要用事务和锁.
2.事务的组成:一条或者多条DML、一条DDL或者一条DCL语句。（DML语句需要使用COMMIT提交事务或者使用ROLLBACK回滚事务，而DDL和DCL是自动提交事务的。）
注意: DML:data manipulation language 数据操作语句: select update insert delete 需要提交与回滚事务 增删改
DDL : data definition language: 数据库定义语句 create ,alter, drop
DCL: data control language 数据库控住语句 grant deny revoke 设置更改数据库用户的角色或者权限
3.为什么要使用事务?
是为了保证数据的安全有效。
每一个事务都是一个原子单元, 事务中的语句可以被作为一个整体, 要么一起被提交, 作用在数据库上使得数据库的数据永久被修改;
要么一起被撤销, 对数据库不做任何修改. 如当执行事务操作（DML语句）时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；
同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作.
4.事务的控制命令
(1) set transaction 设置事务的属性;
(2)提交事务（COMMIT）：通过COMMIT语句可以提交事务，当执行了COMMIT语句后，会确认事务的变化、结束事务、删除保存点、释放锁。当使用COMMIT语句结束事务之后，其他会话可以查看到事务变化后的新数据。
(3)回滚事务（ROLLBACK）：只能对未提交的数据撤销，已经commit的数据无法撤销。
(4)保存点（SAVEPOINT）：用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行ROLLBACK时，通过指定保存点可以回退到指定的点。（设置保存点：SAVEPOINT a;， 回滚部分事务：ROLLBACK TO a;，回滚全部事务：ROLLBACK;）
(5)rollback to savepoint : 回滚到保存点.
例子: 银行账户之间的汇款转账操作 需要三个步骤
(1) 源账户减少存储金额, 例如-1000;
(2)目标账户增加存储金额1000;
(3) 在事务日志中记录该事务.
将整个交易看做是一个事务, 如果操作失败, 那么该事务就会 rollback 回滚, 目标账户与源账户的金额都不会变化.
注意: 事务在没有提交之前可以回滚, 而且在提交前当前用户还可以查看 已经修改的数据, 但是其他用户看不到, 一旦提交事务就没法修改了
5.事务的类型:
(1) 显示方式
所谓显示方式就是利用命令完成, 语法为
新事务开始 SQL statement -- 若干条SQL语句 ... commit| rollback; -- 提交或回滚
Oracle 中的事务不需要设置开始标志,通常 在 (1) 登陆数据库,第一次执行DML 语句;(2) 事务结束后, 第一次执行DML语句时 , 事务就会开启.
(2) 隐式方式
隐式方式的事务没有明确的开始与结束标志, 它从数据库自动开启, 到一个程序正常结束或使用DDL语句时会自动提交, 操作失败也会自动回滚.
如果设置autocommit 为打开状态(默认关闭), 则每次执行DML操作就会自动提交事务.
set autocommit on/off;
需要注意事务在什么时候结束, 主要有以下几种情况
(1) 使用commit, rollback
(2) 执行DDL, 事务自动提交,例如 create, alter, drop, revoke,grant 等
(3) 正常退出SQL*plus 时, 非正常退出则rollback
例子:
新建一个sql*plus1, 执行以下语句 emp(ename,eno,job)
inset into emp values('slice','125','manager');
此时select * from emp ,可以看到这条数据被插入进去了. 但是注意: 此时还没有提交事务!!
同样的, 登陆新的sql*plus, 窗口为sql*plus2, 此时查看发现emp并没有这条数据.
在sql*plus1窗口提交事务 commit; 后, sql*plus2窗口查询emp 的数据才会显示出来.
6.事务的保存点
保存点可以设置在事务中的任何地方, 也可以设置多个点, 可以将较长的事务分成较小的段, 好处是当操作有问题时, 不会全部回滚, 回滚到保存点处.
一旦事务回滚到某个保存点后, Oracle 会把保存点之后持有的锁释放掉, 此时先前当等待被锁资源的事务就可以继续了.
如何使用保存点, 很简单, 只有一行代码
insert into emp values ('alice', '125','manager'); -- 向emp表中添加一条数据 savepoint fst; --创建保存点 --创建成功后, 继续增加一条数据 insert into emp values ('mike', '126','manager'); --
此时查看emp可以看到新增的两条数据, 执行 rollback to fst;
事务成功回滚到保存点处, 第二条数据看不到了.
7.事务的ACID特性
A: 原子性(不可分割, 要么全部执行,要么全部撤销)
C: 一致性
I: 隔离性(不同事务互不干扰)
D: 持久性(一旦提交为对数据库的永久修改)
(三) 锁
1.什么是锁
同一时刻多个用户同时操作相同资源的情况时有发生, 数据库利用锁可以消除多个用户同时造作同一资源产生的隐患.
锁是一种机制, 在访问相同资源时, 可以防止事务之间的破坏性交互.例如, 多个会话同时操作某表时, 优先操作的会话需要持有该表的锁.
锁 可以确保多个会话像队列一样依次进行.
注意:
(1)Oracle 提供了很大程度的并发性: 会话1在修改一条记录, 仅仅该记录会锁定, 其他会话可以随时进行读取操作, 但是读取的为修改前的数据.
(2) 会话1对表emp 的某行记录进行修改时, 另一个会话2 也来修改这个记录, 在没有任何处理情况下最后保存的数据有随机性, 这种数据叫做"脏数据"
如果采用了行级锁, 会话1 修改记录时锁定该行,会话2 只能等待, 避免脏数据的产生.
2.锁的分类
(1) 排他锁: 防止资源的共享, 用于数据的修改, 若事务1给数据A加锁, 其他事务不能对A加任何锁, 此时只允许事务1 对A进行读取和修改, 直到事务完成释放锁为止.
(2) 共享锁(也成为 读锁) : 该模式下锁的数据只能被读取, 不能被修改, 若事务1给数据A加锁, 其他事务不能对A加任何锁, 只能共享锁, 加了该锁的数据可以并发的被读取.
锁 很多都是数据库自动管理, 当事务提交后自动释放锁.
3. 锁的类型
(1) DML锁: 也叫数据锁, 用于保护数据.
(2)DDL锁: 保护模式中对象的结构
(3)内部闩锁: 保护数据库的内部结构, 完全自动调用.
其中DML锁还可以再细分为:
(1) 行级锁TX: 也成为 事务锁, 当修改表中某行记录时, 需要给它加行级锁, 防止两个事务同时修改相同记录. 事务结束 锁也会自动释放, 是粒度最小的锁. --? belong 排它锁
(2)表级锁TM: 为了防止修改表的数据时表结构发生变化. 会话1在修改表emp时 会得到表emp 的TM锁, 而此时不允许其他会话会该表进行变更或删除操作.
例子: 打开 sql*plus ,窗口sql*plus 1, 修改表emp , DML操作
update emp set sal=sal+100 where eno='123';
此时再打开另一个窗口sql*plus2 , 对该表进行DDL操作.
drop table emp; --会报错 -- ORA-00054 资源正忙, 但指定以nowait方式打开获取资源, 或者超时失败
在执行DML操作(增删改)时, 数据库会先申请数据库对象上的共享锁, 防止其他的会话对该对象执行DDL(创删改)操作.
4. 锁等待/死锁
有时候由于占用的资源不能及时释放, 造成锁等待,这严重影响数据库的性能与日常工作,
例如在窗口sql*plus 1 中修改emp 的数据
update emp set sal=sal+100 where eno='123';
虽然sql*plus 1 窗口已经提示更新, 但是事务没有提交!
此时再打开另一个窗口sql*plus2 , 同样也执行修改, 执行上述同样的语句, 不是提示已更新, 而是一直等待.
这是因为sql*plus 1 封锁了记录, 但是事务没有结束, 锁不会释放.
死锁 是锁等待的一个特例, 通常发生在两个或者多个会话之间.
假设一个会话要修改两个资源对象, 可以是表也可以是字段, 修改这两个资源A,B的操作在一个事务S1中,
当S1修改了第一个对象A需要对其锁定, 这时另外一个会话 或者事务S2 也需要修改A, B, 并且已经获得并锁定了第二个对象B,
那么就出现了死锁, 这时两个会话/事务度得不到想要的结果.
例子:
--会话1, sql*plus1 中 update emp set sal=sal+100 where eno='125'; -- 会话2, sql*plus2 中 update emp set sal=sal+100 where eno='126'; --会话1, sql*plus1 中想修改第二个会话已经修改的记录, 则出现锁等待 update emp set sal=sal+100 where eno='126'; -- 会话2 想修改第1个会话已经修改的记录 update emp set sal=sal+100 where eno='125'; -- 检测到死锁!
介绍: 用企业OEM管理器终止锁阻塞!
