存储过程分页

参考

创建分页存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE PROCEDURE split_page (
    qsql         IN VARCHAR2,
    page_index   IN NUMBER,
    page_size    IN NUMBER,
    total        OUT NUMBER,
    res          OUT SYS_REFCURSOR
)
    AS
    qurry varchar2(2000);
    las NUMBER := page_index * page_size;
    fir NUMBER := las - page_size;
BEGIN
    qurry := 'select max(rownum) from ( ' || qsql || ' )';
    EXECUTE IMMEDIATE qurry INTO total;
    qurry := 'select B.* from (select A.*, rownum rownumber from (' || qsql || ') A where rownum <=' || las || ') B where rownumber > ' || fir;
    OPEN res FOR qurry;
END;

qsql: 查询语句
page_index: 查询第几页
page_size: 每页大小
total: 总条数
res: 结果集游标

注意：qsql结果集中列名不能与rownum别名rownumber重复

java 调用

1
2
3
4
db.dirver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@192.168.1.11:1521:xe
db.user=hr
db.password=hr
Connection conn = null;
CallableStatement cstmt = null;
ResultSet res = null;
try {
    conn = DBUtil.getConnection();
    cstmt = conn.prepareCall("{call split_page(?,?,?,?,?)}");
    cstmt.setString(1, "select * from student order by age asc");
    cstmt.setInt(2, 2);
    cstmt.setInt(3, 2);
    cstmt.registerOutParameter(4, OracleTypes.INTEGER);
    cstmt.registerOutParameter(5, OracleTypes.CURSOR);
    cstmt.execute();
    System.out.println(cstmt.getObject(4));
    res = (ResultSet) cstmt.getObject(5);
    while (res.next()) {
        System.out.println(res.getString("name"));
    }

} catch (Exception e) {
    e.printStackTrace();
} finally {
    DBUtil.closeAll(res, cstmt, conn);
}

rownum 和 group by 的关系，当group by 中存在非主键，则oracle会先给列加rownum然后再group by.
如果主键类型是char、varchar2而非int,number等,则先rownum再group by.