Oracle分页存储过程
我写的分页存储过程有问题，请大家帮忙看看：表结构：
STUDENT
(
ID NUMBER,
NAME VARCHAR2(10)
)
CREATE OR REPLACE PACKAGE PAGE_PAGE IS
TYPE REF_CUR IS REF CURSOR;
PROCEDURE PROC_PageDivider(PERROWS in NUMBER,DISPLAYPAGE in NUMBER,OUTPUTDATA OUT PAGE_PAGE.REF_CUR);
END PAGE_PAGE;
CREATE OR REPLACE PACKAGE BODY PAGE_PAGE AS
PROCEDURE PROC_PageDivider
(
PERROWS in NUMBER, --PERROWS每页显示的行数
DISPLAYPAGE in NUMBER, --DISPLAYPAGE要显示的页码
OUTPUTDATA OUT PAGE_PAGE.REF_CUR --返回的数据集
)
IS
SQL_SELECT VARCHAR2(200); --查询语句
PAGESIZE NUMBER; --页数
TOTALROWS NUMBER; --总共行数
BEGIN
SELECT COUNT(*) INTO TOTALROWS FROM STUDENT;
IF TOTALROWS mod PERROWS <>0 THEN
PAGESIZE:=TOTALROWS/PERROWS+1;
ELSE
PAGESIZE:=TOTALROWS/PERROWS;
END IF;
OPEN OUTPUTDATA FOR
SELECT * FROM STUDENT WHERE ID<=(DISPLAYPAGE-1)*PERROWS
MINUS
SELECT * FROM STUDENT WHERE ID<=DISPLAYPAGE*PERROWS;
END PROC_PageDivider; 1、包体的最后加一行 end;
2、SELECT * FROM STUDENT WHERE ID<=(DISPLAYPAGE-1)*PERROWS
MINUS
SELECT * FROM STUDENT WHERE ID<=DISPLAYPAGE*PERROWS;
改成
SELECT * FROM STUDENT WHERE rownum<=DISPLAYPAGE*PERROWS
MINUS
SELECT * FROM STUDENT WHERE rownum<=（DISPLAYPAGE-1）*PERROWS;
3、给过程加个 exception 会更好
if DISPLAYPAGE > PAGESIZE then
页:
[1]