Oracle（六）view,trigger

视图

定义

视图是oracle又一种数据对象，视图的主要的用处是简化操作，提高安全，满足不同用户的查询需求，视图不是一个真正存在的物理表，它是根据别的表动态生成的。

 

快速入门

创建和emp表(empno,ename,job)完全一致的试图

基本语法

Create view 视图名字 asselect语句 [with read only]

 

说明：如果创建视图的时候，末尾带withread only，表示该试图只能读，不能进行其他操作。如果没有带with read only，表示可以进行其他操作。

如果创建视图的时候，不希望用户通过视图对源表进行其他操作，建议带with read only

 

实例：

创建视图

Create view empview as selectempno,ename,job from emp;

打开视图

Select * from empview;

 

视图可以简化操作：

比如：我们希望查询雇员的名字和部门编号和部门名称。

传统：需要查询两张表。

试图简化：

Create or replace view empview as selectemp.ename,dept.deptno,dept.dname from

Emp,dept where emp.deptno=dept.deptno withread only;

以后可以直接查询视图

视图管理

创建视图和修改视图

Create or replace view 视图名 as sql[可以多表..] [with read only]

删除视图

Drop view 视图名

 

试图和表的区别

1.    表占用空间，试图不需要

2.    试图不能加索引

3.    试图可以简化操作

4.    提高安全性

 

 

触发器

很多关系数据库中都提供一种技术，可以在用户进行某种操作的时候，自动的进行另外一个操作，这种技术称为触发器技术。

 

触发器是指存放在数据库中，被隐含执行的存储过程，可以支持dml触发器，还支持基于系统事件（启动数据库，关闭数据库，登陆和ddl操作建立触发器）。

 

提出问题：

当用户登录的时候，自动的记录该用户的名字，登陆时间，ip…

当用户在星期天对某张表进行delete的时候，我们提示不能这样做

当用户删除某条记录的时候，自动将该记录保存到另外一张表中

触发器的分类

Dml（insert , delete , update）触发器

Ddl（create table create view…drop…）触发器

系统触发器（与系统相关的触发器，比如用户登录，退出，启动数据库，关闭数据库）

语法格式

Create [or replace] triger tri1

{before | after}   --表示在此之前触发还是之后

{insert | delect |update [of column[, clumn…]]}

On 用户名.表名   --表示对那张表进行触发

[for each row]     --行级触发器

[when condition]

Begin

触发器要做的事

End;

快速入门

1．

在某张表（my_emp）添加一条数据的时候，提示‘添加了一条数据’

 

（1）创建一张表

Create table my_emp(

Id number,

Name varchar2(32)

);

（2）创建一个触发器

Create otreplace trigger tri1

After insertinto on scott.my_emp

Begin

Dbms_output.put_line(‘添加了一条’);

End;

 

当在emp这个表中添加数据，就会自动触发。

 

2.

在某张表(my_emp)修改多条数据的时候，提示多次‘修改了数据’=>行级触发器和语句级触发器。

 

Create or replace trigger tri2

After update on

Scott.emp

For each row  --表示这是一个行级触发器（同时修改几行，就会触发几次）

Begin

Dbms_output.put_line(‘修改了一条数据’);

End;

 

测试：Update emp set sal=sal+10;   同时修改所有

因为加了fro each row ,所以每行都会触发，会触发14次

 

3.

       Dml触发器：

案例：为了静止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全。

Create or replace trigger tri1

Before delect on

Scott.emp

Begin

If to_char(sysdate,’day’)in(‘星期六’,’星期日’)

Then

Dbms_output.put_line(‘对不起，休息日不能删除员工’);

--上面只是会提示一句话，并不会阻止删除操作，所以需要以下

RAISE_APPLICATION_ERROR(-20001,’对不起休息日不能删除员工’);

End;

 

特别说明：

sysdate：会显示当前系统时间。

To_char(sysdate,’day’)：会字符串显示星期几，例如星期六

RAISE_APPLICATION_ERROR：这个过程，是oracle提供的，会抛出一个异常阻止此操作，可以传入两个参数，第一个是自定义的错误号-20000到20999之间，第二个参数是提示一个信息。

 

 

使用条件谓词来精确提示，用户的操作

当触发器中同时包含多个触发事件（insert,update,delete）时，为了在触发器代码中区分具体的触发事件，可以使用三个条件

Inserting

Updating

Deleting

 

案例：为了禁止工作人员在休息日改变员工信息，可以建立defore语句触发器，从而实现数据的安全，再给出提示，明确提示用户是进行inset,update还是delete操作

 

Create or replace trigger tri3

Before

Insert or update or delete on scott.emp

Begin

Case

When inserting then

Dbms_output.put_line(‘请不要添加’);

Raise_application_error(-20002,’请不要添加’);

When updating then

Dbms_output.put_line(‘请不要修改’);

