sqlserver的存储过程转换成oracle版本的,帮忙改下,学习下。

Question

这是我直接按修改调出来的，改版的话写成创建哈，因为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

Answer 1

建议自已动手：
1）varchar类型修改为varchar2;
2）oracle变量命名不要用@开头;
3）oracle赋值使用 :=，而不单是=，并且也没有关键字set；
4）存储过程的入口参数不能指明参数的长度。
5）oracle没有charindex函数，使用instr。
6）声明变量不用declare关键字。
7）oracle分页使用rownum，如取10-20条记录：
select * from (select rownum rn, t1.* from (select * from t where col1='a' order by col2)  t1 where rownum<=20) where rn>=10;

Answer 2

如2楼所言，自己改下好些，能学到东西。帮你写了一部分，其他的你自己改下:create or replace procedure CMS_spReturnPage
(
   str_select    VARCHAR2,
   str_OrderBy   VARCHAR2,
   i_StartRow    NUMBER,
   i_EndRow      NUMBER
)
IS
    collist       VARCHAR2(2000);
    l_where       VARCHAR2(2000);
    i             NUMBER;  
    i2            NUMBER;
    tmp           VARCHAR2(1000);
    l_dec         VARCHAR2(1000);
    f    VARCHAR2(100);
    d             VARCHAR2(100);
    Symbol        char(2);
    l_sql         VARCHAR2(4000);
    l_sort        VARCHAR2(1000);
BEGIN
 
   l_sort := str_OrderBy||', ';
   l_dec  := '';
   l_where  := '';
   l_sql    := '';
   ....

Answer 3

8)过程中 每个语句要以;结束
9)在过程里执行sql语句用execute immediate sqlstr;
10)连接两个字符不是用+而是用||
11)while 要和loop配合使用，并且要以end loop;结束

Answer 4

--看了下 改动了 你自己在看看create or replace PROCEDURE CMS_spReturnPage(Select varchar2,OrderBy varchar2,StartRow int,EndRow int)
AS
ColList varchar2(2000);
Where varchar2(2000);
i int;  
i2 int;
tmp varchar2(1000);
dec varchar2(1000);
f varchar2(100);
d varchar2(100);
Symbol varchar2(2);
SQL varchar2(4000);
Sort varchar2(1000);
cur sys_refcursor;
begin
Sort:=OrderBy||', ';
dec:='';
Where:='';
SQL:='';
i:=instr(Sort,',');
while  i!= 0 loop
tmp:=substring(Sort,1,i-1);
i2:=instr(tmp,' ');
f:=ltrim(rtrim(left(tmp,i2-1)));
d:=ltrim(rtrim(substring(tmp,i2+1,100)));
Sort:=rtrim(ltrim(substring(Sort,i+1,100)));
i:=instr(Sort,',');
select decode(d,'ASC','>','<')||decode(i,0,'=','') into Symbol from dual;
dec:=dec || 'declare '||f || ' sql_variant; ';
ColList:=isnull(replace(replace(colList,'>','='),'<','=') ||' and ','') ||f||Symbol|| ' ' || f;
Where:=Where || ' OR (' || ColList || ') ';
SQL:=SQL || ',' ||f || '= ' ||f;
end loop;
SQL:=dec||' '||'SET ROWCOUNT '||cast(StartRow as varchar2(10))||'; '||'SELECT '||substring(SQL,3)||' FROM (' ||Select || ') a ORDER BY '|| OrderBy || '; ' ||
           'SET ROWCOUNT ' || cast((1 +EndRow -StartRow) as varchar2(10)) || '; ' ||
           'SELECT * FROM (' || Select || ') a WHERE ' || substring(Where,4) || ' ORDER BY ' || OrderBy || '; ' ||
           'SET ROWCOUNT 0;'
open cur for sql;
end;

Answer 5

LZ定义的临时变量中，有where和SQL，这样改下，执行肯定报错啊！