Oracle 带游标的存储过程

CREATE OR REPLACE PROCEDURE STP_TM_TIMEPROM_DTL(P_VERSION_DT IN DATE) IS

  VERSION_DT         DATE;

  V_PRODUCT_TYPE     VARCHAR2(20);

  SRC_PROVINCE_NAME  VARCHAR2(60);

  SRC_CITY_NAME      VARCHAR2(60);

  SRC_AREA_TOWN      VARCHAR2(60);

  V_SRC_ZONE         VARCHAR2(20);

  BATCH_CODE         VARCHAR2(20);

  BEGIN_TM           VARCHAR2(30);

  LAST_INDEPOT_TM    VARCHAR2(30);

  END_TIME           VARCHAR2(30);

  V_CITY_CODE        VARCHAR2(20);

  AUDIT_FLAG         VARCHAR2(20);

  EFFIC_NAME         VARCHAR2(20);

  DEST_PROVINCE_NAME VARCHAR2(60);

  DEST_CITY_NAME     VARCHAR2(60);

  DEST_AREA_TOWN     VARCHAR2(60);

  SYS_TIMESTANARD    VARCHAR2(20);

  CUR_TIMESTANARD    VARCHAR2(20);

  V_RESULT_SUM       NUMBER := 0;

 

  --用游标遍历

  CURSOR CUR_TM_TIMEPROM_DTL IS

    SELECT T.VERSION_DT,

           T.PRODUCT_TYPE,

           T.SRC_ZONE,

           T.BATCH_CODE,

           T.BEGIN_TM,

           T.LAST_INDEPOT_TM,

           T.END_TIME,

           T.DEST_CITY,

           (CASE T.AUDIT_FLAG

             WHEN '1' THEN

              '产品时效冲突'

             WHEN '2' THEN

              '班次时效冲突'

             ELSE

              '正常'

           END),

           (CASE T.PROMISE_DAY

             WHEN '1' THEN

              '即日'

             WHEN '2' THEN

              '次日'

             WHEN '3' THEN

              '隔日'

             WHEN '4' THEN

              '四日'

             WHEN '5' THEN

              '四日'

             WHEN '6' THEN

              '六日'

             WHEN '7' THEN

              '七日'

             WHEN '8' THEN

              '八日'

             WHEN '9' THEN

              '九日'

             WHEN '10' THEN

              '十日'

             WHEN '11' THEN

              '十一日'

             WHEN '12' THEN

              '十二日'

             WHEN '13' THEN

              '十三日'

             WHEN '14' THEN

              '十四日'

             WHEN '15' THEN

              '十五日'

             else

              ''

           end) || T.PROMISE_TM,

           (CASE T.AUDIT_PROMISE_DAY

             WHEN '1' THEN

              '即日'

             WHEN '2' THEN

              '次日'

             WHEN '3' THEN

              '隔日'

             WHEN '4' THEN

              '四日'

             WHEN '5' THEN

              '四日'

             WHEN '6' THEN

              '六日'

             WHEN '7' THEN

              '七日'

             WHEN '8' THEN

              '八日'

             WHEN '9' THEN

              '九日'

             WHEN '10' THEN

              '十日'

             WHEN '11' THEN

              '十一日'

             WHEN '12' THEN

              '十二日'

             WHEN '13' THEN

              '十三日'

             WHEN '14' THEN

              '十四日'

             WHEN '15' THEN

              '十五日'

             else

              ''

           end) || T.AUDIT_PROMISE_TM

      FROM OMPDW.TM_TIMEPROM_DTL T

     WHERE T.VERSION_DT = P_VERSION_DT

       AND T.SRC_ZONE IN (SELECT a.zone_code FROM omproute.tmp_main_zone a)

       AND T.DEST_CITY IN

           (SELECT DISTINCT (T.CITY_CODE) FROM TM_DISTRICT_ZONE T);

 

BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_TIMERESULT';

  OPEN CUR_TM_TIMEPROM_DTL;

  LOOP

    FETCH CUR_TM_TIMEPROM_DTL

      INTO VERSION_DT,

           V_PRODUCT_TYPE,

           V_SRC_ZONE,

           BATCH_CODE,

           BEGIN_TM,

           LAST_INDEPOT_TM,

           END_TIME,

           V_CITY_CODE,

           AUDIT_FLAG,

           SYS_TIMESTANARD,

           CUR_TIMESTANARD;

  

    SELECT DISTINCT d.PROVINCE_NAME, d.CITY_NAME, d.AREA_TOWN

      INTO SRC_PROVINCE_NAME, SRC_CITY_NAME, SRC_AREA_TOWN

      FROM OMPROUTE.TM_DISTRICT_ZONE d

     WHERE d.ZONE_CODE = V_SRC_ZONE

       and ROWNUM = 1;

  

    SELECT COUNT(1)

      INTO V_RESULT_SUM

      FROM OMPROUTE.TM_DISTRICT_ZONE T

     WHERE T.EFFIC_NAME = '主城区'

       AND T.PRODUCT_CODE = V_PRODUCT_TYPE

       AND T.CITY_CODE = V_CITY_CODE

       AND ROWNUM = 1;

  

    IF (V_RESULT_SUM = 0) THEN

      DEST_PROVINCE_NAME := '';

      DEST_CITY_NAME     := '';

      DEST_AREA_TOWN     := '';

      EFFIC_NAME         := '';

    END IF;

  

    IF (V_RESULT_SUM > 0) THEN

      SELECT DISTINCT T.PROVINCE_NAME,

                      T.CITY_NAME,

                      T.AREA_TOWN,

                      T.EFFIC_NAME

        INTO DEST_PROVINCE_NAME, DEST_CITY_NAME, DEST_AREA_TOWN, EFFIC_NAME

        FROM OMPROUTE.TM_DISTRICT_ZONE T

       WHERE T.EFFIC_NAME = '主城区'

         AND T.PRODUCT_CODE = V_PRODUCT_TYPE

         AND T.CITY_CODE = V_CITY_CODE

         AND ROWNUM = 1;

    END IF;

  

    --注意这里一定要写，不能放在commit后再写，否则会多一条数据

    EXIT WHEN CUR_TM_TIMEPROM_DTL%NOTFOUND; --当游标为空跳出

  

    INSERT INTO TEMP_TIMERESULT

      (VERSION_DT,

       PRODUCT_TYPE,

       PROVINCE_NAME,

       CITY_NAME,

       AREA_TOWN,

       SRC_ZONE,

       BATCH_CODE,

       BEGIN_TM,

       LAST_INDEPOT_TM,

       END_TIME,

       DEST_PROVINCE_NAME,

       DEST_CITY_NAME,

       CITY_CODE,

       DEST_AREA_TOWN,

       EFFIC_NAME,

       AUDIT_FLAG,

       SYS_TIMESTANARD,

       BASE_TIMESTANARD,

       CUR_TIMESTANARD)

    values

      (VERSION_DT,

       (CASE trim(V_PRODUCT_TYPE) WHEN 'T1' THEN '顺丰即日(干线)' WHEN 'T4' THEN

        '顺丰次日' WHEN 'T6' THEN '顺丰隔日' WHEN 'T8' THEN '顺丰次晨' WHEN 'SP4' THEN

        '顺丰次日(陆运件)' ELSE '' END),

       SRC_PROVINCE_NAME,

       SRC_CITY_NAME,

       SRC_AREA_TOWN,

       V_SRC_ZONE,

       BATCH_CODE,

       BEGIN_TM,

       LAST_INDEPOT_TM,

       END_TIME,

       DEST_PROVINCE_NAME,

       DEST_CITY_NAME,

       V_CITY_CODE,

       DEST_AREA_TOWN,

       EFFIC_NAME,

       AUDIT_FLAG,

       SYS_TIMESTANARD,

       CUR_TIMESTANARD,

       CUR_TIMESTANARD);

  

    COMMIT;

  

  END LOOP;

  CLOSE CUR_TM_TIMEPROM_DTL;

 

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line(V_SRC_ZONE);

END STP_TM_TIMEPROM_DTL;