oracle人员权限，字符串转列，统计管理详解

使用表值函数

一、创建type

create or replace type row_sys_user as object(

userid int,

roleid varchar2(500)

)

二、创建table

create or replace type tabemp as table of row_sys_user

三、创建表值函数

create or replace function strsplit1(p_value1 varchar2,

p_split1 varchar2 := ',')

--usage: select * from table(strsplit('1,2,3,4,5'))

return tabemp

pipelined is

v_idx integer;

v_userid integer;

v_strs_last varchar2(4000);

v_str varchar2(500);

v row_sys_user;

cursor temp is

select id,roleids from sys_user;

begin

open temp;

loop

fetch temp into v_userid,v_strs_last;

exit when temp%notfound;

loop

v_idx := instr(v_strs_last, ',');

exit when v_idx = 0;

v_str := substr(v_strs_last, 1, v_idx - 1);

v_strs_last := substr(v_strs_last, v_idx + 1);

v:=row_sys_user(v_userid,v_str);

pipe row(v);

end loop;

v:=row_sys_user(v_userid,v_strs_last);

pipe row(v);

end loop;

close temp;

return;

end strsplit1;

四、获取旧版人员权限

select c.name orgname,d.name deptname,a.realname,b.name

from sys_user a,sys_role b,sys_org c,sys_org d,table(strsplit1('','')) e

where a.id=e.userid and b.id=e.roleid

and a.orgid=c.id and a.deptid=d.id

and c.ext4 in('0200','0300')

order by c.name,d.name,a.realname,b.name