创建存储过程

Posted by     小兵兵 on Friday, August 14, 2020

目录

存储过程

CREATE OR REPLACE PROCEDURE P_ONLINE_SIM_CHECK_TARGT(V_DEAL_TAG  OUT NUMBER,
                                                     V_DEAL_DESC OUT VARCHAR2) AS
  -----------------------------------------------------------------------
  --Description:  实名认证对账 捞取21和24类型的激活订单,比对服务器上的
  --用户实名制(正面照|反面照|人像照)信息是否完整,如缺失则进行异常信息记录
  --Author:       shenlb
  --Date:         2020-07-23 10:11
  -----------------------------------------------------------------------
  --异常信息描述
  V_REMARK      VARCHAR2(2000);
  V_NUM         NUMBER(20);
  V_HIS_NUM     NUMBER(20);
  V_COUNT       NUMBER(20);
  V_HIS_COUNT   NUMBER(20);
  V_TOTAL_COUNT NUMBER(20);
  V_ADD_TIME    DATE;

  --图片(正面照|反面照|人像照)存在标志位
  V_FLAG_PIC_NAME_Z     NUMBER;
  V_FLAG_PIC_NAME_F     NUMBER;
  V_FLAG_PIC_NAME_R     NUMBER;
  V_FLAG_HIS_PIC_NAME_Z NUMBER;
  V_FLAG_HIS_PIC_NAME_F NUMBER;
  V_FLAG_HIS_PIC_NAME_R NUMBER;
  --短信下发
  V_SMS_NOTICE_ID    TI_O_SMS.SMS_NOTICE_ID%TYPE;
  V_PARTITION_ID     TI_O_SMS.PARTITION_ID%TYPE;
  V_SMS_CONTENT      VARCHAR2(500);
  V_FORCE_START_TIME TI_O_SMS.FORCE_START_TIME%TYPE;
  SERIAL_NUMBER      VARCHAR2(20);
  USER_ID            VARCHAR2(50);
