set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[singleKick]
@start nvarchar(20),--开始日期
@end nvarchar(20),--结束日期
@strSplitString nvarchar(1000),--要拆分的字符串
@stationid nvarchar(2),--监测站
@centerid nvarchar(2), --数据中心
@para varchar(10), --判断查询哪一年
@pageCount int, --页数
@pageSize int, --每页大小
@ifNeedTotal varchar(5)as
declare @pageVal int --第几条
declare @startyear nvarchar(4) --开始年
declare @endyear nvarchar(4) --结束年
declare @pageCountVar varchar(10)
declare @pageSizeVar varchar(10)
declare @pageValVar varchar(10)set @pageSizeVar = @pageSize
set @pageVal = (@pageCount-1)*@pageSize
set @pageValVar = @pageVal
set @startyear = substring(@start,1,5)
set @endyear = substring(@end,1,5)
declare @finalVal nvarchar(1000)
declare @sqlsentence nvarchar(3000)
declare @strSplit nvarchar(2)--拆分的根据条件
declare @ifvalue nvarchar(6)
declare @columon int --自增长来定列名
declare @val varchar(10)
set @strSplit = ','
declare @location int
declare @strat int
declare @resultString nvarchar(500) --存储拆分后的字符
declare @resultString1 nvarchar(500) --存储拆分后的字符
declare @ret nvarchar(100)
declare @ret1 nvarchar(100)select @ifvalue = status from T_Item_Calibrate where cast(@start as datetime) = CONVERT(varchar(100), starttime, 23) and cast(@end as datetime) = CONVERT(varchar(100), endtime, 23)
declare @correctVal nvarchar(10)
set @correctVal = '1'
if @ifvalue = 1
begin
select @correctVal = CorrectValue from T_Item_Calibrate where cast(@start as datetime) = CONVERT(varchar(100), starttime, 23) and cast(@end as datetime) = CONVERT(varchar(100), endtime, 23)
end
--SET @strSplitString = @strSplit + @strSplitString + @strSplit
Set @location = CHARINDEX(@strSplit,@strSplitString)
set @resultString = ''
set @columon = 0
WHILE @location <> 0
BEGIN SET @strat = @location
SET @location = CHARINDEX(@strSplit,@strSplitString,@strat+1)
IF @location > 0
BEGIN
set @columon = @columon+1
set @val = @columon
begin
SET @ret = @correctVal+'*a.['+@stationid+'_'+subString(@strSplitString,@strat+1,@location-@strat-1)+'] as col'+@val+' ,'--拆分后的字符
set @resultString = @resultString + @ret
end
END
ENDset @finalVal = substring(@resultString,1,len(@resultString) - 1)declare @sql varchar(1000)
if @ifNeedTotal like 'no'
begin
if @startyear = @endyear
if @pageCount!=1
begin
set @sql = 'select top '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@end+'''and gettime not in( select top '+@pageValVar+' gettime from T_Center_'+@centerid+'_'+@startyear+')'
end
else
begin
set @sql = 'select top '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@end+''''
end
else
begin
if @pageCount!=1
begin
if @para = 'firststep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@startyear+'-12-31''''and gettime not in( select top '+@pageValVar+' gettime from T_Center_'+@centerid+'_'+@startyear+')'
end
if @para = 'secondstep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@endyear+' as a where a.GetTime >= '''+@startyear+'-1-1'' and a.GetTime <= '''+@end+'''and gettime not in( select top '+@pageValVar+' gettime from T_Center_'+@centerid+'_'+@startyear+')'
end
end
else
begin
if @para = 'firststep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@startyear+'-12-31'''
end
if @para = 'secondstep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@endyear+' as a where a.GetTime >= '''+@startyear+'-1-1'' and a.GetTime <= '''+@end+''''
end
end
end
end
else
begin
if @startyear = @endyear
begin
set @sql = 'select CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@end+''''
end
else
begin
if @para = 'firststep'
begin
set @sql = 'select CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@startyear+'-12-31'''
end
if @para = 'secondstep'
begin
set @sql = 'select CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@endyear+' as a where a.GetTime >= '''+@startyear+'-1-1'' and a.GetTime <= '''+@end+''''
end
end
end
print @sql
exec(@sql)
不知该怎么改啊,各位大侠帮帮忙。
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[singleKick]
@start nvarchar(20),--开始日期
@end nvarchar(20),--结束日期
@strSplitString nvarchar(1000),--要拆分的字符串
@stationid nvarchar(2),--监测站
@centerid nvarchar(2), --数据中心
@para varchar(10), --判断查询哪一年
@pageCount int, --页数
@pageSize int, --每页大小
@ifNeedTotal varchar(5)as
declare @pageVal int --第几条
declare @startyear nvarchar(4) --开始年
declare @endyear nvarchar(4) --结束年
declare @pageCountVar varchar(10)
declare @pageSizeVar varchar(10)
declare @pageValVar varchar(10)set @pageSizeVar = @pageSize
set @pageVal = (@pageCount-1)*@pageSize
set @pageValVar = @pageVal
set @startyear = substring(@start,1,5)
set @endyear = substring(@end,1,5)
declare @finalVal nvarchar(1000)
declare @sqlsentence nvarchar(3000)
declare @strSplit nvarchar(2)--拆分的根据条件
declare @ifvalue nvarchar(6)
declare @columon int --自增长来定列名
declare @val varchar(10)
set @strSplit = ','
declare @location int
declare @strat int
declare @resultString nvarchar(500) --存储拆分后的字符
declare @resultString1 nvarchar(500) --存储拆分后的字符
declare @ret nvarchar(100)
declare @ret1 nvarchar(100)select @ifvalue = status from T_Item_Calibrate where cast(@start as datetime) = CONVERT(varchar(100), starttime, 23) and cast(@end as datetime) = CONVERT(varchar(100), endtime, 23)
declare @correctVal nvarchar(10)
set @correctVal = '1'
if @ifvalue = 1
begin
select @correctVal = CorrectValue from T_Item_Calibrate where cast(@start as datetime) = CONVERT(varchar(100), starttime, 23) and cast(@end as datetime) = CONVERT(varchar(100), endtime, 23)
end
--SET @strSplitString = @strSplit + @strSplitString + @strSplit
Set @location = CHARINDEX(@strSplit,@strSplitString)
set @resultString = ''
set @columon = 0
WHILE @location <> 0
BEGIN SET @strat = @location
SET @location = CHARINDEX(@strSplit,@strSplitString,@strat+1)
IF @location > 0
BEGIN
set @columon = @columon+1
set @val = @columon
begin
SET @ret = @correctVal+'*a.['+@stationid+'_'+subString(@strSplitString,@strat+1,@location-@strat-1)+'] as col'+@val+' ,'--拆分后的字符
set @resultString = @resultString + @ret
end
END
ENDset @finalVal = substring(@resultString,1,len(@resultString) - 1)declare @sql varchar(1000)
if @ifNeedTotal like 'no'
begin
if @startyear = @endyear
if @pageCount!=1
begin
set @sql = 'select top '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@end+'''and gettime not in( select top '+@pageValVar+' gettime from T_Center_'+@centerid+'_'+@startyear+')'
end
else
begin
set @sql = 'select top '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@end+''''
end
else
begin
if @pageCount!=1
begin
if @para = 'firststep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@startyear+'-12-31''''and gettime not in( select top '+@pageValVar+' gettime from T_Center_'+@centerid+'_'+@startyear+')'
end
if @para = 'secondstep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@endyear+' as a where a.GetTime >= '''+@startyear+'-1-1'' and a.GetTime <= '''+@end+'''and gettime not in( select top '+@pageValVar+' gettime from T_Center_'+@centerid+'_'+@startyear+')'
end
end
else
begin
if @para = 'firststep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@startyear+'-12-31'''
end
if @para = 'secondstep'
begin
set @sql = 'select '+@pageSizeVar+' CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@endyear+' as a where a.GetTime >= '''+@startyear+'-1-1'' and a.GetTime <= '''+@end+''''
end
end
end
end
else
begin
if @startyear = @endyear
begin
set @sql = 'select CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@end+''''
end
else
begin
if @para = 'firststep'
begin
set @sql = 'select CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.GetTime <= '''+@startyear+'-12-31'''
end
if @para = 'secondstep'
begin
set @sql = 'select CONVERT(varchar(100), a.gettime, 23) as gettimes,'+@finalVal+' from T_Center_'+@centerid+'_'+@endyear+' as a where a.GetTime >= '''+@startyear+'-1-1'' and a.GetTime <= '''+@end+''''
end
end
end
print @sql
exec(@sql)
不知该怎么改啊,各位大侠帮帮忙。
最好是,把相关表也重新设计。比如:
T_Item_Calibrate表里面的时间字段,别再用varchar了,改成date
还有这个过程里面的v_pageSizeVar,v_pageValVar这些明显的数字字段,为什么要在过程里面声明成varchar类型,在oracle里面设计的时候换成number或者pls_integer吧。这个过程,看看oracle语法,改起来不难。另,
最好把过程要实现的目的,过程用到的表在oracle里面的表结构也一起发出来。
v_start in date,--开始日期
v_end in date,--结束日期
v_strSplitString in varchar2,--要拆分的字符串
v_stationid in varchar2,--监测站
v_centerid in varchar2, --数据中心
v_para in varchar2, --判断查询哪一年
v_pageCount in number, --页数
v_pageSize in number, --每页大小
v_ifNeedTotal in varchar2,
v_o_cur out sys_refcursor)
as
v_pageVal number(10) := (v_pageCount - 1) * v_pageSize; --第几条
v_startyear varchar2(4) := to_char(v_start,'yyyy'); --开始年
v_endyear varchar2(4) := to_char(v_end,'yyyy'); --结束年
v_pageCountVar varchar2(10);
v_pageSizeVar number(10) := v_pageSize;
v_pageValVar number(10) := v_pageVal;
v_finalVal varchar2(1000) ;
v_sqlsentence varchar2(3000) ;
v_strSplit varchar2(2) := ',';--拆分的根据条件
v_ifvalue varchar2(6) ;
v_columon number := 0;--自增长来定列名
v_val varchar2(10) ;
v_location number(10);
v_strat number(10);
v_resultString varchar2(500) := ' ';
v_resultString1 varchar2(500);
v_ret varchar2(100);
v_ret1 varchar2(100);
v_correctVal varchar2(10) := '1';
v_i pls_integer := 1;
v_sql varchar2(4000);begin
select status, decode(status,1,CorrectValue,'1') into v_ifvalue, v_correctVal
from T_Item_Calibrate
where v_start = starttime
and v_end = endtime; --SET v_strSplitString = v_strSplit + v_strSplitString + v_strSplit
v_location := instr(v_strSplitString,v_strSplit,v_i); WHILE v_location > 0 LOOP
v_strat := v_location;
v_i := v_i + 1;
v_location := instr(v_strSplitString,v_strSplit,v_i);
v_columon := vcolumon + 1;
v_val := v_columon;
v_ret := v_correctVal
||'*a.['
||v_stationid
||'_'
||subString(v_strSplitString,v_strat+1,v_location-v_strat-1)
||'] as col'
||v_val
||' ,';--拆分后的字符
v_resultString := v_resultString + v_ret ;
v_finalVal := substring(v_resultString,1,length(v_resultString) - 1); if v_ifNeedTotal = 'no' then
if v_startyear = v_endyear then
if v_pageCount!=1 then
v_sql := '..';
end if;
end if;
end if;
end loop;
open v_o_cur for v_sql;
end;
拼接v_sql的部分没改了,另外,在oracle里面没有top关键字,分页用rownum来实现。
具体可以google之。
v_start in date,--开始日期
v_end in date,--结束日期
v_strSplitString in varchar2,--要拆分的字符串
v_stationid in varchar2,--监测站
v_centerid in varchar2, --数据中心
v_para in varchar2, --判断查询哪一年
v_pageCount in number, --页数
v_pageSize in number, --每页大小
v_ifNeedTotal in varchar2,
v_o_cur out sys_refcursor)
as
v_pageVal number(10) := (v_pageCount - 1) * v_pageSize; --第几条
v_startyear varchar2(4) := to_char(v_start,'yyyy'); --开始年
v_endyear varchar2(4) := to_char(v_end,'yyyy'); --结束年
v_pageCountVar varchar2(10);
v_pageSizeVar number(10) := v_pageSize;
v_pageValVar number(10) := v_pageVal;
v_finalVal varchar2(1000) ;
v_sqlsentence varchar2(3000) ;
v_strSplit varchar2(2) := ',';--拆分的根据条件
v_ifvalue varchar2(6) ;
v_columon number := 0;--自增长来定列名
v_val varchar2(10) ;
v_location number(10);
v_strat number(10);
v_resultString varchar2(500) := ' ';
v_resultString1 varchar2(500);
v_ret varchar2(100);
v_ret1 varchar2(100);
v_correctVal varchar2(10) := '1';
v_i pls_integer := 1;
v_sql varchar2(4000);begin
select status, decode(status,1,CorrectValue,'1') into v_ifvalue, v_correctVal
from T_Item_Calibrate
where v_start = starttime
and v_end = endtime; --SET v_strSplitString = v_strSplit + v_strSplitString + v_strSplit
v_location := instr(v_strSplitString,v_strSplit,v_i); WHILE v_location > 0 LOOP
v_strat := v_location;
v_i := v_i + 1;
v_location := instr(v_strSplitString,v_strSplit,v_i);
v_columon := vcolumon + 1;
v_val := v_columon;
v_ret := v_correctVal
||'*a.['
||v_stationid
||'_'
||subString(v_strSplitString,v_strat+1,v_location-v_strat-1)
||'] as col'
||v_val
||' ,';--拆分后的字符
v_resultString := v_resultString + v_ret ;
v_finalVal := substring(v_resultString,1,length(v_resultString) - 1); if v_ifNeedTotal = 'no' then
if v_startyear = v_endyear then
if v_pageCount!=1 then
v_sql := '..';
end if;
end if;
end if;
end loop;
open v_o_cur for v_sql;
end;
v_start in date,--开始日期
v_end in date,--结束日期
v_strSplitString in varchar2,--要拆分的字符串
v_stationid in varchar2,--监测站
v_centerid in varchar2, --数据中心
v_para in varchar2, --判断查询哪一年
v_pageCount in number, --页数
v_pageSize in number, --每页大小
v_ifNeedTotal in varchar2,
v_o_cur out sys_refcursor)
as
v_pageVal number(10) := (v_pageCount - 1) * v_pageSize; --第几条
v_startyear varchar2(4) := to_char(v_start,'yyyy'); --开始年
v_endyear varchar2(4) := to_char(v_end,'yyyy'); --结束年
v_pageCountVar varchar2(10);
v_pageSizeVar number(10) := v_pageSize;
v_pageValVar number(10) := v_pageVal;
v_finalVal varchar2(1000) ;
v_sqlsentence varchar2(3000) ;
v_strSplit varchar2(2) := ',';--拆分的根据条件
v_ifvalue varchar2(6) ;
v_columon number := 0;--自增长来定列名
v_val varchar2(10) ;
v_location number(10);
v_strat number(10);
v_resultString varchar2(500) := ' ';
v_resultString1 varchar2(500);
v_ret varchar2(100);
v_ret1 varchar2(100);
v_correctVal varchar2(10) := '1';
v_i pls_integer := 1;
v_sql varchar2(4000);begin
select status, decode(status,1,CorrectValue,'1') into v_ifvalue, v_correctVal
from T_Item_Calibrate
where v_start = starttime
and v_end = endtime; --SET v_strSplitString = v_strSplit + v_strSplitString + v_strSplit
v_location := instr(v_strSplitString,v_strSplit,v_i); WHILE v_location > 0 LOOP
v_strat := v_location;
v_i := v_i + 1;
v_location := instr(v_strSplitString,v_strSplit,v_i);
v_columon := vcolumon + 1;
v_val := v_columon;
v_ret := v_correctVal
||'*a.['
||v_stationid
||'_'
||subString(v_strSplitString,v_strat+1,v_location-v_strat-1)
||'] as col'
||v_val
||' ,';--拆分后的字符
v_resultString := v_resultString + v_ret ;
v_finalVal := substring(v_resultString,1,length(v_resultString) - 1); if v_ifNeedTotal = 'no' then
if v_startyear = v_endyear then
if v_pageCount!=1 then
v_sql := '..';
end if;
end if;
end if;
end loop;
open v_o_cur for v_sql;
end;