自定義聚集函式，仿wm_concat，使用CLOB，可處理超過4000長度字串

因開發需要，在使用wm_concat函式時，在字串長度超過4000後報錯，所以自定義一個類似的聚集函式，在內部使用CLOB處理，避免超長問題。

原始碼如下：

先以sys使用者授權：

grant execute on dbms_lob to xxx;

CREATE OR REPLACE TYPE typ_concat_clob AS OBJECT

(

  concat_str CLOB,

  splitstr CLOB,

  --ODCIAggregateInitialize做一些初始化操作

  STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return number,

  --ODCIAggregateIterate是主要的處理邏輯所在，這裡定義一個迭代操作

  member function ODCIAggregateIterate(self In Out typ_concat_clob,srcvalue in VARCHAR2) return number,

  -- ODCIAggregateMerge是一個合併函式，如果在使用時指定了partition enabled，就必須定義此函式，用來把並行處理的結果進行合併

  member function ODCIAggregateMerge(self In Out typ_concat_clob,ctx2 In Out typ_concat_clob) return number,

  --ODCIAggregateTerminate是一個終止函式，顧名思義，在這個函式中對結果做最後處理並返回

  member function ODCIAggregateTerminate(self In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return number

)

 

CREATE OR REPLACE TYPE BODY typ_concat_clob IS

  STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return NUMBER

  IS

  BEGIN

    cs_ctx := typ_concat_clob(to_clob(','),to_clob(','));

    RETURN ODCICONST.SUCCESS;

  END;

 

  member function ODCIAggregateIterate(self In Out typ_concat_clob,srcvalue IN VARCHAR2) return NUMBER

  IS

  BEGIN

    dbms_lob.append(self.concat_str,to_clob(srcvalue));

    dbms_lob.append(self.concat_str,self.splitstr);

    RETURN ODCICONST.SUCCESS;

  END;

 

  member function ODCIAggregateMerge(self In Out typ_concat_clob,ctx2 In Out typ_concat_clob) return NUMBER

  IS

  BEGIN

    IF ctx2 IS NOT NULL THEN

      dbms_lob.append(self.concat_str,ctx2.concat_str);

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

 

  member function ODCIAggregateTerminate(self In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return NUMBER

  IS

  BEGIN

    dbms_lob.trim(self.concat_str,dbms_lob.getlength(self.concat_str)-1);

    self.concat_str := dbms_lob.substr(self.concat_str,dbms_lob.getlength(self.concat_str)-1,2);

    returnValue := self.concat_str;

    RETURN ODCICONST.SUCCESS;

  END;

END;

/

 

CREATE OR REPLACE FUNCTION fun_concat_clob(in_str VARCHAR2) RETURN CLOB

  AGGREGATE USING typ_concat_clob;

/

 

使用和wm_concat一樣，不同的是內部處理和返回值都是CLOB型別的：

SELECT fun_concat_clob(name) FROM students GROUP BY class;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28998293/viewspace-1353103/，如需轉載，請註明出處，否則將追究法律責任。