[推荐]Oracle数据库PL-SQL学习资料之七——子程序、包和触发器 - 数据库系统原理课件 - 课外天地李树青

9 子程序
PL/SQL语句块分为匿名和命名两种，匿名语句块每次编译运行且不能存储于数据库中，而且由于没有名称，所以也没法调用。
命名语句块显然具有相反的特点，具体包含过程、函数、包和触发器，其中过程和函数统称为子程序。

9、1 子程序
包含过程和函数

9、1、1 简单使用方法
1）过程的使用
创建为：
set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE HelloWorldProc
AS
BEGIN
dbms_output.put_line('Hello World!');
END HelloWorldProc;
/

调用为：
BEGIN
HelloWorldProc;
END;
/

2）函数的使用
和过程很相似，但是具有返回值，可以放入表达式进行调用，创建为：
CREATE OR REPLACE FUNCTION HelloWorldFunc
RETURN varchar IS
BEGIN
return 'Hello World!';
END HelloWorldFunc;
/

使用为：
set serveroutput on size 1000000;
begin
dbms_output.put_line(helloworldfunc());
end;
/

说明：replace表示如果存在对象则覆盖。如果再次编译出错，则对象被删除而没被创建。

9、1、2 子程序参数

9、1、2、1 简单使用
1）具有参数的过程
set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE MyProc
(
p_str varchar
)
AS
BEGIN
dbms_output.put_line(p_str);
END MyProc;
/

BEGIN
MyProc('Hello World!');
END;
/

复杂的例子：
set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE SelectStudent
(
  p_sid  in stu.sid%TYPE
)
AS
v_sname varchar(8);
BEGIN
  select name into v_sname from stu where sid=p_sid;
  DBMS_OUTPUT.PUT_LINE(v_sname);
END SelectStudent;
/

BEGIN
SelectStudent(1);
END;
/

2）具有参数的函数
CREATE OR REPLACE FUNCTION MyFunc
(
p_str varchar
)
RETURN varchar IS
BEGIN
return upper(p_str);
END MyFunc;
/

set serveroutput on size 1000000;
begin
dbms_output.put_line(MyFunc('hello world!'));
end;
/

注意：不能在子程序中约束参数，如长度和精度等，这些约束信息是从实际参数动态获取的。如下面的定义有误：
CREATE OR REPLACE FUNCTION MyFunc
(
p_str varchar(6)
)
RETURN varchar IS
BEGIN
return upper(p_str);
END MyFunc;
/

但是，由于没有形式参数约束，所以在实际参数赋值时可能会产生运行时错误，如：
CREATE OR REPLACE FUNCTION MyFunc
(
  p_str out varchar
)
RETURN varchar IS
BEGIN
  p_str:='1234567890';
  return upper(p_str);
END MyFunc;
/

set serveroutput on size 1000000;

declare
str varchar(6);
begin
dbms_output.put_line(MyFunc(str));
end;
/

错误为：
declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"SYS.MYFUNC", line 7
ORA-06512: 在line 4

也可以使用%type属性来获取其他变量的类型，虽然在形式参数中可以以此来增加参数约束，但是系统仍然会可能产生运行时错误，如：
CREATE OR REPLACE PACKAGE Globals AS
v_str varchar(6);
END Globals;
/

CREATE OR REPLACE FUNCTION MyFunc
(
  p_str out Globals.v_str%type
)
RETURN varchar IS
BEGIN
  p_str:='1234567890';
  return upper(p_str);
END MyFunc;
/

set serveroutput on size 1000000;

declare
str Globals.v_str%type;
begin
dbms_output.put_line(MyFunc(str));
end;
/

9、1、2、2 参数模式
形式参数具有三种模式，即in，out和in out。默认为in。
一般而言，函数不应该使用out模式，子程序可以使用out模式返回多个形式参数值

1）in模式
在in模式下，形式参数象常量，只读而不能被改变。所以当调用结束，实际参数不变。

set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE ModeTest
(
  p_Parameter    IN NUMBER) IS
  v_LocalVariable  NUMBER := 0;
BEGIN
  IF (p_Parameter IS NULL) THEN
    dbms_output.put_line('p_Parameter is NULL');
  ELSE
    dbms_output.put_line('p_Parameter = ' || p_Parameter);
  END IF;

v_LocalVariable := p_Parameter; -- Legal
--p_InParameter := 7; -- Illegal

END ModeTest;
/

