这段过程有优化的空间么?请进来看看!!(50分)

龙之天涯 · 2008-04-07

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; ---------主要想把下面9个代码用油标搞定,只是不知道如何改边字段F0----F9和游标记录条数相等.想尝试使用 ---------动态数组和油标结合,大家用过来帮帮忙 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; /

龙之天涯 · 2008-04-07

怎么全是有人看,没人答啊

mofaser · 2008-04-07

你最好先了解一下SQL优化的原理。 数据量大的情况下，你用IN / NOT IN的操作会很消耗资源的，建议改成 exists 或 not exists方式。

龙之天涯 · 2008-04-07

B_ZCXXCLK_TEMP表的数据大概也就在5000左右,整个过程执行大概需要18秒, 我想能不能进一步优化, 特别是加强过程的通用性 ---------主要想把下面9个代码用油标搞定,只是不知道如何改边字段F0----F9和游标记录条数相等.想尝试使用 ---------动态数组和油标结合,大家用过来帮帮忙  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); 就是把这段代码换个通用点出来

龙之天涯 · 2008-04-07

我来解释一下,只想把下面这段代码优化一下. 由于where 条件的值不同,没有办法只有写了8行看起来很类似的代码,想通过动态数组的办法来实现 不知道有那位做过类似的? 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);

mofaser · 2008-04-07

如何用数组你自己可以google一下。 如果你想实现动态列名 FX 你可以把执行SQL的方式改成循环语句 FOR .... loop EXECUTE IMMEDIATE 'UPDATE TABLENAME SET F'||TO_CHAR(I)||'= .......'; end loop;

龙之天涯 · 2008-04-07

现在已经改成这样了,通用性强了一点点 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); 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; /

man8888 · 2008-04-07

Commit一次就够了，不要多次提交。 你在亿阳交通工作吧？感觉这存储过程挺亲切的...我写的车辆统计存储过程大概有2000行。 这种大的存储过程只能自己看了，慢慢优化，别靠别人，多看看甲骨文的帮助，Oracle存储过程功能很强大的。

这段过程有优化的空间么?请进来看看!!(50分)

龙之天涯

Unregistered / Unconfirmed

龙之天涯

Unregistered / Unconfirmed

mofaser

Unregistered / Unconfirmed

龙之天涯

Unregistered / Unconfirmed

龙之天涯

Unregistered / Unconfirmed

mofaser

Unregistered / Unconfirmed

龙之天涯

Unregistered / Unconfirmed

man8888

Unregistered / Unconfirmed

Similar threads