CREATE OR REPLACE PACKAGE BODY ALEXMONITOR.PKG_OPERATOR
AS
PROCEDURE GetStationDynamicState(lineID In AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
IS
BEGIN
OPEN out_var for select
-- Station_ID ,
-- max(STATION_STATE) ,
-- extract(hour from (sysdate-max(TestTime))) *3600 + extract(minute from (sysdate-max(TestTime)))*60 + round(extract(second from (sysdate-max(TestTime)))) T_SEC from ALEXMONITOR.StationRealTime where regexp_like(Station_ID, '' || lineID || '.+' ) group by Station_ID;
Station_ID,
STATION_STATE,
extract(hour from (sysdate-TestTime)) *3600 + extract(minute from (sysdate-TestTime))*60 + round(extract(second from (sysdate-TestTime))) T_SEC from ALEXMONITOR.StationRealTime A where TestTime=(select max(TestTime) from ALEXMONITOR.StationRealTime where Station_ID=A.Station_ID and regexp_like(Station_ID, '' || lineID || '.+' ) );
end GetStationDynamicState;
PROCEDURE InsertStationDynamicData(stationID In AlexMonitor.StationRealTime.STATION_ID%type, stationState IN
AlexMonitor.StationRealTime.STATION_STATE%type,fixState in AlexMonitor.StationRealTime.FIXTURE_STATE%type,internet In AlexMonitor.StationRealTime.STATION_INTENET_STATE%type,out_var out number)
IS
BEGIN INSERT INTO AlexMonitor.StationRealTime(STATION_ID,STATION_STATE,TESTTIME,FIXTURE_STATE,STATION_INTENET_STATE) VALUES(
stationID,
stationState,
sysdate,
fixState,
internet);
COMMIT;
out_var:=1;
end InsertStationDynamicData;
PROCEDURE GetSNMessage(SnInfo In AlexMonitor.MessageData.SN%type, out_var out cursorref)
IS
BEGIN
OPEN out_var for select
LINENAME,
PNP_NAME,
STATIONNAME,
LOCATIONINDEX,
SN ,
RESULT,
STATTIME,
STOPTIME,
SYMPTOM_CODE,
SYMPTOM_DESC from MessageData where STOPTIME = (SELECT MAX(STOPTIME) FROM MessageData where SN = SnInfo) and SN = SnInfo;
end GetSNMessage;
end;
/
AS
PROCEDURE GetStationDynamicState(lineID In AlexMonitor.ProduceLine.PL_ID%type, out_var out cursorref)
IS
BEGIN
OPEN out_var for select
-- Station_ID ,
-- max(STATION_STATE) ,
-- extract(hour from (sysdate-max(TestTime))) *3600 + extract(minute from (sysdate-max(TestTime)))*60 + round(extract(second from (sysdate-max(TestTime)))) T_SEC from ALEXMONITOR.StationRealTime where regexp_like(Station_ID, '' || lineID || '.+' ) group by Station_ID;
Station_ID,
STATION_STATE,
extract(hour from (sysdate-TestTime)) *3600 + extract(minute from (sysdate-TestTime))*60 + round(extract(second from (sysdate-TestTime))) T_SEC from ALEXMONITOR.StationRealTime A where TestTime=(select max(TestTime) from ALEXMONITOR.StationRealTime where Station_ID=A.Station_ID and regexp_like(Station_ID, '' || lineID || '.+' ) );
end GetStationDynamicState;
PROCEDURE InsertStationDynamicData(stationID In AlexMonitor.StationRealTime.STATION_ID%type, stationState IN
AlexMonitor.StationRealTime.STATION_STATE%type,fixState in AlexMonitor.StationRealTime.FIXTURE_STATE%type,internet In AlexMonitor.StationRealTime.STATION_INTENET_STATE%type,out_var out number)
IS
BEGIN INSERT INTO AlexMonitor.StationRealTime(STATION_ID,STATION_STATE,TESTTIME,FIXTURE_STATE,STATION_INTENET_STATE) VALUES(
stationID,
stationState,
sysdate,
fixState,
internet);
COMMIT;
out_var:=1;
end InsertStationDynamicData;
PROCEDURE GetSNMessage(SnInfo In AlexMonitor.MessageData.SN%type, out_var out cursorref)
IS
BEGIN
OPEN out_var for select
LINENAME,
PNP_NAME,
STATIONNAME,
LOCATIONINDEX,
SN ,
RESULT,
STATTIME,
STOPTIME,
SYMPTOM_CODE,
SYMPTOM_DESC from MessageData where STOPTIME = (SELECT MAX(STOPTIME) FROM MessageData where SN = SnInfo) and SN = SnInfo;
end GetSNMessage;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26851211/viewspace-753072/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26851211/viewspace-753072/