oracle自定义方法,自定义集合函数 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区...

看到坛子里经常有人问关于列转行的问题，觉得我写的这个函数应该对大家会有所帮助，大家也可以根据我这个函数写出更多的自定义集合函数满足自己的需要。

CREATE OR REPLACE

TYPE concatstrimpl AS OBJECT (

constr                        VARCHAR2 (4000),

STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concatstrimpl)

RETURN NUMBER,

MEMBER FUNCTION odciaggregateiterate (

SELF    IN OUT   concatstrimpl,

VALUE   IN       VARCHAR2

)

RETURN NUMBER,

MEMBER FUNCTION odciaggregateterminate (

SELF          IN OUT   concatstrimpl,

returnvalue   OUT      VARCHAR2,

flags         IN       NUMBER

)

RETURN NUMBER,

MEMBER FUNCTION odciaggregatemerge (

SELF   IN OUT   concatstrimpl,

ctx2   IN       concatstrimpl

)

RETURN NUMBER

);

/

CREATE OR REPLACE

TYPE BODY concatstrimpl

IS

STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concatstrimpl)

RETURN NUMBER

IS

BEGIN

sctx := concatstrimpl ('');

RETURN odciconst.success;

END;

MEMBER FUNCTION odciaggregateiterate (

SELF    IN OUT   concatstrimpl,

VALUE   IN       VARCHAR2

)

RETURN NUMBER

IS

BEGIN

self.constr := self.constr||VALUE;

RETURN odciconst.success;

END;

MEMBER FUNCTION odciaggregateterminate (

SELF          IN OUT   concatstrimpl,

returnvalue   OUT      VARCHAR2,

flags         IN       NUMBER

)

RETURN NUMBER

IS

BEGIN

returnvalue := SELF.constr;

RETURN odciconst.success;

END;

MEMBER FUNCTION odciaggregatemerge (

SELF   IN OUT   concatstrimpl,

ctx2   IN       concatstrimpl

)

RETURN NUMBER

IS

BEGIN

SELF.constr :=    SELF.constr;

RETURN odciconst.success;

END;

END;

/

CREATE OR REPLACE FUNCTION ccstr (input VARCHAR2)

RETURN VARCHAR2 PARALLEL_ENABLE

AGGREGATE USING concatstrimpl;

Example 1:

CDRDEV@cdrprd>SELECT ccstr (table_name || ',')

2    FROM cat

3   WHERE ROWNUM < 6;

CCSTR(TABLE_NAME||',')

--------------------------------------------------------------------------------

A,AA,ACCESS_CONTROL,ACCESS_ID_SEQ,ACCM_INVENTORY,

CDRDEV@cdrprd>

由于这种集合函数是无序的，如果需要这个Table list里面的table按照一定的顺序出现，可以通过把它当着分析函数来使用，结合max来实现排序，请看下面这个例子实现按照Table Name倒序

Example 2:

CDRDEV@cdrprd>SELECT MAX (tab)

2    FROM (SELECT   CCSTR (table_name || ',') OVER (ORDER BY table_name DESC) tab

3              FROM cat

4             WHERE ROWNUM < 6

5          GROUP BY table_name);

MAX(TAB)

--------------------------------------------------------------------------------

ACCM_INVENTORY,ACCESS_ID_SEQ,ACCESS_CONTROL,AA,A,

CDRDEV@cdrprd>

以上这个函数的限制就是最后输出的长度不可以超出4000个字符，因为使用的varchar2的类型。