拼接纵表字段
第一回写oracle的function。作用是将纵表的某个字段拼接起来。
主表：（id,name,other）
子表：（id,rid,code）
rid与主表的id关联。
记录下来。
create or replace function VIEW_PATIENT_SYNDROME_2(v_id_symptom_hospital in varchar2) return varchar2 is --返回症候群名字形如：发热呼吸道1，神经中枢1 v_name_syndrome varchar2(1024) := ''; --临时存储症候群名字 v_name_tmp varchar2(40) := ''; --症候群编码 v_code_syndrome varchar2(1024); --循环累加计数 v_count binary_integer := 1; --症候群总数 v_count_syndrome number; --症候群子表id v_id_syndrome varchar2(40);begin --查询一个病例对应症候群数量 select count(*) into v_count_syndrome from csmw_syndrome_hospital csh where csh.idreference_syndrome = v_id_symptom_hospital; --迭代症候群 while v_count <= v_count_syndrome loop --症候群子表id_syndrome写入v_id_syndrome select id_syndrome into v_id_syndrome from (select rownum, nvl(csh.id_syndrome, '') as id_syndrome from csmw_syndrome_hospital csh where rownum <= v_count and csh.idreference_syndrome = v_id_symptom_hospital order by rownum desc) tmp where rownum = 1; --根据症候群子表id_syndrome取出症状编码写入v_code_syndrome select csh.code_syndrome into v_code_syndrome from csmw_syndrome_hospital csh where csh.id_syndrome = v_id_syndrome; --根据症状编码code_syndrome获取症状名称写入v_name_tmp select cms.name_syndrome into v_name_tmp from csmw_management_syndrome cms where cms.code_syndrome = v_code_syndrome; --拼接症状名称 v_name_syndrome := v_name_syndrome || ',' || v_name_tmp; v_count := v_count + 1; end loop; --去除左边的',' select ltrim(v_name_syndrome, ',') into v_name_syndrome from dual; return v_name_syndrome;end VIEW_PATIENT_SYNDROME_2;
