Oracle多行转一行

1. 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)
   
2. --将诊断表多行的值转换为一行 钟思平 20140610 南昌三三四医院
   
3. RETURN VARCHAR2
   
4. IS
   
5. v_diagnosis_desc VARCHAR2(200) ;
   
6. BEGIN
   
7. 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 )
   
8. LOOP
   
9. v_diagnosis_desc := v_diagnosis_desc||cur.diagnosis_desc||' ; ';
   
10. END LOOP;
    
11. v_diagnosis_desc := Trim(v_diagnosis_desc) ;
    
12. RETURN v_diagnosis_desc ;
    
13. END f_get_diagnosisdesc ;
    
14. 
    
15. --创建同义词
    
16. create or replace public synonym f_get_diagnosisdesc for comm.f_get_diagnosisdesc ;
    
17. commit;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28878983/viewspace-2133883/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28878983/viewspace-2133883/