哪位高手能帮小弟我把此SQL存储过程转换成Oracle的

谁能帮我把此SQL存储过程转换成Oracle的

SQL code

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER proc [dbo].[QueryPagedTable] @PageSize    int,@PageNo        int,@Table        sysname,@Filter        nvarchar(1000),@Order        nvarchar(500)as          begin        IF (LEN(@Order) &gt; 0)            SET @Order = ' ORDER BY ' + @Order        ELSE            SET @Order = ' ORDER BY ID '         declare @newSql Nvarchar(max)          set @newSql=''        declare @beginPage as  nvarchar(100) --开始数字        set    @beginPage=(@PageNo*@PageSize)-(@pageSize-1)        declare  @endPage nvarchar(100)        set @endPage=@beginPage+@PageSize-1        set @newSql='select * from (        SELECT Row_Number() over('+ @order +') as rowNum,* from '+@Table+' as aa where  '+@Filter+') as yy        where rowNum BETWEEN '+ @beginPage+' and '+@endPage+ @Order        print @newSql        exec (@newSql)    end

[解决办法]

SQL code

--转换create or replace procedure QueryPagedTable(p_pagesize int,p_pageno int,p_table varchar2,p_filter varchar2,p_order in out varchar2 )isv_sql varchar2(4000) := '';v_begPage varchar2(100) := '';v_endPage varchar2(100) := '';type cur_type is ref cursor;cur cur_type;v_cols varchar2(4000):='';v_col varchar2(30):='';v_sqlcol varchar2(4000):='';begin    if (length(p_order)&gt;0) then        p_order := ' ORDER BY '|| p_order;    else        p_order := ' ORDER BY ID';    end if;    v_begPage := p_pageno*p_pagesize-(p_pagesize-1);    v_endPage := to_number(v_begPage)+p_pagesize-1;    v_sqlcol := 'select column_name from user_tab_columns where table_name=upper('''||p_table||''')';    open cur for v_sqlcol;    loop        fetch cur into v_col;        exit when cur%notfound;        v_cols := v_cols||','||v_col;    end loop;    close cur;    v_sql := 'select * from (        SELECT Row_Number() over('|| p_order ||') rn'||v_cols||' from '||p_table||' aa where  '||p_filter||') yy        where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || p_order;    dbms_output.put_line(v_sql);    execute immediate v_sql;end;/
[解决办法]
如果你的oracle版本是oracle9i以后，直接用sys_refcursor就可以了
[解决办法]
create or replace procedure QueryPagedTable(
p_pagesize int,
p_pageno int,
p_table varchar2,
p_filter varchar2,
p_order in out varchar2  
)
is
v_sql varchar2(4000) := '';
v_begPage varchar2(100) := '';
v_endPage varchar2(100) := '';
type cur_type is ref cursor;
cur cur_type;
v_cols varchar2(4000):='';
v_col varchar2(30):='';
v_sqlcol varchar2(4000):='';
begin
   if (length(p_order)&gt;0) then
       p_order := ' ORDER BY '|| p_order;
   else
       p_order := ' ORDER BY ID';
   end if;
   v_begPage := p_pageno*p_pagesize-(p_pagesize-1);
   v_endPage := to_number(v_begPage)+p_pagesize-1;
   v_sqlcol := 'select column_name from user_tab_columns where table_name=upper('''||p_table||''')';
   open cur for v_sqlcol;
   loop
       fetch cur into v_col;
       exit when cur%notfound;
       v_cols := v_cols||','||v_col;
   end loop;
   close cur;
   v_sql := 'select * from (
       SELECT Row_Number() over('|| p_order ||') rn'||v_cols||' from '||p_table||' aa where  '||p_filter||') yy
       where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || p_order;
   dbms_output.put_line(v_sql);
   execute immediate v_sql;
end;
/

declare  
   v_pagesize int := 10;
   v_pageno int := 2;
   v_table varchar2(50) := 'emp';
   v_filter varchar2(1000) := 'empno=7369'; 
 
   v_order varchar2(500) := 'empno';
begin
   QueryPagedTable(v_pagesize,v_pageno,v_table,v_filter,v_order);
end;


我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html

哪位高手能帮小弟我把此SQL存储过程转

热点推荐