select JIANGLIPUBLICSITUATION.get_area_name(o.area_no) as address,t.zh_title as zh_title,JIANGLIPUBLICSITUATION.get_main_person_info(t.prp_code) as main_person,t.main_org as main_org,extractvalue(pe.prp_xml, ''data/prp_extend/project_profile'') as project_profile,paper.paper_names as paper_names,patent.cnames as cnames,extractvalue(pe.prp_xml, ''data/proposal/extensionapplication'') as application_situation from proposal t
left join proposal_extend pe on pe.prp_code=t.prp_code
left join
(SELECT prp_code,
LISTAGG(paper_name, '';'') WITHIN GROUP(ORDER BY paper_name) AS paper_names
FROM (select pe.prp_code as prp_code,t.* from proposal_extend pe,xmltable (''data/represent_papers/represent_paper'' passing pe.prp_xml columns paper_name varchar2(500) path ''paper_name'') t
)
GROUP BY prp_code) paper on t.prp_code=paper.prp_code
left join
(SELECT prp_code,
LISTAGG(cname, '';'') WITHIN GROUP(ORDER BY cname) AS cnames
FROM (select pe.prp_code as prp_code,t.* from proposal_extend pe,xmltable (''data/patents/patent'' passing pe.prp_xml columns cname varchar2(500) path ''cname'') t
)
GROUP BY prp_code) patent on t.prp_code=patent.prp_code
left join organization o on o.org_code=t.recommend_org_code
left join organization org on org.org_code=t.org_code
left join const_area c on c.area_no=o.area_no
left join person p on t.psn_code=p.psn_code
left join proposal_cached pc on t.pos_code=pc.pos_code
left join org_department od on t.dept_code=od.dept_code
where t.grant_code=30 and t.major_review_code is not null
如果listagg拼接字符串超过最大长度,可使用xmlagg处理
select xmlagg(xmlparse(content to_char(s.create_date,'yyyy-mm-dd hh24:mi:ss')||';' wellformed) order by s.create_date).getclobval()
from person s where s.log_type='login' and s.key_code=t1.psn_code
xmltable用法如下
select pe.prp_code as prp_code,t.* from proposal_extend pe,
xmltable (
'data/persons/person' passing pe.prp_xml columns
ranking varchar2(50) path '@seq_no',
cname varchar2(50) path 'cname',
prof_title_name varchar2(50) path 'prof_title_name',
org_name varchar2(100) path 'org_name',
org_finish_name varchar2(100) path 'org_finish_name',
work_result_one varchar2(1000) path 'work_result_one'
) t
where pe.prp_code=v_prp_code