create or replace function text(
startTime in varchar2,
endTime in varchar2,
timeType in number,
spid in number,
pagesize in number,
currentpage in number,
datacount out number
)
RETURN WHITE_LIST_TYPE_ARRAY as
strsql varchar2(1000);
spinfos WHITE_LIST_TYPE_ARRAY;
rs SYS_REFCURSOR;
id number;
sp_name varchar2(200);
sp_code varchar2(200);
sp_status number;
sp_type number;
mdn_count number;
record WHITLISTCOUNT;
pageinfo WHITE_LIST_TYPE_ARRAY;
i number;
a number;
currentsize number;
pattern varchar2(20);
psize number;
begin
---初始化参数
i := 1;
spinfos := WHITE_LIST_TYPE_ARRAY();
if timeType = 0 then
pattern := 'yyyy-mm-dd';
else
pattern := 'yyyy_mm';
end if;
------------------------获取数据开始-----------------------------
-- 或许企业编号、 名称、 id
strsql := 'select id, sp_code, sp_name ,sp_type ,sp_status from NM_SP_INFO where id != 0 ';
if spid > 0 then
strsql := strsql || ' and id ='||spid ;
end if;
open rs for strsql;
loop
fetch rs
into id, sp_name, sp_code,sp_type ,sp_status;
exit when rs%NOTFOUND;
strsql := 'select count(mdn) from NM_NET_USER_' || id || ' where 1=1 ';
if startTime is not null then
strsql := strsql || ' and to_char(modify_time,''' || pattern ||
''') >= ''' || startTime || '''';
end if;
if startTime is not null then
strsql := strsql || ' and to_char(modify_time,''' || pattern ||
''') <= ''' || endTime || '''';
end if;
execute immediate strsql into mdn_count;
spinfos.extend;
record := WHITLISTCOUNT(id, sp_name, sp_code,sp_type,sp_status,mdn_count);
spinfos(i) := record;
i := i + 1;
end loop;
close rs ;
datacount := spinfos.count;
---分页开始
i := i -1;
if currentpage < 1 then
return null;
end if;
if currentpage < 2 then
currentsize := 1;
else
currentsize := (currentpage - 1) * pagesize;
currentsize := currentsize + 1;
end if;
if i < currentsize then
return null;
end if;
if i = pagesize then
psize := pagesize;
else
if i < currentsize + pagesize then
psize := MOD(i, pagesize);
end if;
end if;
if psize is null then
psize := pagesize;
end if;
a := 1;
i := 1;
pageinfo := WHITE_LIST_TYPE_ARRAY();
pageinfo.extend(psize);
for i in currentsize..psize + currentsize - 1 LOOP
pageinfo(a) := spinfos(i);
a := a + 1;
end LOOP;
---分页结束
return pageinfo;
end text;
/
一个自定义的数组,分别记录了动态表数据,还有一张数据表中的数据 ,添加分页功能, 还有不足之处请大家多多指教