存储过程-数据位置调换demo

今天药忘吃喽~ 2023-10-06 09:01 20阅读 0赞

\-- 创建日志表

create table T_LOG_DATA_POSITION
    (
      LOG_ID         NUMBER(12) not null,
      ERROR_DESC     VARCHAR2(4000),
      ERROR_POSITION VARCHAR2(4000),
      OCCUR_TIME     DATE default SYSDATE not null,
      TYPE           VARCHAR2(10)
    )

\--创建 序列

REATE SEQUENCE S_DATA_POSITION_ID
    START WITH 1
    MINVALUE 1
    MAXVALUE 999999999
    INCREMENT BY 1
    CACHE 20;

\--设置主键

alter table T_LOG_DATA_POSITION add constraint PK_DATA_POSITION_ID primary key (LOG_ID)
    using index tablespace OTHER_INDX;

\--账户授权 :

grant select, insert, update on SCORE_OPRS.T_LOG_DATA_POSITION to TP_SCORE_OPR_CC;
    grant select, insert, update on SCORE_OPRS.T_LOG_DATA_POSITION to SCORE_OPR_RAC_CC;
    grant select, insert, update on SCORE_OPRS.T_LOG_DATA_POSITION to TP_SCORE_OPR_RAC_CC;

\--存储过程脚本：

CREATE OR REPLACE PACKAGE PKG_DATA_POSITION IS
    
      -- 执行程序逻辑
      PROCEDURE P_PR_DATA_POSITION;
    
      -- 执行日志记录
      PROCEDURE T_INSERT_LOG(P_ERROR_DESC T_LOG_DATA_POSITION.ERROR_DESC%TYPE,P_ERROR_POSITION T_LOG_DATA_POSITION.ERROR_POSITION%TYPE,P_TYPE T_LOG_DATA_POSITION.TYPE%TYPE);
    
    END PKG_DATA_POSITION;
    /
    CREATE OR REPLACE PACKAGE BODY PKG_DATA_POSITION IS
    
      /**
          修正数据存储位置
          createdBy：2020/03/20
      **/
      PROCEDURE P_PR_DATA_POSITION  IS
        --初始化数据赋值给游标
        CURSOR CUR_PRICING_DATA(C_BEGIN_DATE DATE) IS
          SELECT t.SCORERUNID,t.BAK6,t.BAK7,t.DUTY_RISK_PREMIUM FROM T_TABLENAME1 t
           WHERE
           DECODE(translate(t.DUTY_RISK_PREMIUM, '\1234567890 ', '\'),'','0') ='0'
             AND t.DUTY_RISK_PREMIUM IS NOT NULL
             AND t.CREATED_DATE >= C_BEGIN_DATE
             AND t.CREATED_DATE < C_BEGIN_DATE + 1;
    
        V_BEGIN_DATE        DATE;
        V_END_DATE          DATE;
        V_COMMIT_CNT        NUMBER(5);
    
      BEGIN
        --开始执行程序
        V_BEGIN_DATE := trunc(to_date('2019-09-10 00:00:00','yyyy-MM-dd HH24:mi:ss'));
        V_END_DATE   := trunc(to_date('2020-03-19 00:00:00','yyyy-MM-dd HH24:mi:ss'));
        V_COMMIT_CNT := 0;
    
        PKG_DATA_POSITION.T_INSERT_LOG('开始修正数据存储位置','PKG_DATA_POSITION.P_PR_DATA_POSITION','0');
    
        LOOP
          EXIT WHEN V_BEGIN_DATE = V_END_DATE;
    
          --按天处理
          FOR P_DATALIST IN CUR_PRICING_DATA(V_BEGIN_DATE) LOOP
    
            BEGIN
                --数据前进一位
          UPDATE T_TABLENAME1  t
            SET  t.BAK6=P_DATALIST.BAK7
              ,t.BAK7=P_DATALIST.DUTY_RISK_PREMIUM
              ,t.DUTY_RISK_PREMIUM=P_DATALIST.BAK6
          WHERE t.SCORERUNID=P_DATALIST.SCORERUNID;
            END;
    
        PKG_DATA_POSITION.T_INSERT_LOG('Update_ScoreRunId - ' || P_DATALIST.SCORERUNID,'PKG_DATA_POSITION.P_PR_DATA_POSITION','1');
    
            V_COMMIT_CNT := V_COMMIT_CNT + 1;
    
            IF V_COMMIT_CNT = 1000 THEN
              COMMIT;
              V_COMMIT_CNT := 0;
            END IF;
    
          END LOOP;
    
          V_BEGIN_DATE := V_BEGIN_DATE + 1;
          COMMIT;
    
        END LOOP;
    
        PKG_DATA_POSITION.T_INSERT_LOG('修正数据存储位置完成','PKG_DATA_POSITION.P_PR_DATA_POSITION','0');
    
      END P_PR_DATA_POSITION;
    
    
      -- 记录日志
      PROCEDURE T_INSERT_LOG(P_ERROR_DESC T_LOG_DATA_POSITION.ERROR_DESC%TYPE, P_ERROR_POSITION T_LOG_DATA_POSITION.ERROR_POSITION%TYPE, P_TYPE T_LOG_DATA_POSITION.TYPE%TYPE) IS
    
      BEGIN
        INSERT INTO T_LOG_DATA_POSITION (LOG_ID, ERROR_DESC, ERROR_POSITION ,TYPE)
        VALUES (S_DATA_POSITION_ID.NEXTVAL,P_ERROR_DESC,P_ERROR_POSITION,P_TYPE);
        COMMIT;
      END;
    
    END PKG_DATA_POSITION;
    /