Oracle中的包、触发器 以及索引

oracle中的包

什么是包

包是有存储在一起的相关对象组成的PL/SQL结构用于逻辑组合相关的自定义类型、变量、游标、过程和函数

包的组成

包的规范（又称包头）

用于定义常量、变量、游标、过程和函数等用户与程序的接口

包的主体

是包规范的实现，包括变量、游标、过程和函数等

包体内的内容不能被外部应用程序调用

例：

--包的规范

create or replace PACKAGE test_package

as

--声明一个存储过程

procedure add_emp_pro(emp_ in emp1%rowtype);

--声明一个函数

function sum_func(num1 number,num2 number)

return NUMBER;

end test_package;

-------------------------------------------------------------------------------------

create or replace PACKAGE body test_package

as

-- 实现存储过程

PROCEDURE add_emp_pro(emp_ in emp1%rowtype)

as

begin

dbms_output.put_line('成功添加一条数据');

end;

--实现函数

function sum_func(num1 number,num2 number)

return NUMBER

as

begin

return num1+num2;

end;

end test_package;

-----------------------------------------------------------------------------------------

--包的调用

set serveroutput on;

declare

emp_ emp1%rowtype;

begin

emp_.empno:=9527;

emp_.ename:='老张';

test_package.add_emp_pro(emp_);

end;

begin

dbms_output.put_line('两数之和是'||test_package.sum_func(3,5));

end;

创建包的规范

包的调用

语法：

调用包中的存储过程

触发器

触发器是一种过程，与表关系密切，用于保护表中的数据，当一个基表被修改（insert update delete）时，触发器自动执行

触发器可实现多个表之间数据的一致性和完整性

触发器的类型有3种

1.DML触发器

oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发

2.替代触发器

有雨oracle里，不能直接对由两个以上的表建立的视图进行操作，所以给出了替代触发器。他就是oracle8专门为进行视图从左的一种处理方法

3.系统触发器

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

在执行三种DML语句时，:old 和 :new 的存在情况

例：

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;

触发器中使用谓词

谓词用来判断用户所执行的DML语句的类型

INSERTING

UPDATEING

DELETEING

例: stmtType用于记录用户的DML类型

例：

--当有人的工资发生改变时，需要做记录

--当公司多了一个雇员的时候，添加记录中只有新的工资

--当有人离职时，需要把该员工的工资修改记录删除

create or replace trigger emp_sal_trigger

after update or insert or delete 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;

例：

--触发器的禁用

ALTER TRIGGER EMP_SAL_TRIGGER DISABLE;

--触发器的启用

ALTER TRIGGER EMP_SAL_TRIGGER ENABLE;

--删除触发器

drop trigger table1test;

--查询当前用户的所有触发器

select * from user_triggers;

例：

--要求工资只能增加不能降低

create or replace trigger keep_sal_trigger

before update on emp1

for each row

begin

if :new.sal<:old.sal

then :new.sal:=:old.sal;

end if;

end;

索引：可以建立类似目录的数据库对象，实现数据快速查询

当数据库表中存在很多条记录，如大于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);

建立索引的列的特点

经常需要搜索的列

主键列

经常用在连接的列

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

经常需要排序的列

经常出现在where子句的列

不应该建立索引的列

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

只有很少数据值的列

定义为lob类型的列

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

避免限制索引

避免使用不相等操作符（<>、 ！=）

避免使用 is null or is not null

避免在where子句中使用函数

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