数据库中触发器和索引的使用

本文介绍了数据库中的触发器,强调了其在数据一致性和完整性中的作用。触发器分为DML触发器、替代触发器和系统触发器,其中DML触发器在数据插入、更新或删除时自动执行。同时,文章提到了索引的创建和应用,适合于经常搜索、作为主键、链接、排序的列,但不适合于查询少、数据值单一、LOB类型或修改频繁的列。在使用索引时,应避免不等操作符、null判断、函数和不匹配数据类型的比较。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

触发器

 什么是触发器?

   触发器是一种过程,与表关系密切,用于保护表中的数据。当一个表被修改(insert、update或delete)时,触发器字动执行。触发器课实现多个表之间数据的一致性和完整性。

 触发器的类型有3种:

   DML触发器:Oracle可以在DML语句进行触发,可以在DML操作前或操作后进行触发,并且可以对每个行或语句操作上进行触发。

   替代触发器:由于在Oracle里,不能直接对由两个以上的表建立的视图进行操作,所以给出了替代触发器。它就是Oracle 8专门为进行视图操作的一种处理方法。

   系统触发器:Oracle从8i开始提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的事件中进行触发,如Oracle系统的启动与关闭等。

创建DML触发器:

  DML触发器由DML语句激发,并且由该语句的类型决定DML触发器的类型

  可以定义DML触发器进行insert、update、delete操作

  DML触发器可以在上述操作之前或之后激发,也可以在行或语句操作上激发

触发器的语法:

 

触发器的基本使用应用实例:

-- 当有员工的工资发生变化时,触发事件修改该员工对应的部门工资数据
create or replace trigger dept_sal_trigger
--触发的时机
after 
--触发的事件
update or insert or delete on emp1
--把员工对应的部门工资进行修改
declare
  cursor total_sal is select sum(sal) total_sal,deptno from emp1 group by deptno;
  dept_s dept_sal%rowtype;
begin
  -- delete from dept_sal;
  for dept_s in total_sal loop
    update dept_sal set total_sal=dept_s.total_sal where deptno=dept_s.deptno;
    -- insert into dept_sal values(dept_s.deptno,dept_s.total_sal);
  end loop;
  -- 不要添加commit;
EXCEPTION
  when OTHERS THEN rollback;
end;
创建DML触发器
 伪记录 ":old"和":new"
   ":old" 代表操作完成前的旧记录
   ":new" 代表操作完成后的新记录
 在执行三种DML语句时 ":old"和":new"的存在情况
   

创建行级触发器 (for each row)应用实例:
在表emp_sal与emp1表关联,创建触发器
当emp1表中一个人的工资发生改变时,记录该事件
--当有人的工资发生改变时,需要做记录
create or replace trigger emp_sal_trigger
after update on emp1
for each row --行级触发器
begin
  insert into emp_sal values(:old.empno, :new.sal, emp_sal_seq.nextval,sysdate,:old.sal);
exception
  when others then dbms_output.put_line('添加失败');
end;
测试代码:
update emp1 set sal = sal*1.1;  --指定行级触发器
commit;
注意:触发器中不能有commit和rollback语句

触发器中的谓词

 

应用实例:

--当有人的工资发生改变时,需要做记录
--当公司多了一个雇员的时候,添加记录中只有新的工资(没有old.sal,没有new.sal)
--当有人离职时,需要把该员工的工资修改记录删除
create or replace trigger emp_sal_trigger
after update or insert or update on emp1
for each row --行级触发器
begin
  if updating
    then insert into emp_sal values(:old.empno, :new.sal, emp_sal_seq.nextval,sysdate,:old.sal);
  elsif inserting
    then insert into emp_sal values(:new.empno,:new.sal,emp_sal_seq.nextval,sysdate,0); 
  else
    delete from emp_sal where empno=:old.empno;
  end if;
exception
  when others then dbms_output.put_line('添加失败');
end;
测试代码:

--执行(测试)语句
--添加一条数据
insert into emp1 values(9527,'TOM','CLERK',7902,sysdate,1200,null,20);
COMMIT;

