从临时表中把数据分倒到多个表，并建立对应关系、历史代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】从临时表中把数据分倒到多个表，并建立对应关系、历史
作者:yzq124391 / 发布于2011/7/22/ 848
<div>--====================================================================
 -- 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= &apos;W&apos; AND DHI_STATUS=&apos;Y&apos; 
 AND DHI_SAD_ID IN (SELECT SAD_ID 
 FROM SALES_DEALERS 
 WHERE SAD_DMO_ID=-1 
 AND SAD_STATUS = &apos;N&apos;);
 BEGIN
 --循环以前，把所有的DEALER 状态设置为N
 UPDATE DEALERS SET DLR_STATUS = &apos;N&apos; WHERE DLR_ID IS NOT NULL;
 COMMIT;
 UPDATE SALES_DEALERS
 SET SAD_STATUS = &apos;N&apos;
 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 = &apos;Y&apos;,
 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,
 &apos;Y&apos;,
 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 = &apos;W&apos;;
 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, &apos;W&apos;, 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 = &apos;Y&apos;
 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 = &apos;W&apos;
 AND DHI_UPDATE_DATE IS NULL;
 --上一次对应关系，历史无效，新增历史对应关系，标记原有历史结束时间
 IF X_TEMP_NO = &apos;N&apos; 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, &apos;Y&apos;, &apos;W&apos;, X_SAD_TEMP_ID);
 UPDATE DEALERS_HISTORY
 SET DHI_UPDATE_DATE = SYSDATE
 WHERE DHI_SAD_ID = X_SAD_TEMP_ID
 AND DHI_STATUS = &apos;N&apos;
 AND DHI_DLR_IS_W = &apos;W&apos;
 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, &apos;Y&apos;);
 INSERT INTO DEALERS_HISTORY
 (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID)
 VALUES
 (DHI_ID_S.NEXTVAL, SYSDATE, &apos;Y&apos;, &apos;W&apos;, 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, &apos;N&apos;, &apos;W&apos;, X_SAD_TEMP_ID);
 COMMIT;
 UPDATE DEALERS_HISTORY
 SET DHI_UPDATE_DATE = SYSDATE
 WHERE DHI_ID = X_TEMP_NO;
 COMMIT;
 END LOOP;</div>
<div> --导入到正式表结束，更新经销商销售代表导入批次表记录
 --有：导入状态，正式库记录
 --导入正式库结束时间
 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;</div>
试试其它关键字
　临时表中把数据分倒到多个表　
同语言下
可能有用的
yzq124391贡献的其它代码(2)