CREATE OR REPLACE FUNCTION SumString( I_TableName IN VARCHAR2 , I_GroupColName IN VARCHAR2 , I_ResultColName IN VARCHAR2 , I_GroupColValue IN VARCHAR2 , I_Separator IN VARCHAR2 ) RETURN VARCHAR2 IS TYPE T_Cur IS REF CURSOR ; C_Cur T_Cur ; V_Sql VARCHAR2(2000) ; V_Result VARCHAR2(2000) ; V_Tmp VARCHAR2(200) ; V_Cnt NUMBER := 0 ; BEGIN V_Result := '' ; V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ; OPEN C_Cur FOR V_Sql ; LOOP FETCH C_Cur INTO V_Tmp ; EXIT WHEN C_Cur%NOTFOUND ; IF V_Cnt = 0 THEN V_Result := V_Tmp ; ELSE V_Result := V_Result || I_Separator || V_Tmp ; END IF ; V_Cnt := V_Cnt + 1 ; END LOOP ; CLOSE C_Cur ;
RETURN V_Result ; END SUMSTRING; --调用函数 select a.PID, a.PName, SumString('Table2','PID_ID','Class',a.PID,',') from Table1 a,Table2 b where a.PID = b.PID_ID GROUP BY PID,PName
I_TableName IN VARCHAR2 ,
I_GroupColName IN VARCHAR2 ,
I_ResultColName IN VARCHAR2 ,
I_GroupColValue IN VARCHAR2 ,
I_Separator IN VARCHAR2
)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR ;
C_Cur T_Cur ;
V_Sql VARCHAR2(2000) ;
V_Result VARCHAR2(2000) ;
V_Tmp VARCHAR2(200) ;
V_Cnt NUMBER := 0 ;
BEGIN
V_Result := '' ;
V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ;
OPEN C_Cur FOR V_Sql ;
LOOP
FETCH C_Cur INTO V_Tmp ;
EXIT WHEN C_Cur%NOTFOUND ;
IF V_Cnt = 0 THEN
V_Result := V_Tmp ;
ELSE
V_Result := V_Result || I_Separator || V_Tmp ;
END IF ;
V_Cnt := V_Cnt + 1 ;
END LOOP ;
CLOSE C_Cur ;
RETURN V_Result ;
END SUMSTRING; --调用函数
select
a.PID,
a.PName,
SumString('Table2','PID_ID','Class',a.PID,',')
from
Table1 a,Table2 b
where
a.PID = b.PID_ID
GROUP BY
PID,PName