精华内容
下载资源
问答
  • 异常处理;e_toomanystudents的作用域和本块的其他变量相同 预定义型异常情态 预定义型异常情态可以直接使用没有必要声明 Invalid_cursor:当执行非法的游标操作时会引发这个错误如试图关闭已关闭的游标 Cursor_...
  • oracle异常触发器.ppt

    2020-11-21 08:58:59
    oracle异常触发器;异常处理;e_toomanystudents的作用域和本块的其他变量相同 预定义型异常情态 预定义型异常情态可以直接使用没有必要声明 Invalid_cursor:当执行非法的游标操作时会引发这个错误如试图关闭已关闭...
  • oracle异常触发器.ppt

    2020-03-17 14:27:01
    异常处理;e_toomanystudents的作用域和本块的其他变量相同 预定义型异常情态 预定义型异常情态可以直接使用没有必要声明 Invalid_cursor:当执行非法的游标操作时会引发这个错误如试图关闭已关闭的游标 Cursor_...
  • 5.1异常处理概念 5.1.1预定义的异常处理 5.1.2非预定义的异常处理 5.1.3用户自定义的异常处理 5.1.4用户定义的异常处理 5.2异常错误传播 5.2.1在执行部分引发异常错误 5.2.2在声明部分引发异常错误 5.3异常...

    本篇主要内容如下:

    5.1 异常处理概念

    5.1.1 预定义的异常处理

    5.1.2 非预定义的异常处理

    5.1.3 用户自定义的异常处理

    5.1.4  用户定义的异常处理

    5.2 异常错误传播

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

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

    5.3 异常错误处理编程

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

    即使是写得最好的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:创建一个函数get_salary, 该函数检索指定部门的工资总和,其中定义了-20991和-20992号错误,分别处理参数为空和非法部门代码两种错误:

    CREATE TABLE errlog(
      Errcode NUMBER,
      Errtext CHAR(40));
    
    CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)
    RETURN NUMBER 
    AS
      v_sal NUMBER;
    BEGIN
      IF p_deptno IS NULL THEN
        RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’);
      ELSIF p_deptno<0 THEN
        RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
      ELSE
        SELECT SUM(employees.salary) INTO v_sal FROM employees 
        WHERE employees.department_id=p_deptno;
        RETURN v_sal;
      END IF;
    END;
    
    DECLARE 
      V_salary NUMBER(7,2);
      V_sqlcode NUMBER;
      V_sqlerr VARCHAR2(512);
      Null_deptno EXCEPTION;
      Invalid_deptno EXCEPTION;
      PRAGMA EXCEPTION_INIT(null_deptno,-20991);
      PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);
    BEGIN
      V_salary :=get_salary(10);
      DBMS_OUTPUT.PUT_LINE('10号部门工资:' || TO_CHAR(V_salary));
    
      BEGIN
        V_salary :=get_salary(-10);
      EXCEPTION
        WHEN invalid_deptno THEN
          V_sqlcode :=SQLCODE;
          V_sqlerr  :=SQLERRM;
          INSERT INTO errlog(errcode, errtext) 
          VALUES(v_sqlcode, v_sqlerr);
          COMMIT;
      END inner1;
    
      V_salary :=get_salary(20);
      DBMS_OUTPUT.PUT_LINE('部门号为20的工资为:'||TO_CHAR(V_salary));
    
      BEGIN
        V_salary :=get_salary(NULL);
      END inner2;
    
      V_salary := get_salary(30);
      DBMS_OUTPUT.PUT_LINE('部门号为30的工资为:'||TO_CHAR(V_salary));
    
      EXCEPTION
        WHEN null_deptno THEN
          V_sqlcode :=SQLCODE;
          V_sqlerr  :=SQLERRM;
          INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
          COMMIT;
        WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
    END outer;
    

    例5:定义触发器,使用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’

     

    例6. 将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;

    例7. 查询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;

    例8. 利用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;

    参考 https://www.cnblogs.com/soundcode/archive/2012/01/10/2318385.html

     

     

     

    展开全文
  • Oracle数据库-触发器

    2021-07-05 19:57:47
    ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块。 触发器在数据库里以独立的对象存储,它与存储过程和函数不同的是,存储过程与函数需要用户显示调用才执行,而触发器是由一个事件...

    引用-----棒棒的总结:https://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html#!comments

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

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

    触发器组成

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

    2. 触发时间:即该TRIGGER 是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该TRIGGER 的操作顺序。

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

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

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

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

    语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只执行一次;

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

    编写时注意事项

    1. 触发器不接受参数。

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

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

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

    5. 在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE),不能使用DDL语句(CREATE、ALTER、DROP)。

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

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

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

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

    学习时的注意事项

    1. 等待补充
    展开全文
  • oracle触发器实例及异常处理

    千次阅读 2017-08-11 11:50:30
    1.写触发器的业务需求:  我需要在工单表(up_ask)插入数据或更新数据时通过触发器(currentnew_ask_trigger)将工单表的数据插入到一个中间表(nosc_reportnew)中 ... 通过在触发器中添加一个异常处理

    1.写触发器的业务需求:

        我需要在工单表(up_ask)插入数据或更新数据时通过触发器(currentnew_ask_trigger)将工单表的数据插入到一个中间表(nosc_reportnew)中

    2.出现的问题:

        如果工单表的数据超过了中间表字段数据长度就会报错,并且导致工单表也没有插入或更新成功,影响了业务处理。

    3.解决方法:

       通过在触发器中添加一个异常处理,即可将异常抛出。即便工单数据出现字段超长或其他异常,也不会影响业务处理,工单表也能照常插入更新数据。



    一、触发器添加异常处理前:

    CREATE OR REPLACE TRIGGER currentnew_ask_trigger
    AFTER  insert or update 
    ON up_ask
    FOR EACH ROW
       
    BEGIN
      IF updating and (:new.status='已关闭' and :old.status<>'已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%')  then
        INSERT INTO nosc_reportnew (id,issend,CRMID,ACCEPTTIME,COMPLAINTTYPE,USERCITY,USERPHONE,USERLEVEL,USERBRAND,COMPLAINTCONTENT,DUPLICATECOMPLAINT,COMPLAINTFLAG,PROBLEMPLACE,PROBLEMPLACEDETAIL,LONGITUDE,LATITUDE,EQUIPMENTTYPE,DEALDESC,COMPLAINTREASON,SOLVEFLAG,DEALUSER,REMARK,NETTYPE,ACCEPTCITY,GPRSVALUE,GPRSAVERAGE,STARLEVEL)
        VALUES(SEQ_NONC_CURRENTNEW.Nextval,'0',:NEW.sheetsn,:NEW.Createdate,:NEW.complainttype,:NEW.usercity,:NEW.acceptuserphone,:NEW.clientuserlevel,:NEW.clientuserbrand,:NEW.complaintcontent,:NEW.DUPLICATECOMPLAINT,'4',:NEW.problemhappenplace,:NEW.problemhappenplacedetail,'','',:NEW.EQUIPMENTTYPE,:NEW.DEALRESULT,:NEW.complaintreasoncode,:NEW.solveFlag,:NEW.FINISHDEALER,'',:NEW.nettype,:NEW.acceptcity,:NEW.OnlyElement20,:NEW.AVERAGE_USAGE_VOLUME,:NEW.starlevel);
      elsif inserting and (:new.status='已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%') then
        INSERT INTO nosc_reportnew (id,issend,CRMID,ACCEPTTIME,COMPLAINTTYPE,USERCITY,USERPHONE,USERLEVEL,USERBRAND,COMPLAINTCONTENT,DUPLICATECOMPLAINT,COMPLAINTFLAG,PROBLEMPLACE,PROBLEMPLACEDETAIL,LONGITUDE,LATITUDE,EQUIPMENTTYPE,DEALDESC,COMPLAINTREASON,SOLVEFLAG,DEALUSER,REMARK,NETTYPE,ACCEPTCITY,GPRSVALUE,GPRSAVERAGE,STARLEVEL)
        VALUES(SEQ_NONC_CURRENTNEW.Nextval,'0',:NEW.sheetsn,:NEW.Createdate,:NEW.complainttype,:NEW.usercity,:NEW.acceptuserphone,:NEW.clientuserlevel,:NEW.clientuserbrand,:NEW.complaintcontent,:NEW.DUPLICATECOMPLAINT,'4',:NEW.problemhappenplace,:NEW.problemhappenplacedetail,'','',:NEW.EQUIPMENTTYPE,:NEW.DEALRESULT,:NEW.complaintreasoncode,:NEW.solveFlag,:NEW.FINISHDEALER,'',:NEW.nettype,:NEW.acceptcity,:NEW.OnlyElement20,:NEW.AVERAGE_USAGE_VOLUME,:NEW.starlevel);
      end IF;
        
    end;

    二、触发器添加异常处理:

    CREATE OR REPLACE TRIGGER currentnew_ask_trigger
    AFTER  insert or update 
    ON up_ask
    FOR EACH ROW
       DECLARE  -----定义异常
        INSERT_EXCE exception;
    BEGIN
      IF updating and (:new.status='已关闭' and :old.status<>'已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%')  then
        INSERT INTO nosc_reportnew (id,issend,CRMID,ACCEPTTIME,COMPLAINTTYPE,USERCITY,USERPHONE,USERLEVEL,USERBRAND,COMPLAINTCONTENT,DUPLICATECOMPLAINT,COMPLAINTFLAG,PROBLEMPLACE,PROBLEMPLACEDETAIL,LONGITUDE,LATITUDE,EQUIPMENTTYPE,DEALDESC,COMPLAINTREASON,SOLVEFLAG,DEALUSER,REMARK,NETTYPE,ACCEPTCITY,GPRSVALUE,GPRSAVERAGE,STARLEVEL)
        VALUES(SEQ_NONC_CURRENTNEW.Nextval,'0',:NEW.sheetsn,:NEW.Createdate,:NEW.complainttype,:NEW.usercity,:NEW.acceptuserphone,:NEW.clientuserlevel,:NEW.clientuserbrand,:NEW.complaintcontent,:NEW.DUPLICATECOMPLAINT,'4',:NEW.problemhappenplace,:NEW.problemhappenplacedetail,'','',:NEW.EQUIPMENTTYPE,:NEW.DEALRESULT,:NEW.complaintreasoncode,:NEW.solveFlag,:NEW.FINISHDEALER,'',:NEW.nettype,:NEW.acceptcity,:NEW.OnlyElement20,:NEW.AVERAGE_USAGE_VOLUME,:NEW.starlevel);
      elsif inserting and (:new.status='已关闭' or :new.status='已作废') and :new.closedate is not null and (:new.complainttype like '融合通信%' or :new.complainttype like '自有业务%' or :new.complainttype like '基础通信%') then
        INSERT INTO nosc_reportnew (id,issend,CRMID,ACCEPTTIME,COMPLAINTTYPE,USERCITY,USERPHONE,USERLEVEL,USERBRAND,COMPLAINTCONTENT,DUPLICATECOMPLAINT,COMPLAINTFLAG,PROBLEMPLACE,PROBLEMPLACEDETAIL,LONGITUDE,LATITUDE,EQUIPMENTTYPE,DEALDESC,COMPLAINTREASON,SOLVEFLAG,DEALUSER,REMARK,NETTYPE,ACCEPTCITY,GPRSVALUE,GPRSAVERAGE,STARLEVEL)
        VALUES(SEQ_NONC_CURRENTNEW.Nextval,'0',:NEW.sheetsn,:NEW.Createdate,:NEW.complainttype,:NEW.usercity,:NEW.acceptuserphone,:NEW.clientuserlevel,:NEW.clientuserbrand,:NEW.complaintcontent,:NEW.DUPLICATECOMPLAINT,'4',:NEW.problemhappenplace,:NEW.problemhappenplacedetail,'','',:NEW.EQUIPMENTTYPE,:NEW.DEALRESULT,:NEW.complaintreasoncode,:NEW.solveFlag,:NEW.FINISHDEALER,'',:NEW.nettype,:NEW.acceptcity,:NEW.OnlyElement20,:NEW.AVERAGE_USAGE_VOLUME,:NEW.starlevel);
      end IF;
        exception when INSERT_EXCE then raise_application_error('-20002', '不能插入数据到中间表,请检查工单数据!');---抛出异常
    end;


    展开全文
  • Oracle-trigger触发器解读

    千次阅读 2016-06-05 00:09:04
    触发器相关概念及语法概述本篇博文中主要...每当一个特定的数据操作语句(insert update delete)在指定的表上发出时,Oracle自动执行触发器中定义的语句序列。举个简单的例子:当员工表中新增一条记录后,自动打印“成

    触发器相关概念及语法

    概述

    本篇博文中主要探讨以下内容:

    • 什么是触发器
    • 触发器的应用场景
    • 触发器的语法
    • 触发器的类型
    • 案例

    数据:

    这里写图片描述


    触发器的概念和第一个触发器

    数据库触发器是一个与表相关联的,存储的PL/SQL 语句。

    每当一个特定的数据操作语句(insert update delete)在指定的表上发出时,Oracle自动执行触发器中定义的语句序列。

    举个简单的例子:

    当员工表中新增一条记录后,自动打印“成功插入新员工”

    create or replace trigger insertStaffHint
      after insert on xgj_test  
      for each row
    declare
      -- local variables here
    begin
    
         dbms_output.put_line('新增员工成功');
    
    end insertStaffHint;

    这里写图片描述


    触发器的应用场景

    • 复杂的安全性检查
    • 数据的确认
    • 数据库审计
    • 数据的备份和审计

    触发器的语法

    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 

    触发器的类型

    行触发器要求当一个DML语句操作影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器; 在行级触发器中,使用:old 和:new 伪记录变量,识别值的状态

    语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。

    当省略FOR EACH ROW 选项时,BEFORE 和AFTER 触发器为语句触发器,
    而INSTEAD OF 触发器则只能为行触发器。

    其他规则

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

    DML触发器的限制:

    • CREATE TRIGGER语句文本的字符长度不能超过32KB;
    • 触发器体内的SELECT 语句只能为SELECT … INTO …结构,或者为定义游标所使用的SELECT 语句。
    • 触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句;
    • 由触发器所调用的过程或函数也不能使用数据库事务控制语句;
    • 触发器中不能使用LONG, LONG RAW 类型;
    • 触发器内可以参照LOB 类型列的列值,但不能通过 :NEW 修改LOB列中的数据;

    DML触发器基本要点

    • 触发时机:指定触发器的触发时间。如果指定为BEFORE,则表示在执行DML操作之前触发,以便防止某些错误操作发生或实现某些业务规则;如果指定为AFTER,则表示在执行DML操作之后触发,以便记录该操作或做某些事后处理。
    • 触发事件:引起触发器被触发的事件,即DML操作(INSERT、UPDATE、DELETE)。既可以是单个触发事件,也可以是多个触发事件的组合(只能使用OR逻辑组合,不能使用AND逻辑组合)。
    • 条件谓词:当在触发器中包含多个触发事件(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。
    • 解发对象:指定触发器是创建在哪个表、视图上。
    • 触发类型:是语句级还是行级触发器
    • 触发条件:由WHEN子句指定一个逻辑表达式,只允许在行级触发器上指定触发条件,指定UPDATING后面的列的列表。

    触发器应用

    触发器应用一 复杂的安全性检查

    禁止在非工作时间插入数据

    /**
    非工作时间(星球六 星期日, 非9点~18点的区间)
    禁止写入数据
    
    首先要搞清楚: 触发器的类型--语句级触发器。
    不管插入了多少条数据,没有必要对每一行数据都进行校验,只要不在这个时间段内,都不让插入。
    */
    create or replace trigger addStafffCheck
      before insert on xgj_test  
    
    declare
      -- local variables here
    begin
    
      if to_char(sysdate, 'day') in ('星期六', '星期日') or
         to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
      --禁止insert 
           raise_application_error(-20001,'非工作时间禁止插入数据');
      end if;
    end addStafffCheck;

    这里写图片描述

    raise_application_error用于在plsql使用程序中自定义不正确消息。
    该异常只好在数据库端的子程序(流程、函数、包、触发器)中运用,而无法在匿名块和客户端的子程序中运用。
    语法为raise_application_error(error_number,message[,[truefalse]]); 其中 error_number用于定义不正确号,
    该不正确号必须在-20000到-20999之间的负整数; message用于指定不正确消息,并且该消息的长度无法超过2048字节;


    触发器应用二 数据确认

    工资不能越涨越少

    分析: 首先确认下触发器的类型, 因为需要对每一条数据进行确认,所以是个行级触发器。 需要for each row

    /**
    涨后的薪水不能低于涨前的薪水
    
    1 :old 和 :new 代表同一条记录
    2  :old 代表操作该行之前,这一行的值
       :new 代表操作该行之后,这一行的值
    */
    
    create or replace trigger checkSalary
      before update 
      on xgj_test  
      for each row
    declare
      -- local variables here  没有变量声明的话,declare可以省略
    begin
    
     --- if 涨后的薪水 <  涨前的薪水 then  如何表示呢 ?
      if :new.sal < :old.sal then
    
        raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水:'||:old.sal);
      end if;
    end checkSalary;

    这里写图片描述


    触发器应用三 数据库审计

    创建基于值的触发器

    create table xgj_record(info varchar2(256)) ;
    create or replace trigger addrecord
      after update
      on xgj_test  
      for each row
    declare
      -- local variables here
    begin
    
      if :new.sal > 6000 then 
         insert into xgj_record values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
      end if;
    
    end addrecord;

    这里写图片描述


    触发器应用四 数据的备份和同步

    当给员工涨完工资后,自动备份到备份表中

     create table xgj_test_bak as select * from xgj_test ;
    create or replace trigger databack
      after update
      on xgj_test
      for each row   
    
    begin
    
     update xgj_test_bak set sal = :new.sal where username = :new.username ;
    
    end databack;

    这里写图片描述
    oracle中还有个利用快照备份,是异步的。 而利用触发器,是同步的。

    其他

    建立一个触发器, 当职工表 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;

    限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围,即不允许在非工作时间修改departments表。

    CREATE OR REPLACE TRIGGER tr_dept_time
    BEFORE INSERT OR DELETE OR UPDATE
    ON departments
    BEGIN
     IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
         RAISE_APPLICATION_ERROR(-20001, '不是上班时间,不能修改departments表');
     END IF;
    END;

    限定只对部门号为80的记录进行行触发器操作。

    CREATE OR REPLACE TRIGGER tr_emp_sal_comm
    BEFORE UPDATE OF salary, commission_pct
           OR DELETE
    ON HR.employees
    FOR EACH ROW
    WHEN (old.department_id = 80)
    BEGIN
     CASE
         WHEN UPDATING ('salary') THEN
            IF :NEW.salary < :old.salary THEN
    
               RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');
            END IF;
         WHEN UPDATING ('commission_pct') THEN
    
            IF :NEW.commission_pct < :old.commission_pct THEN
               RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');
            END IF;
         WHEN DELETING THEN
              RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');
         END CASE;
    END;
    
    /*
    实例:
    UPDATE employees SET salary = 8000 WHERE employee_id = 177;
    DELETE FROM employees WHERE employee_id in (177,170);
    */

    利用行触发器实现级联更新。在修改了主表regions中的region_id之后(AFTER),级联的、自动的更新子表countries表中原来在该地区的国家的region_id。

    CREATE OR REPLACE TRIGGER tr_reg_cou
    AFTER update OF region_id
    ON regions
    FOR EACH ROW
    BEGIN
     DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id
                      ||'、新的region_id值是'||:new.region_id);
     UPDATE countries SET region_id = :new.region_id
     WHERE region_id = :old.region_id;
    END;

    在触发器中调用过程。

    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;

    更多详细介绍

    展开全文
  • 每当一个特定的数据操作语句(insert、update、delete)在指定的表上发出时,Oracle自动执行触发器中定义的语句序列。例如:当员工信息插入后,自动输出“插入成功”的信息。 create or replace trigger ...
  • Oracle中的触发器

    2013-07-17 20:26:15
    Oracle中的触发器   在oracle中,触发器是一种特殊的子程序,它在插入、更新、删除数据时自动执行。使用触发器可以实现比较复杂的约束。(PK FK UQ CK DF都是单表约束数据完整性。) 触发器可以分为INSERT...
  • ORACLE触发器详解

    万次阅读 多人点赞 2012-09-27 10:00:56
    ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块。 8.1 触发器类型  触发器在数据库里以独立的对象存储,它与存储过程和函数不同的是,存储过程与函数需要用户显示调用才...
  • Oracle函数与触发器

    2013-09-30 11:16:23
    Oracle之函数与触发器 ... 数据库中函数包含四个部分:声明、返回值、函数体和异常处理。   1 create or replace function getname(sno varchar2 )  2 return varchar is   3
  • 好久没写过oracle触发器了,今天再次写到,记录下: [code="java"]create sequence person_seq start with 1 increment by 1 order --按顺序 nocycle --不循环不...
  • Oracle 序列,触发器

    2018-08-09 16:19:35
    --触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块。 --语法: CREATE OR REPLACE TRIGGER TREIGGER_NAME BEFORE/AFTER --之前/之后 INSERT/UPDATE/DELETE --插入/更新/删除 ON X_TABLE --对哪个表 -...
  • Oracle 学习之触发器

    2017-07-27 18:43:00
     触发器是存储在数据库服务器中的程序单元,当一个表或一个视图被改变,或者数据库发生某些事件时,Oracle会自动触发触发器,并执行触发器中的代码。只有在触发器中定义的事件发生时,触发器才被触发。触发器是自动...
  • 前言在ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块。触发器类型触发器在数据库里以独立的对象存储,它与存储过程和函数不同的是,存储过程与函数需要用户显示调用才执行,而触发器...
  • ORACLE : 把触发器

    2014-01-17 11:14:44
    8.1 触发器类型 8.1.1 DML触发器 8.1.2 替代触发器 8.1.3 系统触发器 8.2 创建触发器 8.2.1 触发器触发次序 8.2.2 创建DML触发器 8.2.3 创建替代(INSTEAD OF)触发器 8.2.3 创建系统事件触发器 ...
  • Oracle 中的 触发器

    2018-07-12 11:23:28
    1.简介 触发器就是说某个条件成立的时候,触发器里面所定义的语句就会被自动的执行。触发器不需要人为的去调用,也不能调用。触发器的触发条件其实在你定义的时候就已经设定好了。并且,触发器不能接收参数(自动...
  • 接下来请看第2页精彩内容: 相关阅读: Oracle触发器的使用 Oracle触发器给表自身的字段重新赋值出现ORA-04091异常 Oracle创建触发器调用含参数存储过程 Oracle触发器查询统计本表 MySQL 触发器应用案例 本条技术...
  • Oracle--触发器

    2017-06-28 15:24:58
    系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块。 8.1 触发器类型  触发器在数据库里以独立的对象存储,它与存储过程和函数不同的是,存储过程与函数需要用户显示调用才执行,...
  • Oracle异常处理

    千次阅读 2016-04-09 14:35:49
    总结:整理 oracle异常错误处理 . 2013-08-07 18:00:48 分类: Oracle 5.1 异常处理概念 5.1.1 预定义的异常处理 5.1.2 非预定义的异常处理 5.1.3 用户自定义的异常处理 ...
  • oracle trigger (触发器)

    2012-08-13 10:15:14
    --触发器 trigger --1.DML触发器 DML statements (DELETE, INSERT, UPDATE) ... 对于一条dml sql,可能作用与多行,也可能...语句触发器对每条触发sql,触发器只执行一次;行级触发器是每作用一行就触发一次  触
  • Oracle 序列、触发器 语法

    千次阅读 2019-01-16 11:23:34
     在动作执行之前或者之后,触发业务处理逻辑  插入数据,做一些校验  语法:  create [or replace] trigger 触发器的名称  before | after  insert | update | delete   on 表名  [for each row]  ...
  • Oracle触发器

    千次阅读 2020-03-18 11:02:33
    Oracle学习之触发器 –实现一个id列自动增长的触发器 –首先我们需要建个表 create table person( id number primary key, name varchar2(50) not null ) –查一下看看 select * from person –要实现id列自动增长...
  • ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块。1 触发器类型 触发器在数据库里以独立的对象存储,它与存储过程和函数不同的是,存储过程与函数需要用户显示调用才执行,而触发器...
  • Oracle系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块 触发器在数据库中以独立的对象存储 触发器是:特定实践出现的时候,自动执行的代码块。 类似于存储过程,触发器与存储过程的区别...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 13,808
精华内容 5,523
关键字:

oracle异常处理触发器