oracle学习 —— 视图和触发器
视图
视图也是oracle的一种数据对象，其主要作用是简化的操作，提高安全性。
视图相当于一个虚拟表。它并不存在于物理磁盘中，它是根据其他的表动态生成的。
基本语法：
创建或修改：
create [or replace] view 视图名称 as 查询语句 [with read only];
with read only ：创建可读视图。只能进行查询操作。
删除：
drop view 视图名称;
查询：
select 字段 from 视图名;
简单示例：
sql> select * from emp; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 smith clerk 7902 1980/12/17 1800.00 20 7499 allen salesman 7698 1981/2/20 1600.00 300.00 30 7521 ward salesman 7698 1981/2/22 1250.00 500.00 30 7566 jones manager 7839 1981/4/2 2975.00 20 7654 martin salesman 7698 1981/9/28 1250.00 1400.00 30 7698 blake manager 7839 1981/5/1 2850.00 30 7782 clark manager 7839 1981/6/9 2450.00 10 7788 scott analyst 7566 1987/4/19 3000.00 20 7839 king president 1981/11/17 5000.00 10 7844 turner salesman 7698 1981/9/8 1500.00 0.00 30 7876 adams clerk 7788 1987/5/23 1100.00 20 7900 james clerk 7698 1981/12/3 960.00 30 7902 ford analyst 7566 1981/12/3 3040.00 20 7934 miller clerk 7782 1982/1/23 1350.00 10 14 rows selected sql> create or replace view view_test as select t.empno, t.ename, t.job from emp t where t.deptno = 30; view created sql> select * from view_test; empno ename job ----- ---------- --------- 7499 allen salesman 7521 ward salesman 7654 martin salesman 7698 blake manager 7844 turner salesman 7900 james clerk 6 rows selected
sql> delete from view_test where empno = 7499; 1 row deleted sql> select * from emp; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 smith clerk 7902 1980/12/17 1800.00 20 7521 ward salesman 7698 1981/2/22 1250.00 500.00 30 7566 jones manager 7839 1981/4/2 2975.00 20 7654 martin salesman 7698 1981/9/28 1250.00 1400.00 30 7698 blake manager 7839 1981/5/1 2850.00 30 7782 clark manager 7839 1981/6/9 2450.00 10 7788 scott analyst 7566 1987/4/19 3000.00 20 7839 king president 1981/11/17 5000.00 10 7844 turner salesman 7698 1981/9/8 1500.00 0.00 30 7876 adams clerk 7788 1987/5/23 1100.00 20 7900 james clerk 7698 1981/12/3 960.00 30 7902 ford analyst 7566 1981/12/3 3040.00 20 7934 miller clerk 7782 1982/1/23 1350.00 10 13 rows selected
注意：
因为视图是根据源数据表动态生成的，而不是独立的表，所以对视图的修改是会影响到源数据的。
所以，我们大多数情况下创建视图的时候都会带上 with read only 选项，这样创建的视图就只能进行查询操作而不能进行其他操作，数据也可以更加安全。
sql> create or replace view view_test as select t.empno, t.ename, t.job from emp t where t.deptno = 30 with read only; view created sql> select * from view_test; empno ename job ----- ---------- --------- 7521 ward salesman 7654 martin salesman 7698 blake manager 7844 turner salesman 7900 james clerk sql> delete from view_test where empno = 7521; delete from view_test where empno = 7521 ora-42399: 无法对只读视图执行 dml 操作
视图和表的区别：
一、表占用物理内存空间，而视图不占用物理内存空间。
二、表可以添加索引，视图不能添加索引。 — 视图是动态生成，无物理空间，没有地方存储索引
三、视图可以简化一些操作（多表组合成一个视图等）
四、视图可以增加安全性（只读选项；同一张表可以给不同用户分配不同字段的视图，方便管理）
触发器
触发器也是oracle中的一种数据对象。
触发器技术是许多关系型数据库中都提供的一种技术。
这种可以在用户进行某种操作时，自动的进行另一种操作。这种技术称之为触发器技术。
触发器是指存放在数据库中，被隐含执行的存储过程，可以支持 dml 触发器，还支持基于事件（如启动/关闭数据库、登录/注销等）和 ddl 操作建立触发器。
触发器分类：
一、dml触发器
dml (data manipulation language)： 数据操纵语言。
insert、update、delete、select
二、ddl触发器
ddl（data definition language）：数据定义语言。
create、alter、drop
三、系统触发器
与系统相关的触发器（用户登录、退出、启动数据库连接、关闭数据库连接等）
基本语法：
创建或修改：
create or replace trigger trigger_name
{before | after}
{insert | delete | update [of column1[, column2...]]}
on [schema.] table_name
[for each row]--行级触发器。如果无此行，代表表级触发器
[when condition]
begin
触发器执行体;
end;
dml触发器示例1：– 表级触发器
在emp表中添加一条记录时，提示“一条记录已加入”；
sql> create or replace trigger trigger_test -- 创建或者修改名为 trigger_test 的触发器
2 after insert on scott.emp -- 在 scott 方案下的 emp 表 进行 insert 操作 之后 执行
3 begin
4 dbms_output.put_line('一条记录已加入'); -- 打印一条提示信息
5 end;
6 /
trigger created
sql> insert into emp(empno, ename, job) values(9999,'test','worker');
一条记录已加入
1 row inserted
dml触发器示例2：– 行级触发器
在emp表中，批量修改数据，每修改一条数据，提示一次“一条记录已修改”；
sql> create or replace trigger trigger_test
2 after update on scott.emp
3 for each row -- 行级触发器，每修改一行数据都执行一次 触发器体
4 begin
5 dbms_output.put_line('一条记录已修改');
6 end;
7 /
trigger created
sql> update emp set sal = sal + 10 where deptno = 30;
一条记录已修改
一条记录已修改
一条记录已修改
一条记录已修改
一条记录已修改
5 rows updated
dml触发器示例3：– before + 条件 + 精确到操作 的触发器
在emp表中，添加数据之前判断当前星期，周三与周日不允许添加数据；并给出提示，提示中包含正在进行的操作
sql> create or replace trigger trigger_test
2 before insert or update or delete on -- 在修改数据之前 执行 触发器体
3 scott.emp
4 begin
5 if to_char(sysdate,'day') in ('星期三', '星期日')
6 then
7 -- 因为我们需要在操作语句执行之前判断 来决定是否执行，
8 -- 所以不能采用 dbms 的打印提示，这样仅仅会提示信息，但操作语句仍会继续执行
9 -- dbms_output.put_line('周三和周日不允许修改数据')
10 -- 我们需要使用oracle预定义的过程 raise_application_error 来抛出错误提示，中断执行
11 -- 该过程需要两个参数，错误编号(-20000~-20999)， 错误信息(0 ~ 2k)。
12 case
13 when inserting then -- inserting 表示进行插入操作
14 raise_application_error(-20000, '不能在周三或周日添加数据');
15 when updating then -- updating 表示进行修改操作
16 raise_application_error(-20001, '不能在周三或周日修改数据');
17 when deleting then -- deleting 表示进行删除操作
18 raise_application_error(-20002, '不能在周三或周日删除数据');
19 end case;
20 end if;
21 end;
22 /
trigger created
sql> update emp set sal = 5000 where empno = 7876;
update emp set sal = 5000 where empno = 7876
ora-20001: 不能在周三或周日修改数据
ora-06512: 在 "scott.trigger_test", line 13
ora-04088: 触发器 'scott.trigger_test' 执行过程中出错
sql> delete from emp where empno = 7521;
delete from emp where empno = 7521
ora-20002: 不能在周三或周日删除数据
ora-06512: 在 "scott.trigger_test", line 15
ora-04088: 触发器 'scott.trigger_test' 执行过程中出错
dml触发器示例4： — :old 和 :new 控制修改操作前后数据
在emp表中，修改员工月薪时，打印出修改前与修改后的值，并保证修改后的值不能低于修改前的值。
sql> create or replace trigger trigger_test
2 before update on scott.emp
3 for each row -- 此情况下必须使用行级，因为sal是针对行的，不存在emp表的薪水这种说法
4 begin
5 if :new.sal < :old.sal -- 如果新修改的薪水低于原来的薪水
6 then
7 raise_application_error(-20000, '不能低于原有的值');
8 else
9 dbms_output.put_line('姓名：' || :old.ename || ';修改前：' || :old.sal || ';修改后：' || :new.sal);
10 end if;
11 end;
12 /
trigger created
sql> update emp set sal = 4000 where deptno = 20;
姓名：smith;修改前：1810;修改后：4000
姓名：jones;修改前：2985;修改后：4000
姓名：scott;修改前：3010;修改后：4000
姓名：adams;修改前：1110;修改后：4000
姓名：ford;修改前：3050;修改后：4000
5 rows updated
sql> update emp set sal = 3500 where deptno = 10;
姓名：clark;修改前：2450;修改后：3500
update emp set sal = 3500 where deptno = 10
ora-20000: 不能低于原有的值
ora-06512: 在 "scott.trigger_test", line 4
ora-04088: 触发器 'scott.trigger_test' 执行过程中出错