程序包
程序包是对相关过程、函数、变量、
游标和异常等对象的封装
程序包由规范和主体两部分组成
1,规范:声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
2,主体:声明程序包私有对象和实现在包规范中声明的子程序和游标
创建程序包创建程序包规范示例：
CREATE [OR REPLACE]
PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
创建程序包主体示例：
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
将不同的功能集成 创建程序包示例：
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
Procedure print_ename(p_empno number) is
L_ename emp.ename%type;
Begin
Select ename into l_ename from emp where empno=p_empno;
Dbms_output.put_line(l_ename);
Exception
When no_data_found then
Dbms_output.put_line('Invalid employee number');
End print_ename;
Procedure print_sal(p_empno number) is
L_sal emp.sal%type;
Begin
Select sal into l_sal from emp where empno=p_empno;
Dbms_output.put_line(l_sal);
Exception
When NO_DATA_FOUND then
Dbms_output.put_line('Invalid employee number');
End print_sal;
End employee_pkg;
/
程序包的优点1,模块化
2,更轻松的应用程序设计
3,信息隐藏
4,新增功能
5,性能更佳
程序包中的游标游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是：
1,用 %ROWTYPE 属性引用表定义的记录类型
2,程序员定义的记录类型
有关子程序和程序包的信息USER_OBJECTS 视图包含用户创建的子程序和程序包的信息
SELECT object_name, object_type
FROM USER_OBJECTS WHERE object_type IN ('PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY')
;
USER_SOURCE 视图存储子程序和程序包的源代码
SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';
总结1,子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用
2,有两种类型的PL/SQL子程序，即过程和函数
3,过程用户执行特定的任务，函数用于执行任务并返回值
4,程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
5,程序包由两部分组成，即包规范和包主体
6,使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳
触发器
触发器简介：触发器是一种特殊的存储过程，当特定对象上特定事件出现时，自动触发执行的代码块。
触发器的作用：1,实现复杂的数据完整性规则。
2,自动生成派生数据。
3,提供审计和日志记录。
4,启用复杂的业务逻辑。
5,实施更复杂的安全性检查。
触发器格式：
create [or replace] trigger 触发器名 触发时间 触发事件
on 对象名
[for each row]
pl/sql 语句
触发器的通用语法 1 CREATE [OR REPLACE] TRIGGER trigger_name
2 {BEFORE | AFTER | INSTEAD OF}
3 triggering_event { dml_event_list | ddl_event_list| database_event_list}
4 ON trigger_object { [database] | [schema.] [table_or_view_name]}
5 referencing_clause
6 [FOR EACH ROW]
7 [WHEN trigger_condition]
示例：在employees表上建一触发器，在插入或修改部门号时触发，如果该雇员部门号不是“80”，则commission_pct列值置为0。
SQL > create trigger biufer_employees_department_id
before insert or update of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id< >80 )
begin
:new_value.commission_pct :=0;
end;
/
测试触发器是否有效。
SQL > insert into employees
(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct ) values( 12345,’chen’,’donny’, sysdate, ‘AD_PRES', ‘donny@hotmail.com’,60,10000,.25);
查看结果：
SQL >select commission_pct from employees where employee_id=12345;
触发器的种类1,DML触发器
2,INSTEAD OF触发器
3,DDL触发器
4,系统触发器
一、DML触发器触发器时机:1,BEFORE 触发器.2,AFTER 触发器。
触发器所依赖的表对象不同
1,语句级触发器 各个语句触发器都只会针对指定语句激活一次
2,行级触发器为受到影响的各个行激活的触发器 for each row、:old和: new相关标识符
触发器限制
DML触发器WHEN子句
语法是：WHEN trigger_condition
DML事件包括：INSERT、UPDATE、DELETE
触发器谓语：INSERTING、UPDATING和DELETING
语句级触发器-记录对信息的操作时间和用户，例1：创建一语句级触发器对修改表的时间、人员进行日志记录。
1） 建立实验表
SQL > create table employees_copy as select * from hr.employees;
2） 建立日志表
SQL >create table employees_log( who varchar2(30), when date);
3） 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。
SQL > create or replace trigger biud_employee_copy
before insert or update or delete
on employees_copy
begin
insert into employees_log( who,when)
values( user, sysdate);
end;
4） 测试
SQL >update employees_copy set salary= salary*1.1;
SQL >select *from employees_log;
5） 确定是哪个语句起作用？即是insert/update/delete中的哪一个触发了触发器？
行级触发器-自动生成用户信息数据，例2：创建一行级触发器，为主键生成自增序列号。
1）首先，创建一个实验表和一个序列。
SQL >drop table foo;
SQL >create table foo(id number, data varchar2(20));
SQL >create sequence foo_seq;
2）然后，创建触发器
SQL > create or replace trigger bifer_foo_id_pk
before insert on foo
for each row
begin
select foo_seq.nextval into :new.id from dual;
end;
3）插入数据进行测试
SQL >insert into foo(data) values(‘donny’);
SQL >insert into foo values(5,’chen’);
4）查询结果，测试触发器是否生效。
SQL >select * from foo;
INSTEAD OF触发器替代触发器（Instead-of trigger）只能定义在视图上。是行触发器,触发器的主体部分把对视图的插入操作转换成详细的对各个表的插入
例：演示Instead of触发器的应用案例。
1）创建一个视图company_phone_book，其中name列的定义来自hr.employees表中的两个字段的联合。
SQL >create or replace view company_phone_book as
select first_name||’, ’||last_name name, email, phone_number,
employee_id emp_id from hr.employees;
2）尝试更新视图company_phone_book中的列email和name。
先查询要更新的数据在视图和表中的情况。
SQL >Select name from company_phone_book where emp_id=100;
SQL >Select first_name,last_name from hr.employees where employee_id =100;
更新视图语句：
SQL >update company_phone_book set name=’chen1, donny1’ where emp_id=100;
出现如下的错误提示：ORA-01733: 此处不允许虚拟列
3）创建instead of 触发器更新视图
SQL >create or replace trigger update_name_company_phone_book
instead of
update on company_phone_book
begin
update hr.employees
set employee_id=:new.emp_id,
first_name= substr(:new.name,1,instr(:new.name, ',')-1),
last_name= substr(:new.name, instr(:new.name, ',')+2),
phone_number=:new.phone_number,
email=:new.email
where employee_id=:old.emp_id;
end;
/
4）测试：执行步骤2）中的更新视图语句，然后在其对应的表和视图中查看效果。
重新更新视图语句：
SQL >update company_phone_book
set name=’chen1, donny1’
where emp_id=100;
小结1,触发器是当满足特定事件时自动执行的存储过程。
2,触发器由触发器名称、触发语句、触发器限制和触发操作几部分组成。
3,按照触发事件和触发对象的不同，触发器一般分为以下几种： DML触发器、替代触发器、DDL触发器和系统触发器
4,DML触发器是使用最多的触发器。
5,Instead of触发器定义在视图上。替代触发器是行触发器。