BEGIN

  V_DEAL_TAG  := 0;
  V_DEAL_DESC := 'SUCCESS';

  DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
  DBMS_OUTPUT.PUT_LINE('关于线上号卡实名制对账及业务稽核的系统开发需求----->');
  V_NUM         := 1;
  V_HIS_NUM     := 1;
  V_COUNT       := 0;
  V_HIS_COUNT   := 0;
  V_TOTAL_COUNT := 0;
  SERIAL_NUMBER := '18898971756';
  SELECT SYSDATE INTO V_ADD_TIME FROM DUAL;

  --历史数据对账----
  --捞取历史数据未处理
  V_FLAG_HIS_PIC_NAME_Z := 0;
  V_FLAG_HIS_PIC_NAME_F := 0;
  V_FLAG_HIS_PIC_NAME_R := 0;
  FOR HIS_UNHANDLE_PICS_ITEMS IN (SELECT A.ORDER_ID,
                                         A.TRANSACTION_ID,
                                         A.SERVICE_NO,
                                         A.ACTIVE_TIME,
                                         A.ADD_TIME,
                                         A.CHANNEL_TYPE,
                                         A.ORDER_TYPE,
                                         A.PIC_Z,
                                         A.PIC_F,
                                         A.PIC_R
                                    FROM UCR_CRM1.TF_F_PIC_TRANS_RESULT A
                                   WHERE A.STATE = '0') LOOP
    DBMS_OUTPUT.PUT_LINE('捞取历史未处理数据第' || V_HIS_NUM || '条: 订单ID' ||
                         HIS_UNHANDLE_PICS_ITEMS.ORDER_ID || ' 业务流水号:' ||
                         HIS_UNHANDLE_PICS_ITEMS.TRANSACTION_ID || ' 正面照:' ||
                         HIS_UNHANDLE_PICS_ITEMS.PIC_Z || ' 反面照:' ||
                         HIS_UNHANDLE_PICS_ITEMS.PIC_F || ' 人像照:' ||
                         HIS_UNHANDLE_PICS_ITEMS.PIC_R || ' 业务类型:' ||
                         HIS_UNHANDLE_PICS_ITEMS.ORDER_TYPE || ' 受理时间:' ||
                         ' 手机号:' || HIS_UNHANDLE_PICS_ITEMS.SERVICE_NO);
    V_HIS_NUM := V_HIS_NUM + 1;
    SELECT COUNT(*)
      INTO V_FLAG_PIC_NAME_Z
      FROM UCR_CEN1.TL_B_PIC_TRANS@DBLNK_CENDBN1 T
     WHERE T.FILE_NAME = HIS_UNHANDLE_PICS_ITEMS.PIC_Z;
  
    SELECT COUNT(*)
      INTO V_FLAG_PIC_NAME_F
      FROM UCR_CEN1.TL_B_PIC_TRANS@DBLNK_CENDBN1 T
     WHERE T.FILE_NAME = HIS_UNHANDLE_PICS_ITEMS.PIC_F;
  
    SELECT COUNT(*)
      INTO V_FLAG_PIC_NAME_R
      FROM UCR_CEN1.TL_B_PIC_TRANS@DBLNK_CENDBN1 T
     WHERE T.FILE_NAME = HIS_UNHANDLE_PICS_ITEMS.PIC_R;
    BEGIN
      V_REMARK := '';
      IF V_FLAG_PIC_NAME_Z = 0 THEN
        V_REMARK := V_REMARK || '正面照:' || HIS_UNHANDLE_PICS_ITEMS.PIC_Z || '缺失';
      END IF;
      IF V_FLAG_PIC_NAME_F = 0 THEN
        V_REMARK := V_REMARK || '  反面照:' || HIS_UNHANDLE_PICS_ITEMS.PIC_F || '缺失';
      END IF;
      IF V_FLAG_PIC_NAME_R = 0 THEN
        V_REMARK := V_REMARK || '  人像照:' || HIS_UNHANDLE_PICS_ITEMS.PIC_R || '缺失';
      END IF;
      IF V_REMARK IS NOT NULL THEN
        UPDATE UCR_CRM1.TF_F_PIC_TRANS_RESULT A
           SET A.REMARK      = V_REMARK,
               A.PIC_Z       = HIS_UNHANDLE_PICS_ITEMS.PIC_Z,
               A.PIC_F       = HIS_UNHANDLE_PICS_ITEMS.PIC_F,
               A.PIC_R       = HIS_UNHANDLE_PICS_ITEMS.PIC_R,
               A.UPDATE_TIME = SYSDATE
         WHERE A.ORDER_ID = HIS_UNHANDLE_PICS_ITEMS.ORDER_ID;
        COMMIT;
        V_HIS_COUNT := V_HIS_COUNT + 1;
      ELSE
        UPDATE UCR_CRM1.TF_F_PIC_TRANS_RESULT A
           SET A.STATE = '1', A.UPDATE_TIME = SYSDATE, A.REMARK = ''
         WHERE A.ORDER_ID = HIS_UNHANDLE_PICS_ITEMS.ORDER_ID;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        --失败
        V_DEAL_TAG  := -1;
        V_DEAL_DESC := '失败 [' || SQLERRM || ']';
        DBMS_OUTPUT.PUT_LINE('异常信息:' || V_DEAL_DESC);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('历史图片数量缺失信息条数:' || V_HIS_COUNT);
  --新数据对账对账---
  --订单记录信息
  --21类型的订单 物流上门写卡订单
  FOR ITEMS IN (SELECT T.ORDER_ID,
                       T.TRANSACTION_ID,
                       T.PIC_NNAME_Z,
                       T.PIC_NNAME_F,
                       T.PIC_NNAME_R,
                       T1.NUMBER_OPRTYPE,
                       T1.UPDATE_TIME,
                       T1.SERVICENO
                  FROM UCR_CEN1.TF_F_OAOWRITECARDORDER_INFO@DBLNK_CENDBN1 T,
                       UCR_CEN1.TF_B_CTRM_GERLSUBORDER@DBLNK_CENDBN1      T1
                 WHERE T.ORDER_ID = T1.ORDER_ID
                   AND T1.NUMBER_OPRTYPE = '21'
                   AND T1.STATE = 'AC'
                   AND T1.UPDATE_TIME >= TRUNC(SYSDATE - 1)
                   AND T1.UPDATE_TIME < TRUNC(SYSDATE)
                UNION ALL
                --24类型的订单 物流上门写卡携转订单
                SELECT T.ORDER_ID,
                       T.TRANSACTION_ID,
                       T.PIC_NNAME_Z,
                       T.PIC_NNAME_F,
                       T.PIC_NNAME_R,
                       T1.NUMBER_OPRTYPE,
                       T1.UPDATE_TIME,
                       T1.SERVICENO
                  FROM UCR_CEN1.TF_F_OAOWRITECARDORDER_INFO_NP@DBLNK_CENDBN1 T,
                       UCR_CEN1.TF_B_CTRM_GERLSUBORDER@DBLNK_CENDBN1         T1
                 WHERE T.ORDER_ID = T1.ORDER_ID
                   AND T1.NUMBER_OPRTYPE = '24'
                   AND T1.STATE = 'AC'
                   AND T1.UPDATE_TIME >= TRUNC(SYSDATE - 1)
                   AND T1.UPDATE_TIME < TRUNC(SYSDATE)) LOOP
    DBMS_OUTPUT.PUT_LINE('捞取昨天数据第' || V_NUM || '条: 订单ID' || ITEMS.ORDER_ID ||
                         ' 业务流水号:' || ITEMS.TRANSACTION_ID || ' 正面照:' ||
                         ITEMS.PIC_NNAME_Z || ' 反面照:' || ITEMS.PIC_NNAME_F ||
                         ' 人像照:' || ITEMS.PIC_NNAME_R || ' 业务类型:' ||
                         ITEMS.NUMBER_OPRTYPE || ' 受理时间:' ||
                         ITEMS.UPDATE_TIME || ' 手机号:' || ITEMS.SERVICENO);
    V_NUM             := V_NUM + 1;
    V_FLAG_PIC_NAME_Z := 0;
    V_FLAG_PIC_NAME_F := 0;
    V_FLAG_PIC_NAME_R := 0;
  
    SELECT COUNT(*)
      INTO V_FLAG_PIC_NAME_Z
      FROM UCR_CEN1.TL_B_PIC_TRANS@DBLNK_CENDBN1 T
     WHERE T.INSERT_TIME >= TRUNC(SYSDATE - 2)
       AND T.INSERT_TIME < SYSDATE
       AND T.FILE_NAME = ITEMS.PIC_NNAME_Z;
  
    SELECT COUNT(*)
      INTO V_FLAG_PIC_NAME_F
      FROM UCR_CEN1.TL_B_PIC_TRANS@DBLNK_CENDBN1 T
     WHERE T.INSERT_TIME >= TRUNC(SYSDATE - 2)
       AND T.INSERT_TIME < SYSDATE
       AND T.FILE_NAME = ITEMS.PIC_NNAME_F;
  
    SELECT COUNT(*)
      INTO V_FLAG_PIC_NAME_R
      FROM UCR_CEN1.TL_B_PIC_TRANS@DBLNK_CENDBN1 T
     WHERE T.INSERT_TIME >= TRUNC(SYSDATE - 2)
       AND T.INSERT_TIME < SYSDATE
       AND T.FILE_NAME = ITEMS.PIC_NNAME_R;
  
    DBMS_OUTPUT.PUT_LINE('正面照:' || V_FLAG_PIC_NAME_Z || ' 反面照:' ||
                         V_FLAG_PIC_NAME_F || ' 人像照:' || V_FLAG_PIC_NAME_R);
  
    BEGIN
      V_REMARK := '';
      IF V_FLAG_PIC_NAME_Z = 0 THEN
        V_REMARK := V_REMARK || '正面照:' || ITEMS.PIC_NNAME_Z || '缺失';
      END IF;
      IF V_FLAG_PIC_NAME_F = 0 THEN
        V_REMARK := V_REMARK || '  反面照:' || ITEMS.PIC_NNAME_F || '缺失';
      END IF;
      IF V_FLAG_PIC_NAME_R = 0 THEN
        V_REMARK := V_REMARK || '  人像照:' || ITEMS.PIC_NNAME_R || '缺失';
      END IF;
      IF V_REMARK IS NOT NULL THEN
        INSERT INTO UCR_CRM1.TF_F_PIC_TRANS_RESULT
          (ID,
           ORDER_ID,
           TRANSACTION_ID,
           SERVICE_NO,
           ACTIVE_TIME,
           CHANNEL_TYPE,
           ORDER_TYPE,
           ADD_TIME,
           REMARK,
           STATE,
           PIC_Z,
           PIC_F,
           PIC_R)
        VALUES
          (SYS_GUID(),
           ITEMS.ORDER_ID,
           ITEMS.TRANSACTION_ID,
           ITEMS.SERVICENO,
           ITEMS.UPDATE_TIME,
           '1',
           ITEMS.NUMBER_OPRTYPE,
           V_ADD_TIME,
           V_REMARK,
           '0',
           ITEMS.PIC_NNAME_Z,
           ITEMS.PIC_NNAME_F,
           ITEMS.PIC_NNAME_R);
        COMMIT;
        V_COUNT := V_COUNT + 1;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        --失败
        V_DEAL_TAG  := -1;
        V_DEAL_DESC := '失败 [' || SQLERRM || ']';
        DBMS_OUTPUT.PUT_LINE('异常信息:' || V_DEAL_DESC);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('捞取昨天图片数量缺失信息条数:' || V_COUNT);
  V_TOTAL_COUNT := V_HIS_COUNT + V_COUNT;
  IF V_TOTAL_COUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('开始短信下发------>');
    --短信下发
    FOR ITEM_PHONES IN (SELECT A.USER_ID, A.SERIAL_NUMBER
                          FROM UCR_CRM1.TF_F_USER A
                         WHERE A.SERIAL_NUMBER IN
                               ('13518021375', '15120941595', '15808943160',
                                '13976617686', '13876060003', '13698969899')
                           AND A.REMOVE_TAG = '0') LOOP
      DBMS_OUTPUT.PUT_LINE('用户ID:' || ITEM_PHONES.USER_ID || ' 手机号码:' ||
                           ITEM_PHONES.SERIAL_NUMBER);
      SELECT TO_NUMBER(F_SYS_GETSEQID(0898, 'seq_smssend_id'))
        INTO V_SMS_NOTICE_ID
        FROM DUAL;
    
      SELECT MOD(V_SMS_NOTICE_ID, 1000) INTO V_PARTITION_ID FROM DUAL;
    
      V_SMS_CONTENT := '您有用户实名制对账图片数量缺失共' || V_TOTAL_COUNT || '条,其中历史数据缺失' ||
                       V_HIS_COUNT || '条,昨天数据缺失' || V_COUNT || '条,请尽快处理';
      BEGIN
        INSERT INTO TI_O_SMS
          (SMS_NOTICE_ID,
           PARTITION_ID,
           EPARCHY_CODE,
           IN_MODE_CODE,
           CHAN_ID,
           RECV_OBJECT_TYPE,
           RECV_OBJECT,
           RECV_ID,
           SMS_TYPE_CODE,
           SMS_KIND_CODE,
           NOTICE_CONTENT_TYPE,
           NOTICE_CONTENT,
           REFERED_COUNT,
           FORCE_REFER_COUNT,
           SMS_PRIORITY,
           REFER_TIME,
           REFER_STAFF_ID,
           REFER_DEPART_ID,
           DEAL_TIME,
           DEAL_STATE,
           SEND_TIME_CODE,
           SEND_OBJECT_CODE,
           FORCE_START_TIME)
          SELECT V_SMS_NOTICE_ID,
                 V_PARTITION_ID,
                 '0898',
                 '1',
                 'C006', --短信渠道编码:客户服务
                 '00', --被叫对象类型:00-手机号码
                 ITEM_PHONES.SERIAL_NUMBER, --被叫对象:传手机号码
                 ITEM_PHONES.USER_ID, --被叫对象标识:传用户标识
                 'T1', --短信类型:13-wlan赠送通知
                 '08', --短信种类:02-短信通知
                 '0', --短信内容类型:0-指定内容发送
                 V_SMS_CONTENT, --短信内容
                 0,
                 1, --指定发送次数
                 '6000', --短信优先级
                 SYSDATE, --提交时间
                 'SUPERUSR', --提交员工
                 '36601', --提交部门
                 SYSDATE, --处理时间
                 '15', --处理状态:15-未处理
                 1,
                 1,
                 V_FORCE_START_TIME
            FROM DUAL A;
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          V_DEAL_TAG  := -1;
          V_DEAL_DESC := 'USER_ID:' || USER_ID || 'TI_O_SMS插入异常:' ||
                         SQLERRM;
          DBMS_OUTPUT.PUT_LINE('异常信息:' || V_DEAL_DESC);
      END;
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    --失败
    V_DEAL_TAG  := -1;
    V_DEAL_DESC := '失败 [' || SQLERRM || ']';
    DBMS_OUTPUT.PUT_LINE('异常信息:' || V_DEAL_DESC);
END P_ONLINE_SIM_CHECK_TARGT;

参考

怎么跳过错误继续执行plsql语句呢