ORACLE PL/SQL编程总结（二）

----------异常错误处理---------

即使是写得最好的PL/SQL程序也会遇到错误或未预料到的事件。一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。任何ORACLE错误（报告为ORA-xxxxx形式的Oracle错误号）、PL/SQL运行错误或用户定义条件（不一写是错误），都可以。当然了，PL/SQL编译错误不能通过PL/SQL异常处理来处理，因为这些错误发生在PL/SQL程序执行之前。

ORACLE 提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。

5.1异常处理概念

异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.

有三种类型的异常错误：

1．预定义 ( Predefined )错误

ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。

2．非预定义 ( Predefined )错误

即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。

3．用户定义(User_define) 错误

程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END;

异常处理可以按任意次序排列，但 OTHERS 必须放在最后。

5.1.1 预定义的异常处理

预定义说明的部分 ORACLE 异常错误

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反了唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECT INTO没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已处于打开状态的游标
ORA-6530	Access-INTO-null	试图为null 对象的属性赋值
ORA-6531	Collection-is-null	试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
ORA-6532	Subscript-outside-limit	对嵌套或varray索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或varray 索引得引用大于集合中元素的个数.

对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。

例1：更新指定员工工资，如工资小于1500，则加100；

DECLARE
   v_empno employees.employee_id%TYPE := &empno;
   v_sal   employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;
   IF v_sal<=1500 THEN
        UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno;
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');
   ELSE
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

5.1.2 非预定义的异常处理

对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：

1. 在PL/SQL 块的定义部分定义异常情况：

<异常情况> EXCEPTION;

2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：

PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);

3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

例2：删除指定部门的记录信息，以确保该部门没有员工。

INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO');

DECLARE
   v_deptno departments.department_id%TYPE := &deptno;
   deptno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
   /* -2292 是违反一致性约束的错误代码 */
BEGIN
   DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

5.1.3 用户自定义的异常处理

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。

对于这类异常情况的处理，步骤如下：

1．在PL/SQL 块的定义部分定义异常情况：

<异常情况> EXCEPTION;

2． RAISE <异常情况>；

3．在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

例3：更新指定员工工资，增加100；

DECLARE
   v_empno employees.employee_id%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

5.1.4 用户定义的异常处理

调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。

RAISE_APPLICATION_ERROR 的语法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

这里的error_number 是从 –20,000 到 –20,999 之间的参数，

error_message 是相应的提示信息(< 2048 字节)，

keep_errors 为可选，如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。

例4：定义触发器，使用RAISE_APPLICATION_ERROR阻止没有员工姓名的新员式记录插入：

CREATE OR REPLACE TRIGGER tr_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :new.first_name IS NULL OR :new.last_name is null THEN
    RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.');
  END IF;
END;

5.2 异常错误传播

由于异常错误可以在声明部分和执行部分以及异常错误部分出现，因而在不同部分引发的异常错误也不一样。

5.2.1 在执行部分引发异常错误

当一个异常错误在执行部分引发时，有下列情况：

l 如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。

l 如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。

5.2.2 在声明部分引发异常错误

如果在声明部分引起异常情况，即在声明部分出现错误，那么该错误就能影响到其它的块。比如在有如下的PL/SQL程序：

DECLARE
    name varchar2(12):='EricHu';
    其它语句
BEGIN
    其它语句
EXCEPTION
    WHEN OTHERS THEN
    其它语句
END;

例子中，由于Abc number(3)=’abc’; 出错，尽管在EXCEPTION中说明了WHEN OTHERS THEN语句，但WHEN OTHERS THEN也不会被执行。但是如果在该错误语句块的外部有一个异常错误，则该错误能被抓住，如：

BEGIN
    DECLARE
    name varchar2(12):='EricHu';
    其它语句
   BEGIN
    其它语句
   EXCEPTION
    WHEN OTHERS THEN
    其它语句
    END;
EXCEPTION
WHEN OTHERS THEN
    其它语句
END;

5.3 异常错误处理编程

在一般的应用处理中，建议程序人员要用异常处理，因为如果程序中不声明任何异常处理，则在程序运行出错时，程序就被终止，并且也不提示任何信息。下面是使用系统提供的异常来编程的例子。

5.4 在 PL/SQL 中使用 SQLCODE，SQLERRM异常处理函数

