oracle存储过程查询问题
1.rar
(71.62 KB)
2.rar
(87.89 KB)
1。视图V_BOF1_OJ61每小时会插入2笔数据，对应2个站别的数据（station 列为1 和2 ）
2.通过下面的存储过程 将视图的内容传到IL2_OJ61中（将station前加一个BOF 对应着BOF1和BOF2） 问题是新表IL2_OJ61中只有BOF1没有BOF2
师傅们帮忙解决一下 感激不尽 个人感觉是那条查询语句的问题CREATE OR REPLACE PROCEDURE IL2_MGR."IL2_SP_BOF1_OJ61" (P1 VARCHAR2,P2 VARCHAR2,P3 VARCHAR2,P4 VARCHAR2, P5 OUT VARCHAR2, P6 out VARCHAR2)
--FUNCTION:读取1#转炉二级数据()
--OJ61汽化冷却报文由1#炉DANILI完成写入
--P1 执行的序列号
--P2工位号 暂不处理
--P6返回状态，p5 为本次处理的D值
iS
v_MSG_ID_LOCAL VARCHAR2(50);
v_MSG_ID NUMBER(10);
v_MSG_DATETIME DATE;
v_MSG_FLAG NUMBER(1);
v_FORM_ID VARCHAR2(10 CHAR);
v_INPUT_CODE VARCHAR2(1 CHAR);
v_WORK_SHOP VARCHAR2(1 CHAR);
v_OPERATE_DATE VARCHAR2(8 CHAR);
v_OPERATE_SHIFT VARCHAR2(2 CHAR);
v_OPERATE_CREW VARCHAR2(1 CHAR);
v_OPERATOR VARCHAR2(10 CHAR);
v_OPERATE_TIME VARCHAR2(6 CHAR);
v_STATION VARCHAR2(4 CHAR);
v_VAPREC NUMBER(7,3);
v_QIBAO_PRESSURE NUMBER(7,3);
v_EXIT_WATER_PRESS NUMBER(7,3);
v_YIWEN_WATER_PRESS NUMBER(7,3);
v_YIWEN_BACK_PRESS_DIFF NUMBER(7,3);
v_ERWEN_WATER_PRESS NUMBER(7,3);
v_ERWEN_BACK_PRESS_DIFF NUMBER(7,3);
v_BOF_2ND_N2_PRESS NUMBER(7,3);
v_BOF_N2_PRESS NUMBER(7,3);
v_BOF_2ND_PRESS_DIFF NUMBER(7,3);
v_MOBILEYENZHAO_FLOW NUMBER(7,3);
v_FIXED_FLOW NUMBER(7,3);
v_YIWEN_FLOW NUMBER(7,3);
v_ERWEN_FLOW NUMBER(7,3);
v_DEHYDRATE_FLOW NUMBER(7,3);
v_YENQIQUAN NUMBER(7,3);
v_QIBAO_LEVEL NUMBER(4);
v_DEOXY_LEVEL NUMBER(4);
v_XURE_LEVEL NUMBER(4);
v_LAST_UPD_DATE VARCHAR2(8 CHAR);
v_LAST_UPD_TIME VARCHAR2(6 CHAR);
v_LAST_UPD_EMPL VARCHAR2(10 CHAR);
v_operate_dt date;
v_last_dt date;
v_spliter varchar2(50);
begin
v_spliter:='准备读取L2数据';
select "MSG_ID" into v_MSG_ID
from il2_mgr.V_BOF1_OJ61
where rownum=1 AND MSG_FLAG=1 order by MSG_DATETIME;
P5:=TO_CHAR(v_MSG_ID);
v_MSG_ID_LOCAL:=IL2_FN_GETSTAMP(to_number(p1));
--DBMS_OUTPUT.PUT_LINE(V_MSG_ID);
v_INPUT_CODE:='N';--转炉二级发来没有N
SELECT
O.MSG_ID, O.MSG_DATETIME, O.MSG_FLAG,
O.FORM_ID, O.INPUT_CODE, O.WORK_SHOP,
O.OPERATE_DATE, O.OPERATE_SHIFT, O.OPERATE_CREW,
O.OPERATOR, O.OPERATE_TIME, O.STATION,
O.VAPREC, O.QIBAO_PRESSURE, O.EXIT_WATER_PRESS,
O.YIWEN_WATER_PRESS, O.YIWEN_BACK_PRESS_DIFF, O.ERWEN_WATER_PRESS,
O.ERWEN_BACK_PRESS_DIFF, O.BOF_2ND_N2_PRESS, O.BOF_N2_PRESS,
O.BOF_2ND_PRESS_DIFF, O.MOBILEYENZHAO_FLOW, O.FIXED_FLOW,
O.YIWEN_FLOW, O.ERWEN_FLOW, O.DEHYDRATE_FLOW,
O.YENQIQUAN, O.QIBAO_LEVEL, O.DEOXY_LEVEL,
O.XURE_LEVEL, O.LAST_UPD_DATE, O.LAST_UPD_TIME,
O.LAST_UPD_EMPL
into
v_MSG_ID, v_MSG_DATETIME, v_MSG_FLAG,
v_FORM_ID, v_INPUT_CODE, v_WORK_SHOP,
v_OPERATE_DATE, v_OPERATE_SHIFT, v_OPERATE_CREW,
v_OPERATOR, v_OPERATE_TIME, v_STATION,
v_VAPREC, v_QIBAO_PRESSURE, v_EXIT_WATER_PRESS,
v_YIWEN_WATER_PRESS, v_YIWEN_BACK_PRESS_DIFF, v_ERWEN_WATER_PRESS,
v_ERWEN_BACK_PRESS_DIFF, v_BOF_2ND_N2_PRESS, v_BOF_N2_PRESS,
v_BOF_2ND_PRESS_DIFF, v_MOBILEYENZHAO_FLOW, v_FIXED_FLOW,
v_YIWEN_FLOW, v_ERWEN_FLOW, v_DEHYDRATE_FLOW,
v_YENQIQUAN, v_QIBAO_LEVEL, v_DEOXY_LEVEL,
v_XURE_LEVEL, v_LAST_UPD_DATE, v_LAST_UPD_TIME,
v_LAST_UPD_EMPL
FROM v_BOF1_OJ61 O where MSG_ID=V_MSG_ID;
v_spliter:='L2数据正常,数据准备开始';
--插入到IL2 TABLE
--将MSG 时间赋给operate_date ，无人操作，2011-01-27
V_OPERATE_DATE:=to_char(v_MSG_DATETIME,'yyyymmdd');
V_OPERATE_TIME:=to_char(v_MSG_DATETIME,'hh24miss');
if (nvl(rtrim(V_OPERATE_DATE)||rtrim(V_OPERATE_TIME),0)=0) then
v_operate_dt:=null;
else
v_operate_dt:=to_date(rtrim(V_OPERATE_DATE)||rtrim(V_OPERATE_TIME),'yyyymmddhh24miss');
end if;
if (nvl(rtrim(v_LAST_UPD_DATE)||rtrim(v_LAST_UPD_TIME),0)=0) then
v_last_dt:=null;
else
v_last_dt:=to_date(rtrim(v_LAST_UPD_DATE)||rtrim(v_LAST_UPD_TIME),'yyyymmddhh24miss');
end if;
--增加班号、班别 2011-02-22
v_OPERATE_CREW:=IL2_FN_GETBH(v_operate_dt);
v_OPERATE_SHIFT:=IL2_FN_GETBC(v_operate_dt);
--增加操作人员 2011-04-03,2011-6-19
if nvl(v_STATION,'1')='1' then
v_OPERATOR:=il2_mgr.IL2_FN_GETEMP('OG主操','1',v_MSG_DATETIME);
else
v_OPERATOR:=il2_mgr.IL2_FN_GETEMP('OG主操','1',v_MSG_DATETIME);
end if;
v_spliter:='数据准备完成，写入本地';
INSERT INTO IL2_MGR.IL2_OJ61 (
MSG_ID, INPUT_CODE, OPERATE_DATE,
OPERATE_SHIFT, OPERATE_CREW, OPERATOR, operatetime,
STATION, VAPREC,
QIBAOPRESSURE, EXITWATERPRESS, YIWENWATERPRESS,
YIWENBACKPRESSDIFF, ERWENWATERPRESS, ERWENBACKPRESSDIFF,
BOF2NDN2PRESS, BOFN2PRESS, BOF2NDPRESSDIFF,
MOBILEYENZHAOFLOW, FIXEDFLOW, YIWENFLOE,
ERWENFLOW, DEHYDRATEFLOW, YENQIQUAN,
QIBAOLEVEL, DEOXYLEVEL, XURELEVEL,
LASTUPDDATETIME, LASTUPDEMPL, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
MSG_TIME, MSG_FLAG, MSG_INFO)
VALUES (v_MSG_ID_LOCAL, v_INPUT_CODE, v_operate_dt,
v_OPERATE_SHIFT, v_OPERATE_CREW,
v_OPERATOR,v_operate_dt, 'BOF'||v_STATION,
v_VAPREC, v_QIBAO_PRESSURE, v_EXIT_WATER_PRESS,
v_YIWEN_WATER_PRESS, v_YIWEN_BACK_PRESS_DIFF, v_ERWEN_WATER_PRESS,
v_ERWEN_BACK_PRESS_DIFF, v_BOF_2ND_N2_PRESS, v_BOF_N2_PRESS,
v_BOF_2ND_PRESS_DIFF, v_MOBILEYENZHAO_FLOW, v_FIXED_FLOW,
v_YIWEN_FLOW, v_ERWEN_FLOW, v_DEHYDRATE_FLOW,
v_YENQIQUAN, v_QIBAO_LEVEL, v_DEOXY_LEVEL,
v_XURE_LEVEL,v_last_dt,
v_LAST_UPD_EMPL ,'','','','',sysdate,1,'');
----更新BOF1二级的数据库OJ53标志 为0
--update il2_mgr.V_BOF1_OJ61 set msg_flag=2 where msg_id=V_MSG_ID;
--更新消息表
INSERT INTO IL2_MGR.IL2_INT_TRIGGER(MSG_ID,TABLE_ID_VAL,DESCRIPTION,ATTRIBUTE1,MSG_INFO,MSG_TIME)
VALUES(V_MSG_ID_lOCAL,V_MSG_ID_LOCAL,'','READY','OJ61',sysdate);
----写入日志
--INSERT INTO IL2_MGR.IL2_L2_LOG(NAME,EVENT,STATUS,MSG_DT,ATTRIBUTE1,ATTRIBUTE2,MSG_ID)VALUES
--('BOF1_L2','OJ61','Success',sysdate,TO_CHAR(v_MSG_ID),to_char(v_MSG_DATETIME,'yyyy-mm-dd hh24:mi:ss'),V_MSG_ID_lOCAL);
p6 :='Stauts:SUCCESS;SOURCE:MSG_ID='||v_MSG_ID||',MSG_DATETIME='||to_char(v_MSG_DATETIME,'yyyy-mm-dd hh24:mi:ss')||';TARGET:IL2_OJ61.MSG_ID='||V_MSG_ID_lOCAL;
COMMIT;
EXCEPTION--错误处理
WHEN NO_DATA_FOUND
THEN
p6:='N';
WHEN OTHERS
THEN
p6 :='Stauts:FAILURE;LAST_STEP:'||v_spliter||'SOURCE:MSG_ID='||v_MSG_ID||',MSG_DATETIME='||to_char(v_MSG_DATETIME,'yyyy-mm-dd hh24:mi:ss')|| 'SQLCODE:'||TO_CHAR (SQLCODE) || ' SQLERRM:'|| rtrim(SQLERRM);
ROLLBACK;
end;
/