--修改工资
UPDATE emp1 set sal=sal*1.5 where empno=9527;
COMMIT;

--员工离职
delete from emp1 where empno=9527;
COMMIT;
对触发器本身的删除和修改

--触发器禁用
alter trigger emp_sal_trigger disable;
--触发器开启
alter trigger emp_sal_trigger enable;
--删除触发器
drop trigger emp_sal_trigger;
--查询当前用户的所有触发器
select * from user_triggers;
执行结果:


索引(给数据加目录)
  当数据库表中存在很多条记录,如大于10万条时,查询速度便成为一个问题
  在书中查询某内容时,首先在目录中查询所需知识点,然后根据目录中提供的页码找到要查询内容,大大缩短了查询时间。
可以建立类似目录的数据库对象,实现数据快速查询,这就是索引。
  按照索引的存储结构分类
 B树索引(重点)
 位图索引
 反向键索引
按照索引值是否唯一分类
 唯一索引
 非唯一索引
按索引列分类
 单列索引
 组合索引
 基于函数的索引

索引的语法:

  
/*
  主键约束/唯一约束 : 系统会自动建立索引,以SYS_开始
  1. 为一个表的列或组合列建立索引后,读取的速度加快
  2. 但写的速度却减慢了,因为插入,修改和删除数据后,还要更新索引
  3. 索引也需要空间,系统要占用大约为表1.2倍的硬盘和内存空间来保存索引,增加了空间负担
  
  索引创建的原则
  1. 在大表上建立索引才有意义
  2. 在where子句或是连接条件上经常引用的列上建立索引
  3. 索引的层次不要超过4层
  
  提高查询效率是以消耗一定的系统资源为代价的,索引不能盲目的建立,这是考验一个dba是否优秀的很重要的指标
  
  索引分类:
  1. 按照数据存储方式,分为B*树,反向索引,位图索引
  2. 按照索引列的个数分为,单列索引,复合索引
  3. 按照索引列值的唯一性,分为唯一索引和非唯一索引
  此外还有函数索引,全局索引,分区索引等.
*/

--查看有多少张表
select * from user_tables;

--查看表t10结构
select column_name,data_type,data_length,nullable,data_default from all_tab_columns where lower(table_name)='t10';

--查看表t10内容
select * from t10 ;

--建立索引
create index idx_t10_name on t10(name);

--删除索引
drop index idx_t10_name;

--查看所有索引(从字典数据表中查询)
select index_name,table_name from user_indexes where lower(index_name)='idx_t10_id_name' ;

--建立复合索引
create index idx_t10_id_name on t10(id,name);
--在pet表的adopt_time字段上创建降序唯一索引adopt_time_index
create unique index adopt_time_index on pet(adopt_time desc);
--在pet表的type_id字段上创建位图索引type_id_bitmap_index
create bitmap index type_id_bitmap_index on pet(type_id);
--在pet表的health、love字段上创建组合索引health_love_index
create index health_love_index on pet(health,love);
--创建基于函数TO_CHAR(adopt_time,'YYYY')索引to_char_index
create index to_char_index on pet(TO_CHAR(adopt_time,'YYYY'));
--在pet表的master_id 字段上创建反向键索引master_id_reverse_index
create index master_id_reverse_index on pet(master_id) reverse;
--删除在pet表上创建的反向键索引master_id_reverse_index
drop index master_id_reverse_index;
建立索引的列的特点:

  1.经常需要搜索的列
  2.主键列

  3.经常用在链接的列

  4.经常需要根据范围搜索的列

  5.经常需要排序的列

  6.经常出现在where子句的列

不应该建立索引的列:

  1.查询中很少使用或参考的列

  2.只有很少数据值的列

  3.定义为lob类型的列

  4.修改性能远远大于检索性能

避免限制索引:

  1.避免使用不等操作符(<>、!=)

  2.避免使用is null or is not  null

  3.避免在where子句中使用函数

  4.避免在比较时使用不匹配的数据类型

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值