SumString函数的5各参数的意思分别是:
1. 表名;
2. 你想Group BY的字段名
3. 你想sum的那个字段名
4. Group By字段的值
5. Sum字符串时的分隔符
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 C1, SumString( '表', 'C1' , 'C2' , 'C1' , '\' ) SUM_B FROM 表 GROUP BY C1;
1. 表名;
2. 你想Group BY的字段名
3. 你想sum的那个字段名
4. Group By字段的值
5. Sum字符串时的分隔符
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 C1, SumString( '表', 'C1' , 'C2' , 'C1' , '\' ) SUM_B FROM 表 GROUP BY C1;
参考:
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
(select c1,c2,row_number()over(order by c1,c2 desc)+dense_rank()over(order by c1) rn,max(c2)over(partition by c1) mav from tablename)
start with c2=mav
connect by prior c2=c2-1
group by c1