由于ORACLE 的错信息最大长度是512字节，为了得到完整的错误提示信息，我们可用 SQLERRM和 SUBSTR 函数一起得到错误提示信息，方便进行错误，特别是如果WHEN OTHERS异常处理器时更为方便。

SQLCODE 返回遇到的Oracle错误号,

SQLERRM 返回遇到的Oracle错误信息.

如: SQLCODE=-100 è SQLERRM=’no_data_found ‘

SQLCODE=0 è SQLERRM=’normal, successfual completion’

例1：将ORACLE错误代码及其信息存入错误代码表

CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 错误信息  */
   FOR err_num IN -100 .. 0 LOOP
      err_msg := SQLERRM(err_num);
      INSERT INTO errors VALUES(err_num, err_msg);
   END LOOP;
END;
DROP TABLE errors;

例2：查询ORACLE错误代码；

BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, 'Eric','Hu', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, '胡','勇', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

例3：利用ORACLE错误代码，编写异常错误处理代码；

DECLARE
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是违反唯一约束条件的错误代码 */
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, 'Eric','Hu', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '胡','勇',SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

***********************************************

-----------把过程与函数说透-----------

6.1 引言

过程与函数（另外还有包与触发器）是命名的PL/SQL块（也是用户的方案对象），被编译后存储在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。所以，可以将商业逻辑、企业规则写成函数或过程保存到数据库中，以便共享。

过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：

1．创建存储过程和函数。

2．正确使用系统级的异常处理和用户定义的异常处理。

3．建立和管理存储过程和函数。

6.2 创建函数

1. 创建函数（语法如下：）

CREATE [OR REPLACE] FUNCTION function_name

(arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1],

[arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],

......

[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])

[ AUTHID DEFINER | CURRENT_USER ]

RETURN return_type

IS | AS

<类型.变量的声明部分>

BEGIN

执行部分

RETURN expression

EXCEPTION

异常处理部分

END function_name;

l IN,OUT,IN OUT是形参的模式。若省略，则为IN模式。IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。

　　l 一般，只有在确认function_name函数是新函数或是要更新的函数时，才使用OR REPALCE关键字，否则容易删除有用的函数。

例子：获取某部门的工资总和：

--获取某部门的工资总和

CREATE OR REPLACE

FUNCTION get_salary(

Dept_no NUMBER,

Emp_count OUT NUMBER)

RETURN NUMBER

IS

V_sum NUMBER;

BEGIN

SELECT SUM(SALARY), count(*) INTO V_sum, emp_count

FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no;

RETURN v_sum;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END get_salary;

2. 函数的调用

　　函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：

　　第一种参数传递格式：位置表示法。

　　即在调用时按形参的排列顺序，依次写出实参的名称，而将形参与实参关联起来进行传递。用这种方法进行调用，形参与实参的名称是相互独立，没有关系，强调次序才是重要的。

　　格式为：

1	`argument_value1[,argument_value2 …]`

例子：计算某部门的工资总和：

DECLARE

V_num NUMBER;

V_sum NUMBER;

BEGIN

V_sum :=get_salary(10, v_num);

DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);

END;

第二种参数传递格式：名称表示法。

　　即在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来进行传递。这种方法，形参与实参的名称是相互独立的，没有关系，名称的对应关系才是最重要的，次序并不重要。

　　格式为：

1	`argument => parameter [,…]`

　　其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同parameter 为实际参数。

　在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。

例子：计算某部门的工资总和：

DECLARE

V_num NUMBER;

V_sum NUMBER;

BEGIN

V_sum :=get_salary(emp_count => v_num, dept_no => 10);

DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);

END;

第三种参数传递格式：组合传递。

即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。

CREATE OR REPLACE FUNCTION demo_fun(

Name VARCHAR2,--注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似

Age INTEGER,

Sex VARCHAR2)

RETURN VARCHAR2

AS

V_var VARCHAR2(32);

BEGIN

V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;

RETURN v_var;

END;

DECLARE

Var VARCHAR(32);

BEGIN

Var := demo_fun('user1', 30, sex => '男');

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40, sex => '男');

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var);

END;

无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。

传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

　　3. 参数默认值

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

CREATE OR REPLACE FUNCTION demo_fun(

Name VARCHAR2,

Age INTEGER,

Sex VARCHAR2 DEFAULT '男')

RETURN VARCHAR2

AS

V_var VARCHAR2(32);

BEGIN

V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;

RETURN v_var;

END;

具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

DECLARE

