Oracle触发器创建定时任务

被秃头支配的恐惧

已于 2022-05-20 16:05:19 修改

阅读量912

点赞数 1

分类专栏： sql

于 2021-10-19 15:21:05 首次发布

本文链接：https://blog.csdn.net/qq_43582366/article/details/120845926

版权

oracle sql 数据库触发器 trigger

sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

源码

CREATE OR REPLACE TRIGGER "CPM_NEW_EMAIL_INSERT_TRIGGER" BEFORE INSERT ON "AW_B01_000010_000001" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
DECLARE
  MESSAGE VARCHAR2(1000);--邮件的内容
  TITLE VARCHAR2(1000);--邮件主题
  V_SQL VARCHAR2(1000);--SQL语句
  ADDRESS VARCHAR2(1000);--邮箱地址
  V_ERRORCODE VARCHAR2(200); --异常代码
  V_ERRORTEXT VARCHAR2(4000); --异常信息
  PRAGMA AUTONOMOUS_TRANSACTION;--异常处理
BEGIN
  IF (:NEW.ADDRESS IS NULL) THEN--如果邮箱地址为空，证明是新增的定时发送邮件配置

    MESSAGE:=:NEW.CONTENT;--邮件内容
    TITLE:=:NEW.TITLE;--邮件标题
    V_SQL:='SELECT LISTAGG(ADDRESS,'';'') WITHIN GROUP(ORDER BY ADDRESS) FROM AW_B01_000010_000001 A WHERE A.MATTER='''||:NEW.MATTER||''' ';
    EXECUTE IMMEDIATE V_SQL INTO ADDRESS;--查询出需要发送哪些邮箱
  --创建定时任务
   DECLARE
    JOB NUMBER;
    BEGIN
         DBMS_JOB.SUBMIT(
         JOB => JOB,
         WHAT => 'DECLARE
                    X_RETURN_MSG    VARCHAR2(1);
                    X_RETURN_STATUS VARCHAR2(200);
                    BEGIN
                    TGK_REP.COE_SEND_MAIL_PKG.SEND_MAIL(
                    P_TO=> '''||ADDRESS||'''
                    ,P_SUBJECT       =>'''||TITLE||'''
                    ,P_MESSAGE       =>'''||MESSAGE||'''
                    ,X_RETURN_STATUS => X_RETURN_STATUS
                    ,X_RETURN_MSG    => X_RETURN_MSG);
                    DBMS_OUTPUT.PUT_LINE(X_RETURN_STATUS);
                    DBMS_OUTPUT.PUT_LINE(X_RETURN_MSG);
                    INSERT INTO COE_SEND_EMAIL_RECORD(STATUS,MESSAGE,TEXT) VALUES(X_RETURN_STATUS,X_RETURN_MSG,'''||MESSAGE||''');
                  END;',
        NEXT_DATE =>TO_DATE(TO_CHAR(:NEW.TIME, 'YYYY/MM/DD')||' 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
        );
    END;
   --新增的是邮箱地址
  ELSE
    FOR ROW_CUR_FIRST_INDEX IN (SELECT * FROM AW_B01_000010_000001 A WHERE A.MATTER=:NEW.MATTER) LOOP
      IF ROW_CUR_FIRST_INDEX.TIME IS NOT NULL THEN--筛选出需要发送的记录
        ADDRESS:=:NEW.ADDRESS;
        TITLE:=ROW_CUR_FIRST_INDEX.TITLE;--邮件标题
        MESSAGE:=ROW_CUR_FIRST_INDEX.CONTENT;--邮件内容
           DECLARE
              JOB NUMBER;
              BEGIN
                   DBMS_JOB.SUBMIT(
                   JOB => JOB,
                   WHAT => 'DECLARE
                              X_RETURN_MSG    VARCHAR2(1);
                              X_RETURN_STATUS VARCHAR2(200);
                              BEGIN
                              TGK_REP.COE_SEND_MAIL_PKG.SEND_MAIL(
                              P_TO=> '''||ADDRESS||'''
                              ,P_SUBJECT       =>'''||TITLE||'''
                              ,P_MESSAGE       =>'''||MESSAGE||'''
                              ,X_RETURN_STATUS => X_RETURN_STATUS
                              ,X_RETURN_MSG    => X_RETURN_MSG);
                              DBMS_OUTPUT.PUT_LINE(X_RETURN_STATUS);
                              DBMS_OUTPUT.PUT_LINE(X_RETURN_MSG);
                              INSERT INTO COE_SEND_EMAIL_RECORD(STATUS,MESSAGE,TEXT) VALUES(X_RETURN_STATUS,X_RETURN_MSG,'''||MESSAGE||''');
                            END;',
                  NEXT_DATE =>TO_DATE(TO_CHAR(ROW_CUR_FIRST_INDEX.TIME, 'YYYY/MM/DD')||' 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
                  );
              END;
              END IF;
    END LOOP;

  END IF;
      COMMIT;

   --异常捕获
EXCEPTION
  WHEN OTHERS THEN
    --异常代码获取
    V_ERRORCODE := SQLCODE;
    --异常信息获取
    V_ERRORTEXT := SUBSTR(SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),
                          1,
                          4000);
    --插入日志表（异常信息插入）
    INSERT INTO TGK_LOAD_ERROR
      (OBJECT_NAME, SPLITSTR, ERRNUM, ERRMSG, INSERT_TIME)
    VALUES
      ('AW_B01_000007_000001',
       '触发器报错',
       V_ERRORCODE,
       V_ERRORTEXT,
       SYSDATE); 
END;

需求：在对某张表进行插入或者修改时，获取时间以及内容，作为定时发送邮件的内容和时间来创建定时任务。
须知：用户要求有两种情况：第一种是新增或者修改一个或者多个邮箱；第二种是新增或者修改多个邮件的标题内容和发送时间。所以需要进行判断。
当邮箱地址不为空时，证明用户新增的是邮箱地址，如果做的是修改操作，则需要删掉旧的定时任务，然后获取对应的邮件标题内容时间加上地址组合成一个新的定时任务。
当邮箱地址为空时，证明是新增的邮件标题内容时间，这时候循环遍历需要发送的邮箱组成一个定时任务即可。如果是修改，则通过标题和内容去匹配旧的定时任务进行删除，然后创建新的定时任务。
实现：
创建触发器：

CREATE OR REPLACE TRIGGER "CPM_NEW_EMAIL_INSERT_TRIGGER" BEFORE INSERT ON "AW_B01_000010_000001" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW

CPM_NEW_EMAIL_INSERT_TRIGGER代表触发器的名称。
BEFORE INSERT ON "AW_B01_000010_000001" 代表在表AW_B01_000010_000001插入数据时触发，BEFORE UPDATE就是在表修改数据时触发
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW就是对于每一条新增的数据，OLD作为旧数据，NEW作为新数据，插入操作时候OLD是不会有数据的，只有在修改操作的时候才会有。

定义变量

  MESSAGE VARCHAR2(1000);--邮件的内容
  TITLE VARCHAR2(1000);--邮件主题
  V_SQL VARCHAR2(1000);--SQL语句
  ADDRESS VARCHAR2(1000);--邮箱地址
  V_ERRORCODE VARCHAR2(200); --异常代码
  V_ERRORTEXT VARCHAR2(4000); --异常信息
  PRAGMA AUTONOMOUS_TRANSACTION;--异常处理

PRAGMA AUTONOMOUS_TRANSACTION用于捕获异常，当出现异常的时候就会跳到最后面的异常捕获

判断情况，创建定时任务

IF (:NEW.ADDRESS IS NULL) THEN--如果邮箱地址为空，证明是新增的定时发送邮件配置

    MESSAGE:=:NEW.CONTENT;--邮件内容
    TITLE:=:NEW.TITLE;--邮件标题
    V_SQL:='SELECT LISTAGG(ADDRESS,'';'') WITHIN GROUP(ORDER BY ADDRESS) FROM AW_B01_000010_000001 A WHERE A.MATTER='''||:NEW.MATTER||''' ';
    EXECUTE IMMEDIATE V_SQL INTO ADDRESS;--查询出需要发送哪些邮箱
  --创建定时任务
   DECLARE
    JOB NUMBER;
    BEGIN
         DBMS_JOB.SUBMIT(
         JOB => JOB,
         WHAT => 'DECLARE
                    X_RETURN_MSG    VARCHAR2(1);
                    X_RETURN_STATUS VARCHAR2(200);
                    BEGIN
                    TGK_REP.COE_SEND_MAIL_PKG.SEND_MAIL(
                    P_TO=> '''||ADDRESS||'''
                    ,P_SUBJECT       =>'''||TITLE||'''
                    ,P_MESSAGE       =>'''||MESSAGE||'''
                    ,X_RETURN_STATUS => X_RETURN_STATUS
                    ,X_RETURN_MSG    => X_RETURN_MSG);
                    DBMS_OUTPUT.PUT_LINE(X_RETURN_STATUS);
                    DBMS_OUTPUT.PUT_LINE(X_RETURN_MSG);
                    INSERT INTO COE_SEND_EMAIL_RECORD(STATUS,MESSAGE,TEXT) VALUES(X_RETURN_STATUS,X_RETURN_MSG,'''||MESSAGE||''');
                  END;',
        NEXT_DATE =>TO_DATE(TO_CHAR(:NEW.TIME, 'YYYY/MM/DD')||' 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
        );
    END;

触发器中变量的赋值格式：变量名:=:NEW.字段例如将新增记录的name赋值给username变量写法就是：USERNAME:=:NEW.NAME
运行SQL以及将运行结果赋值给变量
这里涉及到如何在触发器中，前后存在单引号时如何传入带引号的字符串与变量的问题。

带引号的字符串：下面的SQL需要将查询出来的数据用分号（“:”）进行拼接，在单引号内传入‘;’时，就需要在‘;’外面再套一层单引号进行转义，正确的写法是两个单引号 ‘’;‘’
传入触发器里面的变量：'''||:NEW.MATTER||'''
最后将SQL语句运行并将运行结果赋值到另外一个变量EXECUTE IMMEDIATE V_SQL INTO ADDRESS;V_SQL就是需要运行的语句，ADDRESS就是接收结果的变量

V_SQL:='SELECT LISTAGG(ADDRESS,'';'') WITHIN GROUP(ORDER BY ADDRESS) FROM AW_B01_000010_000001 A WHERE A.MATTER='''||:NEW.MATTER||''' ';
EXECUTE IMMEDIATE V_SQL INTO ADDRESS;--查询出需要发送哪些邮箱

创建定时任务

what中存放需要执行的语句，可以是一段SQL，NEXT_DATE就是执行时间，可以设为一个固定时间，也可以设置为每隔一段时间运行，TO_DATE(TO_CHAR(:NEW.TIME, 'YYYY/MM/DD')||' 10:00:00', 'YYYY-MM-DD HH24:MI:SS')意思就是获取新增数据中的时间加上早上十点转化为字符串格式再转回时间格式。这里没有设置间隔时间（Interval => TRUNC(sysdate,'mi') + 1/ (24*60)），所以定时任务只会执行一次，执行完毕定时任务则自动删除。其他相关的时间间隔表达式见另外一篇文章。链接：https://blog.csdn.net/qq_43582366/article/details/120847167

--创建定时任务
   DECLARE
    JOB NUMBER;
    BEGIN
         DBMS_JOB.SUBMIT(
         JOB => JOB,
         WHAT => '放需要执行的语句',
        NEXT_DATE =>TO_DATE(TO_CHAR(:NEW.TIME, 'YYYY/MM/DD')||' 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
        );

异常捕获

关于COMMIT：客户说触发器会自动commit，不用在代码里面写，但是本人尝试过很多次，不加commit虽然触发器不会报错，数据也正常插入表格，但是不会创建触发器，所以猜测commit是为了提交创建触发器的代码。

COMMIT;

   --异常捕获
EXCEPTION
  WHEN OTHERS THEN
    --异常代码获取
    V_ERRORCODE := SQLCODE;
    --异常信息获取
    V_ERRORTEXT := SUBSTR(SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),
                          1,
                          4000);
    --插入日志表（异常信息插入）
    INSERT INTO TGK_LOAD_ERROR
      (OBJECT_NAME, SPLITSTR, ERRNUM, ERRMSG, INSERT_TIME)
    VALUES
      ('AW_B01_000007_000001',
       '触发器报错',
       V_ERRORCODE,
       V_ERRORTEXT,
       SYSDATE);