Oracle学习笔记 _17_视图和触发器Oracle学习第17天

104 阅读 0 评论 69 点赞

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

Oracle学习第17天

—— 视图和触发器

视图

视图也是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

二、DDL触发器（DBA账户创建）

DDL（Data Definition Language）：数据定义语言。

CREATE、ALTER、DROP

三、系统触发器（DBA账户创建）

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

DML触发器基本语法：

创建或修改：

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' 执行过程中出错

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

DDL触发器基本语法

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

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

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： -- 记录用户登录退出记录

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

CREATE OR REPLACE TRIGGER trigger_name
AFTER [BEFORE] LOGON [LOGFF] ON DATABASE
BEGIN
  执行语句;
END;

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 用户操作，普通用户仅可以管理自己创建的或被授权的触发器。

一、禁止使用（临时让触发器失效）

ALTER TRIGGER 触发器名 DISABLE;

二、激活使用

ALTER TRIGGER 触发器名 ENABLE;

三、删除触发器

DROP TRIGGER 触发器名;

最后

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

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

本文分类：『Oracle』
浏览次数：104 次浏览
发布日期：2023-05-23 03:51:01
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujocf0_13_j_18_z.html

Oracle学习笔记 _17_视图和触发器Oracle学习第17天

Oracle学习第17天

—— 视图和触发器

视图

触发器

最后

评论列表共有 0 条评论

发表评论取消回复

Oracle学习笔记 _17_视图和触发器Oracle学习 第17天

Oracle学习 第17天

—— 视图和触发器

视图

触发器

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

Oracle学习笔记 _17_视图和触发器Oracle学习第17天

Oracle学习第17天

发表评论取消回复