1.类型
create or replace
type type_split as table of varchar2(4000);
2.函数体
create or replace
function f_split(
p_list varchar2,
p_sep varchar2 := ','
)
return type_split pipelined
is
l_idx int;
v_list varchar2(4000) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end f_split;
3.程序包声明
create or replace
PACKAGE Pk_pagination
IS
type type_cur
IS
ref
CURSOR; --定义游标变量用于返回记录集
PROCEDURE Proc_pagination (
Psql IN VARCHAR2, --产生分页数据的查询语句
Pindex IN NUMBER, --要显示的页数索引,从0开始
Psize IN NUMBER, --每页显示记录数
PpageCount OUT NUMBER, --返回总的分页数
ProwCount OUT NUMBER, --返回总的记录数
v_cur OUT type_cur --返回分页数据的游标
);
END Pk_pagination;
1)程序实体
create or replace
PACKAGE body Pk_pagination
IS
PROCEDURE Proc_pagination(
Psql IN VARCHAR2, --产生分页数据的查询语句
Pindex IN NUMBER, --要显示的页数索引,从0开始
Psize IN NUMBER, --每页显示记录数
PpageCount OUT NUMBER, --返回的分页数
ProwCount OUT NUMBER, --返回的记录数
v_cur OUT type_cur --返回分页数据的游标
)
AS
v_sql VARCHAR2(4000);
v_begin NUMBER;
v_end NUMBER;
BEGIN
/*不需要查询总数量和分页记录数的情况*/
IF regexp_like(Psql, '/\*no count\*/') THEN
ProwCount := 0;
PpageCount := 0;
v_end := (Pindex - 1) * Psize + Psize;
v_begin := v_end - Psize + 1;
v_sql := 'select rownum as rn, t.* from (' || Psql || ') t where rownum < ' || (v_end + 1);
v_sql := 'select * from (' || v_sql || ') where rn > ' || (v_begin - 1);
OPEN v_cur FOR v_sql;
ELSE
IF regexp_like(Psql, '/\*.*count.*\*/') THEN
v_sql := REPLACE(REPLACE(regexp_substr(Psql,'/\*.*count.*\*/'), '/*', ''), '*/', '');
ELSE
v_sql := 'select count(*) from (' || Psql || ')';
END IF;
EXECUTE immediate v_sql INTO Prowcount; --计算记录总数
IF Prowcount = 0 THEN
OPEN v_cur FOR SELECT 0 AS rn FROM dual WHERE 1 = 0;
PpageCount := 0;
ELSE
PpageCount := ceil(Prowcount / Psize); --计算分页总数
--显示任意页内容
v_end := (Pindex - 1) * Psize + Psize;
v_begin := v_end - Psize + 1;
--v_sql := 'select rownum as rn, t.* from (' || Psql || ') t';
--v_sql := 'select * from (' || v_sql || ') where rn between ' || v_begin || ' and ' || v_end;
v_sql := 'select rownum as rn, t.* from (' || Psql || ') t where rownum < ' || (v_end + 1);
v_sql := 'select * from (' || v_sql || ') where rn > ' || (v_begin - 1);
OPEN v_cur FOR v_sql;
END IF;
END IF;
END Proc_Pagination;
END Pk_pagination;