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 ;
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;
提示错误为:
行号= 26 列号= 22 错误文本= PL/SQL: SQL Statement ignored
行号= 29 列号= 34 错误文本= PL/SQL: ORA-00907: 缺少右括号
错误在open rst for SELECT 后 以前的对应的SQLSERVER存储过程如下:
/*查询指定表的所有信息*/
CREATE procedure Report_MaTolStartupEff
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime,
@Time nvarchar(10)
as
DECLARE @MaTol nvarchar(100),@s nvarchar(4000),@Group nvarchar(200)
SET @Group='[dbo].[GetTimeGroup]([StartTime],'+QUOTENAME(@Time,'''')+')'
begin
if(@Name='All')
begin
SET @MaTol=''
end
if(@Name='Dept')
begin
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTolGrp')
begin
SET @MaTol='[MaTolGrpID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTol')
begin
SET @MaTol='[MaTolName]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Time='')
begin
select @Group='DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName'
end
select @s='SELECT [MaTolName],[ONLINE],[WORKING],dbo.GetTimeStr([ONLINE]) as STRONLINE,dbo.GetTimeStr([WORKING]) AS STRWORKING
,[STARTUPEFF]=cast([ONLINE]*10000/[WORKING] as decimal(10,2))/100,[Group]
from(Select sum([statusTimeValue]) AS [ONLINE],[WORKING]=sum([dbo].[GetWorkTime]([StartTime])),MaTolName=DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName ,[Group]='+@Group+'
From [StatusTable] WHERE '+@MaTol+' [StartTime] BETWEEN '+QUOTENAME(@begin,'''') +' AND '+QUOTENAME(@end,'''')+'
Group by MaTolName,DeptName,MaTolGrpName,'+@Group+'
)a'
EXECUTE(@s)
end
GO
return Varchar2 is
Result Varchar2(100);
begin
Result:=Type ¦ ¦id ¦ ¦Type;
return(Result);
end ;
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;
提示错误为:
行号= 26 列号= 22 错误文本= PL/SQL: SQL Statement ignored
行号= 29 列号= 34 错误文本= PL/SQL: ORA-00907: 缺少右括号
错误在open rst for SELECT 后 以前的对应的SQLSERVER存储过程如下:
/*查询指定表的所有信息*/
CREATE procedure Report_MaTolStartupEff
@ID nvarchar(50),
@Name nvarchar(50),
@begin DateTime,
@end DateTime,
@Time nvarchar(10)
as
DECLARE @MaTol nvarchar(100),@s nvarchar(4000),@Group nvarchar(200)
SET @Group='[dbo].[GetTimeGroup]([StartTime],'+QUOTENAME(@Time,'''')+')'
begin
if(@Name='All')
begin
SET @MaTol=''
end
if(@Name='Dept')
begin
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTolGrp')
begin
SET @MaTol='[MaTolGrpID]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Name='MaTol')
begin
SET @MaTol='[MaTolName]='+QUOTENAME(@ID,'''')+' AND'
end
if(@Time='')
begin
select @Group='DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName'
end
select @s='SELECT [MaTolName],[ONLINE],[WORKING],dbo.GetTimeStr([ONLINE]) as STRONLINE,dbo.GetTimeStr([WORKING]) AS STRWORKING
,[STARTUPEFF]=cast([ONLINE]*10000/[WORKING] as decimal(10,2))/100,[Group]
from(Select sum([statusTimeValue]) AS [ONLINE],[WORKING]=sum([dbo].[GetWorkTime]([StartTime])),MaTolName=DeptName+'+QUOTENAME('.','''')+'+MaTolGrpName+'+QUOTENAME('.','''')+'+MaTolName ,[Group]='+@Group+'
From [StatusTable] WHERE '+@MaTol+' [StartTime] BETWEEN '+QUOTENAME(@begin,'''') +' AND '+QUOTENAME(@end,'''')+'
Group by MaTolName,DeptName,MaTolGrpName,'+@Group+'
)a'
EXECUTE(@s)
end
GO
vGroup:='GetTimeGroup(StartTime,' ¦ ¦' ¦ ¦vTime ¦ ¦' ¦ ¦')';
改成:
vGroup := 'GetTimeGroup(StartTime,' || vTime || ')'; 将
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
改成:
MaTol:='DeptID=' || QUOTENAME(vID,'') || ' AND'; 其它地方类似哦....
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
改成:
MaTol:='DeptID=' ¦ ¦ QUOTENAME(@vID,'') ¦ ¦ ' AND';
将
SET @MaTol='[DeptID]='+QUOTENAME(@ID,'''')+' AND'
改成:
SET @MaTol:='[DeptID]=' ¦ ¦ QUOTENAME(@vID,'') ¦ ¦ ' AND';
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 就没错误,什么原因啊?