Oracle Practices--PL/SQL-based triggers and packages

PL/SQL based triggers and packages

Package

/*

Packages : Encapsulation of related stored procedures, functions, variables, cursors, and exceptions

There are declarations and the composition of the subject

Advantages:1. Modular; 2. Easier application design ; 3. Information Hiding ; 4. Better Performance

*/

-- Declaration of package : keyword: Package is end

Create or Replace package My_packas procedure Packagetest (v_num number); End my_pack;--can also be the body of end;--package create or replace P Ackage Body My_packas Procedure Packagetest (v_num number) is--as/is not less, conforming to the creation of a stored procedure begin   Dbms_output.put_line (v_num ); End;end My_pack;

-- Call of the package

Begin my_pack.packagetest; end;

-- The package can also be used for declarations, such as declaring a ref cursor

Create or Replace package Ref_packis type ref_cur is ref cursor;end;

Trigger

/*

Trigger: A stored procedure that is automatically executed when a particular event occurs and cannot be called explicitly

function:1. automatically generate data; 2. Customize complex security permissions;3. provide audit and log records; 4. enable complex business logic

*/

CREATE TABLE Deptinfo (      deptid number primary key,      dname varchar2 (),      empnum number) CREATE TABLE employee ( C3/>eid number primary key,      ename varchar2,      deptid number references Deptinfo (DeptID))

-- create triggers , keywords: Trigger , After , before , on , For each row

Create or replace Trigger tri_deptnumafter--has before after instead ofinsert or update or delete on employee--for employee Operation F Or each row--be careful not to forget for each rowbegin if inserting then--with ing form   update deptinfo Set empnum = Empnum +1 where DeptID =: New.deptid; elsif updating then--ing form, remove E, consistent with English grammar   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;

-- Test trigger

SELECT * from Deptinfo;insert to Deptinfo values (1, ' Chang ', 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);d elete from employee  where Eid =1;update employee set DeptID =2 W Here DeptID =1;select  * from employee;

Article Source: http://blog.csdn.net/ysjian_pingcx/article/details/25744607