oracle 创建函数／程序包

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;