非常感谢各位了,帮忙给改成oracle的吧,我oracle一点不会
改完之后我也可以学习一下。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[CMS_spReturnPage](@Select varchar(8000), @OrderBy varchar(1000),
@StartRow int, @EndRow int)
AS
BEGIN
declare @ColList varchar(2000);
declare @Where varchar(2000);
declare @i int;
declare @i2 int;
declare @tmp varchar(1000);
declare @dec varchar(1000);
declare @f varchar(100);
declare @d varchar(100);
declare @Symbol char(2);
declare @SQL varchar(5000);
declare @Sort varchar(1000);
set @Sort = @OrderBy + ', '
set @dec = ''
set @Where = ''
set @SQL = ''
set @i = charindex(',' , @Sort)
while @i != 0
begin
set @tmp = left(@Sort,@i-1)
set @i2 = charindex(' ', @tmp)
set @f = ltrim(rtrim(left(@tmp,@i2-1)))
set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
set @i = charindex(',', @Sort)
set @symbol = case when @d = 'ASC' then '>' else '<' end +
case when @i=0 then '=' else '' end
set @dec = @dec + 'declare @' + @f + ' sql_variant; '
set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +
@f + @Symbol + ' @' + @f
set @Where = @Where + ' OR (' + @ColList + ') '
set @SQL = @SQL + ', @' + @f + '= ' + @f
end
set @SQL = @dec + ' ' +
'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
'SELECT ' + substring(@SQL,3,7000) + ' FROM (' + @Select + ') a ORDER BY '+ @OrderBy + '; ' +
'SET ROWCOUNT ' + convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
'SELECT * FROM (' + @Select + ') a WHERE ' + substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; ' +
'SET ROWCOUNT 0;'
exec(@SQL)
END
改完之后我也可以学习一下。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[CMS_spReturnPage](@Select varchar(8000), @OrderBy varchar(1000),
@StartRow int, @EndRow int)
AS
BEGIN
declare @ColList varchar(2000);
declare @Where varchar(2000);
declare @i int;
declare @i2 int;
declare @tmp varchar(1000);
declare @dec varchar(1000);
declare @f varchar(100);
declare @d varchar(100);
declare @Symbol char(2);
declare @SQL varchar(5000);
declare @Sort varchar(1000);
set @Sort = @OrderBy + ', '
set @dec = ''
set @Where = ''
set @SQL = ''
set @i = charindex(',' , @Sort)
while @i != 0
begin
set @tmp = left(@Sort,@i-1)
set @i2 = charindex(' ', @tmp)
set @f = ltrim(rtrim(left(@tmp,@i2-1)))
set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
set @i = charindex(',', @Sort)
set @symbol = case when @d = 'ASC' then '>' else '<' end +
case when @i=0 then '=' else '' end
set @dec = @dec + 'declare @' + @f + ' sql_variant; '
set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +
@f + @Symbol + ' @' + @f
set @Where = @Where + ' OR (' + @ColList + ') '
set @SQL = @SQL + ', @' + @f + '= ' + @f
end
set @SQL = @dec + ' ' +
'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
'SELECT ' + substring(@SQL,3,7000) + ' FROM (' + @Select + ') a ORDER BY '+ @OrderBy + '; ' +
'SET ROWCOUNT ' + convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
'SELECT * FROM (' + @Select + ') a WHERE ' + substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; ' +
'SET ROWCOUNT 0;'
exec(@SQL)
END
不着调 SQL与O的区别
我也很久没用了,现改了一部分,剩下的LZ自己补充吧,顺便学习下Oracle也好。
create or replace procedure p_cms_spreturnpage
(
str_select varchar2,
str_orderby varchar2,
i_startrow number,
i_endrow number
)
is
ColList varchar2(2000);
str_where varchar2(2000);
i number;
i2 number;
tmp varchar2(1000);
str_dec varchar2(1000);
f varchar2(100);
d varchar2(100);
Symbol varchar2(2);
str_sql varchar2(4000);
l_str_sort varchar2(1000);
cur sys_refcursor;
begin l_str_sort :=str_orderby||', ';
str_dec :='';
str_where :='';
str_sql :='';
i :=instr(l_str_sort,',');
while i!= 0 loop
tmp :=substr(l_str_sort,1,i-1);
i2 :=instr(tmp,' ');
f :=ltrim(rtrim(left(tmp,i2-1)));
d :=ltrim(rtrim(substr(tmp,i2+1,100)));
l_str_sort :=rtrim(ltrim(substr(l_str_sort,i+1,100)));
i :=instr(l_str_sort,',');
....
end loop;
str_sql:='.......'; open cur for str_sql;
end;