CREATE OR REPLACE PROCEDURE p_page (
p_sqlcount IN VARCHAR2, -- 记录数sql语句
p_sql IN VARCHAR2, -- 结果集sql语句
p_startpage IN INT, --起始页号
p_pagenum IN INT, --每页记录数
r_count OUT INT, --总记录数
r_cursor OUT sys_refcursor -- 结果集
)
IS
v_start INT;
v_end INT;
v_sql VARCHAR2 (2000);
BEGIN
v_start := (p_startpage - 1) * p_pagenum + 1;
v_end := v_start + p_pagenum - 1; EXECUTE IMMEDIATE p_sqlcount
INTO r_count; v_sql :=
'SELECT * '
|| ' FROM (select t1.*, ROWNUM r '
|| ' FROM ('
|| p_sql
|| ' ) t1 where rownum <='
|| TO_CHAR (v_end)
|| ') t2 where r >='
|| TO_CHAR (v_start); OPEN r_cursor FOR v_sql;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END p_page;
/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wzy0623/archive/2007/08/14/1742788.aspx
在pl/sql中测试该存储过程:
参数输入如下:p_sqlcount select * from jtglpt_t_dm_bp_sql select * from jtglpt_t_dm_bp_startpage 1p_pagenum 20为啥老是报“数据类型不一致,应是-但却获得-”的错误!存储过程没有问题。该如何测试这个存储过程,是不是输入的参数有问题,请高手指教!在下分不多但请帮助,不甚感激!
p_sqlcount IN VARCHAR2, -- 记录数sql语句
p_sql IN VARCHAR2, -- 结果集sql语句
p_startpage IN INT, --起始页号
p_pagenum IN INT, --每页记录数
r_count OUT INT, --总记录数
r_cursor OUT sys_refcursor -- 结果集
)
IS
v_start INT;
v_end INT;
v_sql VARCHAR2 (2000);
BEGIN
v_start := (p_startpage - 1) * p_pagenum + 1;
v_end := v_start + p_pagenum - 1; EXECUTE IMMEDIATE p_sqlcount
INTO r_count; v_sql :=
'SELECT * '
|| ' FROM (select t1.*, ROWNUM r '
|| ' FROM ('
|| p_sql
|| ' ) t1 where rownum <='
|| TO_CHAR (v_end)
|| ') t2 where r >='
|| TO_CHAR (v_start); OPEN r_cursor FOR v_sql;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END p_page;
/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wzy0623/archive/2007/08/14/1742788.aspx
在pl/sql中测试该存储过程:
参数输入如下:p_sqlcount select * from jtglpt_t_dm_bp_sql select * from jtglpt_t_dm_bp_startpage 1p_pagenum 20为啥老是报“数据类型不一致,应是-但却获得-”的错误!存储过程没有问题。该如何测试这个存储过程,是不是输入的参数有问题,请高手指教!在下分不多但请帮助,不甚感激!
p_sqlcount: select count(1) from jtglpt_t_dm_b
这个地方没有使用绑定变量,通用的分页存储过程,后果很严重
v_sql :=
'SELECT * '
|| ' FROM (select t1.*, ROWNUM r '
|| ' FROM ('
|| p_sql
|| ' ) t1 where rownum <='
|| TO_CHAR (v_end)
|| ') t2 where r >='
|| TO_CHAR (v_start); OPEN r_cursor FOR v_sql;
'SELECT * '
|| ' FROM (select t1.*, ROWNUM r '
|| ' FROM ('
|| p_sql
|| ' ) t1 where rownum <=:v_end'
|| ') t2 where r >=:v_start';
OPEN r_cursor FOR v_sql using v_end, v_start;