数据库中触发器和索引的使用
来源:互联网 发布:淘宝口红代购知乎 编辑:程序博客网 时间:2024/05/21 20:29
触发器
什么是触发器?
触发器是一种过程,与表关系密切,用于保护表中的数据。当一个表被修改(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;
--当有人的工资发生改变时,需要做记录create or replace trigger emp_sal_triggerafter update on emp1for 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_triggerafter update or insert or update on emp1for 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;执行结果:
/* 主键约束/唯一约束 : 系统会自动建立索引,以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_indexcreate unique index adopt_time_index on pet(adopt_time desc);--在pet表的type_id字段上创建位图索引type_id_bitmap_indexcreate bitmap index type_id_bitmap_index on pet(type_id);--在pet表的health、love字段上创建组合索引health_love_indexcreate index health_love_index on pet(health,love);--创建基于函数TO_CHAR(adopt_time,'YYYY')索引to_char_indexcreate index to_char_index on pet(TO_CHAR(adopt_time,'YYYY'));--在pet表的master_id 字段上创建反向键索引master_id_reverse_indexcreate index master_id_reverse_index on pet(master_id) reverse;--删除在pet表上创建的反向键索引master_id_reverse_indexdrop 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.避免在比较时使用不匹配的数据类型
- 数据库中触发器和索引的使用
- 数据库中触发器的使用
- 谈数据库索引和Sqlite中索引的使用
- 谈数据库索引和Sqlite中索引的使用
- 谈数据库索引和Sqlite中索引的使用
- [转载]谈数据库索引和Sqlite中索引的使用
- MYSQL数据库的索引、视图、触发器、游标和存储过程
- MYSQL数据库的索引、视图、触发器、游标和存储过程
- MYSQL数据库的索引、视图、触发器、游标和存储过程
- SQL数据库触发器和游标的使用
- Oracle中查看无效的对象、约束、触发器和索引
- MYSQL数据库学习----索引和触发器
- 数据库中主键和索引的区别以及使用场景
- 数据库中主键和索引的区别以及使用场景
- Oracle中序列和触发器的使用
- Oracle中序列和触发器的使用
- 触发器中OLD和NEW的使用
- 数据库中使用索引
- Android EasyRTMP 介绍
- java学习第24天,学习String字面量
- Python连接MySQL数据库执行sql语句时的参数问题
- 应试C++
- Android关于触摸事件跟点击事件两个方法的关系
- 数据库中触发器和索引的使用
- Java:如何用链表实现队列
- erlang shell 命令中文使用说明
- HTML(五) 使用img标签
- Air800(GPRS+GPS模块)连接OneNet平台
- LeetCode
- 150. Evaluate Reverse Polish Notation
- ElasticSearch 常用的查询过滤语句
- 产品经理如何学机器学习——一篇以产品为中心的机器学习概论