CREATE OR REPLACE FUNCTION comm.f_get_diagnosisdesc(v_patient_id diagnosis.patient_id%type,v_visit_id diagnosis.visit_id%TYPE,v_diagnosis_type diagnosis.diagnosis_type%type)
--將診斷表多行的值轉換為一行鍾思平 20140610 南昌三三四醫院
RETURN VARCHAR2
IS
v_diagnosis_desc VARCHAR2(200) ;
BEGIN
FOR cur IN (SELECT diagnosis_desc from medrec.diagnosis WHERE patient_id = v_patient_id AND visit_id = v_visit_id AND diagnosis_type = v_diagnosis_type )
LOOP
v_diagnosis_desc := v_diagnosis_desc||cur.diagnosis_desc||' ; ';
END LOOP;
v_diagnosis_desc := Trim(v_diagnosis_desc) ;
RETURN v_diagnosis_desc ;
END f_get_diagnosisdesc ;
--建立同義詞
create or replace public synonym f_get_diagnosisdesc for comm.f_get_diagnosisdesc ;
commit;

Oracle多行轉一行

相關文章