Raise_application_error(-20003,’请不要修改’);

When deleting then

Dbms_output.put_line(‘请不要删除’);

Raise_application_error(-20004,’请不要删除’);

End case;

End;

 

 

使用 :old 和 :new

：new 修饰符访问操作完成后列的值

：old  修饰符访问操作完成钱列的值

 

案例：

1.    在修改emp表雇员的薪水时，显示雇员工资修改前和修改后的值。

2.    如何确保在修改员工工不能低于原有工资。

Create or replace trigger tri4

Before

Update on Scott.emp

For each row                    --强制要加行级

Begin

If :new.sal<:old then

Dbms_output.put_line(‘工资不能低于原来的工资’);

Raise_application_error(-20005,’工资不能低于原来工资’);

Else

Dbms_output.put_line(‘原来的工资是’||:old.sal||’现在的工资’||:new.sal);

End if;

End;

 

 

触发器练习

1.

编写一个触发器，保证当用户在删除一张表（emp）记录的时候，自动把删除的记录备份到另外一张表(emp_bak)中…

 

先建立一张备份表

Create table emp_bak(

In number,

Name varchar2(2000)

);

 

Create orreplace trigger tri4

Before delete on

Scott.emp

For each row

Begin

--执行语句

       Insertinto emp_bak(id,name) values(:old.empno,:old.ename);

End;

 

2.

编写一个新的触发器，如何控制员工的新工资不能低于原来的工资，同时也不能高出原来工资的20%，使用约束显然无法实现该规则。

Create or replace trigger tri5

Before update on

Scott.emp

For each row

Begin

If (:new.sal<:old.sal or :new.sal>:old.sal*1.2) then

       Dbms_output.put_line(‘工资范围不对！’)；

       --阻止执行

       Raise_application_error(-20010,’工资范围不对’);

       Endif;

       End;

 

3.

阻止把小于18岁的用户增加到数据库emp表中，请编写一个触发器。

--emp2[id,name,birthday]

创建一张表，该表有一个字段birthday

Create table emp2(

Id number primary key,

Name varchar2(32),

Birthday date)

 

--创建触发器

Create or replace trigger tri6

Before insert on

Scott.emp2

For each row

Begin

If add_months (:new.birthday,18*12)>sysdate then

Dbms_output.put_line(‘年龄太小了’)；

Rasie_application_error(-20011,’年龄太小了’);

End if;

End;

 

 

系统触发器

系统触发器，主要是针对oracle事件的触发器，比如用户登录(logon logoff)数据库的启动

或者关闭(startup,shutdown…)    注：需要系统管理员(sys)来完成

常用的属性函数

Ora_client_ip_address  //返回客户端的ip

Ora_database_name  //返回数据库名

Ora_login_user  //返回登陆用户名

Ora_sysevent  //返回触发触发器的系统事件名

Ora_des_encrypted_password  //返回用户des(md5)加密后的密码

 

基本语法：

Create or replacetrigger 触发器名

After [before] logon [logoff] on database

Begin

--执行语句

End;

 

 

案例：记录用户的登陆和退出事件

完成登陆（logon）和退出（logoff）触发器

创建一张表，用于保存用户登录或退出的情况

用sys用户建立

Create table log_table(

Username varchar2(20),

Logon_time date,

Logoff_time date,

Address varchar2(20));

 

--创建触发器

--1.登陆触发器

Create or replace trigger tri7

After logon on database

Begin

Insert into log_table (username,logon_time,address)

values(ora_login_user,sysdate,ora_client_ip_address);

End;

 

--2.退出触发器

Create or replace trigger tri7

before logoff on database

Begin

Insert into log_table (username,logoff_time,address)

values(ora_login_user,sysdate,ora_client_ip_address);

End;

 

 

Ddl触发器

Ddl触发器主要的用途是，记录在oracle中，发生的ddl操作 

注：需要系统管理员(sys)来完成

 

基本语法：

Create or replacetrigger 触发器名

After ddl on 用户名.schema               --只有after

Begin

--执行语句

End;

 

快速入门

案例：编写一个触发器，记录某个用户进行的ddl操作

1.    创建一张表

 

Create table my_ddl_record

(event varchar2(64),

Username varchar2(64),

Ddl_time date);

 

2.    创建触发器

Create ot replace trigger tri8

After ddl on scott.schema

Begin

Insert into my_ddl_recordmvalues(ora_sysevent,ora_login_user,sysdate)

End;

 

 

触发器的管理

(1).禁用触发器

Alter trigger 触发器名disable;

(2).激活触发器

Alter trigger 触发器名enable;

(3).激活一张表所有触发器

Alter table 表名enable all trigger;

(4).禁用一张表所有触发器

Alter table 表名disable all trigger;

(5).删除触发器

Drop trigger 触发器名;

 

 

When <异常情况名>then

<异常处理代码>