PL/SQL 编程(三 )程序包和包体,触发器,视图,索引

时间:2022-01-29 00:09:47

一、程序包和包体

程序包(package):存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序,则只能在定义该局部子程序的块中调用该局部子程序。

它具有面向对象程序设计语言的特点,是对这些PL/SQL 程序设计元素的封装。包类似于JAVA语言中的类,其中变量相当于类中的成员变量,过程和函数相当于类方法。

create or replace package stuinfo as
type stucur
is ref cursor;
procedure showname(scla in number, stus out stucur);
end stuinfo;

create or replace package body stuinfo as
procedure showname(scla in number, stus out stucur) as
begin
open stus for
select * from student s where s.class = scla;

end;

end stuinfo;
-- Created on 2017/8/16 by ADMINISTRATOR 
declare
-- Local variables here
i integer;
type stuc
is ref cursor;
sts stuc;
stu student
%rowtype;

begin
-- Test statements here
stuinfo.showname(95033,sts);
loop
fetch sts
into stu;
exit when sts%notfound;
dbms_output.put_line(stu.sname);
end loop;
end;

 

二、

1.触发器:

触发器和存储过程类似,触发器可以调用存储过程,但是触发器不需要调用,触发器只能由数据可的特定事件来触发。

特定的触发事件:

用户在指定的表或视图中座DML操作,主要有:insert,update,delete等。

用户做DDL操作,主要有:create,alter,drop等。

数据库事件,主要包括:logon/logoff用户的登录或注销。

startup/shutdown数据库的打开或关闭。

erros特定的错误信息等。

2.触发器的作用:

触发器可以根据不同的事件进行调用,有着更加精细的控制能力,可以完成很多普通语句完成不了的功能,主要作用:

自动生成自增长的字段。

执行更复杂的业务逻辑。

防止无意义的操作。

提供审计。

允许或限制修改某些表。

实现完整性规则。

保证数据的同步复制。

触发器的类型:

3.触发器的类型

数据操作语言(DML)触发器:

这种触发器定义到表上,对表执行insert,update,delete操作是可以激发该类型的触发器。利用该触发器可以复制,检查,替换某种符合指定条件的数据。按照触发器级别可以分两种,第一种行级触发器,表示每条记录修改时都会激发给触发器;第二种语句级触发器,表示sql语句执行实际发该触发器,与修改多少条记录无关。以数据的更改事件为准,则分为before和after两种。

数据定义语言(DDL)触发器。当create,alter,drop模式对象是会触发相关触发器。在oracle中可以简单的理解一个用户就有一个和他同名的模式,利用它可以使某些表不能被修改或删除。

复合触发器:

Oracle11g的新特性,相当于在一个触发器中包含了四种触发器,包含了before类型的语句级,before类型的行级,after类型的语句级,after类型的行级。这使得变量的传递更加方便。

instead of触发器:

这种类型的触发器通常作用在视图上,对由多个源表的视图座DML操作通常是不被允许的,这种情况可以利用instead of触发器解决问题。利用它可以把对视图的DML操作转换成对多个源表进行操作。

用户和系统事件触发器:

作用在数据库上游数据库事件激发的触发器,如登录注销,利用它可以记录数据库的登录情况。

4.触发器例子:

--建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去。 
CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
BEFORE
DELETE --指定触发时机为删除操作前触发
ON scott.emp
FOR EACH ROW --说明创建的是行级触发器
BEGIN
--将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

 三、视图和索引

视图是一个或多个表或视图导出的表。视图是一个虚表,试图所对应的数据不进行实际存储,数据库中只存储视图的定义,对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基表。

我们可以像使用表一样使用视图,但需要注意的是:查询视图没有什么限制,插入/更新/删除视图的操作会受到一定的限制;所有针对视图的操作都会影响到视图的基表;为了防止用户通过视图间接修改基表的数据,可以将视图创建为只读视图(带上with read only选项)。

--建表:
create table Student
(Id
char(6),
Name
varchar2(8),
Sex
char(2),
Class_id
char(4));

create unique index index_id on Student(Id);

create index index_name on Student(Name);

create bitmap index index_sex on Student(Sex);
conn scott/tiger;

grant insert,update,delete on emp to system;

conn system
/orcl1234;

create or replace VIEW v_emp
as
select empno,ename,job,hiredate,deptno
from scott.emp;

索引

为了提高查询的速度,当用户对查询速度不满意而需要对数据库的性能进行调校时,优先考虑建立索引。
CREATE INDEX idx_ename ON employee (ename, sal DESC);

适当的使用索引可以提高数据检索速度,可以给经常需要进行查询的字段创建索引。

向表中“添加”行或从表中“删除”行时,必须花费额外的时间来更新该表的索引,所以当需要从大表中检索少数几行时创建索引。一般我们认为当任何单个查询要检索的行小于整个表总行数的10%时,索引就非常有用。

表的主键和唯一键将自动创建索引。