set serveroutput on size 1000000;
declare
  num number;
BEGIN
  num:=1;
  ModeTest(num);
  dbms_output.put_line(num);
END;
/

2）out模式
在out模式下，实际参数传来的值被忽略，形式参数是个永远未被初始化的变量，具有空值，可读可写，调用结束后，形式参数的值被赋给实际参数。

set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE ModeTest
(
  p_Parameter    OUT NUMBER) IS
  v_LocalVariable  NUMBER := 0;
BEGIN
  IF (p_Parameter IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('p_Parameter is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('p_Parameter = ' || p_Parameter);
  END IF;

/* Assign 7 to p_Parameter. This is legal, since we are writing to an OUT parameter. */
p_Parameter := 7; -- Legal

  /* Assign p_Parameter to v_LocalVariable. In Oracle7 version
     7.3.4, and Oracle8 version 8.0.4 or higher (including 8i),
     this is legal.  Prior to 7.3.4, it is illegal to read from an
     OUT parameter. */
  v_LocalVariable := p_Parameter;  -- Possibly illegal
END ModeTest;
/

set serveroutput on size 1000000;
declare
  num number;
BEGIN
  num:=1;
  ModeTest(num);
  dbms_output.put_line(num);
END;
/

注意：如果子程序内部发生异常，即便是已经改变形式参数，形式参数的值也不会被赋给实际参数
set serveroutput on size 1000000;

/* Assign 7 to p_Parameter. This is legal, since we are writing to an OUT parameter. */
p_Parameter := 7; -- Legal

END ModeTest;
/

set serveroutput on size 1000000;
declare
  num number;
BEGIN
  num:=1;
  ModeTest(num);
EXCEPTION
  WHEN others THEN
    --DBMS_OUTPUT.PUT_LINE(SQLCODE());
    --DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200));
    dbms_output.put_line(num);
END;
/

另外，out模式下的实际参数不能是常量或者表达式，因为没法赋值，会导致错误

3）in out模式
是in模式和out模式的组合，实际参数的值可以传给形式参数，同时在调用结束后，形式参数的值也被赋给实际参数。

set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE ModeTest
(
  p_Parameter    IN OUT NUMBER) IS
  v_LocalVariable  NUMBER := 0;
