Oracle实践--PL/SQL基础之触发器和程序包

pl/sql基础之触发器和程序包

程序包

/*

程序包：对相关存储过程，函数，变量，游标和异常等对象的封装

有声明和主体组成

优点：1.模块化；2.更轻松的应用程序设计;3.信息隐藏;4.性能更佳

*/

--程序包的声明：关键字：package is end

create or replace package my_pack
as
 procedure packagetest(v_num number);
end my_pack;--也可以是 end;
--程序包的主体
create or replace package body my_pack
as
 procedure packagetest(v_num number)
 is-- as/is不能少，符合存储过程的创建
 begin
   dbms_output.put_line(v_num);
 end;
end my_pack;

--程序包的调用

begin
 my_pack.packagetest(500);
end;

--程序包也可以只用来声明，比如声明一个ref游标

create or replace package ref_pack
is
 type ref_cur is ref cursor;
end;

触发器

/*

触发器：当特定事件出现事自动执行的存储过程，不能被显试地调用

功能：1.自动生成数据；2.自定义复杂的安全权限；3.提供审计和日志记录；4.启用复杂的业务逻辑

*/

create table deptinfo(
      deptid number primary key,
      dname varchar2(20),
      empnum number
)
create table employee(
      eid number primary key,
      ename varchar2(20),
      deptid number references deptinfo(deptid)
)

--创建触发器，关键字：trigger，after，before，on，for each row

create or replace trigger tri_deptnum
after--有before after instead of
insert or update or delete on employee -- 对employee操作
for each row--注意别忘了 for each row
begin
 if inserting then--用ing形式
   update deptinfo set empnum = empnum +1 where deptid = :new.deptid;
 elsif updating then-- ing形式，去掉e，与英文语法一致
   update deptinfo set empnum = empnum -1 where deptid = :old.deptid;
   update deptinfo set empnum = empnum +1 where deptid = :new.deptid;
 elsif deleting then
   update deptinfo set empnum = empnum -1 where deptid = :old.deptid;
 end if;
end;

--测试触发器

select * from deptinfo;
insert into deptinfo values(1,'长安大学',0);
insert into deptinfo values(2,'helloworld',0);
 
insert into employee values(1,'ysjain',1);
insert into employee values(2,'ysjain',1);
insert into employee values(3,'ysjain',2);
insert into employee values(4,'ysjain',2);
delete from employee  where eid =1;
update employee set deptid =2 where deptid =1;
select  * from employee;