其中QUOTENAME 函数为
(id in Varchar2, Type In Varchar2)
return Varchar2 is
Result Varchar2(100);
begin
Result:=Type ¦ ¦id ¦ ¦Type;
return(Result);
end ; 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,
' ¦ ¦vGroup ¦ ¦' as sGroup
From StatusTable WHERE MaTol ¦ ¦StartTime BETWEEN dbegin AND dend
Group by status,MaTolName,DeptName,MaTolGrpName,' ¦ ¦vGroup ¦ ¦') a group by MaTolName,sGroup;
end;
end; 说明 问题,我初步判断错在以下两个地方
' ¦ ¦vGroup ¦ ¦' as sGroup 这个地方的分组有问题,输出为 ' ¦ ¦vGroup ¦ ¦' 而我要输出的是分组的值 WHERE MaTol ¦ ¦StartTime BETWEEN dbegin AND dend, 其中MaTol为上面的IF条件的值,MaTol ¦ ¦StartTime BETWEEN dbegin AND dend 拼接的语法么问题,但是条件值有问题,我怀疑是字符串多引号的问题,各位高手,看看是什么原因,解决立马给分
(id in Varchar2, Type In Varchar2)
return Varchar2 is
Result Varchar2(100);
begin
Result:=Type ¦ ¦id ¦ ¦Type;
return(Result);
end ; 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,
' ¦ ¦vGroup ¦ ¦' as sGroup
From StatusTable WHERE MaTol ¦ ¦StartTime BETWEEN dbegin AND dend
Group by status,MaTolName,DeptName,MaTolGrpName,' ¦ ¦vGroup ¦ ¦') a group by MaTolName,sGroup;
end;
end; 说明 问题,我初步判断错在以下两个地方
' ¦ ¦vGroup ¦ ¦' as sGroup 这个地方的分组有问题,输出为 ' ¦ ¦vGroup ¦ ¦' 而我要输出的是分组的值 WHERE MaTol ¦ ¦StartTime BETWEEN dbegin AND dend, 其中MaTol为上面的IF条件的值,MaTol ¦ ¦StartTime BETWEEN dbegin AND dend 拼接的语法么问题,但是条件值有问题,我怀疑是字符串多引号的问题,各位高手,看看是什么原因,解决立马给分
这句,看不懂。vTime是做为变量,还是做为固定值出现呢?
按你这种写法,'||vTIME||'是做为整体出现,不会给vTime
赋值的。其他的还有很多,希望LZ能仔细好好看看。
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 ¦ ¦' ¦ ¦')';
vGroup:='GetTimeGroup(StartTime,' ¦ ¦QUOTENAME(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';
end if;
if(vTime='') then
--vGroup:='DeptName ¦ ¦' ¦ ¦QUOTENAME('.','''') ¦ ¦' ¦ ¦MaTolGrpName ¦ ¦' ¦ ¦QUOTENAME('.','''') ¦ ¦' ¦ ¦MaTolName';
--vGroup:='DeptName ¦ ¦ QUOTENAME('.', '') ¦ ¦MaTolGrpName ¦ ¦QUOTENAME('.', '') ¦ ¦MaTolName';
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,
' ¦ ¦vGroup ¦ ¦' as sGroup
From StatusTable WHERE MaTol ¦ ¦ StartTime BETWEEN dbegin AND dend
Group by status,MaTolName,DeptName,MaTolGrpName,' ¦ ¦vGroup ¦ ¦') a group by MaTolName,sGroup;
end;
end;
各位高手,上面的存储过程错哪了,怎么老提示ORA-01858: 在要求输入数字处找到非数字字符,我把WHERE MaTol ¦ ¦ StartTime BETWEEN dbegin AND dend 改成WHERE StartTime BETWEEN dbegin AND dend 就没错误,什么原因啊?