BEGIN
  IF (p_Parameter IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('p_Parameter is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('p_Parameter = ' || p_Parameter);
  END IF;

/* Assign p_Parameter to v_LocalVariable. This is legal, since we are reading from an IN OUT parameter. */
v_LocalVariable := p_Parameter;

/* Assign 7 to p_Parameter. This is legal, since we are writing to an IN OUT parameter. */
p_Parameter := 7;

END ModeTest;
/

set serveroutput on size 1000000;
declare
  num number;
BEGIN
  num:=1;
  ModeTest(num);
  dbms_output.put_line(num);
END;
/

9、1、2、3 引用传递和数值传递
缺省情况下，in参数使用引用传递，而in out和out参数使用数值传递
如果显式使用引用传递方式来访问in out和out参数，可以使用NOCOPY编译器提示，此时将会导致形式参数的改变会影响实际参数，甚至在异常发生后也会产生形式参数的改变影响实际参数。
set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE ModeTest
(
  p_Parameter    OUT NOCOPY NUMBER) IS
  v_LocalVariable  NUMBER := 0;
BEGIN
  IF (p_Parameter IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('p_Parameter is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('p_Parameter = ' || p_Parameter);
  END IF;

p_Parameter := 7; -- Legal

v_LocalVariable := p_Parameter;
v_LocalVariable:=1/0;

END ModeTest;
/

输出为：
p_Parameter is NULL
7

注意：即便是使用了NOCOPY，并不代表原有的实际参数值会传递到形式参数中，而只能说明形式参数的改变会导致实际参数的改变。

使用NOCOPY方式参数传递速度更快，如：
set serveroutput on size 1000000;

CREATE OR REPLACE PACKAGE CopyFast AS
TYPE StudentArray IS TABLE OF stu%ROWTYPE;

  PROCEDURE PassStudents1(p_Parameter IN StudentArray);
  PROCEDURE PassStudents2(p_Parameter IN OUT StudentArray);
  PROCEDURE PassStudents3(p_Parameter IN OUT NOCOPY StudentArray);

PROCEDURE Go;
END CopyFast;
/

show errors

CREATE OR REPLACE PACKAGE BODY CopyFast AS
  PROCEDURE PassStudents1(p_Parameter IN StudentArray) IS
  BEGIN
    NULL;
  END PassStudents1;

  PROCEDURE PassStudents2(p_Parameter IN OUT StudentArray) IS
  BEGIN
    NULL;
  END PassStudents2;

  PROCEDURE PassStudents3(p_Parameter IN OUT NOCOPY StudentArray) IS
  BEGIN
    NULL;
  END PassStudents3;

  PROCEDURE Go IS
    v_StudentArray StudentArray := StudentArray(NULL);
    v_StudentRec stu%ROWTYPE;
    v_Time1 NUMBER;
    v_Time2 NUMBER;
    v_Time3 NUMBER;
    v_Time4 NUMBER;
  BEGIN
    -- Fill up the array with 50,001 copies of David Dinsmore's
    -- record.
    SELECT *
      INTO v_StudentArray(1)
      FROM stu
      WHERE sid = 1;
    v_StudentArray.EXTEND(1, 1);

    -- Call each version of PassStudents, and time them.
    -- DBMS_UTILITY.GET_TIME will return the current time, in
    -- hundredths of a second.
    v_Time1 := DBMS_UTILITY.GET_TIME;
    PassStudents1(v_StudentArray);
    v_Time2 := DBMS_UTILITY.GET_TIME;
    PassStudents2(v_StudentArray);
    v_Time3 := DBMS_UTILITY.GET_TIME;
    PassStudents3(v_StudentArray);
    v_Time4 := DBMS_UTILITY.GET_TIME;

    -- Output the results.
    DBMS_OUTPUT.PUT_LINE('Time to pass IN: ' || TO_CHAR((v_Time2 - v_Time1) / 100));
    DBMS_OUTPUT.PUT_LINE('Time to pass IN OUT: ' || TO_CHAR((v_Time3 -   v_Time2) / 100));
    DBMS_OUTPUT.PUT_LINE('Time to pass IN OUT NOCOPY: ' || TO_CHAR((v_Time4 - v_Time3) / 100));
  END Go;
END CopyFast;
/
show errors

BEGIN
CopyFast.Go;
END;
/

9、1、2、4 没有参数的子程序
如果子程序或者函数没有参数，则在声明和调用时不使用括号，如：
set serveroutput on size 1000000;

CREATE OR REPLACE PROCEDURE NoParamsP AS
BEGIN
DBMS_OUTPUT.PUT_LINE('No Paramaters!');
END NoParamsP;
/

CREATE OR REPLACE FUNCTION NoParamsF
RETURN DATE AS
BEGIN
RETURN SYSDATE;
END NoParamsF;
/

BEGIN
NoParamsP;
DBMS_OUTPUT.PUT_LINE('Calling NoParamsF on ' || TO_CHAR(NoParamsF, 'DD-MON-YYYY'));
END;
/

9、1、2、5 使用命名符
一般的子程序都使用定位符来传递实际参数，为了方便也可以使用命名符来赋值，如：
CREATE OR REPLACE PROCEDURE CallMe
(
  p_ParameterA VARCHAR2,
  p_ParameterB NUMBER,
  p_ParameterC BOOLEAN,
  p_ParameterD DATE
)
AS
BEGIN
  NULL;
END CallMe;
/

DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(7,6);
  v_Variable3 BOOLEAN;
  v_Variable4 DATE;
BEGIN
  CallMe( p_ParameterC => v_Variable3, p_ParameterD => v_Variable4,p_ParameterA => v_Variable1, p_ParameterB => v_Variable2);
END;
/

注意
1）可以重新调整参数位置
2）可以混合使用定位符和命名符，但是命名符只能在定位符后使用，如：
DECLARE
  v_Variable1 VARCHAR2(10);
  v_Variable2 NUMBER(7,6);
  v_Variable3 BOOLEAN;
  v_Variable4 DATE;
BEGIN
   CallMe(v_Variable1, v_Variable2,p_ParameterC => v_Variable3,p_ParameterD => v_Variable4);
END;
/

9、1、2、6 使用参数缺省值
CREATE OR REPLACE PROCEDURE AddNewStudent
(
  p_Sid  stu.sid%TYPE,
  p_Name stu.name%TYPE DEFAULT 'NONE',
  p_Sex  stu.sex%TYPE DEFAULT 'M'
)
AS
BEGIN
  INSERT INTO stu VALUES (p_Sid, p_Name, p_Sex);
END AddNewStudent;
/

BEGIN
AddNewStudent(200,'Simon','M');
AddNewStudent(300);
END;
/

select * from stu;
delete from stu where sid>100

注意：
1）缺省值一般只能从尾到头来定义，调用时的缺省值也只能是最后几个参数，如果跳跃定义，必须通过命名符来访问参数，如：
CREATE OR REPLACE PROCEDURE AddNewStudent
(
  p_Sid  stu.sid%TYPE,
  p_Name stu.name%TYPE DEFAULT 'NONE',
  p_Sex  stu.sex%TYPE
)
AS
BEGIN
  INSERT INTO stu VALUES (p_Sid, p_Name, p_Sex);
END AddNewStudent;
/

BEGIN
AddNewStudent(p_Sid=>400, p_Sex=>'F');
END;
/

2）default关键词可以使用“:=”代替

9、1、3 CALL语句
可以直接在SQL语句块中调用子程序，但是不能在PL/SQL语句块中调用子程序，如果需要在PL/SQL语句块中使用CALL，需要使用动态SQL语句，如：
set serveroutput on size 1000000;
CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('CallProc1 called with ' || p1);
END CallProc1;
/

CREATE OR REPLACE PROCEDURE CallProc2(p1 IN OUT VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('CallProc2 called with ' || p1);
p1 := p1 || ' returned!';
END CallProc2;
/

CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
  RETURN VARCHAR2 AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('CallFunc called with ' || p1);
  RETURN p1;
END CallFunc;
/

-- Some valid calls direct from SQL.
CALL CallProc1('Hello!');
CALL CallProc1();
VARIABLE v_Output VARCHAR2(50);
CALL CallFunc('Hello!') INTO :v_Output;
PRINT v_Output
CALL CallProc2(:v_Output);
PRINT v_Output

-- This is illegal
--BEGIN
-- CALL CallProc1();
--END;
--/

-- But these are legal
DECLARE
  v_Result VARCHAR2(50);
BEGIN
  EXECUTE IMMEDIATE 'CALL CallProc1(''Hello from PL/SQL'')';
  EXECUTE IMMEDIATE
    'CALL CallFunc(''Hello from PL/SQL'') INTO :v_Result' USING OUT v_Result;
END;
/

9、2 包
包是相关对象的共同存储体，包含两个部分，一是包头，即包的说明，另一个是包体，用于定义包中成员。这两个部分是分开定义和存储的。

相对于子程序而言，特点为：
1）封装相关对象
2）包只能存储于全局，不可能为局部
3）依赖性要小于子程序
4）性能较好

9、2、1 定义
9、2、1、1 包头
如：
CREATE OR REPLACE PACKAGE StuPackage AS
  PROCEDURE AddStudent(p_SID IN stu.sid%TYPE,p_Name IN stu.name%TYPE,p_sex IN stu.sex%TYPE);
  PROCEDURE RemoveStudent(p_SID IN stu.sid%TYPE);
  e_StudentNotRegistered EXCEPTION;
  TYPE t_StudentIDTable IS TABLE OF stu.sid%TYPE INDEX BY BINARY_INTEGER;
END StuPackage;
/

注意：代码显示红色是因为把RemoveStudent中的Rem当成注释

9、2、1、2 包体
如：
CREATE OR REPLACE PACKAGE BODY StuPackage AS
  PROCEDURE AddStudent(p_SID IN stu.sid%TYPE,p_Name IN stu.name%TYPE,p_Sex IN stu.sex%TYPE)
  is
  begin
      insert into stu values(p_SID,p_Name,p_Sex);
  end AddStudent;

  PROCEDURE RemoveStudent(p_SID IN stu.sid%TYPE)
  is
  begin
      delete from stu where sid=p_SID;
      IF SQL%NOTFOUND THEN
      RAISE e_StudentNotRegistered;
    END IF;
  end RemoveStudent;
END StuPackage;
/

说明：
1）包体独立定义和编译，当然要保证包头先行定义和编译
2）包体不是必须的，此时的包充当封装全局变量的作用
3）包体的定义要和包头的定义原型一致

9、2、2 使用包
9、2、2、1 一般使用
begin
StuPackage.AddStudent(900,'胡歌','M');
end;
/

select * from stu;

begin
StuPackage.RemoveStudent(900);
end;
/

select * from stu;

9、2、2、2 包内部成员的使用
上述包中的异常变量可以在包外部使用，如：
set serveroutput on size 1000000;

begin
  RAISE StuPackage.e_StudentNotRegistered;
EXCEPTION
  WHEN others THEN
  DBMS_OUTPUT.PUT_LINE('Error');
end;
/

但是下面包中定义的成员不能在包外部使用，如：
CREATE OR REPLACE PACKAGE StuPackage AS
  PROCEDURE AddStudent(p_SID IN stu.sid%TYPE,p_Name IN stu.name%TYPE,p_sex IN stu.sex%TYPE);
  PROCEDURE RemoveStudent(p_SID IN stu.sid%TYPE);
  TYPE t_StudentIDTable IS TABLE OF stu.sid%TYPE INDEX BY BINARY_INTEGER;
END StuPackage;
/

CREATE OR REPLACE PACKAGE BODY StuPackage
as
e_StudentNotRegistered EXCEPTION;

  PROCEDURE AddStudent(p_SID IN stu.sid%TYPE,p_Name IN stu.name%TYPE,p_Sex IN stu.sex%TYPE)
  is
  begin
      insert into stu values(p_SID,p_Name,p_Sex);
  end AddStudent;

  PROCEDURE RemoveStudent(p_SID IN stu.sid%TYPE)
  is
  begin
      delete from stu where sid=p_SID;
      IF SQL%NOTFOUND THEN
      RAISE e_StudentNotRegistered;
    END IF;
  end RemoveStudent;
END StuPackage;
/

此时会出错，如：
set serveroutput on size 1000000;

begin
  RAISE StuPackage.e_StudentNotRegistered;
EXCEPTION
  WHEN others THEN
  DBMS_OUTPUT.PUT_LINE('Error');
end;
/

9、2、2、3 包成员的重载
set serveroutput on size 1000000;

CREATE OR REPLACE TYPE t1 AS OBJECT
(
f NUMBER
);
/
show errors

CREATE OR REPLACE TYPE t2 AS OBJECT
(
f NUMBER
);
/
show errors

CREATE OR REPLACE PACKAGE Overload AS
PROCEDURE Proc(p_Parameter1 IN t1);
PROCEDURE Proc(p_Parameter1 IN t2);
END Overload;
/
show errors

CREATE OR REPLACE PACKAGE BODY Overload AS
  PROCEDURE Proc(p_Parameter1 IN t1) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Proc(t1): ' || p_Parameter1.f);
  END Proc;

  PROCEDURE Proc(p_Parameter1 IN t2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Proc(t2): ' || p_Parameter1.f);
  END Proc;
END Overload;
/
show errors

set serveroutput on

DECLARE
  v_Obj1 t1 := t1(1);
  v_Obj2 t2 := t2(2);
BEGIN
  Overload.Proc(v_Obj1);
  Overload.Proc(v_Obj2);
END;
/

9、2、2、4 包的初始化
直接在包体中使用begin和end来封装初始化代码，如：
CREATE OR REPLACE PACKAGE Random AS
-- Random number generator. Uses the same algorithm as the
-- rand() function in C.

  -- Used to change the seed.  From a given seed, the same
  -- sequence of random numbers will be generated.
  PROCEDURE ChangeSeed(p_NewSeed IN NUMBER);

-- Returns a random integer between 1 and 32767.
FUNCTION Rand RETURN NUMBER;

-- Same as Rand, but with a procedural interface.
PROCEDURE GetRand(p_RandomNumber OUT NUMBER);

-- Returns a random integer between 1 and p_MaxVal.
FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER;

  -- Same as RandMax, but with a procedural interface.
  PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
                       p_MaxVal IN NUMBER);
END Random;
/

CREATE OR REPLACE PACKAGE BODY Random AS

  /* Used for calculating the next number. */
  v_Multiplier  CONSTANT NUMBER := 22695477;
  v_Increment   CONSTANT NUMBER := 1;

/* Seed used to generate random sequence. */
v_Seed number := 1;

  PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS
  BEGIN
    v_Seed := p_NewSeed;
  END ChangeSeed;

  FUNCTION Rand RETURN NUMBER IS
  BEGIN
    v_Seed := MOD(v_Multiplier * v_Seed + v_Increment,
                  (2 ** 32));
    RETURN BITAND(v_Seed/(2 ** 16), 32767);
  END Rand;

  PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS
  BEGIN
    -- Simply call Rand and return the value.
    p_RandomNumber := Rand;
  END GetRand;

  FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN MOD(Rand, p_MaxVal) + 1;
  END RandMax;

  PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
                       p_MaxVal IN NUMBER) IS
  BEGIN
    -- Simply call RandMax and return the value.
    p_RandomNumber := RandMax(p_MaxVal);
  END GetRandMax;

BEGIN
  /* Package initialization.  Initialize the seed to the current
     time in seconds. */
  ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS')));
END Random;
/

说明：SSSSS意思为“Seconds past midnight”

使用为：
set serveroutput on size 1000000;
declare
  p_rand number;
begin
  Random.GetRand(p_rand);
  DBMS_OUTPUT.PUT_LINE(p_rand);
  Random.GetRandMax(p_rand,6);
  DBMS_OUTPUT.PUT_LINE(p_rand);

  DBMS_OUTPUT.PUT_LINE(Random.rand);
  DBMS_OUTPUT.PUT_LINE(Random.randmax(100));

end;
/

9、3 触发器
触发器和子程序相似，区别在于
1）不能定义为局部，子程序可以，方法为：
set serveroutput on size 1000000;
declare
  p_rand number;

  PROCEDURE NoParamsP as
  BEGIN
    DBMS_OUTPUT.PUT_LINE('No Paramaters!');
  END NoParamsP;

begin
NoParamsP;
end;
/

2）主要通过事件来隐式触发，无需显示调用
3）不接受参数
4）不能对SYS对象做触发器，所以新建新的其他用户表，如：
create table Scott.stu
(
    sid integer primary key,
    name varchar(8) not null,
    sex char(1) check(sex in('M','F'))
);

insert into scott.stu values(1,'Tom','M');
insert into scott.stu values(2,'Alice','F');
insert into scott.stu values(3,'eric','M');

5）触发器不能超过32K，如果更大，需要将代码封装进包或者存储过程，在触发器中调用它们

9、3、1 触发器类型
Oracle的触发器按照触发方式可以分为三种，分别为DML触发器、instead-of触发器和系统触发器。

9、3、1、1 DML触发器
9、3、1、1、1 简单使用
CREATE OR REPLACE TRIGGER scott.OperatingStu
AFTER INSERT OR DELETE OR UPDATE ON scott.stu
BEGIN
DBMS_OUTPUT.PUT_LINE('OK!');
END OperatingStu;
/

观察为：
insert into scott.stu values(100,'Jim','M');

9、3、1、1、2 详细定义
DML触发器的定义有三个要点
1）可以对三种DML操作进行定义，如insert、update和delete
2）可以指定两个时机，如after和before
3）可以指定行级或者语句级，默认为语句级，如为行级，则每条操作记录都会触发一次触发器，如：
CREATE OR REPLACE TRIGGER scott.OperatingStu
  AFTER INSERT OR DELETE OR UPDATE ON scott.stu
  for each row
BEGIN
  DBMS_OUTPUT.PUT_LINE('OK!');
END OperatingStu;
/

此时更新多条记录会导致多次触发，如：
update scott.stu set name=upper(name);

9、3、1、1、3 触发顺序
1）如果同一个表有多个相同触发器，触发的顺序应该是先触发新的触发器，后激活旧的触发器，如：
set serveroutput on size 1000000;

CREATE OR REPLACE TRIGGER scott.OperatingStuAlso
  AFTER INSERT OR DELETE OR UPDATE ON scott.stu
  for each row
BEGIN
  DBMS_OUTPUT.PUT_LINE('OK!!');
END OperatingStuAlso;
/

可以观察为：
update scott.stu set name=upper(name);

2）如果同一个表存在行级和语句级的触发器，触发顺序为（不考虑不存在的情况）：
语句级别之前的触发器
每一行之前的触发器
执行语句操作行
每一行之后的触发器
语句级别之后的触发器

9、3、1、1、4 相关标识符
触发器中可以使用:old和:new两个相关标识符，它们是系统提供的PL/SQL绑定变量，分别保存更新前的表记录信息和更新后的表记录信息

注意：
1）可以更新:new，将会改写更新信息，如：
CREATE OR REPLACE TRIGGER GenerateStudentID
  BEFORE INSERT OR UPDATE ON scott.stu
  FOR EACH ROW
BEGIN
  SELECT max(sid)+1 INTO :new.sid FROM scott.stu;
END GenerateStudentID;
/

insert into scott.stu(name,sex) values('Lee','M');
select * from scott.stu;

此时即便是指定sid也会被改写，如：
insert into scott.stu values(1000,'Lee','M');
select * from scott.stu;

2）只能在行级别触发器才能使用:old和:new两个相关标识符，因为语句触发器会影响多行
3）:old不能修改，只能够读取，:new只能在行级别之前触发器中被修改，不能在行级别之后触发器中被修改
4）:old和:new两个相关标识符都是记录类型，可以理解为triggering_table%rowtype类型，但是实际上不是记录，所以不能作为一般记录来处理，如不能整个记录来进行赋值，不能当作参数赋予子程序
5）对于insert操作的:old和delete操作的:new，相关信息都为null
6）可以使用REFERENCING替换默认的相关标识符名称（注意位置），如：
CREATE OR REPLACE TRIGGER GenerateStudentID
  BEFORE INSERT OR UPDATE ON scott.stu
  REFERENCING new as new_table
  FOR EACH ROW
BEGIN
  SELECT max(sid)+1 INTO :new_table.sid FROM scott.stu;
END GenerateStudentID;
/
7）还有:parent相关标识符标识嵌套表所对应的父表当前行

9、3、1、1、5 指定触发的条件（WHEN）
set serveroutput on size 1000000;
CREATE OR REPLACE TRIGGER scott.OperatingStu
  AFTER INSERT OR DELETE OR UPDATE ON scott.stu
  for each row
  when (new.sex='M')
BEGIN
  DBMS_OUTPUT.PUT_LINE('OK!');
END OperatingStu;
/

注意：在when语句中的相关标识符不用加冒号

上述触发器只会在操作男生记录时触发，如：
update scott.stu set name=upper(name);

当然，也可以写为：
set serveroutput on size 1000000;
CREATE OR REPLACE TRIGGER scott.OperatingStu
  AFTER INSERT OR DELETE OR UPDATE ON scott.stu
  for each row
BEGIN
  if :new.sex='M' then
    DBMS_OUTPUT.PUT_LINE('OK!');
  end if;
END OperatingStu;
/

9、3、1、1、6 触发器谓词
通过INSERTING、UPDATING和DELETING函数来判断触发类型，如：
set serveroutput on size 1000000;

CREATE OR REPLACE TRIGGER scott.OperatingStu
  AFTER INSERT OR DELETE OR UPDATE ON scott.stu
  for each row
BEGIN
  if INSERTING then
    DBMS_OUTPUT.PUT_LINE('INSERTING');
  elsif UPDATING then
    DBMS_OUTPUT.PUT_LINE('UPDATING');
  elsif DELETING then
    DBMS_OUTPUT.PUT_LINE('DELETING');
  end if;
END OperatingStu;
/

update scott.stu set name=upper(name);

9、3、1、2 instead-of触发器
用于给视图创建触发器，对于不能直接更新的试图通过此触发器来使之能够更新，如创建新的视图：
create or replace view scott.new_stu
as select upper(name) upper from scott.stu;

select * from scott.new_stu;

此时不能插入更新，如：
insert into scott.new_stu values('Jim');

建立instead-of触发器，如：
create or replace trigger scott.insertView
instead of insert on scott.new_stu
declare
  p_num integer;
begin
  select max(sid) into p_num from scott.stu;
  insert into scott.stu values(p_num+1,:new.upper,'M');
end;
/

注意：所有的instead-of触发器都是行级的，for each row可写可不写

此时可以更新，如：
insert into scott.new_stu values('Jim');

select * from scott.new_stu;
select * from scott.stu;

说明：
视图在没有下面几项内容的时候，通常可以更新：
1）集合操作符（union等）
2）聚合运算函数（sum等）
3）group by，connect by和start with
4）distinct操作符
5）连接

9、3、1、3 系统触发器
9、3、1、3、1 一般使用
当发生诸如数据库关闭或者启动之类的系统事件和执行DDL语句时，激发系统触发器

下面的系统触发器可以记录任何创建的数据字典对象信息，如：
建立信息储存表，如：
CREATE TABLE scott.ddl_creations
(
  user_id       VARCHAR2(30),
  object_type   VARCHAR2(20),
  object_name   VARCHAR2(30),
  object_owner  VARCHAR2(30),
  creation_date DATE
);

创建系统触发器，如：
CREATE OR REPLACE TRIGGER scott.LogCreations
AFTER CREATE ON DATABASE
BEGIN
INSERT INTO ddl_creations (user_id, object_type, object_name,object_owner, creation_date)VALUES (USER, SYS.DICTIONARY_OBJ_TYPE, SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYSDATE);
END LogCreations;
/

创建新表，如：
create table stubak
(
    sid integer primary key,
    name varchar(8) not null,
    sex char(1) check(sex in('M','F'))
);

查询：
select * from scott.ddl_creations;
drop table stubak;

9、3、1、3、2 数据库级别触发器和模式级别触发器
系统触发器可以在数据库级别和模式级别进行定义，其中只要有事件发生就会触发数据库级别触发器，而只有以指定模式发生事件时才会触发模式级别触发器。默认为该用户模式对应的模式触发器。
注意：DML和instead_of触发器没有模式之分

set serveroutput on size 1000000;

建立UserA和UserB帐户
Connect system/system@school AS SYSDBA

CREATE USER UserA IDENTIFIED BY UserA;
GRANT connect, resource, ADMINISTER DATABASE TRIGGER TO UserA;

CREATE USER UserB IDENTIFIED BY UserB;
GRANT connect, resource, ADMINISTER DATABASE TRIGGER TO UserB;

create table temp_table
(
info varchar2(100)
);

grant all on temp_table to UserA;
grant all on temp_table to UserB;

以UserA帐户登录创建模式触发器
connect UserA/UserA
CREATE OR REPLACE TRIGGER LogUserAConnects AFTER LOGON ON SCHEMA
BEGIN
insert into sys.temp_table values('LogUserAConnects fired!');
END LogUserAConnects;
/

以UserB帐户登录创建模式触发器
connect UserB/UserB
CREATE OR REPLACE TRIGGER LogUserBConnects AFTER LOGON ON SCHEMA
BEGIN
insert into sys.temp_table values('LogUserBConnects fired!');
END LogUserBConnects;
/

以system帐户登录创建数据库触发器
Connect system/system@school AS SYSDBA

CREATE OR REPLACE TRIGGER LogAllConnects AFTER LOGON ON DATABASE
BEGIN
insert into sys.temp_table values('LogAllConnects fired!');
END LogAllConnects;
/

以三个帐户分别登录三次
connect UserA/UserA;
connect UserB/UserB;
Connect system/system@school AS SYSDBA;

查看temp_table表信息
select * from temp_table;

输出为：
INFO
-----------------------
LogUserAConnects fired!
LogAllConnects fired!
LogUserBConnects fired!
LogAllConnects fired!
LogAllConnects fired!

9、3、1、3、3 事件属性函数
利用事件属性函数可以得到更多的事件信息，如：
Connect system/system@school AS SYSDBA

CREATE OR REPLACE TRIGGER InfoTrigger AFTER CREATE ON DATABASE
BEGIN
  insert into temp_table values(sysevent());
  insert into temp_table values(database_name());
  insert into temp_table values(login_user());
  insert into temp_table values(dictionary_obj_type());
  insert into temp_table values(dictionary_obj_name());
  insert into temp_table values(dictionary_obj_owner());
END InfoTrigger;
/

delete from temp_table;

create table stutemp
(
    sid integer primary key,
    name varchar(8) not null,
    sex char(1) check(sex in('M','F'))
);

select * from temp_table;

9、3、1、3、4 使用when
CREATE OR REPLACE TRIGGER InfoTrigger AFTER CREATE ON DATABASE
when (dictionary_obj_type()='TABLE')
BEGIN
insert into temp_table values(dictionary_obj_name());
insert into temp_table values(dictionary_obj_owner());
END InfoTrigger;
/

create table stutempalso
(
    sid integer primary key,
    name varchar(8) not null,
    sex char(1) check(sex in('M','F'))
);

select * from temp_table;

9、3、1、3、5 补充
1）触发器可以与子程序和包、表等同名（但是不建议）
2）触发器代码中不能使用任何事务控制语句
3）可以查看数据字典了解触发器的情况，如查看用户和DBA的触发器情况：
select trigger_name,trigger_type,triggering_event from user_triggers;
select trigger_name,trigger_type,triggering_event from dba_triggers;

[此贴子已经被作者于2010-12-11 20:03:49编辑过]

主题：[推荐]Oracle数据库PL-SQL学习资料之七——子程序、包和触发器