函数如下：
CREATE OR REPLACE FUNCTION FUN_GET_ROLESOFUSER(v_emp_id in number , in_type in number) -- 获取当前用户所有角色
RETURN VARCHAR2 is
return_roles VARCHAR2(10240);
v_code varchar2(128);
v_name varchar2(128);
v_name_split varchar2(128);
v_code_split varchar2(128);
cursor role_cur is
select distinct r.ROLE_ID, r.ROLE_NAME from tbl_user_role ur join tbl_role r on ur.ROLE_ID = r.ROLE_ID
where ur.emp_id = v_emp_id;
BEGIN
-- in_type = 1 , 返回 角色name列表(逗号隔离)
-- in_type = 2 , 返回 角色id列表(逗号隔离)
return_roles:='';
v_name_split:='
';
v_code_split:=',';
open role_cur;
loop
fetch role_cur
into v_code, v_name;
exit when role_cur%notfound;
if in_type = 1 then
return_roles := return_roles || v_name ||v_name_split;
elsif in_type = 2 then
return_roles := return_roles || v_code ||v_code_split;
else
return_roles := return_roles || v_code ||v_code_split;
end if;
end loop;
close role_cur;
-- 去尾逗号
if in_type = 1 and length(v_name_split)>0 and length(return_roles)-length(v_name_split)>0 then
return_roles := substr(return_roles,0,length(return_roles)-length(v_name_split));
elsif in_type = 2 and length(v_code_split)>0 and length(return_roles)-length(v_code_split)>0 then
return_roles := substr(return_roles,0,length(return_roles)-length(v_code_split));
end if;
return(return_roles);
exception
when others then
return null;
END FUN_GET_ROLESOFUSER;