is
procedure Report_MaTolStartupEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol varchar2(100);
vGroup varchar2(200);
v_Resultvid varchar2(200);
v_Resultdot varchar2(200);
v_Resultyun varchar2(200);
v_mysql varchar2(1000);
begin
v_Resultvid := QUOTENAME(vID,'''');
v_Resultdot := QUOTENAME('.','');
v_Resultyun := QUOTENAME('运行','');
vGroup:='GetTimeGroup(StartTime,' ¦ ¦QUOTENAME(vTime,'''') ¦ ¦')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID=' ¦ ¦v_Resultvid ¦ ¦' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID=' ¦ ¦v_Resultvid ¦ ¦' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName=' ¦ ¦v_Resultvid ¦ ¦' AND';
end if;
if(nvl(vTime,'#')='#') then
vGroup:='DeptName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolGrpName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolName';
end if;
v_Mysql := 'SELECT MaTolName,sONLINE,WORKING,GetTimeStr(sONLINE) as STRONLINE,';
v_Mysql := v_Mysql ¦ ¦'GetTimeStr(WORKING) AS STRWORKING,';
v_Mysql := v_Mysql ¦ ¦'cast(sONLINE*10000/WORKING as decimal(10,2))/100 as STARTUPEFF,';
v_Mysql := v_Mysql ¦ ¦'sGroup From ';
v_Mysql := v_Mysql ¦ ¦'(Select sum(statusTimeValue) AS sONLINE,';
--v_Mysql := v_Mysql ¦ ¦'(dend-dbegin)*86400 as WORKING,';
v_Mysql := v_Mysql ¦ ¦'(' ¦ ¦dend ¦ ¦'-' ¦ ¦dbegin ¦ ¦')*86400 as WORKING,';
--v_Mysql := v_Mysql ¦ ¦'(to_date(' ¦ ¦dend ¦ ¦','yyyy-mm-dd') - to_date(' ¦ ¦dbegin ¦ ¦','yyyy-mm-dd'))*86400 as WORKING,';
v_Mysql := v_Mysql ¦ ¦'(DeptName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolGrpName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolName) as MaTolName,';
v_Mysql := v_Mysql ¦ ¦vGroup ¦ ¦' as sGroup ';
v_Mysql := v_Mysql ¦ ¦'From StatusTable WHERE ' ¦ ¦MaTol ¦ ¦' StartTime BETWEEN ''' ¦ ¦dbegin ¦ ¦''' AND ''' ¦ ¦dend ¦ ¦''' Group by MaTolName,';
v_Mysql := v_Mysql ¦ ¦' DeptName,MaTolGrpName,' ¦ ¦vGroup ¦ ¦')';
insert into tmp_ok values(v_Mysql,sysdate);
commit;
open rst for v_Mysql;
end;
end;
v_Mysql := v_Mysql ¦ ¦'(' ¦ ¦dend ¦ ¦'-' ¦ ¦dbegin ¦ ¦')*86400 as WORKING,';
这样计算的结果为;
(24-4月 -08-01-4月 -08)*86400 as WORKING,
得不到我要的值,大家看这个地方如何修改 v_Mysql := v_Mysql ¦ ¦'(' ¦ ¦dend ¦ ¦'-' ¦ ¦dbegin ¦ ¦')*86400 as WORKING,';
procedure Report_MaTolStartupEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol varchar2(100);
vGroup varchar2(200);
v_Resultvid varchar2(200);
v_Resultdot varchar2(200);
v_Resultyun varchar2(200);
v_mysql varchar2(1000);
begin
v_Resultvid := QUOTENAME(vID,'''');
v_Resultdot := QUOTENAME('.','');
v_Resultyun := QUOTENAME('运行','');
vGroup:='GetTimeGroup(StartTime,' ¦ ¦QUOTENAME(vTime,'''') ¦ ¦')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID=' ¦ ¦v_Resultvid ¦ ¦' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID=' ¦ ¦v_Resultvid ¦ ¦' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName=' ¦ ¦v_Resultvid ¦ ¦' AND';
end if;
if(nvl(vTime,'#')='#') then
vGroup:='DeptName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolGrpName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolName';
end if;
v_Mysql := 'SELECT MaTolName,sONLINE,WORKING,GetTimeStr(sONLINE) as STRONLINE,';
v_Mysql := v_Mysql ¦ ¦'GetTimeStr(WORKING) AS STRWORKING,';
v_Mysql := v_Mysql ¦ ¦'cast(sONLINE*10000/WORKING as decimal(10,2))/100 as STARTUPEFF,';
v_Mysql := v_Mysql ¦ ¦'sGroup From ';
v_Mysql := v_Mysql ¦ ¦'(Select sum(statusTimeValue) AS sONLINE,';
--v_Mysql := v_Mysql ¦ ¦'(dend-dbegin)*86400 as WORKING,';
v_Mysql := v_Mysql ¦ ¦'(' ¦ ¦dend ¦ ¦'-' ¦ ¦dbegin ¦ ¦')*86400 as WORKING,';
--v_Mysql := v_Mysql ¦ ¦'(to_date(' ¦ ¦dend ¦ ¦','yyyy-mm-dd') - to_date(' ¦ ¦dbegin ¦ ¦','yyyy-mm-dd'))*86400 as WORKING,';
v_Mysql := v_Mysql ¦ ¦'(DeptName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolGrpName ¦ ¦''' ¦ ¦v_Resultdot ¦ ¦''' ¦ ¦MaTolName) as MaTolName,';
v_Mysql := v_Mysql ¦ ¦vGroup ¦ ¦' as sGroup ';
v_Mysql := v_Mysql ¦ ¦'From StatusTable WHERE ' ¦ ¦MaTol ¦ ¦' StartTime BETWEEN ''' ¦ ¦dbegin ¦ ¦''' AND ''' ¦ ¦dend ¦ ¦''' Group by MaTolName,';
v_Mysql := v_Mysql ¦ ¦' DeptName,MaTolGrpName,' ¦ ¦vGroup ¦ ¦')';
insert into tmp_ok values(v_Mysql,sysdate);
commit;
open rst for v_Mysql;
end;
end;
v_Mysql := v_Mysql ¦ ¦'(' ¦ ¦dend ¦ ¦'-' ¦ ¦dbegin ¦ ¦')*86400 as WORKING,';
这样计算的结果为;
(24-4月 -08-01-4月 -08)*86400 as WORKING,
得不到我要的值,大家看这个地方如何修改 v_Mysql := v_Mysql ¦ ¦'(' ¦ ¦dend ¦ ¦'-' ¦ ¦dbegin ¦ ¦')*86400 as WORKING,';
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货