Question

如何创建高效的Oracle分页存储过程

Answer 1

PROCEDURE p_qry_products(ret OUT pkg_pub.ret_cur,
pi_prodname IN T_TABLE.prodname%TYPE, --
pi_pageindex IN int, -- 起始记录数
pi_pagecnt IN int) -- 每页显示记录数
IS
BEGIN

OPEN ret FOR
SELECT *
FROM (SELECT COUNT(0) over() total_cnt, rownum row_num, u.*
FROM (SELECT your_cols
FROM T_TABLE rec
WHERE 1 = 1) u) res
WHERE res.row_num >= pi_pageindex
AND res.row_num < pi_pageindex + pi_pagecnt;
END p_qry_products;