oracle分页程序

  --分页程序 in_num 每页多少条记录，in_page 页码
  create or replace procedure zhl_2(in_num number, in_page number) is
  in_totalpage   number := 0; --总页数
  in_totalrecord number := 0; --总记录数
  startrecord    number := 0; --开始记录数
  endrecord      number := 0; --结尾记录数
  type red is record(
    id   zhl_table.id%type,
    name zhl_table.name%type);
  type red_array is table of red index by binary_integer;
  red_a red_array;
begin
  startrecord := (in_page - 1) * in_num + 1; --计算得出开始记录数
  endrecord   := in_num * in_page; --计算得出截止记录数
  for var in (select * from zhl_table) loop
    red_a(var.id) := var;
  end loop;
  for I in 1 .. red_a.count loop
    if (I >= startrecord AND I <= endrecord) THEN
      dbms_output.put_line('id:' || red_a(I).id || 'name:' || red_a(I).name);
    END IF;
  end loop;

  in_totalrecord := red_a.count;
  in_totalpage   := floor((in_totalrecord + in_num - 1) / in_num);

  dbms_output.put_line('总页数' || in_totalpage || '总记录数' ||
                       in_totalrecord || '当前是第' || in_page || '页');
end;

--利用row_num分页,且能排序
select *
  from (select a.*, rownum row_num
          from (select * from zhl_table order by id desc) a) b
 where b.row_num <= 10
   and b.row_num >= 5