前言

利用存储过程插入、更新或者删除数据,不仅比SQL更灵活,逻辑更清晰,代码复用率高,而且还能将操作数据的记录写到日志里,便于查找问题和分析数据。

日志表

作用

记录循环游标的数量

建表脚本

-- Create table
create table OTH_RUN_LOG
(
  LOG_ID      NUMBER(32),
  LAN_CODE    NUMBER(16),
  REGION_CODE NUMBER(16),
  TABLE_CODE  VARCHAR2(32),
  PROC_NAME   VARCHAR2(64),
  PROC_STATE  VARCHAR2(6),
  START_TIME  DATE,
  END_TIME    DATE,
  STATE       VARCHAR2(6),
  SUCCESS_NUM NUMBER(12),
  FAIL_NUM    NUMBER(12)
)

序列

作用

日志的LOG_ID

建序列脚本

-- Create sequence 
create sequence RUN_LOG_SWQ
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 100;

存过脚本

DECLARE
  FLAG      NUMBER(32);
  V_LOG_ID  NUMBER(32);
  V_CUR_NUM NUMBER(32);
  CURSOR SALE IS
    SELECT *
      FROM JKTEB A
     WHERE EXISTS (SELECT 1
              FROM BS_NEED_INFOR_CP C
             WHERE A.NEED_NO = C.NEED_NO
               AND A.PRO_TEAM_NO = C.PRO_TEAM_NO);
BEGIN
  FLAG      := 0;
  V_CUR_NUM := 0;
  SELECT RUN_LOG_SWQ.NEXTVAL INTO V_LOG_ID FROM DUAL;
  INSERT INTO OTH_RUN_LOG
    (LOG_ID, TABLE_CODE, SUCCESS_NUM)
  VALUES
    (V_LOG_ID, 'BS_NEED_INFOR_CP', V_CUR_NUM);
  COMMIT;
  FOR V_ROW IN SALE LOOP
    BEGIN
      UPDATE BS_NEED_INFOR_CP A
         SET A.CREATE_DATE  = V_ROW.CREATE_DATE,
             A.DATE_OF_LINE = V_ROW.DATE_OF_LINE
       WHERE A.NEED_NO = V_ROW.NEED_NO
         AND A.PRO_TEAM_NO = V_ROW.PRO_TEAM_NO;
      FLAG := FLAG + 1;
      IF FLAG > 5000 THEN
        FLAG := 0;
        COMMIT;
      END IF;
      V_CUR_NUM := V_CUR_NUM + 1;
    END;
  END LOOP;
  UPDATE OTH_RUN_LOG A
     SET A.SUCCESS_NUM = V_CUR_NUM
   WHERE A.LOG_ID = V_LOG_ID;
  COMMIT;
END;

原文链接:https://miansen.wang/2018/09/08/1/