oracle包分为包规范和包体 一：首先创建包规范 create or replace package p_pagewithgroup is -- author : 肖伟 -- created : 2014/3/18 -- purpose : 分组分页过程 type type_cur is ref cursor; --定义游标变量用于返回记录集 procedure up_getrecordwith
oracle包分为包规范和包体
一：首先创建包规范
create or replace package p_pagewithgroup is
-- author : 肖伟
-- created : 2014/3/18
-- purpose : 分组分页过程
type type_cur is ref cursor; --定义游标变量用于返回记录集
procedure up_getrecordwithgroupbypage(
tblname in varchar2, --表名
pagesize in number, --页面大小
pageindex in number, --当前页
isrecount out number,--返回总条数
strwhere in varchar2,-- 查询条件 (注意: 不要加 where)
strcolums in varchar2, --字段集合，逗号分割
strselectcolums in varchar2, --字段集合，逗号分割
strgroup in varchar2,--分组条件语句
strorder in varchar2,-- 排序条件语句
v_cur out type_cur --返回当前页数据记录
);
end p_pagewithgroup;
二：创建包结构体
create or replace package body p_pagewithgroup is
procedure up_getrecordwithgroupbypage(
tblname in varchar2, --表名
pagesize in number, --页面大小
pageindex in number, --当前页
isrecount out number,--返回总条数
strwhere in varchar2,-- 查询条件 (注意: 不要加 where)
strcolums in varchar2, --字段集合，逗号分割
strselectcolums in varchar2, --字段集合，逗号分割
strgroup in varchar2,--分组条件语句
strorder in varchar2,-- 排序条件语句
v_cur out type_cur --返回当前页数据记录
)
as
--定义变量
v_sql varchar2(8000);
v_count number;
v_row_start number; --开始记录
v_row_end number; --结束记录
begin
------------------------------------------------------------显示总条数
v_sql := 'select count(1) from ' || tblname;
if strwhere is not null or strwhere <> ''
then
v_sql := v_sql || ' where ' || strwhere;
end if;
if strgroup is not null or strgroup <> ''
then
v_sql := v_sql || ' group by ' || strgroup;
end if;
dbms_output.put_line (v_sql);
--v_sql:='select count(1) from bs_user where 1=1';
execute immediate v_sql into v_count ;
isrecount:=v_count;
------------------------------------------------------------显示任意页内容
-- if pagesize<1 then
-- pagesize:=1;
-- end if;
v_row_start := (pageindex - 1) * pagesize + 1;
v_row_end := pageindex * pagesize;
v_sql:='select '||strselectcolums||' from (select t.*,rownum as rn from (select '||strcolums||' from '||tblname;
if strwhere is not null or strwhere <> ''
then
v_sql := v_sql || ' where ' || strwhere;
end if;
if strgroup is not null or strgroup <> ''
then
v_sql := v_sql || ' group by ' || strgroup;
end if;
if strorder is not null or strorder <> ''
then
v_sql := v_sql || ' order by ' || strorder;
end if;
v_sql := v_sql ||') t) where rn between '||v_row_start||' and '||v_row_end;
dbms_output.put_line (v_sql);
open v_cur for v_sql;
end up_getrecordwithgroupbypage;
----------------------------------------------------------
end p_pagewithgroup;
三：测试
首先打开pl/sql command window
sql>declare
-- local variables here
isrecount number;--返回总条数
my_cur p_pagewithgroup.type_cur ; --返回当前页数据记录
begin
-- test statements here
p_pagewithgroup.up_getrecordwithgroupbypage('t_division',5,1,isrecount,' 1=1 ',' * ',' * ',null,null,my_cur );
dbms_output.put_line(to_char(isrecount));
end;
.syntaxhighlighter{padding-top:20px;padding-bottom:20px;}【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！