varVARCHAR(32);

BEGIN

Var := demo_fun('user1', 30);

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40);

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var);

END;

6.3 存储过程

6.3.1 创建过程

建立存储过程

　　在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.

　　创建过程语法:

CREATE [OR REPLACE] PROCEDURE procedure_name

([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],

[arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],

......

[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])

[ AUTHID DEFINER | CURRENT_USER ]

{ IS | AS }

<声明部分>

BEGIN

<执行部分>

EXCEPTION

<可选的异常错误处理程序>

END procedure_name;

说明：相关参数说明参见函数的语法说明。

例子：使用存储过程向departments表中插入数据。

CREATE OR REPLACE

PROCEDURE insert_dept

(v_dept_id IN departments.department_id%TYPE,

v_dept_name IN departments.department_name%TYPE,

v_mgr_id IN departments.manager_id%TYPE,

v_loc_id IN departments.location_id%TYPE)

IS

ept_null_error EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_null_error, -1400);

ept_no_loc_id EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291);

BEGIN

INSERT INTO departments

(department_id, department_name, manager_id, location_id)

VALUES

(v_dept_id, v_dept_name, v_mgr_id, v_loc_id);

DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功');

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复');

WHEN ept_null_error THEN

RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空');

WHEN ept_no_loc_id THEN

RAISE_APPLICATION_ERROR(-20002, '没有该地点');

END insert_dept;

/*调用实例一:

DECLARE

ept_20000 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20000, -20000);

ept_20001 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20001, -20001);

ept_20002 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20002, -20002);

BEGIN

insert_dept(300, '部门300', 100, 2400);

insert_dept(310, NULL, 100, 2400);

insert_dept(310, '部门310', 100, 900);

EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');

WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');

WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');

END;

调用实例二：

DECLARE

ept_20000 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20000, -20000);

ept_20001 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20001, -20001);

ept_20002 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20002, -20002);

BEGIN

insert_dept(v_dept_name => '部门310', v_dept_id => 310,

v_mgr_id => 100, v_loc_id => 2400);

insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900);

EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');

WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');

WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');

END;

*/

6.3.2 调用存储过程

存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：

EXEC[UTE] procedure_name( parameter1, parameter2…);

例：EXECUTE logexecution;

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

例子：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；

DECLARE

V_num NUMBER;

V_sum NUMBER(8, 2);

PROCEDURE proc_demo

(

Dept_no NUMBER DEFAULT 10,

Sal_sum OUT NUMBER,

Emp_count OUT NUMBER

)

IS

BEGIN

SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count

FROM employees WHERE department_id=dept_no;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END proc_demo;

--调用方法：

BEGIN

Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);

Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num);

END;

6.3.3 AUTHID

过程中的AUTHID 指令可以告诉ORACLE ，这个过程使用谁的权限运行．默任情况下，存储过程会作为调用者的过程运行，但是具有设计者的特权．这称为设计者权利运行。

例子：建立过程，使用AUTOID CURRENT_USER；

CONNECT HR/qaz

CREATE OR REPLACE PROCEDURE logexecution

AUTHID CURRENT_USER

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT testuser1/userpwd1

INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE);

EXECUTE HR.logexecution

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

ORACLE8i 可以支持事务处理中的事务处理的概念．这种子事务处理可以完成它自己的工作，独立于父事务处理进行提交或者回滚．通过使用这种方法，开发者就能够这样的过程，无论父事务处理是提交还是回滚，它都可以成功执行。

例1：建立过程，使用自动事务处理进行日志记录；

DROP TABLE logtable;

CREATE TABLE logtable(

Username varchar2(20),

Dassate_time date,

Mege varchar2(60)

);

CREATE TABLE temp_table( N number );

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)

AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO logtable VALUES ( user, sysdate, p_message );

COMMIT;

END log_message;

BEGIN

Log_message (‘About to insert into temp_table‘);

INSERT INTO temp_table VALUES (1);

Log_message (‘Rollback to insert into temp_table‘);

ROLLBACK;

END;

SELECT * FROM logtable;

SELECT * FROM temp_table;

例2：建立过程，没有使用自动事务处理进行日志记录；

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)

AS

BEGIN

INSERT INTO logtable VALUES ( user, sysdate, p_message );

COMMIT;

END log_message;

BEGIN

Log_message ('About to insert into temp_table');

INSERT INTO temp_table VALUES (1);

Log_message ('Rollback to insert into temp_table');

