我是靠谱客的博主 着急蜜粉,这篇文章主要介绍Oracle学习笔记 _17_视图和触发器Oracle学习 第17天,现在分享给大家,希望可以做个参考。

Oracle学习 第17天

                —— 视图和触发器

 

视图

 

                视图也是Oracle的一种数据对象,其主要作用是简化数据库的操作,提高安全性。

 

                视图相当于一个虚拟表。它并不存在于物理磁盘中,它是根据其他的表动态生成的。

 

 

 

基本语法:

                创建或修改:

复制代码
1
CREATE [OR REPLACE] VIEW 视图名称 AS 查询语句 [WITH READ ONLY];

 

★ WITH READ ONLY :创建可读视图。只能进行查询操作。                

                删除:

复制代码
1
DROP VIEW 视图名称;

                查询:

复制代码
1
SELECT 字段 FROM 视图名;

简单示例:

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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

 

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 选项,这样创建的视图就只能进行查询操作而不能进行其他操作,数据也可以更加安全。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

 

        二、DDL触发器(DBA账户创建)

 

                DDL(Data Definition Language):数据定义语言。

 

                        CREATE、ALTER、DROP

 

        三、系统触发器(DBA账户创建)

 

                与系统相关的触发器(用户登录、退出、启动数据库连接、关闭数据库连接等)

 

DML触发器基本语法:

 

                创建或修改:

复制代码
1
2
3
4
5
6
7
8
9
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表中添加一条记录时,提示“一条记录已加入”;

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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表中,批量修改数据,每修改一条数据,提示一次“一条记录已修改”;

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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表中,添加数据之前判断当前星期,周三与周日不允许添加数据;并给出提示,提示中包含正在进行的操作

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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表中,修改员工月薪时,打印出修改前与修改后的值,并保证修改后的值不能低于修改前的值。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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' 执行过程中出错

★ 对于INSERT操作来说,只有 :new , 没有 :old;同样,对于DELETE操作来说,只有 :old , 没有 :new 。

 

 

 

 

 

DDL触发器基本语法

复制代码
1
2
3
4
5
CREATE OR REPLACE TRIGGER trigger_name AFTER DDL ON 方案名.SCHEMA -- DDL 触发器只有AFTER一种情况 DDL 与 SCHEMA 都是关键字 BEGIN -- 执行语句; END;

DDL触发器示例1: -- 记录SYS用户进行的DDL操作

 

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as sys AS SYSDBA SQL> SQL> -- 创建表 记录用户的操作日志 SQL> CREATE TABLE ddl_log_test( 2 event VARCHAR2(64), 3 username VARCHAR2(64), 4 ddl_time DATE 5 ); Table created SQL> SQL> -- 创建DDL触发器 SQL> CREATE OR REPLACE TRIGGER tri_ddl_log 2 AFTER DDL ON scott.schema 3 BEGIN 4 INSERT INTO ddl_log_test VALUES(ora_sysevent, ora_login_user, SYSDATE);        -- 系统函数 5 END; 6 / Trigger created SQL> SELECT * FROM ddl_log_test; EVENT USERNAME DDL_TIME --------------------------------------- ---------------------------------------------------------------- ----------- SQL> conn scott/scott; Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as scott SQL> create table test( username varchar2(10), userage number); Table created SQL> drop table test1; Table dropped SQL> drop table test; Table dropped SQL> SELECT * FROM ddl_log_test; EVENT USERNAME DDL_TIME ------------------------------------------------------ ---------------------------------------------------------- ----------- CREATE SCOTT 2018/6/14 2 DROP SCOTT 2018/6/14 2 DROP SCOTT 2018/6/14 2

 

 

 

 

★ 由于时间为默认时间格式,无时分秒。可以自行添加

★ DDL 与 系统语句会自动执行 COMMIT , 无需手动提交 ,DML 语句需要手动提交(退出SQL控制台时,也会自动提交)。

 

系统触发器

 

                系统触发器是基于Oracle事件上所建立的一种触发器,通常与系统事件函数组合使用。

                常用的系统事件属性函数有:

 

                        ※   ORA_CLIENT_IP_ADDRESS        返回客户端IP地址

 

                          ORA_DATABASE_NAME            返回数据库名称

 

                          ORA_LOGIN_USER                     返回登录用户名

 

                          ORA_SYSEVENT                         返回触发该触发器的系统事件名称

 

                          ORA_DES_ENCRYPTED_PASSWORD        返回用户密码(DES(MD5)加密后)

 

系统触发器示例1: -- 记录用户登录退出记录

登录退出触发器基本语法:

复制代码
1
2
3
4
5
6
CREATE OR REPLACE TRIGGER trigger_name AFTER [BEFORE] LOGON [LOGFF] ON DATABASE BEGIN 执行语句; END;

 

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
SQL> conn sys/root as sysdba; Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as sys AS SYSDBA SQL> show user; User is "SYS" SQL> SQL> -- 创建一张空表记录登录日志 SQL> CREATE TABLE log_test( 2 username VARCHAR2(20), 3 logon_time DATE, 4 logoff_time DATE, 5 address VARCHAR2(30) 6 ); Table created SQL> SQL> -- 登录触发器 SQL> CREATE OR REPLACE TRIGGER trigger_logon 2 AFTER logon ON DATABASE 3 BEGIN 4 INSERT INTO log_test(username,logon_time,address) VALUES(ora_login_user, sysdate, ora_client_ip_address); 5 END; 6 / Trigger created SQL> SQL> -- 退出触发器 SQL> CREATE OR REPLACE TRIGGER trigger_logoff 2 BEFORE logoff ON DATABASE 3 BEGIN 4 INSERT INTO log_test(username, logoff_time, address) VALUES(ora_login_user, SYSDATE, ora_client_ip_address); 5 END; 6 / Trigger created Commit complete SQL> conn scott/scott; Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as scott SQL> conn sys/root as sysdba; Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as sys AS SYSDBA SQL> SELECT * FROM log_test; USERNAME LOGON_TIME LOGOFF_TIME ADDRESS -------------------- ----------- ----------- ------------------------------ SCOTT 2018/6/14 2 SCOTT 2018/6/14 2 SYS 2018/6/14 2 SCOTT 2018/6/14 2 SCOTT 2018/6/14 2 SYS 2018/6/14 2 SYS 2018/6/14 2 SYS 2018/6/14 2 SYS 2018/6/14 2 SYS 2018/6/14 2 SYS 2018/6/14 2 SCOTT 2018/6/14 2 SYS 2018/6/14 2 13 rows selected

★ 上示例中日期格式采用了默认的格式,一般情况下,日志中的时间需要采用精确到分或者秒,自行转换时间格式即可。

 

 

 

触发器管理               

 

 

                对触发器本身的一些操作,一般需要 DBA 用户操作,普通用户仅可以管理自己创建的或被授权的触发器。 

 

        一、禁止使用(临时让触发器失效)

复制代码
1
ALTER TRIGGER 触发器名 DISABLE;

        二、激活使用

复制代码
1
ALTER TRIGGER 触发器名 ENABLE;

 

        三、删除触发器

复制代码
1
DROP TRIGGER 触发器名;

 

 

 

最后

以上就是着急蜜粉最近收集整理的关于Oracle学习笔记 _17_视图和触发器Oracle学习 第17天的全部内容,更多相关Oracle学习笔记内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(71)

评论列表共有 0 条评论

立即
投稿
返回
顶部