使用表值函数
一、创建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