SUMC函数 子表转成一行显示

CREATE OR REPLACE FUNCTION SUMC (input   VARCHAR2)   RETURN   VARCHAR2
  PARALLEL_ENABLE   AGGREGATE   USING   TypeSumVarchar;
  


CREATE OR REPLACE TYPE "TYPESUMVARCHAR"                                          as object
  (
   sum VARCHAR2(32767),
      static function ODCIAggregateInitialize(sctx IN OUT TypeSumVarchar)  return number,
      member function ODCIAggregateIterate(self IN OUT TypeSumVarchar,value IN varchar2) return number,
      member function ODCIAggregateTerminate(self IN TypeSumVarchar,returnValue OUT VARCHAR2, flags IN number)
        return number,
      member function ODCIAggregateMerge(self IN OUT TypeSumVarchar,ctx2 IN TypeSumVarchar) return number
  )
/
CREATE OR REPLACE TYPE BODY "TYPESUMVARCHAR" IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPESUMVARCHAR)
    RETURN NUMBER IS
  BEGIN
    SCTX := TYPESUMVARCHAR('');
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT TYPESUMVARCHAR,
                                       VALUE IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    SELF.SUM := SELF.SUM || ',' || VALUE;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN TYPESUMVARCHAR,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := SUBSTR(SELF.SUM, 2);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPESUMVARCHAR,
                                     CTX2 IN TYPESUMVARCHAR) RETURN NUMBER IS
  BEGIN
    RETURN ODCICONST.SUCCESS;
  END;
END;
/
CREATE OR REPLACE FUNCTION "SUMC" (input   VARCHAR2)   RETURN   VARCHAR2
  PARALLEL_ENABLE   AGGREGATE   USING   TypeSumVarchar;
/
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000)
/
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);
        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;
    RETURN;
END splitstr;
/