sql转oracle分页存储过程

Question

这个是sqlserver的分页存储过程，帮忙转下oracle的分页存储过程，能测试通过，在线等CREATE PROCEDURE  GetRecordFromPage   
          @tblName            varchar(255),                --   表名 
          @RetColumns         varchar(1000) = '*',         --   需要返回的列，默认为全部   
          @Orderfld           varchar(255),                --   排序字段名   
          @PageSize           int  =  10,                  --   页尺寸   
          @PageIndex          int  =  1,                   --   页码   
          @IsCount            bit  =  0,                   --   返回记录总数,   非   0   值则返回   
          @OrderType          varchar(50)  =  'asc',       --   设置排序类型,   非   asc   值则降序   
          @strWhere           varchar(1000)  =  ''         --   查询条件   (注意:   不要加   where)   
  AS
      
  declare   @strSQL       varchar(1000)          --   主语句   
  declare   @strTmp       varchar(300)           --   临时变量   
  declare   @strOrder     varchar(400)           --   排序类型   
    
  if  @IsCount  !=   0    --执行总数统计
  
    begin
      if @strWhere != ''   
          set   @strSQL   = 'select   count(*)   as   Total   from   ['   +   @tblName   +   ']  where' + @strWhere 
      else
          set   @strSQL   =  'select   count(*)   as   Total   from   ['  +   @tblName   +   ']'
    end
    
  else                    --执行查询操作
  
  begin
            
if  @OrderType  !=   'asc'   
begin   
set   @strTmp   =   '<(select   min'   
set   @strOrder   =   '   order   by   ['   +   @Orderfld   +']   desc'   
end   
else   
begin   
set  @strTmp   =   '>(select   max'  
set  @strOrder   =   '   order   by   ['   +   @Orderfld   +']   asc'  
end   

set  @strSQL   =   'select   top   '  +   str(@PageSize)   +   ' ' + @RetColumns + '    from   ['  
+   @tblName   +   ']   where   ['   +   @Orderfld   +   ']'   +   @strTmp   +   '(['  
+   @Orderfld   +   '])   from   (select   top   '   +   str((@PageIndex-1)*@PageSize)   +   '   ['   
+   @Orderfld   +   ']   from   ['   +   @tblName   +   ']'   +   @strOrder   +   ')   as   tblTmp)'   
+   @strOrder   

if  @strWhere  !=   ''   
set   @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '  from   ['   
+   @tblName   +  ']   where   ['   +   @Orderfld   +   ']'  +   @strTmp   +   '(['   
+   @Orderfld   +   '])   from   (select   top   '   +   str((@PageIndex-1)*@PageSize)   +   '  ['   
+   @Orderfld   +   ']   from   ['   +   @tblName   +   ']   where   ('   +   @strWhere   +   ')   '   
+   @strOrder   +   ')   as   tblTmp)   and   ('   +   @strWhere   +   ')  '   +   @strOrder   

if  @PageIndex   =   1   
begin   
set   @strTmp=''   
if   @strWhere!=''   
set   @strTmp   =   '  where   ('   +   @strWhere   +   ')'   

set   @strSQL   =   'select   top   '  +   str(@PageSize)   +   '   ' + @RetColumns + '   from   [' 
+   @tblName   +   ']'   +   @strTmp   +   '  '  +   @strOrder   
end
 end
    
exec  (@strSQL)

Answer 1

CREATE
PROCEDURE GetRecordFromPage(
    tblName    VARCHAR2,              --   表名
    RetColumns VARCHAR2 DEFAULT '*',  --   需要返回的列，默认为全部
    Orderfld   VARCHAR2,              --   排序字段名
    PageSize   NUMBER DEFAULT 10,     --   页尺寸
    PageIndex  NUMBER DEFAULT 1,      --   页码
    IsCount    NUMBER DEFAULT 0,      --   返回记录总数,   非   0   值则返回
    OrderType  VARCHAR DEFAULT 'asc', --   设置排序类型,   非   asc   值则降序
    strWhere   VARCHAR DEFAULT NULL , --   查询条件   (注意:   不要加   where)
    cur OUT sys_refcursor )
AS
  strSQL VARCHAR(1000); --   主语句
  --strTmp       VARCHAR(300);           --   临时变量
  strOrder VARCHAR(400); --   排序类型
BEGIN
  IF IsCount != 0 THEN --执行总数统计
    OPEN cur FOR 'select   count(*)   as   Total   from   '||tblName||
    CASE
    WHEN strWhere IS NOT NULL THEN
      '  where ' ||strWhere
    END;
  ELSE --执行查询操作
    strOrder := '  order  by  ' ||Orderfld||' ' ||
    CASE orderType
    WHEN 'asc' THEN
      'asc'
    ELSE
      'desc'
    END;
    strSQL := 'select '||retcolumns||' from(' || 'select t.*,rownum rn from' ||'(select '||RetColumns||' from ' ||tblName ||
    CASE
    WHEN strWhere IS NOT NULL THEN
      ' where '||strWhere
    END ||strOrder || ')t where rownum<=:1 ' ||') where rn>:2';
    OPEN cur FOR strsql USING PageSize*PageIndex,PageSize*(PageIndex-1);
  END IF;
END;

Answer 2

CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size　　int, --the size of a page of list
　　　　　　　　　　　　　　　　　　　　　　 v_current_page int, --the current page of list
　　　　　　　　　　　　　　　　　　　　　　 v_table_name varchar2, --the talbe name
　　　　　　　　　　　　　　　　　　　　　　 v_order_field　varchar2,--the order field
　　　　　　　　　　　　　　　　　　　　　　 v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank.
　　　　　　　　　　　　　　　　　　　　　　 --v_sql_select　 varchar2, --the select sql for procedure
　　　　　　　　　　　　　　　　　　　　　　 --v_sql_count　　varchar2, --the count sql for procedure
　　　　　　　　　　　　　　　　　　　　　　 --v_out_recordcount OUT int, --the num of return rows
　　　　　　　　　　　　　　　　　　　　　　 p_cursor OUT refcursor_pkg.return_cursor) as
　v_sql　　　　 varchar2(3000); --the sql for select all rows of list
　v_sql_count　 varchar2(3000); --the count sql for procedure
　v_sql_order　 varchar2(2000); --the order of list
　v_count　　　 int; -- the amount rows fo original list
　v_endrownum　 int; --the end row num of the current page
　v_startrownum int; --the start row num of the current page
BEGIN
　----set the order of list
　if　v_order_field!='NO' then
　　v_sql_order :=' ORDER BY '|| v_order_field ||' '||v_order_sequence;
　else
　　 v_sql_order :='';
　end if;
　----catch the amount rows of list
　v_sql_count:='SELECT COUNT(ROWNUM) FROM '||v_table_name;
　execute immediate v_sql_count into v_count;
　-- v_out_recordcount := v_count;
　----set the value of start and end row
　if v_order_sequence='desc' then
　　v_endrownum:=v_count-(v_current_page-1)*v_page_size;
　　v_startrownum:=v_endrownum - v_page_size + 1;
　else
　　v_endrownum:= v_current_page * v_page_size;
　　v_startrownum := v_endrownum - v_page_size + 1;
　end if;
　----the sql for page slide
　v_sql := 'SELECT * FROM (SELECT '||v_table_name||'.*, rownum rn FROM '||v_table_name||' WHERE rownum <= ' ||
　　　　　 to_char(v_endrownum) ||' '|| v_sql_order||')　WHERE rn >= ' ||
　　　　　 to_char(v_startrownum)||' '||v_sql_order;
　open p_cursor for v_sql;
END TABLEPAGE_SELECT;
备注：输入order by 的sqeuence是，应该为“ desc”或者“ asc”
　　若输入两个order by则，v_order_field=" a[sequence] ,order by b "

Answer 3

改下
CREATE or replace
PROCEDURE GetRecordFromPage(
    tblName    VARCHAR2,              --   表名
    RetColumns VARCHAR2 DEFAULT 't.*',  --   需要返回的列，默认为全部
    Orderfld   VARCHAR2,              --   排序字段名
    PageSize   NUMBER DEFAULT 10,     --   页尺寸
    PageIndex  NUMBER DEFAULT 1,      --   页码
    IsCount    NUMBER DEFAULT 0,      --   返回记录总数,   非   0   值则返回
    OrderType  VARCHAR DEFAULT 'asc', --   设置排序类型,   非   asc   值则降序
    strWhere   VARCHAR DEFAULT NULL , --   查询条件   (注意:   不要加   where)
    cur OUT sys_refcursor )
AS
  strSQL VARCHAR(1000); --   主语句
  --strTmp       VARCHAR(300);           --   临时变量
  strOrder VARCHAR(400); --   排序类型
BEGIN
  IF IsCount != 0 THEN --执行总数统计
    OPEN cur FOR 'select   count(*)   as   Total   from   '||tblName||
    CASE
    WHEN strWhere IS NOT NULL THEN
      '  where ' ||strWhere
    END;
  ELSE --执行查询操作
    strOrder := '  order  by  ' ||Orderfld||' ' ||
    CASE orderType
    WHEN 'asc' THEN
      'asc'
    ELSE
      'desc'
    END;
    strSQL := 'select '||retcolumns||' from (select '||RetColumns||','
      ||'row_number()over('||strOrder||') rn from ' ||tblName ||' t'||
    CASE
    WHEN strWhere IS NOT NULL THEN
      ' where '||strWhere
    END ||strOrder || ') where rn<=:1 and rn>:2';
    OPEN cur FOR strsql USING PageSize*PageIndex,PageSize*(PageIndex-1);
  END IF;
END;
测试
variable c1 refcursor;
BEGIN
GetRecordFromPage(tblName=>'emp',RetColumns=>'empno,ename,sal',Orderfld=>'sal',PageSize=>5,
  PageIndex=>2,strWhere=>'comm is null',OrderType=>'desc',cur=>:c1);
END;
/
print c1;
匿名块已完成
C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPNO                  ENAME      SAL                    
---------------------- ---------- ---------------------- 
7782                   CLARK      2450                   
7934                   MILLER     1300                   
7876                   ADAMS      1100                   
7900                   JAMES      950                    
7369                   SMITH      800                    

Answer 4

他的代码只能在plsql plus里面调用

Answer 5

...那个测试代码print是要在sqlplus或oracle sql developer上调用
不过只是为了测试，分页过程用游标返回结果集。就看你的程序怎么处理这个游标。c#我不会，java能够很好地处理
由于是拼动态语句，所以只能使用动态游标来返回结果。要不就在程序端写个过程来拼好语句再传入后台查询

Answer 6

那用java写个动态拼接的sql语句啊

Answer 7

在java里把这个存储过程改成一个函数传参数进去，经过拼接返回一条sql语句出来，怎么写啊 8楼

Answer 8

java我不会写，可以去java区问问

Answer 9

自己试试吧，超出oracle的范围了
无非就是if else 的判断来拼字符串，什么语言都能轻松做到
上面的case when也是类似于if else的判断