ROLLBACK;

END;

SELECT * FROM logtable;

SELECT * FROM temp_table;

6.3.5 开发存储过程步骤

开发存储过程、函数、包及触发器的步骤如下：

6.3.5.1 使用文字编辑处理软件编辑存储过程源码

使用文字编辑处理软件编辑存储过程源码，要用类似WORD 文字处理软件进行编辑时，要将源码存为文本格式。

6.3.5.2 在SQLPLUS或用调试工具将存储过程程序进行解释

在SQLPLUS或用调试工具将存储过程程序进行解释；

在SQL>下调试，可用START 或GET 等ORACLE命令来启动解释。如：

SQL>START c:\stat1.sql

如果使用调式工具，可直接编辑和点击相应的按钮即可生成存储过程。

6.3.5.3 调试源码直到正确

我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是：

l 使用 SHOW ERROR命令来提示源码的错误位置；

l 使用 user_errors 数据字典来查看各存储过程的错误位置。

6.3.5.4 授权执行权给相关的用户或角色

如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。

　　GRANT语法：

GRANT system_privilege | role

TO user | role | PUBLIC [WITH ADMIN OPTION]

GRANT object_privilege | ALL ON schema.object

TO user | role | PUBLIC [WITH GRANT OPTION]

--例子：

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

6.3.6 与过程相关数据字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

7.1 程序包简介

程序包(PACKAGE，简称包)是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来标识包。它具有面向对象程序设计语言的特点，是对这些PL/SQL 程序设计元素的封装。包类似于c#和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。

与高级语言中的类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同，即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。

当然，对于不包含在程序包中的过程、函数是独立存在的。一般是先编写独立的过程与函数，待其较为完善或经过充分验证无误后，再按逻辑相关性组织为程序包

程序包的优点

u 简化应用程序设计：程序包的说明部分和包体部分可以分别创建各编译。主要体现在以下三个方面：

1) 可以在设计一个应用程序时，只创建各编译程序包的说明部分，然后再编写引用该程序包的PL/SQL块。

2) 当完成整个应用程序的整体框架后，再回头来定义包体部分。只要不改变包的说明部分，就可以单独调试、增加或替换包体的内容，这不会影响其他的应用程序。

3) 更新包的说明后必须重新编译引用包的应用程序，但更新包体，则不需重新编译引用包的应用程序，以快速进行进行应用程序的原形开发。

u 模块化：可将逻辑相关的PL/SQL块或元素等组织在一起，用名称来唯一标识程序包。把一个大的功能模块划分人适当个数小的功能模块，分别完成各自的功能。这样组织的程序包都易于编写，易于理解更易于管理。

u 信息隐藏：因为包中的元素可以分为公有元素和私有元素。公有元素可被程序包内的过程、函数等的访问，还可以被包外的PL/SQL访问。但对于私有元素只能被包内的过程、函数等访问。对于用户，只需知道包的说明，不用了解包休的具体细节。

u 效率高：程序包在应用程序第一次调用程序包中的某个元素时，ORACLE将把整个程序包加载到内存中，当第二次访问程序包中的元素时，ORACLE将直接从内在中读取，而不需要进行磁盘I/O操作而影响速度，同时位于内在中的程序包可被同一会话期间的其它应用程序共享。因此，程序包增加了重用性并改善了多用户、多应用程序环境的效率。

对程序包的优点可总结如下：在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私用变量），而写可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE将整个包调入内存，当再次访问包内元素时，ORACLE直接从内存中读取，而不需要进行磁盘I/O操作，从而使程序执行效率得到提高。

一个包由两个分开的部分组成：

包说明（PACKAGE）：包说明部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。

包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。

包说明和包主体分开编译，并作为两部分分开的对象存放在数据库字典中，可查看数据字典user_source, all_source, dba_source，分别了解包说明与包主体的详细信息。

7.2 程序包的定义

程序包的定义分为程序包说明定义和程序包主体定义两部分组成。

程序包说明用于声明包的公用组件，如变量、常量、自定义数据类型、异常、过程、函数、游标等。包说明中定义的公有组件不仅可以在包内使用，还可以由包外其他过程、函数。但需要说明与注意的是，我们为了实现信息的隐藏，建议不要将所有组件都放在包说明处声明，只应把公共组件放在包声明部分。包的名称是唯一的，但对于两个包中的公有组件的名称可以相同，这种用“包名.公有组件名“加以区分。

