SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM subscriber ORDER BY servernumber) a WHERE region=517 and ROWNUM <= 500) WHERE rn > 480 ; 参考一下!
--提供一个参考 create or replace procedure fenye_pro( v_tablename varchar2, --表名 v_ordercol varchar2,--要排序的字段 如果有多个用,隔开 v_pagesize int, --一页显示的记录数 v_pagenow int,--要显示第几页 v_pagerows out number,--总页数 v_counts out number,--总记录数 recode_cursor out sys_refcursor, v_order varchar2 default 'asc') --降序还是升序 默认是升序 as v_begin number:=1+(v_pagenow-1)*v_pagesize; v_end number:=v_pagenow*v_pagesize; v_sqlstr varchar2(4000); v_flag number:=0; begin select count(*) into v_flag from user_tables where table_name=upper(v_tablename); if v_flag=0 then dbms_output.put_line('输入的表'||v_tablename||'不存在'); else v_sqlstr:='select count(*) from '||v_tablename; execute immediate v_sqlstr into v_counts; v_pagerows:=ceil(v_counts/v_pagesize); v_sqlstr:='select * from (select rownum rn,t.* from (select * from '||v_tablename|| ' order by '||v_ordercol||' '||v_order||') t where rownum<='||v_end||') where rn>='||v_begin; open recode_cursor for v_sqlstr; --dbms_output.put_line(v_sqlstr); end if; exception when others then dbms_output.put_line('参数输入格式或类型不符'); end;--调用分页过程var cnt number; var pagerow number; var r_c refcursor; exec fenye_pro('emp','sal desc,hiredate',4,1,:cnt,:pagerow,:r_c); print r_c; RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 1 7777 aspen SALESMAN 10 2 7839 KING PRESIDENT 1981-11-17 5000 10 3 7902 FORD ANALYST 7566 1981-12-03 3000 900 20 4 7788 SCOTT ANALYST 7566 1987-04-19 3000 900 20
create or replace procedure Paging (countPerPgae in number, pageNum in number,res_cur out sys_refcursor) as total number(10,2); pageTotal number(10,2); sqlStr varchar2(4000); begin select count(*) into total from t_admin; pageTotal := trunc(total/countPerPgae)+1; sqlStr := 'select * from (select temp.*, rownum num from (select * from t_admin order by admin_id desc)temp) where num>'||to_char(countPerPgae)||'*('||to_char(pageNum)||'-1) and num<'||to_char(countPerPgae)||'*('||to_char(pageNum)||'+1)-('||to_char(pageNum)||'-1)'; open res_cur for sqlStr; end;
Oracle的分页 select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;在分页的时候可以把以上的sql语句当做模板使用create or replace package testpackage astype test_cursor is ref cursor;end testpackage; create or replace procedure fenye(tablename in varchar2,pagesize in number,pagenow in number,myrows out number,mypagecount out number,p_cursor out testpackage.test_cursor) isv_sql varchar2(1000);v_begin number:= (pagenow-1)*pagesize+1;v_end number:=pagenow*pagesize;beginv_sql:='select * from (select t1.*,rownum rn from (select * from '|| tablename ||' order by sal) t1 where rownum<='|| v_end ||') where rn>='|| v_begin;open p_cursor for v_sql;--组装sql语句v_sql:='select count(*) from ' || tablename;--执行sql,并把返回值赋给myrowsexecute immadiate v_sql into myrows;if mod(myrows,pagesize)=0 thenmypagecount:=myrows/pagesize;elsemypagecount:=myrows/pagesize=1;end if;close p_cursor;end;
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT * FROM subscriber
ORDER BY servernumber) a
WHERE region=517 and ROWNUM <= 500)
WHERE rn > 480 ;
参考一下!
create or replace procedure fenye_pro(
v_tablename varchar2, --表名
v_ordercol varchar2,--要排序的字段 如果有多个用,隔开
v_pagesize int, --一页显示的记录数
v_pagenow int,--要显示第几页
v_pagerows out number,--总页数
v_counts out number,--总记录数
recode_cursor out sys_refcursor,
v_order varchar2 default 'asc') --降序还是升序 默认是升序
as
v_begin number:=1+(v_pagenow-1)*v_pagesize;
v_end number:=v_pagenow*v_pagesize;
v_sqlstr varchar2(4000);
v_flag number:=0;
begin
select count(*) into v_flag from user_tables where table_name=upper(v_tablename);
if v_flag=0 then
dbms_output.put_line('输入的表'||v_tablename||'不存在');
else
v_sqlstr:='select count(*) from '||v_tablename;
execute immediate v_sqlstr into v_counts;
v_pagerows:=ceil(v_counts/v_pagesize);
v_sqlstr:='select * from (select rownum rn,t.* from (select * from '||v_tablename||
' order by '||v_ordercol||' '||v_order||') t where rownum<='||v_end||') where rn>='||v_begin;
open recode_cursor for v_sqlstr;
--dbms_output.put_line(v_sqlstr);
end if;
exception
when others then
dbms_output.put_line('参数输入格式或类型不符');
end;--调用分页过程var cnt number;
var pagerow number;
var r_c refcursor;
exec fenye_pro('emp','sal desc,hiredate',4,1,:cnt,:pagerow,:r_c); print r_c;
RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
1 7777 aspen SALESMAN 10
2 7839 KING PRESIDENT 1981-11-17 5000 10
3 7902 FORD ANALYST 7566 1981-12-03 3000 900 20
4 7788 SCOTT ANALYST 7566 1987-04-19 3000 900 20
create or replace
procedure Paging
(countPerPgae in number, pageNum in number,res_cur out sys_refcursor)
as
total number(10,2);
pageTotal number(10,2);
sqlStr varchar2(4000);
begin
select count(*) into total from t_admin;
pageTotal := trunc(total/countPerPgae)+1;
sqlStr := 'select * from (select temp.*, rownum num from (select * from t_admin order by admin_id desc)temp) where num>'||to_char(countPerPgae)||'*('||to_char(pageNum)||'-1) and num<'||to_char(countPerPgae)||'*('||to_char(pageNum)||'+1)-('||to_char(pageNum)||'-1)';
open res_cur for sqlStr;
end;