因为oracle 中的存储过程无法像SQLServer的存储过程直接返回结果集,所以要返回结果集就得使用引用游标, 首先定义一个包,在包中定义一个引用游标。
create
or
replace
package pkg_query
as
type cur_query
is
ref
cursor
;
end
pkg_query;
创建实现分页的存储过程:
CREATE
OR
REPLACE
PROCEDURE
prc_query (p_tableName
in
varchar2
,
--
表名
p_strWhere
in
varchar2
,
--
查询条件
p_orderColumn
in
varchar2
,
--
排序的列
p_orderStyle
in
varchar2
,
--
排序方式
p_curPage
in
out
Number
,
--
当前页
p_pageSize
in
out
Number
,
--
每页显示记录条数
p_totalRecords out
Number
,
--
总记录数
p_totalPages out
Number
,
--
总页数 www@bitscn@com
v_cur out pkg_query.cur_query)
--
返回的结果集
IS
v_sql
VARCHAR2
(
1000
):
=
''
;
--
sql语句
v_startRecord
Number
(
4
);
--
开始显示的记录条数
v_endRecord
Number
(
4
);
--
结束显示的记录条数
BEGIN
--
记录中总记录条数
v_sql :
=
'
SELECT TO_NUMBER(COUNT(*)) FROM
'
||
p_tableName
||
'
WHERE 1=1
'
;
IF
p_strWhere
IS
NOT
NULL
or
p_strWhere
<>
'
'
THEN
v_sql :
=
v_sql
||
p_strWhere;
END
IF
;
EXECUTE
IMMEDIATE v_sql
INTO
p_totalRecords;
--
验证页面记录大小
IF
p_pageSize
<
0
THEN
p_pageSize :
=
0
;
END
IF
;
--
根据页大小计算总页数
IF
MOD(p_totalRecords,p_pageSize)
=
0
THEN
p_totalPages :
=
p_totalRecords
/
p_pageSize;
ELSE
p_totalPages :
=
p_totalRecords
/
p_pageSize
+
1
;
END
IF
;
--
验证页号
IF
p_curPage
<
1
THEN
p_curPage :
=
1
;
END
IF
;
IF
p_curPage
>
p_totalPages
THEN
p_curPage :
=
p_totalPages;
END
IF
;
--
实现分页查询
v_startRecord :
=
(p_curPage
-
1
)
*
p_pageSize
+
1
; v_endRecord :
=
p_curPage
*
p_pageSize; v_sql :
=
'
SELECT * FROM (SELECT A.*, rownum r FROM
'
||
'
(SELECT * FROM
'
||
p_tableName;
IF
p_strWhere
IS
NOT
NULL
or
p_strWhere
<>
'
'
THEN
v_sql :
=
v_sql
||
'
WHERE 1=1
'
||
p_strWhere;
END
IF
;
IF
p_orderColumn
IS
NOT
NULL
or
p_orderColumn
<>
'
'
THEN
v_sql :
=
v_sql
||
'
ORDER BY
'
||
p_orderColumn
||
'
'
||
p_orderStyle;
END
IF
; v_sql :
=
v_sql
||
'
) A WHERE rownum <=
'
||
v_endRecord
||
'
) B WHERE r >=
'
||
v_startRecord; DBMS_OUTPUT.put_line(v_sql);
OPEN
v_cur
FOR
v_sql;
END
prc_query;