CREATE OR REPLACE PROCEDURE DY_CREATEYD14(V_TIME DATE,V_ZMLM IN VARCHAR2,V_ID NUMBER,
V_INSTR IN VARCHAR2,V_OUTSTR IN VARCHAR2)
AS
ERRMSG VARCHAR2(1000);
V_CID VARCHAR2(24);
V_LJDM VARCHAR2(4);
V_CB VARCHAR2(10);
CURSOR C_CML IS SELECT CDM FROM B_CML WHERE ZMLM=V_ZMLM AND BLZD1 IS NOT NULL ORDER BY BLZD1;
TIME1 DATE;
TIME2 DATE;
SECS VARCHAR(6);
errcode varchar(256);
BEGIN -- SELECT SYSDATE INTO TIME1 FROM DUAL;
SELECT SUBSTR(V_ZMLM,3,1) INTO V_LJDM FROM DUAL;
------------------清理临时数据------------------------
DELETE FROM DY_YD14_TEMP WHERE ZMLM=V_ZMLM;
DELETE FROM DY_YD14 WHERE ZMLM=V_ZMLM;
COMMIT;
LCJ_STATIONYARD_BACKDATE_BYDAT(V_TIME ,V_ZMLM ,V_ID ,V_INSTR ,V_OUTSTR); UPDATE B_ZCXXCLK_TEMP A
SET CB=(SELECT CB FROM B_GDML WHERE GDM=A.GDM AND ZMLM=V_ZMLM AND ROWNUM=1),FXH2=(SELECT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM AND DZLM=A.DZH AND ROWNUM=1)
WHERE ZCXXID=V_ID ; --统一修改CZJM字段,在CZD_CZJM字典中找不到的车种,直接取CZJM的第一位
UPDATE B_ZCXXCLK_TEMP A
set CZJM=(SELECT F_CZJM FROM CZD_CZJM WHERE F_DM=A.CZ AND ROWNUM=1)
WHERE Zcxxid=V_ID AND CZ IN(SELECT F_DM FROM CZD_CZJM) AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC'); UPDATE B_ZCXXCLK_TEMP A
set CZJM=SUBSTR(CZJM,1,1)
WHERE Zcxxid=V_ID AND CZ NOT IN(SELECT F_DM FROM CZD_CZJM) AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC'); UPDATE B_ZCXXCLK_TEMP
SET FXH2='99'
WHERE ZCXXID=V_ID AND (FXH2='999'); UPDATE B_ZCXXCLK_TEMP
SET FXH2='-1'
WHERE ZCXXID=V_ID AND (FXH2 IS NULL OR FXH2='');-----读取站调大表车辆方向----------------------
INSERT INTO DY_YD14_TEMP(ZMLM,ZH,ZHM,XH,HJ,CB)
select V_ZMLM,dir_scope,row_text ,B.ROW_INDEX,0,'HJ' from CZD_sub_header a,CZD_sub_headER_row b where a.header_INDEX = B.header_INDEX AND A.CODE=5 AND A.TYPE =3
AND A.TAB_NUM=1000
ORDER BY B.ROW_INDEX;
-----读取结束----------------------------------
--带方向车辆按组号分类
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,FXH2,FXH2,1,1,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2<>'-1' AND FYBZ='0'
GROUP BY FXH2;
OPEN C_CML;
LOOP
FETCH C_CML INTO V_CB;
EXIT WHEN C_CML%NOTFOUND;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,FXH2,FXH2,1,1,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2<>'-1' AND FYBZ='0'
AND CB=V_CB
GROUP BY FXH2;
END LOOP;
CLOSE C_CML;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT ZMLM,'不明',ZHM,KZBZ,XH,HJ,CB FROM DY_YD14 WHERE ZMLM=V_ZMLM AND ZH NOT IN(SELECT DISTINCT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM) AND ZH<>'99' AND KZBZ='1'; DELETE FROM DY_YD14 WHERE ZMLM=V_ZMLM AND ZH NOT IN (SELECT DISTINCT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM) AND ZH NOT IN('不明');
--不带方向车辆按车种分类 INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,CZJM,CZJM,0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0' AND CZJM IN('P','C','N','G','B','W')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'K,X,JC,KC','K,X,JC,KC',0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0' AND CZJM IN('K','X','JC','KC')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'QT,99','QT,99',0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND ((FXH2='-1' AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC')) or FXH2='99' ) AND FYBZ='0'; OPEN C_CML;
LOOP
FETCH C_CML INTO V_CB;
EXIT WHEN C_CML%NOTFOUND;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,CZJM,CZJM,0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0'
AND CB=V_CB AND CZJM IN('P','C','N','G','B','W')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'K,X,JC,KC','K,X,JC,KC',0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0'
AND CB=V_CB AND CZJM IN('K','X','JC','KC')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'QT,99','QT,99',0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND ((FXH2='-1' AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC')) or FXH2='99') AND FYBZ='0'
AND CB=V_CB ;
END LOOP;
CLOSE C_CML;
V_CB := 'HJ';
UPDATE DY_YD14_TEMP A
SET A.HJ=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'SD';
UPDATE DY_YD14_TEMP A
SET A.F0=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1);
V_CB := 'SB';
UPDATE DY_YD14_TEMP A
SET A.F1=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'SF';
UPDATE DY_YD14_TEMP A
SET A.F2=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XD';
UPDATE DY_YD14_TEMP A
SET A.F3=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XB';
UPDATE DY_YD14_TEMP A
SET A.F4=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XF';
UPDATE DY_YD14_TEMP A
SET A.F5=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'JH';
UPDATE DY_YD14_TEMP A
SET A.F6=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'FZ';
UPDATE DY_YD14_TEMP A
SET A.F7=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'ZY';
UPDATE DY_YD14_TEMP A
SET A.F8=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1);
DELETE FROM DY_YD14_TEMP
WHERE ZH='SUM'; INSERT INTO DY_YD14_TEMP(ZMLM,ZH,ZHM,XH,HJ,F0,F1,F2,F3,F4,F5,F6,F7,F8,CB)
SELECT V_ZMLM,'SUM','合计','9999',SUM(HJ),SUM(F0),SUM(F1),SUM(F2),SUM(F3),SUM(F4),
SUM(F5),SUM(F6),SUM(F7),SUM(F8),'HJ'
From DY_YD14_TEMP
WHERE ZMLM=V_ZMLM;
---------开始生成上传数据---------------------------------------------------开始生成上传数据------------------------------------------
DELETE FROM CZD_YD14; --先生成大方向号
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, FXH2,COUNT(*),0,'CZ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2<>-1 AND FYBZ='0'
GROUP BY GDM,FXH2;
--大方向空车计
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, 0,COUNT(*),CZJM,'CZ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2=-1 AND FYBZ='0'
GROUP BY GDM,CZJM;
UPDATE B_ZCXXCLK_TEMP A
SET FXH2=(SELECT DIRECTION_CODE FROM FXL_FXLB WHERE TELE_CODE=A.DZH AND ROWNUM=1)
WHERE ZCXXID=V_ID ;
UPDATE B_ZCXXCLK_TEMP A
SET FXH2=-1
WHERE ZCXXID=V_ID AND FXH2 IS NULL; --大方向空车计
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, 0,COUNT(*),CZJM,'LJ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2=-1 AND FYBZ='0'
GROUP BY GDM,CZJM; UPDATE CZD_YD14 A
SET DIR_NUM=(SELECT DIRECTION_CODE FROM CZD_CARKIND WHERE A.CAR_KIND=CZDM)
WHERE BALANCE_TIME=V_TIME AND REMARK='LJ'; --先生成大方向号
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, FXH2,COUNT(*),0,'LJ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
And FXH2<>-1 AND FYBZ='0'
GROUP BY GDM,FXH2;---------生成上传数据完成------------------------------------------
--DELETE FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRMSG:='生成YD14失败:';
ERRMSG:=SUBSTRB(SQLERRM,1,256);
DBMS_OUTPUT.PUT_LINE(ERRMSG);
ROLLBACK;
END;
/
V_INSTR IN VARCHAR2,V_OUTSTR IN VARCHAR2)
AS
ERRMSG VARCHAR2(1000);
V_CID VARCHAR2(24);
V_LJDM VARCHAR2(4);
V_CB VARCHAR2(10);
CURSOR C_CML IS SELECT CDM FROM B_CML WHERE ZMLM=V_ZMLM AND BLZD1 IS NOT NULL ORDER BY BLZD1;
TIME1 DATE;
TIME2 DATE;
SECS VARCHAR(6);
errcode varchar(256);
BEGIN -- SELECT SYSDATE INTO TIME1 FROM DUAL;
SELECT SUBSTR(V_ZMLM,3,1) INTO V_LJDM FROM DUAL;
------------------清理临时数据------------------------
DELETE FROM DY_YD14_TEMP WHERE ZMLM=V_ZMLM;
DELETE FROM DY_YD14 WHERE ZMLM=V_ZMLM;
COMMIT;
LCJ_STATIONYARD_BACKDATE_BYDAT(V_TIME ,V_ZMLM ,V_ID ,V_INSTR ,V_OUTSTR); UPDATE B_ZCXXCLK_TEMP A
SET CB=(SELECT CB FROM B_GDML WHERE GDM=A.GDM AND ZMLM=V_ZMLM AND ROWNUM=1),FXH2=(SELECT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM AND DZLM=A.DZH AND ROWNUM=1)
WHERE ZCXXID=V_ID ; --统一修改CZJM字段,在CZD_CZJM字典中找不到的车种,直接取CZJM的第一位
UPDATE B_ZCXXCLK_TEMP A
set CZJM=(SELECT F_CZJM FROM CZD_CZJM WHERE F_DM=A.CZ AND ROWNUM=1)
WHERE Zcxxid=V_ID AND CZ IN(SELECT F_DM FROM CZD_CZJM) AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC'); UPDATE B_ZCXXCLK_TEMP A
set CZJM=SUBSTR(CZJM,1,1)
WHERE Zcxxid=V_ID AND CZ NOT IN(SELECT F_DM FROM CZD_CZJM) AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC'); UPDATE B_ZCXXCLK_TEMP
SET FXH2='99'
WHERE ZCXXID=V_ID AND (FXH2='999'); UPDATE B_ZCXXCLK_TEMP
SET FXH2='-1'
WHERE ZCXXID=V_ID AND (FXH2 IS NULL OR FXH2='');-----读取站调大表车辆方向----------------------
INSERT INTO DY_YD14_TEMP(ZMLM,ZH,ZHM,XH,HJ,CB)
select V_ZMLM,dir_scope,row_text ,B.ROW_INDEX,0,'HJ' from CZD_sub_header a,CZD_sub_headER_row b where a.header_INDEX = B.header_INDEX AND A.CODE=5 AND A.TYPE =3
AND A.TAB_NUM=1000
ORDER BY B.ROW_INDEX;
-----读取结束----------------------------------
--带方向车辆按组号分类
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,FXH2,FXH2,1,1,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2<>'-1' AND FYBZ='0'
GROUP BY FXH2;
OPEN C_CML;
LOOP
FETCH C_CML INTO V_CB;
EXIT WHEN C_CML%NOTFOUND;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,FXH2,FXH2,1,1,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2<>'-1' AND FYBZ='0'
AND CB=V_CB
GROUP BY FXH2;
END LOOP;
CLOSE C_CML;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT ZMLM,'不明',ZHM,KZBZ,XH,HJ,CB FROM DY_YD14 WHERE ZMLM=V_ZMLM AND ZH NOT IN(SELECT DISTINCT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM) AND ZH<>'99' AND KZBZ='1'; DELETE FROM DY_YD14 WHERE ZMLM=V_ZMLM AND ZH NOT IN (SELECT DISTINCT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM) AND ZH NOT IN('不明');
--不带方向车辆按车种分类 INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,CZJM,CZJM,0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0' AND CZJM IN('P','C','N','G','B','W')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'K,X,JC,KC','K,X,JC,KC',0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0' AND CZJM IN('K','X','JC','KC')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'QT,99','QT,99',0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND ((FXH2='-1' AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC')) or FXH2='99' ) AND FYBZ='0'; OPEN C_CML;
LOOP
FETCH C_CML INTO V_CB;
EXIT WHEN C_CML%NOTFOUND;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,CZJM,CZJM,0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0'
AND CB=V_CB AND CZJM IN('P','C','N','G','B','W')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'K,X,JC,KC','K,X,JC,KC',0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0'
AND CB=V_CB AND CZJM IN('K','X','JC','KC')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'QT,99','QT,99',0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND ((FXH2='-1' AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC')) or FXH2='99') AND FYBZ='0'
AND CB=V_CB ;
END LOOP;
CLOSE C_CML;
V_CB := 'HJ';
UPDATE DY_YD14_TEMP A
SET A.HJ=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'SD';
UPDATE DY_YD14_TEMP A
SET A.F0=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1);
V_CB := 'SB';
UPDATE DY_YD14_TEMP A
SET A.F1=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'SF';
UPDATE DY_YD14_TEMP A
SET A.F2=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XD';
UPDATE DY_YD14_TEMP A
SET A.F3=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XB';
UPDATE DY_YD14_TEMP A
SET A.F4=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XF';
UPDATE DY_YD14_TEMP A
SET A.F5=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'JH';
UPDATE DY_YD14_TEMP A
SET A.F6=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'FZ';
UPDATE DY_YD14_TEMP A
SET A.F7=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'ZY';
UPDATE DY_YD14_TEMP A
SET A.F8=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1);
DELETE FROM DY_YD14_TEMP
WHERE ZH='SUM'; INSERT INTO DY_YD14_TEMP(ZMLM,ZH,ZHM,XH,HJ,F0,F1,F2,F3,F4,F5,F6,F7,F8,CB)
SELECT V_ZMLM,'SUM','合计','9999',SUM(HJ),SUM(F0),SUM(F1),SUM(F2),SUM(F3),SUM(F4),
SUM(F5),SUM(F6),SUM(F7),SUM(F8),'HJ'
From DY_YD14_TEMP
WHERE ZMLM=V_ZMLM;
---------开始生成上传数据---------------------------------------------------开始生成上传数据------------------------------------------
DELETE FROM CZD_YD14; --先生成大方向号
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, FXH2,COUNT(*),0,'CZ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2<>-1 AND FYBZ='0'
GROUP BY GDM,FXH2;
--大方向空车计
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, 0,COUNT(*),CZJM,'CZ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2=-1 AND FYBZ='0'
GROUP BY GDM,CZJM;
UPDATE B_ZCXXCLK_TEMP A
SET FXH2=(SELECT DIRECTION_CODE FROM FXL_FXLB WHERE TELE_CODE=A.DZH AND ROWNUM=1)
WHERE ZCXXID=V_ID ;
UPDATE B_ZCXXCLK_TEMP A
SET FXH2=-1
WHERE ZCXXID=V_ID AND FXH2 IS NULL; --大方向空车计
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, 0,COUNT(*),CZJM,'LJ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2=-1 AND FYBZ='0'
GROUP BY GDM,CZJM; UPDATE CZD_YD14 A
SET DIR_NUM=(SELECT DIRECTION_CODE FROM CZD_CARKIND WHERE A.CAR_KIND=CZDM)
WHERE BALANCE_TIME=V_TIME AND REMARK='LJ'; --先生成大方向号
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, FXH2,COUNT(*),0,'LJ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
And FXH2<>-1 AND FYBZ='0'
GROUP BY GDM,FXH2;---------生成上传数据完成------------------------------------------
--DELETE FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRMSG:='生成YD14失败:';
ERRMSG:=SUBSTRB(SQLERRM,1,256);
DBMS_OUTPUT.PUT_LINE(ERRMSG);
ROLLBACK;
END;
/
不知道有那位做过类似的?
V_CB 这个变量的值,通过以下游标可以取到.
有"x"行记录,相应的就会更新 F"X"列
CURSOR C_CML IS SELECT CDM FROM B_CML WHERE ZMLM=V_ZMLM AND BLZD1 IS NOT NULL ORDER BY BLZD1;
V_CB := 'SD';
UPDATE DY_YD14_TEMP A
SET A.F0=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1);
V_CB := 'SB';
UPDATE DY_YD14_TEMP A
SET A.F1=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'SF';
UPDATE DY_YD14_TEMP A
SET A.F2=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XD';
UPDATE DY_YD14_TEMP A
SET A.F3=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XB';
UPDATE DY_YD14_TEMP A
SET A.F4=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'XF';
UPDATE DY_YD14_TEMP A
SET A.F5=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'JH';
UPDATE DY_YD14_TEMP A
SET A.F6=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'FZ';
UPDATE DY_YD14_TEMP A
SET A.F7=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); V_CB := 'ZY';
UPDATE DY_YD14_TEMP A
SET A.F8=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1);
V_INSTR IN VARCHAR2,V_OUTSTR IN VARCHAR2)
AS
ERRMSG VARCHAR2(1000);
V_CID VARCHAR2(24);
V_LJDM VARCHAR2(4);
V_CB VARCHAR2(10);
CURSOR C_CML IS SELECT CDM FROM B_CML WHERE ZMLM=V_ZMLM AND BLZD1 IS NOT NULL ORDER BY BLZD1;
TIME1 DATE;
TIME2 DATE;
SECS VARCHAR(6);
errcode varchar(256);
vsql varchar2(1000);
vnum number;
--开头加声明此
BEGIN -- SELECT SYSDATE INTO TIME1 FROM DUAL;
SELECT SUBSTR(V_ZMLM,3,1) INTO V_LJDM FROM DUAL;
------------------清理临时数据------------------------
DELETE FROM DY_YD14_TEMP WHERE ZMLM=V_ZMLM;
DELETE FROM DY_YD14 WHERE ZMLM=V_ZMLM;
COMMIT;
LCJ_STATIONYARD_BACKDATE_BYDAT(V_TIME ,V_ZMLM ,V_ID ,V_INSTR ,V_OUTSTR);
UPDATE B_ZCXXCLK_TEMP A
SET CB=(SELECT CB FROM B_GDML WHERE GDM=A.GDM AND ZMLM=V_ZMLM AND ROWNUM=1),FXH2=(SELECT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM AND DZLM=A.DZH AND ROWNUM=1)
WHERE ZCXXID=V_ID ;
--统一修改CZJM字段,在CZD_CZJM字典中找不到的车种,直接取CZJM的第一位
UPDATE B_ZCXXCLK_TEMP A
set CZJM=(SELECT F_CZJM FROM CZD_CZJM WHERE F_DM=A.CZ AND ROWNUM=1)
WHERE Zcxxid=V_ID AND CZ IN(SELECT F_DM FROM CZD_CZJM) AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC'); UPDATE B_ZCXXCLK_TEMP A
set CZJM=SUBSTR(CZJM,1,1)
WHERE Zcxxid=V_ID AND CZ NOT IN(SELECT F_DM FROM CZD_CZJM) AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC');
UPDATE B_ZCXXCLK_TEMP
SET FXH2='99'
WHERE ZCXXID=V_ID AND (FXH2='999'); UPDATE B_ZCXXCLK_TEMP
SET FXH2='-1'
WHERE ZCXXID=V_ID AND (FXH2 IS NULL OR FXH2=''); -----读取站调大表车辆方向----------------------
INSERT INTO DY_YD14_TEMP(ZMLM,ZH,ZHM,XH,HJ,CB)
select V_ZMLM,dir_scope,row_text ,B.ROW_INDEX,0,'HJ' from CZD_sub_header a,CZD_sub_headER_row b where a.header_INDEX = B.header_INDEX AND A.CODE=5 AND A.TYPE =3
AND A.TAB_NUM=1000
ORDER BY B.ROW_INDEX;
-----读取结束----------------------------------
--带方向车辆按组号分类
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,FXH2,FXH2,1,1,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2 <>'-1' AND FYBZ='0'
GROUP BY FXH2;
OPEN C_CML;
LOOP
FETCH C_CML INTO V_CB;
EXIT WHEN C_CML%NOTFOUND;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,FXH2,FXH2,1,1,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2 <>'-1' AND FYBZ='0'
AND CB=V_CB
GROUP BY FXH2;
END LOOP;
CLOSE C_CML;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT ZMLM,'不明',ZHM,KZBZ,XH,HJ,CB FROM DY_YD14 WHERE ZMLM=V_ZMLM AND ZH NOT IN(SELECT DISTINCT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM) AND ZH <>'99' AND KZBZ='1'; DELETE FROM DY_YD14 WHERE ZMLM=V_ZMLM AND ZH NOT IN (SELECT DISTINCT XFXH FROM DP_ZMFX WHERE BZLM=V_ZMLM) AND ZH NOT IN('不明');
--不带方向车辆按车种分类 INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,CZJM,CZJM,0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0' AND CZJM IN('P','C','N','G','B','W')
GROUP BY CZJM ;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'K,X,JC,KC','K,X,JC,KC',0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0' AND CZJM IN('K','X','JC','KC')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'QT,99','QT,99',0,4,COUNT(*),'HJ' FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND ((FXH2='-1' AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC')) or FXH2='99' ) AND FYBZ='0';
OPEN C_CML;
LOOP
FETCH C_CML INTO V_CB;
EXIT WHEN C_CML%NOTFOUND;
INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,CZJM,CZJM,0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0'
AND CB=V_CB AND CZJM IN('P','C','N','G','B','W')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'K,X,JC,KC','K,X,JC,KC',0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID AND FXH2='-1' AND FYBZ='0'
AND CB=V_CB AND CZJM IN('K','X','JC','KC')
GROUP BY CZJM ; INSERT INTO DY_YD14(ZMLM,ZH,ZHM,KZBZ,XH,HJ,CB)
SELECT V_ZMLM,'QT,99','QT,99',0,4,COUNT(*),V_CB FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND ((FXH2='-1' AND CZJM NOT IN('P','C','N','G','B','W','K','X','JC','KC')) or FXH2='99') AND FYBZ='0'
AND CB=V_CB ;
END LOOP;
CLOSE C_CML;
V_CB := 'HJ';
UPDATE DY_YD14_TEMP A
SET A.HJ=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=V_CB AND ROWNUM=1); vnum:=0;
open C_CML;
loop
fetch C_CML into V_CB;
exit when C_CML%notfound;
vsql:='UPDATE DY_YD14_TEMP A' ¦ ¦' SET A.F' ¦ ¦to_char(vnum) ¦ ¦'=(SELECT HJ FROM DY_YD14 B WHERE B.ZH=A.ZH AND B.CB=''' ¦ ¦V_CB ¦ ¦''' AND ROWNUM=1)';
execute immediate vsql;
vnum := vnum+1;
end loop;
close C_CML;
DELETE FROM DY_YD14_TEMP
WHERE ZH='SUM'; INSERT INTO DY_YD14_TEMP(ZMLM,ZH,ZHM,XH,HJ,F0,F1,F2,F3,F4,F5,F6,F7,F8,CB)
SELECT V_ZMLM,'SUM','合计','9999',SUM(HJ),SUM(F0),SUM(F1),SUM(F2),SUM(F3),SUM(F4),
SUM(F5),SUM(F6),SUM(F7),SUM(F8),'HJ'
From DY_YD14_TEMP
WHERE ZMLM=V_ZMLM;
---------开始生成上传数据------------------------------------------ ---------开始生成上传数据------------------------------------------
DELETE FROM CZD_YD14; --先生成大方向号
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, FXH2,COUNT(*),0,'CZ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2 <>-1 AND FYBZ='0'
GROUP BY GDM,FXH2;
--大方向空车计
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, 0,COUNT(*),CZJM,'CZ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2=-1 AND FYBZ='0'
GROUP BY GDM,CZJM;
UPDATE B_ZCXXCLK_TEMP A
SET FXH2=(SELECT DIRECTION_CODE FROM FXL_FXLB WHERE TELE_CODE=A.DZH AND ROWNUM=1)
WHERE ZCXXID=V_ID ;
UPDATE B_ZCXXCLK_TEMP A
SET FXH2=-1
WHERE ZCXXID=V_ID AND FXH2 IS NULL; --大方向空车计
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, 0,COUNT(*),CZJM,'LJ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
AND FXH2=-1 AND FYBZ='0'
GROUP BY GDM,CZJM; UPDATE CZD_YD14 A
SET DIR_NUM=(SELECT DIRECTION_CODE FROM CZD_CARKIND WHERE A.CAR_KIND=CZDM)
WHERE BALANCE_TIME=V_TIME AND REMARK='LJ'; --先生成大方向号
INSERT INTO CZD_YD14(TAB_NUM,BALANCE_TIME,DIR_NUM,CAR_NUM,CAR_KIND,REMARK,GD_NO)
SELECT 1000,V_TIME, FXH2,COUNT(*),0,'LJ',GDM FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID
And FXH2 <>-1 AND FYBZ='0'
GROUP BY GDM,FXH2; ---------生成上传数据完成------------------------------------------
--DELETE FROM B_ZCXXCLK_TEMP WHERE ZCXXID=V_ID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRMSG:='生成YD14失败:';
ERRMSG:=SUBSTRB(SQLERRM,1,256);
DBMS_OUTPUT.PUT_LINE(ERRMSG);
ROLLBACK;
END;
/