拼接纵表字段

第一回写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;