ORACLE分页查询存储过程
- 建表语句
CREATE TABLE `a` ( `name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `id` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ;
- 分页查询的包声明和包体
create or replace package PAGINATING_A_Pack is -- Author : ADMINISTRATOR -- Created : 2013/1/13 17:02:46 -- Purpose : 分页查询 表A 的数据 -- Public type declarations TYPE CUR IS REF CURSOR; call PAGINATING_A_Pack.PAGINATING_A_APP(1,10); PROCEDURE PAGINATING_A_P( P_CURSOR OUT PAGINATING_A_Pack.CUR, startno IN INTEGER , getnum IN INTEGER ); procedure PAGINATING_A_APP (startno IN INTEGER , getnum IN INTEGER); end PAGINATING_A_Pack;
create or replace package body PAGINATING_A_Pack is PROCEDURE PAGINATING_A_P( P_CURSOR OUT PAGINATING_A_Pack.CUR, startno IN INTEGER , getnum IN INTEGER ) IS /*************************************************************** *NAME : [Name] *PURPOSE : --分页查询 A表 赋值游标 ************************************************************/ BEGIN OPEN p_CURSOR FOR select row_n.ID, row_n.name, row_n.rownum_ --name INTO a_name FROM (SELECT row_.*, ROWNUM rownum_ FROM (select * from A) row_ WHERE ROWNUM <= startno + getnum) row_n WHERE rownum_ >= startno; END; procedure PAGINATING_A_APP (startno IN INTEGER , getnum IN INTEGER) IS /*************************************************************** *NAME : [Name] *PURPOSE : --分页查询 A表 分页查询************************************************************/ c PAGINATING_A_Pack.CUR; type A_ROW is record( id A.id%type, name A.name%type, num integer); a_r A_ROW; BEGIN PAGINATING_A_P(c,startno,getnum); LOOP FETCH c INTO a_r; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE('=NAME='||a_r.name); END LOOP; CLOSE c; END; end PAGINATING_A_Pack;
- 分页查询存储过程的调用
call PAGINATING_A_Pack.PAGINATING_A_APP(1,10);