包体是包的具体实现细节，其实现在包说明中声明的所有公有过程、函数、游标等。当然也可以在包体中声明仅属于自己的私有过程、函数、游标等。创建包体时，有以下几点需要注意：

u 包体只能在包说明被创建或编译后才能进行创建或编译。

u 在包体中实现的过程、函数、游标的名称必须与包说明中的过程、函数、游标一致，包括名称、参数的名称以及参数的模式（IN、OUT、IN OUT）。并建设按包说明中的次序定义包体中具体的实现。

u 在包体中声明的数据类型、变量、常量都是私有的，只能在包体中使用而不能被印刷体外的应用程序访问与使用。

u 在包体执行部分，可对包说明，包体中声明的公有或私有变量进行初始化或其它设置。

创建程序包说明语法格式:

CREATE [OR REPLACE] PACKAGE package_name
  [AUTHID {CURRENT_USER | DEFINER}]
  {IS | AS}
  [公有数据类型定义[公有数据类型定义]…]
  [公有游标声明[公有游标声明]…]
  [公有变量、常量声明[公有变量、常量声明]…]
  [公有函数声明[公有函数声明]…]
  [公有过程声明[公有过程声明]…]
END [package_name];

其中：AUTHIDCURRENT_USER和AUTHIDDEFINER选项说明应用程序在调用函数时所使用的权限模式，它们与CREATEFUNCTION语句中invoker_right_clause子句的作用相同。

创建程序包主体语法格式:

CREATE [OR REPLACE] PACKAGE BODY package_name
  {IS | AS}
  [私有数据类型定义[私有数据类型定义]…]
  [私有变量、常量声明[私有变量、常量声明]…]
  [私有异常错误声明[私有异常错误声明]…]
  [私有函数声明和定义[私有函数声明和定义]…]
  [私有函过程声明和定义[私有函过程声明和定义]…]
  [公有游标定义[公有游标定义]…]
  [公有函数定义[公有函数定义]…]
  [公有过程定义[公有过程定义]…]
BEGIN
  执行部分(初始化部分)
END package_name;

其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致。

7.3 包的开发步骤

与开发存储过程类似，包的开发需要几个步骤：

1．将每个存储过程调式正确；

2．用文本编辑软件将各个存储过程和函数集成在一起；

3．按照包的定义要求将集成的文本的前面加上包定义；

4．按照包的定义要求将集成的文本的前面加上包主体；

5．使用SQLPLUS或开发工具进行调式。

7.4 包定义的说明

对包内共有元素的调用格式为：包名.元素名称

7.5 子程序重载

PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型。

7.6 加密实用程序

ORACLE 提供了一个实用工具来加密或者包装用户的PL/SQL，它会将用户的PL/SQL改变为只有ORACLE能够解释的代码版本．

WRAP 实用工具位于$ORACLE_HOME/BIN。

格式为：

WRAP INAME=<input_file_name> [ONAME=<output_file_name>]

wrap iname=e:\sample.txt

注意：在加密前，请将PL/SQL程序先保存一份，以备后用。

7.7 删除包

可以使用 DROP PACKAGE 命令对不需要的包进行删除，语法如下：

DROP PACKAGE [BODY] [user.]package_name;

DROP PROCEDURE OpenCurType; --删除存储过程
--删除我们实例中创建的各个包
DROP PACKAGE demo_pack;
DROP PACKAGE demo_pack1;
DROP PACKAGE emp_mgmt;
DROP PACKAGE emp_package;

7.8 包的管理

包与过程、函数一样，也是存储在数据库中的，可以随时查看其源码。若有需要，在创建包时可以随时查看更详细的编译错误。不需要的包也可以删除。

同样，为了避免调用的失败，在更新表的结构后，一定要记得重新编译依赖于它的程序包。在更新了包说明或包体后，也应该重新编译包说明与包体。语法如下：

ALTER PACKAGE package_name COMPILE [PACKAGE|BODY|SPECIFICATION]；

也可以通过以下数据字典视图查看包的相关。

DBA_SOURCE, USER_SOURCE, USER_ERRORS,DBA-OBJECTS

******************************************************************

----------把触发器说透----------

触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。

8.1 触发器类型

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

8.1.1 DML触发器

ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

8.1.2 替代触发器

由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法。

8.1.3 系统触发器

ORACLE 8i 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

　　触发器组成:

