SQLSERVER与ORACLE存储过程对比ORACLE版本(函数,程序包,程序体,存储过程)
create or replace function GetDayName(theDate in varchar2)
RETURN varchar2 is Result varchar2(64);
BEGIN
Result := substr(theDate,1, 4) + '年' + substr(theDate, 6, 2) + '月' + substr(theDate,9, 2) + '日';
return(Result);
END GetDayName;
create or replace function GetGroupbyName(theDate in varchar2)
RETURN varchar2 is Result varchar2(64);
BEGIN
Result :=substr(theDate, 1, 10);
return Result;
END GetGroupbyName;
CREATE or replace FUNCTION uf_GetYearName(theDate in varchar2)
RETURN varchar2 is Result varchar2(64);
BEGIN
Result := substr(theDate,1, 4) + '年' ;
return Result;
END uf_GetYearName;
CREATE or replace FUNCTION uf_GetMonthName(theDate in varchar2)
RETURN varchar2 is Result varchar2(64);
BEGIN
Result:=substr(theDate,1, 4) + '年' + substr(theDate, 6, 2) + '月';
return Result;
END uf_GetMonthName;
CREATE OR REPLACE FUNCTION uf_GetDayName(theDate in varchar2)
RETURN varchar2 is Result varchar2(64);
BEGIN
Result:=substr(theDate, 4) + '年' + substr(theDate, 6, 2) + '月' + substr(theDate,9, 2) + '日';
return Result;
END uf_GetDayName;
CREATE or replace FUNCTION GetTimeStr(sec in NUMBER)
RETURN varchar2
is
result varchar2(64);
BEGIN
select trunc(sec / 3600)||':'|| trunc(mod(sec,3600)/60)||':'||trunc(mod(mod(sec,3600),60)) Into result from dual;
--如果你格式转化没错的话，基本没问题了。一般我直接to_char来转化的
return result;
END GetTimeStr;
CREATE or replace FUNCTION uf_MillisecondToTimeString(ms in NUMBER)
RETURN varchar2
is
result varchar2(64);
BEGIN
select trunc((ms/1000)/3600)||':'|| trunc(mod(ms/1000,3600)/60)|| ':' || trunc(mod(mod(ms/1000,3600),60)) Into result from dual;
return result;
END uf_MillisecondToTimeString;
create or replace function GetTimeGroup(time in Date,fmt in varchar2)
RETURN varchar2 is Result varchar2(30);
BEGIN
if fmt='day' then
Select to_char(time,'yyyy-mm-dd') Into Result From dual;
Elsif fmt='month' then
Select to_char(time,'yyyy-mm') Into Result From dual;
Elsif fmt='quarter' then
Select to_char(time,'yyyy')||'年'||
Case When to_char(time,'mm')<=3 Then '1'
When to_char(time,'mm')<=6 Then '2' When to_char(time,'mm')<=9 Then '3' Else '4' End||'季度'
Into Result From dual;
Elsif fmt='year' then
Select to_char(time,'yyyy') Into Result From dual;
Else Result:= 'error';
End If;
Return Result;
End GetTimeGroup;
CREATE Or Replace FUNCTION uf_GetSeasonName(theDate Varchar2)
Return Varchar2 AS
Result Varchar2(64);
BEGIN
Result:=substr(theDate, 0, 4)||'年'||ceil(substr(theDate, 6, 2)/3)||'季度' ;
return Result;
End;
CREATE or replace FUNCTION GetWorkTime(time Date)
RETURN number is
v_value number(10);
v_week_d number(3);
BEGIN
select case to_char(time,'day') when '星期一' then 1 when '星期二' then 2 when '星期三' then 3 when '星期四' then 4 when '星期五' then 5 when '星期六' then 6 when '星期日' then 7 end
into v_week_d from dual; --取星期几
select value into v_value from
(SELECT case WeekID
when 0 then 7
when WeekID then WeekID
end as WeekID,
sum( (StartTime-EndTime+RestTime)*24*60 )as value FROM WorkSheet
group by WeekID) a
where WeekID=v_week_d;
return v_value;
END GetWorkTime;
create or replace function QUOTENAME(id in Varchar2, Type In Varchar2)
return Varchar2 is
Result Varchar2(100);
begin
Result:=Type||id||Type;
return(Result);
end ;
视图和存储过程
数据库中AlarmRunInfo表字段AlarmID要修改。
在manager console 里创建 StatusTable
SELECT A.*,M.MaTolID,D.DeptName,G.MaTolGrpName,D.DeptID,G.MaTolGrpID
FROM (SELECT
case BaseState
when 'IDLE' then '空闲'
when 'RUNNING ' then '运行'
end as status, BaseStateDuring as statusTimeValue,StartTime,MaTolName
FROM MachineBaseRunState
UNION ALL
SELECT
case D.IsError
when 0 then '空闲'
when 1 then '故障'
end as status, AlarmDuring as statusTimeValue,R.StartTime,R.MaTolName
FROM AlarmRunInfo R ,AlarmInfoDefine D
WHERE R.MaTolName=(SELECT MaTolName From MachineTools WHERE MaTolID= D.MaTolID) AND R.AlarmID=D.AlarmID) A,
MachineTools M,MachineToolGroups G,Departments D
WHERE M.DeptID=D.DeptID AND M.MaTolGrpID=G.MaTolGrpID AND A.MaTolName=M.MaTolName
CREATE or replace VIEW StatusTable AS
SELECT A.*,M.MaTolID,D.DeptName,G.MaTolGrpName,D.DeptID,G.MaTolGrpID
FROM (SELECT
case BaseState
when 'IDLE' then '空闲'
when 'RUNNING ' then '运行'
end as status, BaseStateDuring as statusTimeValue,StartTime,MaTolName
FROM MachineBaseRunState
UNION ALL
SELECT
case D.IsError
when 0 then '空闲'
when 1 then '故障'
end as status, AlarmDuring as statusTimeValue,R.StartTime,R.MaTolName
FROM AlarmRunInfo R ,AlarmInfoDefine D
WHERE R.MaTolName=(SELECT MaTolName From MachineTools WHERE MaTolID= D.MaTolID) AND R.AlarmID=D.AlarmID) A,
MachineTools M,MachineToolGroups G,Departments D
WHERE M.DeptID=D.DeptID AND M.MaTolGrpID=G.MaTolGrpID AND A.MaTolName=M.MaTolName；
end;
1-------建立程序包
CREATE OR REPLACE package pk_wtWorkpieceDetailReport
is
type mytype is ref cursor;
procedure WorkpieceDetailReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date );
end;
---先创建包,再执行存储过程
create or replace package body pk_wtWorkpieceDetailReport
is
procedure WorkpieceDetailReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date )
is
begin
open rst for SELECT MaTolName,ProgramName, StartTime, During
FROM ProgramRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName;
end;
end;
--------------------------------------------------------------------------------
2:CREATE OR REPLACE package pk_wtWorkpiece_DetailReport
is
type mytype is ref cursor;
procedure Workpiece_DetailReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date );
end;
---先创建包,再执行存储过程
create or replace package body pk_wtWorkpiece_DetailReport
is
procedure Workpiece_DetailReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date )
is
begin
open rst for SELECT MaTolName,ProgramName, StartTime, GetTimeStr(During) AS During
FROM ProgramRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName;
end;
end;
------------------------------------------------------------------------------------------------
3:
CREATE OR REPLACE package pk_wtWorkpiece_CountReport
is
type mytype is ref cursor;
procedure Workpiece_CountReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date );
end;
---先创建包,再执行存储过程
create or replace package body pk_wtWorkpiece_CountReport
is
procedure Workpiece_CountReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date )
is
begin
if(MaTolName='#') then
open rst for SELECT MaTolName,ProgramName, count(ProgramName) as ProgramNameCount,ProgramName As ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59' group by MaTolName,ProgramName;
elsif(MaTolName IS not null) then
open rst for SELECT MaTolName,ProgramName, count(ProgramName) as ProgramNameCount,ProgramName As ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName group by MaTolName,ProgramName;
else
open rst for SELECT MaTolName, count(*) as ProgramNameCount,MaTolName as ReportGroup
FROM ProgramRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'group by MaTolName,ProgramName;
end if;
end;
end;
4:
CREATE OR REPLACE package pk_wtReport_MaTolInfo
is
type mytype is ref cursor;
procedure Report_MaTolInfo( rst out mytype,vID in nvarchar2,vtable in nvarchar2 );
end;
---先创建包,再执行存储过程
create or replace package body pk_wtReport_MaTolInfo
is
procedure Report_MaTolInfo( rst out mytype ,vID in nvarchar2,vtable in nvarchar2 )
is
--declare vvalue nvarchar2;
begin
--vvalue: =vtable;
if vtable='Departments' then
open rst for SELECT D.* FROM Departments D,UsersInDepartments U WHERE D.DeptID=U.DeptID AND U.UserID=cast(vID AS rowid);
elsif(vtable='MachineToolGroups') then
open rst for SELECT * FROM MachineToolGroups WHERE DeptID=cast(vID AS rowid); --根据DeptID查机床组
elsif(vtable='MachineTools') then
open rst for SELECT * FROM MachineTools WHERE MaTolGrpID=cast(vID AS rowid); --根据MaTolGrpID查机床
end if;
end;
end;
is
procedure Report_MaTolInfo( rst out mytype ,vID in nvarchar2,vtable in nvarchar2 )
is
--declare vvalue nvarchar2;
begin
--vvalue: =vtable;
if vtable='Departments' then
open rst for SELECT D.* FROM Departments D,UsersInDepartments U WHERE D.DeptID=U.DeptID AND U.UserID=vID;
elsif(vtable='MachineToolGroups') then
open rst for SELECT * FROM MachineToolGroups WHERE DeptID=vID; --根据DeptID查机床组
elsif(vtable='MachineTools') then
open rst for SELECT * FROM MachineTools WHERE MaTolGrpID=vID; --根据MaTolGrpID查机床
end if;
end;
end;
5:
CREATE OR REPLACE package pk_wtMachineBaseRunStateReport
is
type mytype is ref cursor;
procedure MachineBaseRunStateReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtMachineBaseRunStateReport
is
procedure MachineBaseRunStateReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date)
is
begin
if(MaTolName='ALL') then
open rst for select matolname, case basestate when 'IDLE' THEN '空闲' when 'RUNNING' THEN '运行' when 'OFFLINE' THEN '关机'when '' then '其他'end as basestate,starttime,basestateduring
from machinebaserunstate
where StartTime>=dtpBegin and StartTime <=dtpEnd group by basestate,matolname,starttime,basestateduring;
else
open rst for select matolname, case basestate when 'IDLE' THEN '空闲' when 'RUNNING' THEN '运行' when 'OFFLINE' THEN '关机'when '' then '其他'end as basestate,starttime,basestateduring
from machinebaserunstate
where StartTime>=dtpBegin and StartTime <=dtpEnd and MaTolName=MaTolName group by basestate,matolname,starttime,basestateduring;
end if;
end;
end;
6:
CREATE OR REPLACE package pk_wtAlarm_FrequencyReport1
is
type mytype is ref cursor;
procedure Alarm_FrequencyReport1( rst out mytype ,MaTolName in nvarchar2,Alarm_no in nvarchar2,dtpBegin Date,dtpEnd Date,grouptime int );
end;
---先创建包,再执行存储过程
create or replace package body pk_wtAlarm_FrequencyReport1
is
procedure Alarm_FrequencyReport1( rst out mytype ,MaTolName in nvarchar2,Alarm_no in nvarchar2,dtpBegin Date,dtpEnd Date,grouptime int)
is
begin
if(grouptime=0) then
open rst for select count(AlarmID) as countAlarmID,Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,uf_GetDayName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup from AlarmRunInfo where AlarmID =Alarm_no and StartTime>=dtpBegin and StartTime <=dtpEnd AND MaTolName=MaTolName group by uf_GetDayName(to_char(StartTime, 'yyyy-mm-dd')),AlarmInfo;
elsif(grouptime=1) then
open rst for select count(AlarmID) as countAlarmID,Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,uf_GetMonthName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup from AlarmRunInfo where AlarmID =Alarm_no and StartTime>=dtpBegin and StartTime <=dtpEnd AND MaTolName=MaTolName group by uf_GetMonthName(to_char(StartTime, 'yyyy-mm-dd')),AlarmInfo;
elsif(grouptime=2) then
open rst for select count(AlarmID) as countAlarmID,Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,uf_GetSeasonName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup from AlarmRunInfo where AlarmID =Alarm_no and StartTime>=dtpBegin and StartTime <=dtpEnd AND MaTolName=MaTolName group by uf_GetSeasonName(to_char(StartTime, 'yyyy-mm-dd')),AlarmInfo;
elsif(grouptime=3) then
open rst for select count(AlarmID) as countAlarmID,Alarm_no as AlarmID ,AlarmInfo as AlarmInfo,GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring ,uf_GetYearName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup from AlarmRunInfo where AlarmID =Alarm_no and StartTime>=dtpBegin and StartTime <=dtpEnd AND MaTolName=MaTolName group by uf_GetYearName(to_char(StartTime, 'yyyy-mm-dd')),AlarmInfo;
end if;
end;
end;
7:
CREATE OR REPLACE package pk_wtAlarm_FrequencyReport
is
type mytype is ref cursor;
procedure Alarm_FrequencyReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtAlarm_FrequencyReport
is
procedure Alarm_FrequencyReport( rst out mytype ,MaTolName in nvarchar2,dtpBegin Date,dtpEnd Date)
is
begin
open rst for SELECT count(AlarmID) as countAlarmID,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,GetTimeStr(sum(AlarmDuring)) AS sumAlarmDuring,AlarmID as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59' and MaTolName=MaTolName group by AlarmID,AlarmInfo;
end;
end;
8:
CREATE OR REPLACE package pk_wtAlarm_DistributedReport
is
type mytype is ref cursor;
procedure Alarm_DistributedReport( rst out mytype ,MaTolName in nvarchar2,AlarmID in nvarchar2,dtpBegin Date,dtpEnd Date,grouptime int );
end;
---先创建包,再执行存储过程
create or replace package body pk_wtAlarm_DistributedReport
is
procedure Alarm_DistributedReport( rst out mytype ,MaTolName in nvarchar2,AlarmID in nvarchar2,dtpBegin Date,dtpEnd Date,grouptime int)
is
begin
if(grouptime=0) then
open rst for SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
uf_GetDayName(to_char(StartTime, 'yyyy-mm-dd')) as AlarmTime,
uf_GetDayName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName
and AlarmID=AlarmID group by AlarmID,AlarmInfo,uf_GetDayName(to_char(StartTime, 'yyyy-mm-dd'));
elsif(grouptime=1) then
open rst for SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
uf_GetMonthName(to_char(StartTime, 'yyyy-mm-dd')) as AlarmTime,
uf_GetMonthName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName
and AlarmID=AlarmID group by AlarmID,AlarmInfo,uf_GetMonthName(to_char(StartTime, 'yyyy-mm-dd'));
elsif(grouptime=2) then
open rst for SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
uf_GetSeasonName(to_char(StartTime, 'yyyy-mm-dd')) as AlarmTime,
uf_GetSeasonName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName
and AlarmID=AlarmID group by AlarmID,AlarmInfo,uf_GetSeasonName(to_char(StartTime, 'yyyy-mm-dd'));
elsif(grouptime=3) then
open rst for SELECT count(*) as countAlarm,AlarmID AS AlarmID, AlarmInfo AS AlarmInfo,
uf_GetYearName(to_char(StartTime, 'yyyy-mm-dd')) as AlarmTime,
uf_GetYearName(to_char(StartTime, 'yyyy-mm-dd')) as ReportGroup
FROM AlarmRunInfo
WHERE StartTime>=dtpBegin||'00:00:00'and StartTime <=dtpEnd||'23:59:59'AND MaTolName=MaTolName
and AlarmID=AlarmID group by AlarmID,AlarmInfo,uf_GetYearName(to_char(StartTime, 'yyyy-mm-dd'));
end if;
end;
end;
9：
CREATE OR REPLACE package pk_wtAlarm_StatisicReport
is
type mytype is ref cursor;
procedure Alarm_StatisicReport
( rst out mytype,
UserId rowID,
Workshop rowID,
Team rowID,
Machine in nvarchar2,
machinetype in nvarchar2,
dtpBegin Date,
dtpEnd Date);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtAlarm_StatisicReport
is
procedure Alarm_StatisicReport
( rst out mytype,
UserId rowID,
Workshop rowID,
Team rowID,
Machine in nvarchar2,
machinetype in nvarchar2,
dtpBegin Date,
dtpEnd Date)
is
begin
if (Workshop='66755c63-9846-401d-a228-4c164b82330e')AND(machinetype IS null)AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId)AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName order by StartTime desc;
elsif(machinetype IS null) then -- /*DeptName不为空，MaTolType为空*/
begin
if(Workshop IS NOT null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop) order by StartTime desc;
elsif(Workshop IS not null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')and(Team is not null and Team<>'66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team) order by StartTime desc;
else
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine) order by StartTime desc;
end if;
end;
elsif(machinetype IS not null) then
if(Workshop='66755c63-9846-401d-a228-4c164b82330e')AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId)AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=machinetype order by StartTime desc;
elsif(Workshop IS NOT null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND mt.MaTolType=machinetype order by StartTime desc;
elsif(Workshop IS not null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')and(Team is not null AND Team<>'66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND mt.MaTolType=machinetype order by StartTime desc;
else
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)AND mt.MaTolType=machinetype order by StartTime desc;
end if;
end if;
end;
end;
is
procedure Alarm_StatisicReport
(
UserId varchar2,
Workshop varchar2,
Team varchar2,
Machine varchar2,
machinetype varchar2,
dtpBegin Date,
dtpEnd Date,
rst out mytype)
is
begin
if (Workshop='1111111111')AND(machinetype IS null)AND(Team='1111111111')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId)AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName order by StartTime desc;
elsif(machinetype IS null) then -- /*DeptName不为空，MaTolType为空*/
begin
if(Workshop IS NOT null AND Workshop<>'1111111111')AND(Team='1111111111')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop) order by StartTime desc;
elsif(Workshop IS not null AND Workshop<>'1111111111')and(Team is not null and Team<>'1111111111')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team) order by StartTime desc;
else
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine) order by StartTime desc;
end if;
end;
elsif(machinetype IS not null) then
if(Workshop='1111111111')AND(Team='1111111111')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId)AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=machinetype order by StartTime desc;
elsif(Workshop IS NOT null AND Workshop<>'1111111111')AND(Team='1111111111')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND mt.MaTolType=machinetype order by StartTime desc;
elsif(Workshop IS not null AND Workshop<>'1111111111')and(Team is not null AND Team<>'1111111111')AND(Machine is NULL) then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND mt.MaTolType=machinetype order by StartTime desc;
else
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)AND mt.MaTolType=machinetype order by StartTime desc;
end if;
end if;
end;
end;
is
procedure Alarm_StatisicReport
(
UserId varchar2,
Workshop varchar2,
Team varchar2,
Machine varchar2,
machinetype varchar2,
dtpBegin Date,
dtpEnd Date,
rst out mytype)
is
begin
if (Workshop='1111111111')AND(machinetype='')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId)AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName order by StartTime desc;
elsif(machinetype='') then -- /*DeptName不为空，MaTolType为空*/
begin
if(Workshop<>'' AND Workshop<>'1111111111')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop) order by StartTime desc;
elsif(Workshop<>'' AND Workshop<>'1111111111')and(Team<>'' and Team<>'1111111111')AND(Machine='') then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team) order by StartTime desc;
else
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine) order by StartTime desc;
end if;
end;
elsif(machinetype<>'') then
if(Workshop='1111111111')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId)AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=machinetype order by StartTime desc;
elsif(Workshop<>'' AND Workshop<>'1111111111')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND mt.MaTolType=machinetype order by StartTime desc;
elsif(Workshop<>'' AND Workshop<>'1111111111')and(Team<>'' AND Team<>'1111111111')AND(Machine='') then
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND mt.MaTolType=machinetype order by StartTime desc;
else
open rst for SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.AlarmID AS AlarmID,mr.AlarmInfo AS AlarmInfo,mr.StartTime AS StartTime,GetTimeStr(mr.AlarmDuring) AS AlarmDuring
FROM Departments D,MachineToolGroups MG,
UsersInDepartments U,MachineTools mt,AlarmRunInfo mr
WHERE (U.UserID=UserId )AND(mr.StartTime<=dtpEnd AND mr.StartTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName
AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)AND mt.MaTolType=machinetype order by StartTime desc;
end if;
end if;
end;
end;
10:
CREATE OR REPLACE package pk_wtDownTimeReasonReport
is
type mytype is ref cursor;
procedure DownTimeReasonReport
( rst out mytype,
UserId rowID,
Workshop rowID,
Team rowID,
Machine in nvarchar2,
machinetype in nvarchar2,
dtpBegin Date,
dtpEnd Date);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtDownTimeReasonReport
is
procedure DownTimeReasonReport
( rst out mytype,
UserId rowID,
Workshop rowID,
Team rowID,
Machine in nvarchar2,
machinetype in nvarchar2,
dtpBegin Date,
dtpEnd Date)
is
begin
if (Workshop='66755c63-9846-401d-a228-4c164b82330e')AND(machinetype IS null)AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(machinetype IS null) then -- /*DeptName不为空，MaTolType为空*/
begin
if(Workshop IS NOT null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(Workshop IS not null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')and(Team is not null and Team<>'66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
else
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
end if;
end;
elsif(machinetype IS not null) then
if(Workshop='66755c63-9846-401d-a228-4c164b82330e')AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(Workshop IS NOT null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')AND(Team='66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(Workshop IS not null AND Workshop<>'66755c63-9846-401d-a228-4c164b82330e')and(Team is not null AND Team<>'66755c63-9846-401d-a228-4c164b82330e')AND(Machine is NULL) then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
else
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND(D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
end if;
end if;
end DownTimeReasonReport;
end pk_wtDownTimeReasonReport;
is
procedure DownTimeReasonReport
( rst out mytype,
UserId varchar2,
Workshop varchar2,
Team varchar2,
Machine varchar2,
machinetype varchar2,
dtpBegin Date,
dtpEnd Date)
is
begin
if (Workshop='1111111111')AND(machinetype='')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(machinetype='') then -- /*DeptName不为空，MaTolType为空*/
begin
if(Workshop <>'' AND Workshop<>'1111111111')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(Workshop<>'' AND Workshop<>'1111111111')and(Team<>''and Team<>'1111111111')AND(Machine='') then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
else
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
end if;
end;
elsif(machinetype<>'') then
if(Workshop='1111111111')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID AND mt.MaTolName = mr.MaTolName AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(Workshop<>'' AND Workshop<>'1111111111')AND(Team='1111111111')AND(Machine='') then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
elsif(Workshop<>'' AND Workshop<>'1111111111')and(Team<>'' AND Team<>'1111111111')AND(Machine='') then
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND (D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
else
open rst for SELECT a.DeptName,a.MaTolGrpName,a.MaTolName,a.MaTolType,a.InfoText,a.StateTime ,b.DownTimeReason
FROM (SELECT D.DeptName AS DeptName, MG.MaTolGrpName AS MaTolGrpName,mt.MaTolName AS MaTolName,mt.MaTolType AS MaTolType,
mr.InfoText AS InfoText,mr.StateTime AS StateTime
FROM Departments D,MachineToolGroups MG, UsersInDepartments U,MachineTools mt,MachineRunInfo mr
WHERE (U.UserID=UserId)AND infotag='STOPCON' AND(mr.StateTime<=dtpEnd AND mr.StateTime>=dtpBegin)
AND (D.DeptID = MG.DeptID)AND (U.DeptID = D.DeptID)and mt.MaTolGrpID =MG.MaTolGrpID
AND mt.MaTolName = mr.MaTolName AND(D.DeptID=Workshop)AND(MG.MaTolGrpID=Team)AND(mt.MaTolName=Machine)AND mt.MaTolType=machinetype ) a, downTimeReason b where a.infoText=b.downTimeReasonNo order by StateTime desc;
end if;
end if;
end DownTimeReasonReport;
end pk_wtDownTimeReasonReport;
11:
CREATE OR REPLACE package pk_wtReport_MaTolStartupEff
is
type mytype is ref cursor;
procedure Report_MaTolStartupEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
Time nvarchar2);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtReport_MaTolStartupEff
is
procedure Report_MaTolStartupEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol nvarchar2(100);
vGroup nvarchar2(200);
begin
vGroup:='GetTimeGroup(StartTime,'||'||vTime||'||')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName='||QUOTENAME(vID,'''')||' AND';
elsif(vTime='') then
vGroup:='DeptName||'||QUOTENAME('.','''')||'||MaTolGrpName||'||QUOTENAME('.','''')||'||MaTolName';
end if;
open rst for SELECT MaTolName,sONLINE,WORKING,GetTimeStr(sONLINE) as STRONLINE,
GetTimeStr(WORKING) AS STRWORKING,
cast(sONLINE*10000/WORKING as decimal(10,2))/100 as STARTUPEFF,
sGroup
from
(
Select sum(statusTimeValue) AS sONLINE,
sum(GetWorkTime(StartTime)) as WORKING,
('||DeptName||' || QUOTENAME('.', '''') ||
'||MaTolGrpName||' || QUOTENAME('.', '''') || '||MaTolName||') as MaTolName,
'||vGroup||' as sGroup
From StatusTable
WHERE '||MaTol||'||'|| StartTime||'
BETWEEN '||QUOTENAME(dbegin,'''') ||' AND '||QUOTENAME(dend,'''')||'
Group by MaTolName,DeptName,MaTolGrpName,'||vGroup||');
end;
end;
12
CREATE OR REPLACE package pk_wtReport_MaTolRunEff
is
type mytype is ref cursor;
procedure Report_MaTolRunEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtReport_MaTolRunEff
is
procedure Report_MaTolRunEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol nvarchar2(100);
vGroup nvarchar2(200);
begin
vGroup:='GetTimeGroup(StartTime,'||'||vTime||'||')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName='||QUOTENAME(vID,'''')||' AND';
elsif(vTime='') then
vGroup:='DeptName||'||QUOTENAME('.','''')||'||MaTolGrpName||'||QUOTENAME('.','''')||'||MaTolName';
end if;
open rst for SELECT MaTolName,
sum(case status when QUOTENAME('运行','''') then vValue else 0 end) as RUNNING,
GetTimeStr(sum(case status when QUOTENAME('运行','''') then vValue else 0 end)) AS STRRUNNING,
sum(vValue) as sONLINE,GetTimeStr(sum(vValue)) AS STRONLINE,
cast(sum(case status when QUOTENAME('运行','''') then vValue else 0 end)*10000/sum(vValue) as decimal(10,2))/100 as RUNEFF,
sGroup From
(Select status,sum(statusTimeValue) AS vValue,
('||DeptName||' || QUOTENAME('.', '''') ||'||MaTolGrpName||' || QUOTENAME('.', '''') || '||MaTolName||') as MaTolName,
'||Group||' as sGroup
From StatusTable WHERE '||MaTol||'||'|| StartTime||' BETWEEN '||QUOTENAME(dbegin,'''') ||' AND '||QUOTENAME(dbegin,'''')||'
Group by status,MaTolName,DeptName,MaTolGrpName,'||vGroup||') a group by MaTolName,sGroup;
end;
end;
13:
CREATE OR REPLACE package pk_wtReport_MaTolStatus_Group
is
type mytype is ref cursor;
procedure Report_MaTolStatus_Group
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date
);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtReport_MaTolStatus_Group
is
procedure Report_MaTolStatus_Group
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date
)
is
MaTol nvarchar2(100);
vGroup nvarchar2(200);
TimeValue number(16);
begin
TimeValue:=(dbegin-dend)*24*60*60;
if(Name='All') then
open rst for SELECT case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then TimeValue-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then TimeValue-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) as STRstatusTimeValue,MaTolName as sGroup,TimeValue AS TimeValue
FROM StatusTable
WHERE StartTime BETWEEN dbegin AND dend group by rollup(MaTolName ,status) ;
elsif(Name='Dept') then
open rst for SELECT case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then TimeValue-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then TimeValue-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolName as sGroup,TimeValue as TimeValue
FROM StatusTable
WHERE DeptID=cast(vID AS rowID)
AND StartTime BETWEEN dbegin AND dend group by rollup(MaTolName ,status) ;
elsif(Name='MaTolGrp') then
open rst for SELECT case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then TimeValue -sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then TimeValue-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolName as sGroup,TimeValue as TimeValue
FROM StatusTable
WHERE MaTolGrpID=cast(vID AS rowid) AND StartTime BETWEEN dbegin AND dend group by rollup(MaTolName ,status) ;
elsif(Name='MaTol') then
open rst for SELECT case
when grouping(MaTolName)=1 then '合计'
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then TimeValue -sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then TimeValue-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolName as sGroup,TimeValue as TimeValue
FROM StatusTable
WHERE MaTolName=vID AND StartTime BETWEEN dbegin AND dend group by rollup(MaTolName ,status) ;
end if;
end;
end;
--和Report_MaTolStatus相比多一个机床分组;
14:
CREATE OR REPLACE package pk_wtReport_MaTolStatus
is
type mytype is ref cursor;
procedure Report_MaTolStatus
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date
);
end;
---先创建包,再执行存储过程
create or replace package body pk_wtReport_MaTolStatus
is
procedure Report_MaTolStatus
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date
)
is
MaTolNum int;
vValues number(16);
TimeValue Number(16);
--TimeValue:=DATEDIFF (second,@begin,@end)
begin
TimeValue:=(dbegin-dend)*24*60*60;
if(Name='All') then
SELECT Count(MaTolName) into MaTolNum FROM MachineTools;
vValues:=TimeValue * MaTolNum;
open rst for SELECT case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolNum as MaTolNum,TimeValue as TimeValue
FROM StatusTable
WHERE StartTime BETWEEN dbegin AND dend group by rollup(status);
elsif(Name='Dept') then
SELECT Count(MaTolName) into MaTolNum FROM MachineTools WHERE DeptID=cast(vID AS rowid);
vValues:=TimeValue * MaTolNum;
open rst for SELECT case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolNum as MaTolNum,TimeValue as TimeValue
FROM StatusTable
WHERE DeptID=cast(vID AS rowid)
AND StartTime BETWEEN dbegin AND dend group by rollup(status);
elsif(Name='MaTolGrp') then
SELECT Count(MaTolName) into MaTolNum FROM MachineTools WHERE MaTolGrpID=cast(vID AS rowid);
vValues:=TimeValue * MaTolNum;
open rst for SELECT case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status,case
when grouping(status)=1 then vValues -sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolNum as MaTolNum,TimeValue as TimeValue
FROM StatusTable
WHERE MaTolGrpID=cast(vID AS rowid) AND StartTime BETWEEN dbegin AND dend group by rollup(status);
elsif(Name='MaTol') then
MaTolNum:=1;
vValues:=TimeValue * MaTolNum;
open rst for SELECT case
when grouping(status)=1 then '关机'
when grouping(status)=0 then status
end as status ,case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end as statusTimeValue,GetTimeStr(case
when grouping(status)=1 then vValues-sum(statusTimeValue)
when grouping(status)=0 then sum(statusTimeValue)
end) AS STRstatusTimeValue,MaTolNum as MaTolNum,TimeValue as TimeValue
FROM StatusTable
WHERE MaTolName=vID AND StartTime BETWEEN dbegin AND dend group by rollup(status);
end if;
end;
end;
--根据条件结果分别写入，待寻求更好的办法;
