存储过程 从临时表中把数据分倒到多个表，并建立对应关系、历史记录

--====================================================================
  -- NAME: PRO_SD_WEEKLY_IMPORT_TRANS
  -- DESC: ---执行WEEKLY导入正式库事务操作----
  -- IN  : IN_BATCH_ID
  --     :
  --     :
  -- OUT :
  -- HISTORY: 2008-06-11     CREATED
  -- NOTE:
  --
  --==================================================================
  PROCEDURE PRO_SD_WEEKLY_IMPORT_TRANS(P_BATCH_ID NUMBER) AS
    X_DEALERCODE         VARCHAR2(10);
    X_STORE_CATEGORY     VARCHAR2(20);
    X_DITDISTINCT        VARCHAR2(20);
    X_PROVINCE           VARCHAR2(60);
    X_CITY               VARCHAR2(60);
    X_DITNAME            VARCHAR2(100);
    X_ADDRESS            VARCHAR2(200);
    X_PHONE              VARCHAR2(200);
    X_CONTACT            VARCHAR2(60);
    X_MANAGER            VARCHAR2(60);
    X_IS_SNX             VARCHAR2(2);
    X_ZIPCODE            VARCHAR2(10);
    X_IS_WAR             VARCHAR2(2);
    X_RIGHTRECORDS       NUMBER(10) := 0;
    X_DEALERS_DUP        NUMBER(10) := 0;
    X_SALES_DUP          NUMBER(10) := 0;
    X_DEALER_TEMP_ID     NUMBER(10);
    X_SALE_TEMP_ID       NUMBER(10) := -1;
    X_SAD_TEMP_ID        NUMBER(10) := -1;
    X_NEW_DEALER_TEMP_ID NUMBER(10);
    X_NEW_SALE_TEMP_ID   NUMBER(10);
    X_TEMP_NO            VARCHAR2(10);
    
    -- 取得本批次所有临时数据id
    CURSOR CUR_DEALER_TEMP(
                 P_BATCH_ID NUMBER)
    IS
         SELECT DIT_ID 
         FROM DEALER_IMPORT_TEMP
         WHERE DIT_SDB_ID= P_BATCH_ID 
         AND  DIT_DEALERCODE IS NOT NULL; 
    
    -- 取出无效经销商销售代表对应关系的历史ID
    CURSOR CUR_DEALERS_HIS 
    IS
         SELECT DHI_ID 
         FROM DEALERS_HISTORY 
         WHERE DHI_UPDATE_DATE IS NULL
         AND DHI_DLR_IS_W= 'W' AND DHI_STATUS='Y' 
         AND DHI_SAD_ID IN (SELECT SAD_ID 
                            FROM SALES_DEALERS 
                            WHERE SAD_DMO_ID=-1 
                            AND SAD_STATUS = 'N');
  BEGIN
    --循环以前，把所有的DEALER 状态设置为N
    UPDATE DEALERS SET DLR_STATUS = 'N' WHERE DLR_ID IS NOT NULL;
    COMMIT;
    UPDATE SALES_DEALERS
       SET SAD_STATUS = 'N'
     WHERE SAD_DMO_ID = -1
       AND SAD_ID IS NOT NULL;
    COMMIT;
      
    FOR REC_DATA IN CUR_DEALER_TEMP(
        P_BATCH_ID)
    LOOP
      X_TEMP_NO := REC_DATA.DIT_ID;
      --搜出临时库中一条数据，
      --导入到正式表中，顺序为：SALES  和  DEALERS  -》 SALESDEALER
      SELECT DIT_DEALERCODE,
             DIT_STORE_CATEGORY,
             DIT_DISTINCT,
             DIT_PROVINCE,
             DIT_CITY,
             DIT_NAME,
             DIT_ADDRESS,
             DIT_PHONE,
             DIT_CONTACT,
             DIT_MANAGER,
             DIT_IS_SNX,
             DIT_ZIPCODE,
             DIT_IS_WAR
        INTO X_DEALERCODE,
             X_STORE_CATEGORY,
             X_DITDISTINCT,
             X_PROVINCE,
             X_CITY,
             X_DITNAME,
             X_ADDRESS,
             X_PHONE,
             X_CONTACT,
             X_MANAGER,
             X_IS_SNX,
             X_ZIPCODE,
             X_IS_WAR
        FROM DEALER_IMPORT_TEMP
       WHERE DIT_ID = X_TEMP_NO;
      -- 如果DEALER_CODE 或 SALES_NAME 为空 ，结束操作，不插入任何一条记录
      IF X_DEALERCODE IS NULL OR X_MANAGER IS NULL THEN
        ROLLBACK;
      END IF;
    
      --正式库记录加一
      X_RIGHTRECORDS := X_RIGHTRECORDS + 1;
    
      BEGIN
        --首先判断DEALER的CODE是否存在,如果原记录中有相应的DEALER 更新DEALERS 表,
        SELECT DLR_ID
          INTO X_DEALER_TEMP_ID
          FROM DEALERS
         WHERE DLR_CODE = X_DEALERCODE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          X_DEALER_TEMP_ID := -1;
      END;
      
      IF X_DEALER_TEMP_ID > 0 THEN
        UPDATE DEALERS
           SET DLR_PROVINCE                = X_PROVINCE,
               DLR_CITY                    = X_CITY,
               DLR_NAME                    = X_DITNAME,
               DLR_ADDRESS                 = X_ADDRESS,
               DLR_TEL                     = X_PHONE,
               DLR_CONTACT                 = X_CONTACT,
               DLR_ONWAY_FLAG              = X_IS_SNX,
               DLR_CATEGORY                = X_STORE_CATEGORY,
               DLR_TYPE                    = X_STORE_CATEGORY,
               DLR_POSTCODE                = X_ZIPCODE,
               DLR_WAR_FLAG                = X_IS_WAR,
               DLR_UPDATE_DATE             = SYSDATE,
               DLR_STATUS                  = 'Y',
               DLR_LBR_BATCH_NUMBER_UPDATE = P_BATCH_ID
         WHERE DLR_ID = X_DEALER_TEMP_ID;
        COMMIT;
        X_DEALERS_DUP        := X_DEALERS_DUP + 1;
        X_NEW_DEALER_TEMP_ID := X_DEALER_TEMP_ID;
      ELSE
        INSERT INTO DEALERS
          (DLR_ID,
           DLR_CODE,
           DLR_TYPE,
           DLR_CREATE_DATE,
           DLR_PROVINCE,
           DLR_CITY,
           DLR_NAME,
           DLR_ADDRESS,
           DLR_TEL,
           DLR_CONTACT,
           DLR_ONWAY_FLAG,
           DLR_CATEGORY,
           DLR_POSTCODE,
           DLR_WAR_FLAG,
           DLR_STATUS,
           DLR_LBR_BATCH_NUMBER_CREATE)
        VALUES
          (DLR_ID_S.NEXTVAL,
           X_DEALERCODE,
           X_STORE_CATEGORY,
           SYSDATE,
           X_PROVINCE,
           X_CITY,
           X_DITNAME,
           X_ADDRESS,
           X_PHONE,
           X_CONTACT,
           X_IS_SNX,
           X_STORE_CATEGORY,
           X_ZIPCODE,
           X_IS_WAR,
           'Y',
           P_BATCH_ID);
        COMMIT;
        SELECT DLR_ID_S.CURRVAL INTO X_NEW_DEALER_TEMP_ID FROM DUAL;
      END IF;
      
      --如果SAL_NAME不对应则更新 SALES表，否则插入一条记录
      BEGIN
        SELECT SAL_ID
          INTO X_SALE_TEMP_ID
          FROM SALES
         WHERE SAL_NAME = X_MANAGER
           AND SAL_CATEGORY = 'W';
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          X_SALE_TEMP_ID := -1;
      END;
      IF X_SALE_TEMP_ID > 0 THEN
        UPDATE SALES
           SET SAL_OFFICE = X_DITDISTINCT, SAL_UPDATE_DATE = SYSDATE
         WHERE SAL_ID = X_SALE_TEMP_ID;
        COMMIT;
        X_SALES_DUP        := X_SALES_DUP + 1;
        X_NEW_SALE_TEMP_ID := X_SALE_TEMP_ID;
      ELSE
        INSERT INTO SALES
          (SAL_ID, SAL_NAME, SAL_OFFICE, SAL_CATEGORY, SAL_CREATE_DATE)
        VALUES
          (SAL_ID_S.NEXTVAL, X_MANAGER, X_DITDISTINCT, 'W', SYSDATE);
        COMMIT;
        SELECT SAL_ID_S.CURRVAL INTO X_NEW_SALE_TEMP_ID FROM DUAL;
      END IF;
      
      --实现SALES和DEALERS表的关系映射
      BEGIN
        SELECT SAD_ID
          INTO X_SAD_TEMP_ID
          FROM SALES_DEALERS
         WHERE SAD_DLR_ID = X_NEW_DEALER_TEMP_ID
           AND SAD_SAL_ID = X_NEW_SALE_TEMP_ID
           AND SAD_DMO_ID = -1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          X_SAD_TEMP_ID := -1;
      END;
      IF X_SAD_TEMP_ID > 0 THEN
        UPDATE SALES_DEALERS
           SET SAD_STATUS = 'Y'
         WHERE SAD_ID = X_SAD_TEMP_ID;
        COMMIT;
        SELECT DHI_STATUS
          INTO X_TEMP_NO
          FROM DEALERS_HISTORY
         WHERE DHI_SAD_ID = X_SAD_TEMP_ID
           AND DHI_DLR_IS_W = 'W'
           AND DHI_UPDATE_DATE IS NULL;
           --上一次对应关系，历史无效，新增历史对应关系，标记原有历史结束时间
        IF X_TEMP_NO = 'N' THEN
          INSERT INTO DEALERS_HISTORY
            (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID)
          VALUES
            (DHI_ID_S.NEXTVAL, SYSDATE, 'Y', 'W', X_SAD_TEMP_ID);
          UPDATE DEALERS_HISTORY
             SET DHI_UPDATE_DATE = SYSDATE
           WHERE DHI_SAD_ID = X_SAD_TEMP_ID
             AND DHI_STATUS = 'N'
             AND DHI_DLR_IS_W = 'W'
             AND DHI_UPDATE_DATE IS NULL;
          COMMIT;
        END IF;
      ELSE
        INSERT INTO SALES_DEALERS
          (SAD_ID, SAD_DLR_ID, SAD_SAL_ID, SAD_DMO_ID, SAD_STATUS)
        VALUES
          (SAD_ID_S.NEXTVAL, X_NEW_DEALER_TEMP_ID, X_NEW_SALE_TEMP_ID, -1, 'Y');
        INSERT INTO DEALERS_HISTORY
          (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID)
        VALUES
          (DHI_ID_S.NEXTVAL, SYSDATE, 'Y', 'W', SAD_ID_S.CURRVAL);
        COMMIT;
      END IF;
    END LOOP;
    
    FOR REC_DEALER IN CUR_DEALERS_HIS
    LOOP
      X_TEMP_NO := REC_DEALER.DHI_ID;
      SELECT DHI_SAD_ID
        INTO X_SAD_TEMP_ID
        FROM DEALERS_HISTORY
       WHERE DHI_ID = X_TEMP_NO;
      INSERT INTO DEALERS_HISTORY
        (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID)
      VALUES
        (DHI_ID_S.NEXTVAL, SYSDATE, 'N', 'W', X_SAD_TEMP_ID);
      COMMIT;
      UPDATE DEALERS_HISTORY
         SET DHI_UPDATE_DATE = SYSDATE
       WHERE DHI_ID = X_TEMP_NO;
      COMMIT;
    END LOOP;

    --导入到正式表结束，更新经销商销售代表导入批次表记录
        --有：导入状态，正式库记录
        --导入正式库结束时间
    UPDATE SALES_DEALERS_BATCH
       SET SDB_LOAD_STATUS       = 3,
           SDB_ERROR_COUNT       = SDB_TEMP_COUNT - X_RIGHTRECORDS,
           SDB_FORMER_COUNT      = X_RIGHTRECORDS,
           SDB_DEALERS_DUPLICATE = X_DEALERS_DUP,
           SDB_SALES_DUPLICATE   = X_SALES_DUP,
           SDB_DB_END            = SYSDATE
     WHERE SDB_ID = P_BATCH_ID;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END PRO_SD_WEEKLY_IMPORT_TRANS;