create or replace function getUserByUnit(ccmsOriginId in number,
indexId in varchar2)
return varchar2 is
str varchar2(4000) default ‘‘;
i number default 0;
begin
for x in (select s.user_id
from t_sys_unit u, t_sys_staff s
where u.ccms_origin_id = ccmsOriginId
and u.node_id = s.org_node_id
and not exists (select c.alert_config_id
from T_YJ_ALERT_CONFIG c
where c.unit_id = ccmsOriginId
and c.staff_id = s.user_id
and c.index_id = indexId)) Loop
if i <> 0 then
str := str || ‘,‘;
end if;
str := str || x.user_id;
i := i + 1;
end Loop;
return(str);
end getUserByUnit;
使用：
select t.unit_id,
t.staff_id,
t.index_id,
t.alert_value1,
t.alert_value2,
‘1‘ message_type
from T_YJ_ALERT_CONFIG t
where t.staff_id is not null
union all
select t.unit_id,
getuserbyunit(t.unit_id, t.index_id) staff_id,
t.index_id,
t.alert_value1,
t.alert_value2,
‘2‘ message_type
from T_YJ_ALERT_CONFIG t
where t.staff_id is null
原文：http://my.oschina.net/u/2285090/blog/518764