　　l 触发事件：引起触发器被触发的事件。例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

　　l 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

　　l 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。

　　l 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

　　l 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

　　l 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

编写触发器时，需要注意以下几点：

l 触发器不接受参数。

l 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l 触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

8.2 创建触发器

创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

其中：

BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

每张表最多可建立12 种类型的触发器，它们是:

BEFORE INSERT

BEFORE INSERT FOR EACH ROW

AFTER INSERT

AFTER INSERT FOR EACH ROW

BEFORE UPDATE

BEFORE UPDATE FOR EACH ROW

AFTER UPDATE

AFTER UPDATE FOR EACH ROW

BEFORE DELETE

BEFORE DELETE FOR EACH ROW

AFTER DELETE

AFTER DELETE FOR EACH ROW

8.2.1 触发器触发次序

1. 执行 BEFORE语句级触发器;

2. 对与受语句影响的每一行：

l 执行 BEFORE行级触发器

l 执行 DML语句

l 执行 AFTER行级触发器

3. 执行 AFTER语句级触发器

8.2.2 创建DML触发器

触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。

DML触发器的限制

l CREATE TRIGGER语句文本的字符长度不能超过32KB；

l 触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。

l 触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；

l 由触发器所调用的过程或函数也不能使用数据库事务控制语句；

l 触发器中不能使用LONG, LONG RAW 类型；

l 触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；

DML触发器基本要点

l 触发时机：指定触发器的触发时间。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

l 触发事件：引起触发器被触发的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。

l 条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。

1）。INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。

2）。UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE 时，如果修改了column_x列，则取值为TRUE，否则为FALSE。其中column_x是可选的。

3）。DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

解发对象：指定触发器是创建在哪个表、视图上。

l 触发类型：是语句级还是行级触发器。

l 触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表。

　　问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值.

　　实现: :NEW 修饰符访问操作完成后列的值

:OLD 修饰符访问操作完成前列的值

特性	INSERT	UPDATE	DELETE
OLD	NULL	实际值	实际值
NEW	实际值	实际值	NULL

例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;

CREATE OR REPLACE TRIGGER tr_del_emp

BEFORE DELETE --指定触发时机为删除操作前触发

ON scott.emp

FOR EACH ROW --说明创建的是行级触发器

BEGIN

--将修改前数据插入到日志记录表 del_emp ,以供监督使用。

INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

DELETE emp WHERE empno=7788;

DROP TABLE emp_his;

DROP TRIGGER del_emp;

例2：在触发器中调用过程。

CREATE OR REPLACE PROCEDURE add_job_history

( p_emp_id job_history.employee_id%type

, p_start_date job_history.start_date%type

, p_end_date job_history.end_date%type

, p_job_id job_history.job_id%type

, p_department_id job_history.department_id%type

)

IS

BEGIN

INSERT INTO job_history (employee_id, start_date, end_date,

job_id, department_id)

VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);

END add_job_history;

--创建触发器调用存储过程...

CREATE OR REPLACE TRIGGER update_job_history

AFTER UPDATE OF job_id, department_id ON employees

FOR EACH ROW

BEGIN

add_job_history(:old.employee_id, :old.hire_date, sysdate,

:old.job_id, :old.department_id);

END;

8.2.3 创建替代（INSTEAD OF）触发器

创建触发器的一般语法是:

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.] view_name --只能定义在视图上

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定

[WHEN condition]

PL/SQL_block | CALL procedure_name;

其中：

INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。

INSTEAD_OF 用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：

例1：

CREATE OR REPLACE VIEW emp_view AS

SELECT deptno, count(*) total_employeer, sum(sal) total_salary

FROM emp GROUP BY deptno;

　　在此视图中直接删除是非法：

1 2	`SQL>DELETE` `FROM` `emp_view` `WHERE` `deptno=10;` `DELETE` `FROM` `emp_view` `WHERE` `deptno=10`

　　ERROR 位于第 1 行:

　　ORA-01732: 此视图的数据操纵操作非法

　　但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：

CREATE OR REPLACE TRIGGER emp_view_delete

INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

DELETE FROM emp WHERE deptno= :old.deptno;

END emp_view_delete;

DELETE FROM emp_view WHERE deptno=10;

DROP TRIGGER emp_view_delete;

DROP VIEW emp_view;

　　例2：创建复杂视图，针对INSERT操作创建INSTEAD OF触发器，向复杂视图插入数据。

