Oracle11g全新讲解之触发器和视图索引

用户4919348

发布于 2023-05-27 14:23:14

2670

发布于 2023-05-27 14:23:14

文章被收录于专栏：波波烤鸭波波烤鸭

触发器

1.触发器的基本讲解

当特定事件出现时自动执行的存储过程

语法结构

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
declare
begin
end;

案例：对学生表进行增加删除修改后打印一句操作成功

create or replace trigger trigger01
after insert or update or delete on t_student
declare
   
begin
   dbms_output.put_line('操作成功');
end ;

2.触发器的类型

2.1 语句级触发器

关注的是执行了这条语句

案例：创建一个对学生表的增删改的审计触发器

准备表

CREATE TABLE t_audit_table
(
  stablename varchar2(30),
  nins number,--记录添加次数
  nupd number,--记录修改次数
  ndel number,--记录删除次数
  startdate date,
  enddate date
)

实现：

create or replace trigger trigger02
    after insert or delete or update on t_student
    declare
       v_count number(3);
    begin
        -- 先判断t_student在这个日志表中是否有这条记录，如果没有，要先插入数据
        select count(*) into v_count from t_audit_table where stablename='t_student';
        if v_count<=0 then
             insert into t_audit_table(stablename,nins,nupd,ndel) values('t_student', 0,0 ,0);
        end if;
  
        if inserting then
            update t_audit_table set nins=nins+1 where stablename='t_student';
        end if;
        if updating then
             update t_audit_table set nupd=nupd+1 where stablename='t_student';
        end if;
        if deleting then
            update t_audit_table set ndel=ndel+1 where stablename='t_student';
        end if;

2.2 行级触发器

和影响的行数：影响了多少行数据。那么这个触发器就会触发多少次

create or replace trigger trigger02
    after insert or delete or update on t_student
    FOR EACH ROW
    declare
       v_count number(3);
    begin
        -- 先判断t_student在这个日志表中是否有这条记录，如果没有，要先插入数据
        select count(*) into v_count from t_audit_table where stablename='t_student';
        if v_count<=0 then
             insert into t_audit_table(stablename,nins,nupd,ndel) values('t_student', 0,0 ,0);
        end if;
  
        if inserting then
            update t_audit_table set nins=nins+1 where stablename='t_student';
        end if;
        if updating then
             update t_audit_table set nupd=nupd+1 where stablename='t_student';
        end if;
        if deleting then
            update t_audit_table set ndel=ndel+1 where stablename='t_student';
        end if;

2.3 限制行级触发器

对部分数据做特定的处理，比如：不能删除管理员

create or replace trigger trigger03
   before  delete on t_student
    for each row
    when(old.stuname='小李6')  
  declare
  begin
         dbms_output.put_line('班长不能被删除');
   
        RAISE_APPLICATION_ERROR(-20001, '班长不能被删除');
  end;

视图和索引

1. 视图

1.1 视图的介绍

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表。

视图所对应的数据， 并不是真正的存储在视图中 ，而是 存储在所引用的数据表 中。
视图的结构和数据，是对数据表进行查询的结果。

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合。它存储了要执行检索的 查询语句的定义 ，以便在引用该视图时使用。

使用视图的优点:

简化数据操作：视图可以简化用户处理数据的方式。
着重于特定数据：不必要的数据或敏感数据，可以不出现在视图中。视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
提供向后兼容性：视图使用户能够在表的架构更改时，为表创建向后兼容接口。
集中分散数据。
简化查询语句。
重用SQL语句。
保护数据安全。
共享所需数据。
更改数据格式。

1.2 视图的语法

CREATE [OR REPLACE] [FORCE] VIEW '视图名'
AS '子查询'
[WITH [CASCADED|LOCAL] CHECK OPTION]
-- 只读。
[WITH READ ONLY]

说明： OR REPLACE：若所创建的试图已经存在，Oracle 自动重建该视图 FORCE：不管基表是否存在，Oracle 都会自动创建该视图 sub_query：一条完整的 SELECT 语句，可以在该语句中定义别名 WITH CHECK OPTION：数据表插入或修改的数据行，必须满足视图定义的约束 WITH READ ONLY：该视图上不能进行任何 DML 操作

简单案例

CREATE OR REPLACE VIEW v_student
AS 
SELECT * FROM t_student
WHERE age >= 18
WITH CHECK OPTION;

查看视图

select * from v_student

删除视图

DROP VIEW [IF EXISTS] '视图名'[,'视图名2'] ... [RESTRICT|CASCADE];
-- RESTRICT：限制。
-- CASCADE：级联。

DROP VIEW 'view_name';

1.3 视图案例

1.3.1 简单视图

如果视图中的语句只是 单表查询 ，并且 没有聚合函数 ，我们就称之为 简单视图 。

-- 1.简单视图：针对单表查询。没有使用聚合函数，这一类的视图我们就称为简单视图
create or replace view v_t_student
as
select * from t_student;

select * from v_t_student where id = 1;
-- 简单视图可以像普通的表结构那样去使用。不仅可以查询。还可以DML操作,本质还是对物理表做的DML操作
update v_t_student set age = 22 where id = 1;

1.3.2 带检查约束视图

视图的数据可能只是原来数据的一部分。那么我们做更新处理的时候也不能超过数据的访问

create or replace view v_t_student
as
select * from t_student where id in (1,2,3,4,5)
with check option;

select * from v_t_student;

update v_t_student set age = 33 where id = 306;

1.3.3 只读视图

有些情况下我们为了保证数据的安全。访问改视图的用户我们不允许做DML操作。这时我们可以添加 with read only 关键字

-- 只读视图：有些情况下我们为了保证数据的安全。访问改视图的用户我们不允许做DML操作。这时我们可以添加 with read only 关键字
create or replace view v_t_student
as
select id,name from t_student
with read only; -- 表示该视图只读

1.3.4 带错误视图

有的时候。创建视图的时候，表可能并不存在。创建视图后可能存在。如果此时我们需要创建这样的视图，那么需要添加 force 关键字

create or replace force view v_t_student
as
select id,name from t_student1
with read only; -- 表示该视图只读

1.3.5 复杂视图

在视图的SQL语句中。有聚会函数或者多表关联查询。

-- 复杂视图
create or replace view v_student1
as
select t1.id,t1.name,t2.name className
from t_student t1 left join t_class t2
on t1.class_id = t2.id;

select * from v_student1;
-- 在复杂视图中。我们可以DML操作主表。不能对从表做处理
update v_student1 set name = '波哥' where id = 4;
update v_student1 set classname = 'aa' where id = 302;
--复杂视图还有 聚合函数的使用-这种情况肯定不能DML操作了
create or replace view v_student1
as
select count(1) num ,avg(age) avgage from t_student;
select * from v_student1;

2.索引

索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是B树，位图索引也是如此，只不过是叶子节点不同B数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

语法：

create [unique | bitmap] index [schema.] 索引名
on [schema.] 表名 (列名1, .., 列名N);

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-05-15，如有侵权请联系 cloudcommunity@tencent.com 删除

存储

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度