【笔记】分页存储过程

这个存储过程是用 row_number()   over(partation by col1 order by col2) 来实现的：

1、创建包

SQL> CREATE OR REPLACE PACKAGE pagination_package IS
  2         TYPE pagination_cursor IS REF CURSOR;
  3         PROCEDURE total_records(
  4                   table_name IN VARCHAR2, --表
  5                   total_num OUT INTEGER --总记录数
  6         );
  7         PROCEDURE pagination(
  8                   curr_page IN INTEGER,  --当前页数
  9                   records_pre_page IN INTEGER,  --每页显示记录数
 10                   table_name IN VARCHAR2,  --表名
 11                   --partitionBy_field IN VARCHAR2, --分组
 12                   orderBy_field IN VARCHAR2, --排序
 13                   total_pages OUT INTEGER,  --总页数
 14                   v_cursor OUT pagination_cursor  --接收游标
 15         );
 16  END;
 17  /

Package created

Executed in 0.015 seconds

2、创建包体，并实现存储过程

SQL> CREATE OR REPLACE PACKAGE BODY pagination_package AS
  2         PROCEDURE total_records(
  3                   table_name IN VARCHAR2, --表
  4                   total_num OUT INTEGER --总记录数
  5         ) AS
  6              v_sql VARCHAR2(2000);
  7              v_totalCount INTEGER;
  8         BEGIN
  9              v_sql := 'SELECT COUNT(*) FROM '|| table_name;
 10              EXECUTE IMMEDIATE v_sql INTO v_totalCount;
 11              total_num := v_totalCount;
 12         END total_records;
 13 
 14         PROCEDURE pagination(
 15                   curr_page IN INTEGER,  --当前页数
 16                   records_pre_page IN INTEGER,  --每页显示记录数
 17                   table_name IN VARCHAR2,  --表名
 18                   --partitionBy_field IN VARCHAR2, --分组
 19                   orderBy_field IN VARCHAR2, --排序
 20                   total_pages OUT INTEGER,  --总页数
 21                   v_cursor OUT pagination_cursor  --接收游标
 22         ) AS
 23              v_sql VARCHAR2(2000);
 24              v_start INTEGER;
 25              v_end INTEGER;
 26              v_totalPage INTEGER;
 27         BEGIN
 28              v_sql := 'SELECT COUNT(*) FROM '|| table_name;
 29              EXECUTE IMMEDIATE v_sql INTO v_totalPage;
 30              total_pages := ceil(v_totalPage/records_pre_page); --总页数
 31              v_end := curr_page * records_pre_page;--每页结束值
 32              v_start := v_end - records_pre_page + 1;--每页开始值
 33              --IF orderBy_field IS NOT NULL THEN
 34                 v_sql := 'SELECT * FROM
 35                       (SELECT t.*, row_number() over(ORDER BY '|| orderBy_field ||' ) rn FROM '|| table_name ||' t)
 36                       WHERE rn BETWEEN '|| v_start ||' AND '|| v_end;
 37              OPEN v_cursor FOR v_sql;
 38         END pagination;
 39  END;
 40  /

Package body created

Executed in 0.015 seconds

3、测试（在JAVA中）略

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22786751/viewspace-620536/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22786751/viewspace-620536/