　　l 创建视图：

CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME")

AS

SELECT r.region_id,

r.region_name,

c.country_id,

c.country_name

FROM regions r,

countries c

WHERE r.region_id = c.region_id;

　　l 创建触发器：

CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF

INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER;

BEGIN

SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;

IF v_count = 0 THEN

INSERT INTO regions

(region_id, region_name

) VALUES

(:new.r_id, :new.r_name

);

END IF;

SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;

IF v_count = 0 THEN

INSERT

INTO countries

(

country_id,

country_name,

region_id

)

VALUES

(

:new.c_id,

:new.c_name,

:new.r_id

);

END IF;

END;

创建INSTEAD OF触发器需要注意以下几点：

l 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

l 不能指定BEFORE 或 AFTER选项。

l FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。

l 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

8.2.4 创建系统事件触发器

ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：

　　创建触发器的一般语法是:

CREATE OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER}

{ddl_event_list | database_event_list}

ON { DATABASE | [schema.]SCHEMA }

[WHEN condition]

PL/SQL_block | CALL procedure_name;

　　其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；

　　 database_event_list：一个或多个数据库事件，事件间用 OR 分开；

系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

　　下面给出系统触发器的种类和事件出现的时机（前或后）：

事件	允许的时机	说明
STARTUP	AFTER	启动数据库实例之后触发
SHUTDOWN	BEFORE	关闭数据库实例之前触发（非正常关闭不触发）
SERVERERROR	AFTER	数据库服务器发生错误之后触发
LOGON	AFTER	成功登录连接到数据库后触发
LOGOFF	BEFORE	开始断开数据库连接之前触发
CREATE	BEFORE，AFTER	在执行CREATE语句创建数据库对象之前、之后触发
DROP	BEFORE，AFTER	在执行DROP语句删除数据库对象之前、之后触发
ALTER	BEFORE，AFTER	在执行ALTER语句更新数据库对象之前、之后触发
DDL	BEFORE，AFTER	在执行大多数DDL语句之前、之后触发
GRANT	BEFORE，AFTER	执行GRANT语句授予权限之前、之后触发
REVOKE	BEFORE，AFTER	执行REVOKE语句收权限之前、之后触犯发
RENAME	BEFORE，AFTER	执行RENAME语句更改数据库对象名称之前、之后触犯发
AUDIT / NOAUDIT	BEFORE，AFTER	执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

8.2.5 系统触发器事件属性

事件属性\事件	Startup/Shutdown	Servererror	Logon/Logoff	DDL	DML
事件名称	＊	＊	＊	＊	＊
数据库名称	＊
数据库实例号	＊
错误号		＊
用户名			＊	＊
模式对象类型				＊	＊
模式对象名称				＊	＊
列					＊

　　除DML语句的列属性外，其余事件属性值可通过调用ORACLE定义的事件属性函数来读取。

函数名称	数据类型	说明
Ora_sysevent	VARCHAR2（20）	激活触发器的事件名称
Instance_num	NUMBER	数据库实例名
Ora_database_name	VARCHAR2（50）	数据库名称
Server_error(posi)	NUMBER	错误信息栈中posi指定位置中的错误号
Is_servererror(err_number)	BOOLEAN	检查err_number指定的错误号是否在错误信息栈中，如果在则返回TRUE，否则返回FALSE。在触发器内调用此函数可以判断是否发生指定的错误。
Login_user	VARCHAR2(30)	登陆或注销的用户名称
Dictionary_obj_type	VARCHAR2(20)	DDL语句所操作的数据库对象类型
Dictionary_obj_name	VARCHAR2(30)	DDL语句所操作的数据库对象名称
Dictionary_obj_owner	VARCHAR2(30)	DDL语句所操作的数据库对象所有者名称
Des_encrypted_password	VARCHAR2(2)	正在创建或修改的经过DES算法加密的用户口令

例1：创建触发器，存放有关事件信息。

DESC ora_sysevent

DESC ora_login_user

--创建用于记录事件用的表

CREATE TABLE ddl_event

(crt_date timestamp PRIMARY KEY,

event_name VARCHAR2(20),

user_name VARCHAR2(10),

obj_type VARCHAR2(20),

obj_name VARCHAR2(20));

--创建触犯发器

CREATE OR REPLACE TRIGGER tr_ddl

AFTER DDL ON SCHEMA

BEGIN

INSERT INTO ddl_event VALUES

