CREATEORREPLACE PACKAGE PAGING AStype 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;
CREATEORREPLACE 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=nullor condition=''then sqlcondition:=' ';else
sqlcondition:='where '||condition;endif;--需要查询的字段if selectcolumn=nullor selectcolumn=''or selectcolumn='*'then sqlcolumn:=' stu.* ';
tablenameTemp:=tablename;elseif instr(selectcolumn,'DISTINCT ',1,1)>0then
tablenameTemp:='(select '||selectcolumn||' from '||tablename||' '||sqlcondition||')';
conditionTemp:=substr(selectcolumn,instr(selectcolumn,'DISTINCT ',1,1)+9);else
tablenameTemp:='(select * from '||tablename||' '||sqlcondition||')';
conditionTemp:=selectcolumn;endif;loop
end_index:=instr(conditionTemp,',',start_index,1);
dbms_output.put_line(end_index);exitwhen end_index<1;
sqlcolumn:=sqlcolumn||' '||substr(conditionTemp,start_index+1,end_index-start_index);
start_index:=end_index;endloop;
dbms_output.put_line(sqlcolumn);
sqlcolumn:=sqlcolumn||' '||substr(conditionTemp,start_index+1);
dbms_output.put_line(sqlcolumn);endif;
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;
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 numbe...