代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
从临时表中把数据分倒到多个表,并建立对应关系、历史
作者:
yzq124391
/ 发布于
2011/7/22
/
812
<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= '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;</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>
试试其它关键字
临时表中把数据分倒到多个表
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
yzq124391
贡献的其它代码
(
2
)
.
实现防迅雷等下载工具盗链
.
从临时表中把数据分倒到多个表,并建立对应关系、历史
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3