请从plc数据表无记录，记录丢失，先方面看看下面过程有没有问题
CREATE OR REPLACE PROCEDURE lg_yc.PRO_MQHSSJ IS
T_ID NUMBER;
T_PHZL NUMBER;
T_CLKS DATE;
T_CGWC DATE;
T_LH NUMBER; --炉号
T_CYLL NUMBER; --吹氧流量
T_CO_HL NUMBER; --CO含量
T_CO2_HL NUMBER; --CO2含量
T_HFKD NUMBER; --环缝开度
T_LKYL NUMBER; --炉口压力
T_JQLL NUMBER; --机前流量
T_CYSC SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(40,90,360,380,400,420,500,520,540,560);
errorException EXCEPTION;
errorCode NUMBER;
errorMsg VARCHAR2(1000);
CURSOR cur_Config IS
SELECT * FROM (
SELECT * FROM (
SELECT t.id, t.lh, t.t0, t.t11, t.t21, t.t26,t.cfid,t.t27,t.t15
FROM ycshow.t_yc_zl1_ylsj t
WHERE t0 > SYSDATE - 10 AND t.t21 IS NOT NULL
UNION ALL
SELECT t.id, t.lh, t.t0, t.t11, t.t21, t.t26,t.cfid,t.t27,t.t15
FROM ycshow.t_yc_zl2_ylsj t
WHERE t0 > SYSDATE - 10 AND t.t21 IS NOT NULL
)
ORDER BY t0 DESC
)
WHERE ROWNUM <= 6;
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''yyyy/mm/dd hh24:mi:ss''';
FOR cur_SubConfig IN cur_Config LOOP
T_ID := cur_SubConfig.Id;
T_LH := cur_SubConfig.Lh;
T_CGWC := cur_SubConfig.t21;--上一炉出钢完成时间
T_CLKS := cur_SubConfig.t11;--上一炉吹炼开始时间
T_PHZL := TO_NUMBER(SUBSTR(cur_SubConfig.Lh,3,1));
FOR i IN 1..T_CYSC.COUNT LOOP
T_CYLL := NULL;
T_CO_HL := NULL;
T_CO2_HL := NULL;
T_HFKD := NULL;
T_LKYL := NULL;
T_JQLL := NULL;
IF T_PHZL = 1 THEN -- 1#炉
IF i = 1 THEN
SELECT
AVG(YQLL),
AVG(YCCC2_DB6_X34_0 * YCCC1_MD412 + YCCC2_DB6_X34_2 * YCCC2_MD48),
AVG(Z1JL_DB101_DBD200),
AVG(RD1_DB301_DBD68),
AVG(YCCC2_DB6_X34_0 * YCCC1_I16_0 + YCCC2_DB6_X34_2 * YCCC2_M16_4),
AVG(YC_1_DB16_DBD24)
INTO
T_CYLL, -- 吹氧流量
T_CO_HL, -- CO含量
T_CO2_HL, -- CO2含量
T_HFKD, -- 环缝开度
T_LKYL, -- 炉口压力
T_JQLL -- 机前流量
FROM YCSHOW.t_plc_zl1_ycjd t1
LEFT JOIN ycshow.T_YC_PLCDATA_QST_YQ_ZL1 t2 ON t1.CREATE_TIME = t2.CREATE_TIME
LEFT JOIN YCSHOW.T_YC_HB_ALL t3 ON t1.CREATE_TIME = t3.TIME
LEFT JOIN YCSHOW.T_PLC_HB2022 t4 ON t1.CREATE_TIME = t4.CREATE_TIME
WHERE t1.YQ_FLAG <= T_CYSC(i)
AND t1.CREATE_TIME BETWEEN T_CLKS AND T_CGWC;
ELSE
SELECT
AVG(YQLL),
AVG(YCCC2_DB6_X34_0 * YCCC1_MD412 + YCCC2_DB6_X34_2 * YCCC2_MD48),
AVG(Z1JL_DB101_DBD200),
AVG(RD1_DB301_DBD68),
AVG(YCCC2_DB6_X34_0 * YCCC1_I16_0 + YCCC2_DB6_X34_2 * YCCC2_M16_4),
AVG(YC_1_DB16_DBD24)
INTO
T_CYLL,
T_CO_HL,
T_CO2_HL,
T_HFKD,
T_LKYL,
T_JQLL
FROM YCSHOW.t_plc_zl1_ycjd t1
LEFT JOIN ycshow.T_YC_PLCDATA_QST_YQ_ZL1 t2 ON t1.CREATE_TIME = t2.CREATE_TIME
LEFT JOIN YCSHOW.T_YC_HB_ALL t3 ON t1.CREATE_TIME = t3.TIME
LEFT JOIN YCSHOW.T_PLC_HB2022 t4 ON t1.CREATE_TIME = t4.CREATE_TIME
WHERE t1.YQ_FLAG > T_CYSC(i-1) AND t1.YQ_FLAG <= T_CYSC(i)
AND t1.CREATE_TIME BETWEEN T_CLKS AND T_CGWC;
END IF;
ELSE -- 2#炉
IF i = 1 THEN
SELECT
AVG(YQLL),
AVG(YCCC2_DB6_X34_1 * YCCC2_MD412 + YCCC2_DB6_X34_3 * YCCC2_MD48),
AVG(Z2JL_DB2_DBD160),
AVG(RD2_DB301_DBD68),
AVG(YCCC2_DB6_X34_1 * YCCC2_M4_4 + YCCC2_DB6_X34_3 * YCCC2_M16_4),
AVG(YC_2_DB16_DBD24)
INTO
T_CYLL,
T_CO_HL,
T_CO2_HL,
T_HFKD,
T_LKYL,
T_JQLL
FROM YCSHOW.t_plc_zl2_ycjd t1
LEFT JOIN ycshow.T_YC_PLCDATA_QST_YQ_ZL1 t2 ON t1.CREATE_TIME = t2.CREATE_TIME
LEFT JOIN YCSHOW.T_YC_HB_ALL t3 ON t1.CREATE_TIME = t3.TIME
LEFT JOIN YCSHOW.T_PLC_HB2022 t4 ON t1.CREATE_TIME = t4.CREATE_TIME
WHERE t1.YQ_FLAG <= T_CYSC(i)
AND t1.CREATE_TIME BETWEEN T_CLKS AND T_CGWC;
ELSE
SELECT
AVG(YQLL),
AVG(YCCC2_DB6_X34_1 * YCCC2_MD412 + YCCC2_DB6_X34_3 * YCCC2_MD48),
AVG(Z2JL_DB2_DBD160),
AVG(RD2_DB301_DBD68),
AVG(YCCC2_DB6_X34_1 * YCCC2_M4_4 + YCCC2_DB6_X34_3 * YCCC2_M16_4),
AVG(YC_2_DB16_DBD24)
INTO
T_CYLL,
T_CO_HL,
T_CO2_HL,
T_HFKD,
T_LKYL,
T_JQLL
FROM YCSHOW.t_plc_zl2_ycjd t1
LEFT JOIN ycshow.T_YC_PLCDATA_QST_YQ_ZL1 t2 ON t1.CREATE_TIME = t2.CREATE_TIME
LEFT JOIN YCSHOW.T_YC_HB_ALL t3 ON t1.CREATE_TIME = t3.TIME
LEFT JOIN YCSHOW.T_PLC_HB2022 t4 ON t1.CREATE_TIME = t4.CREATE_TIME
WHERE t1.YQ_FLAG > T_CYSC(i-1) AND t1.YQ_FLAG <= T_CYSC(i)
AND t1.CREATE_TIME BETWEEN T_CLKS AND T_CGWC;
END IF;
END IF;
INSERT INTO LG_YC.t_MH_hb_MQSJSJ (
lh, CYSC, cyll, co_hl, co2_hl, hfkd, lkyl, jqll
) VALUES (
T_LH, T_CYSC(i), T_CYLL, T_CO_HL, T_CO2_HL, T_HFKD, T_LKYL, T_JQLL
);
END LOOP;
END LOOP;
COMMIT;
EXCEPTION
when errorException then
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
when others then
errorCode := SQLCODE;
errorMsg := SUBSTR(SQLERRM, 1, 200);
NULL;
end PRO_MQHSSJ;
本文介绍了AVG函数的基本用法及参数设置，展示了如何通过distinct和all关键字来区分重复值求平均值，并给出了具体的示例代码。
扫一扫
479
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
