Oracle practice -- triggers and packages based on PL/SQL

Triggers and packages based on PL/SQL

Package

/*

Package: encapsulate objects such as stored procedures, functions, variables, cursors, and exceptions.

Voice and body composition

Advantages: 1. modularization; 2. Easier application design; 3. Information Hiding; 4. Better performance

*/

--Package Declaration: Keyword: package is end

Create or replace package my_packas procedure packageTest (v_num number); end my_pack; -- or end; -- create or replace package body my_packas procedure packageTest (v_num number) is -- as/is should not be small, meet the creation of the stored procedure begin dbms_output.put_line (v_num); end my_pack;

-- Package call

begin my_pack.packageTest(500);end;

-- The package can be declared only, for example, declaring a ref cursor.

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

Trigger

/*

Trigger: when a specific event occurs, the stored procedure is automatically executed and cannot be explicitly called.

Functions: 1. automatically generate data; 2. Customize complex security permissions; 3. provide audit and logging; 4. Enable complex business logic

*/

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))

--Create a trigger, Keyword: trigger, after, before, on, for each row

Create or replace trigger tri_deptNumafter -- before after instead ofinsert or update or delete on employee -- operate on employee for each row -- Do not forget for each rowbegin if inserting then -- update deptinfo set in ing format empnum = empnum + 1 where deptid =: new. deptid; elsif updating then -- ing form, remove e, consistent with the English syntax 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 the trigger

Select * from deptinfo; insert into deptinfo values (1, 'changan Emy ', 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;