(systimestamp,ora_sysevent, ora_login_user,

ora_dict_obj_type, ora_dict_obj_name);

END tr_ddl;

　　例2：创建登录、退出触发器。

CREATE TABLE log_event

(user_name VARCHAR2(10),

address VARCHAR2(20),

logon_date timestamp,

logoff_date timestamp);

--创建登录触发器

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logon_date)

VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

--创建退出触发器

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logoff_date)

VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logoff;

8.2.6 使用触发器谓词

ORACLE 提供三个参数INSERTING, UPDATING, DELETING 用于判断触发了哪些操作。

谓词	行为
INSERTING	如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE
UPDATING	如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE
DELETING	如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

8.2.7 重新编译触发器

如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。

在PL/SQL程序中可以调用ALTER TRIGGER语句重新编译已经创建的触发器，格式为：

1	`ALTER` `TRIGGER` `[schema.] trigger_name COMPILE [ DEBUG]`

其中：DEBUG 选项要器编译器生成PL/SQL 程序条使其所使用的调试代码。

8.3 删除和使能触发器

l 删除触发器：

1	`DROP` `TRIGGER` `trigger_name;`

　　当删除其他用户模式中的触发器名称，需要具有DROP ANY TRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTER DATABASE TRIGGER系统权限。

此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

l 禁用或启用触发器

数据库TRIGGER 的状态：

有效状态(ENABLE)：当触发事件发生时，处于有效状态的数据库触发器TRIGGER 将被触发。

无效状态(DISABLE)：当触发事件发生时，处于无效状态的数据库触发器TRIGGER 将不会被触发，此时就跟没有这个数据库触发器(TRIGGER) 一样。

数据库TRIGGER的这两种状态可以互相转换。格式为：

ALTER TIGGER trigger_name [DISABLE | ENABLE ];

--例：ALTER TRIGGER emp_view_delete DISABLE;

　　 ALTER TRIGGER语句一次只能改变一个触发器的状态，而ALTER TABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。格式为：

ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

--例：使表EMP 上的所有TRIGGER 失效：

ALTER TABLE emp DISABLE ALL TRIGGERS;

8.4 触发器和数据字典

相关数据字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,

TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,

STATUS, ACTION_TYPE

FROM user_triggers;

8.5 数据库触发器的应用举例

　例子：创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。

DECLARE

No NUMBER;

Name VARCHAR2(20);

BEGIN

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE VIEW myview AS

SELECT empno, ename, ''E'' type FROM emp

UNION

SELECT dept.deptno, dname, ''D'' FROM dept

');

-- 创建INSTEAD OF 触发器trigger3;

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig3

INSTEAD OF INSERT ON myview

REFERENCING NEW n

FOR EACH ROW

DECLARE

Rows INTEGER;

BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…'');

IF :n.type = ''D'' THEN

SELECT COUNT(*) INTO rows

FROM dept WHERE deptno = :n.empno;

IF rows = 0 THEN

DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…'');

INSERT INTO dept(deptno, dname, loc)

VALUES (:n.empno, :n.ename, ''none’’);

ELSE

DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||

''的部门已存在，插入操作失败！'');

END IF;

ELSE

SELECT COUNT(*) INTO rows

FROM emp WHERE empno = :n.empno;

IF rows = 0 THEN

DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’);

INSERT INTO emp(empno, ename)

VALUES(:n.empno, :n.ename);

ELSE

DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||

''的人员已存在，插入操作失败!'');

END IF;

END;

');

INSERT INTO myview VALUES (70, 'demo', 'D');

INSERT INTO myview VALUES (9999, USER, 'E');

SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;

DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name);

SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;

DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name);

DELETE FROM emp WHERE empno=9999;

DELETE FROM dept WHERE deptno=70;

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3');

END;

8.6 数据库触发器的应用实例

用户可以使用数据库触发器实现各种功能：

　　l 复杂的审计功能；

l 增强数据的完整性管理；

l 帮助实现安全控制；

l 管理复杂的表复制；

l 防止非法的事务发生；

l 自动生成派生的列值；

谢谢各位博友的支持！

未经博主同意不得随意转载。

如需转载请注明出处：http://www.cnblogs.com/ZRJ-boke/p/6602452.html

posted on 2017-03-22 22:23 随缘相识-(志同道合) 阅读(891) 评论(0) 编辑收藏举报

刷新页面返回顶部