应需求分析的要求，需要对查询结果做个显示调整，我这里写了一个函数和一个类来实现这个功能，具体代码如下：

1. type：

     CREATE OR REPLACE TYPE "STRING_AGG_TYPE"                                          as object
(
   total varchar2(4000),
   static function
        ODCIAggregateInitialize(sctx IN OUT string_agg_type )
        return number,
   member function
        ODCIAggregateIterate(self IN OUT string_agg_type ,
                             value IN varchar2 )
        return number,
   member function
        ODCIAggregateTerminate(self IN string_agg_type,
                               returnValue OUT varchar2,
                               flags IN number)
        return number,
   member function
        ODCIAggregateMerge(self IN OUT string_agg_type,
                           ctx2 IN string_agg_type)
        return number
)

2.type body：

CREATE OR REPLACE TYPE BODY "STRING_AGG_TYPE"
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
    sctx := string_agg_type( null );
    return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type,
                                     value IN varchar2 )
return number
is
begin
    self.total := self.total || ',' || value;
    return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN string_agg_type,
                                       returnValue OUT varchar2,
                                       flags IN number)
return number
is
begin
    returnValue := ltrim(self.total,',');
    return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type)
return number
is
begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
end;
end;

3.function：

CREATE OR REPLACE FUNCTION join_char(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;

具体示例如下：

记录一下~

hzl.jpg

ITPub博客

oracle行转列相关函数

最新文章