存储过程返回结果集
和exists的使用
*描述:根据申请人,获取相应的数据
*/procedure get_****(p_id in nvarchar2, o_result out reftype) as
begin
open o_result for
select (a.form_no || '-' || b.emp_name || '-' || a.store_name) as name,
a.form_no as value
from **** a
inner join employee b
on a.form_applicant = b.emp_id
inner join **** c
on a.form_no = c.form_no
and c.form_kind = '****'
where exists
(select 0
from employee aa
where aa.emp_id = p_emp_id
and aa.dept_code = b.dept_code)
and (c.form_status = ****' or
(c.form_status in ('****') and exists
(select 0
from **** bb
where bb.form_kind = '****'
and bb.form_no = a.form_no
and bb.app_status = '****'
and bb.phase_id in ('****'))));
end;