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子句中使用函数
避免在比较时使用不匹配的数据类型