Oracle自用万能分页排序存储过程，LigerComBox，LigerGrid中用起来不错，尚存很多瑕疵

最新推荐文章于 2022-08-20 15:27:36 发布

琴声何来

最新推荐文章于 2022-08-20 15:27:36 发布

阅读量139

点赞数

分类专栏：菜鸟笔记：可复用代码

本文链接：https://blog.youkuaiyun.com/weixin_42458407/article/details/102603316

版权

菜鸟笔记：可复用代码专栏收录该内容

3 篇文章

订阅专栏

CREATE OR REPLACE PACKAGE PAGING AS

  type stu_cursor is ref cursor;
  procedure PagingAndSorting(tablename in varchar2,selectcolumn in varchar2,condition in varchar2,page in number,pagesize in number,sortname in varchar2,sortorder varchar2,stulist out stu_cursor,cnt out number);

END PAGING;

CREATE OR REPLACE PACKAGE BODY PAGING AS
--对任意表的所有数据进行分页排序输出 参数为表名，要查的列名（用逗号隔开），查询条件（where从句不写where），当前页，每页行数，排序列名（只支持单列），排序顺序，返回结果表，返回结果总行数
  procedure PagingAndSorting(tablename in varchar2,selectcolumn in varchar2,condition in varchar2,page in number,pagesize in number,sortname in varchar2,sortorder varchar2,stulist out stu_cursor,cnt out number) AS
  sqlstr varchar2(600);
  sqlcondition varchar2(300);
  sqlcolumn varchar2(200):=null;
  start_index number(6,0):=0;
  end_index number(6,0):=0;
  tablenameTemp varchar2(200):=null;
  conditionTemp varchar2(300):=null;
  BEGIN
    --查询条件
    if condition=null or condition=''
      then sqlcondition:=' ';
    else
      sqlcondition:='where '||condition;
    end if;
    --需要查询的字段
    if selectcolumn=null or selectcolumn='' or selectcolumn='*'
      then sqlcolumn:=' stu.* ';
      tablenameTemp:=tablename;
    else
      if instr(selectcolumn,'DISTINCT ',1,1)>0
      then
        tablenameTemp:='(select '||selectcolumn||' from '||tablename||' '||sqlcondition||')';
        conditionTemp:=substr(selectcolumn,instr(selectcolumn,'DISTINCT ',1,1)+9);
      else
        tablenameTemp:='(select * from '||tablename||' '||sqlcondition||')';
        conditionTemp:=selectcolumn;
      end if;
      loop
      end_index:=instr(conditionTemp,',',start_index,1);
      dbms_output.put_line(end_index);
      exit when end_index<1;
      sqlcolumn:=sqlcolumn||' '||substr(conditionTemp,start_index+1,end_index-start_index);
      start_index:=end_index;
      end loop;
      dbms_output.put_line(sqlcolumn);
      sqlcolumn:=sqlcolumn||' '||substr(conditionTemp,start_index+1);
      dbms_output.put_line(sqlcolumn);
    end if;
    sqlstr:='select count(*) from '||tablenameTemp;
    EXECUTE IMMEDIATE sqlstr into cnt;  --总行数
    sqlstr:='select * from (select '||sqlcolumn||',rownum r from '||tablenameTemp||' stu '||' order by '||sortname||' '||sortorder||') org where org.r between '||to_char((page-1)*pagesize+1)||' and '||to_char(page*pagesize);
    dbms_output.put_line(sqlstr);
    open stulist for sqlstr;

  END PagingAndSorting;
END PAGING;