文章/答案/技术大牛

发布

问将WM_CONCAT转换为Listagg
EN

Stack Overflow用户

提问于 2015-10-19 19:12:07

回答 3查看 10.4K关注 0票数 4

我的数据库管理员正在将我的oracle数据库从v10升级到v12。我有一些使用wm_concat的旧SP，我需要将其更改为listagg。有问题的代码如下：

Select  registration_id,package_set_id,
        REPLACE(REPLACE(WM_CONCAT(REPLACE( (case when ROW_NUMBER() over (partition by product_id,product_detail_set_id,registration_id,product_family_id,application_id,package_Set_id,
               legal_status order by packset_country)=1 then legal_status else null end), ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
               REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID   ORDER BY Packset_country  ), ',' , ' | '), '#', ',') as legal_status,

        (REPLACE(REPLACE(WM_CONCAT(REPLACE(ev_code, ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
               REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID   ORDER BY ev_code  ), ',' , ' | '), '#', ',')) EV_CODES,

         min(marketed_date) over (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID) as marketed_date,

         (REPLACE(REPLACE(WM_CONCAT(REPLACE(Packset_country, ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID, REGISTRATION_ID ,PRODUCT_FAMILY_ID,
                APPLICATION_ID,PACKAGE_SET_ID   ORDER BY Packset_country, reg_packset_country_id ), ',' , ' | '), '#', ',')) REGISTRATION_PACKSET_COUNTRIES,
         ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID  
                ORDER BY Packset_country desc ,reg_packset_country_id)  ROW_NUM,     
         REPLACE(REPLACE(WM_CONCAT(REPLACE( (case when currently_marketed_in_country='Y' then packset_country end), ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
                REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID  ORDER BY packset_country ,currently_marketed_in_country,reg_packset_country_id ), ',' , ' | '), '#', ',') as CURRENTLY_MARKETED_COUNTRIES
from radw_dwh.dw202_fact_reg_pack_countries

预期的结果是：

我试图更改它，但当我试图在"LISTAGG“侧使用"ROW_NUMBER()”时出现问题。

我该如何解决这个问题呢？

wm-concat

sql

oracle

listagg

回答 3

Stack Overflow用户

发布于 2015-10-19 19:51:02

列表的基本语法是：

LISTAGG(col_name_to_be_aggregated, ',') WITHIN GROUP (ORDER BY col)

在您的示例中，由于有一个子查询as结果集为WM_CONCAT，因此可以将相同的子查询放在LISTAGG中的col_name_to_be_aggregated位置。

我认为您还可以去掉所有的替换函数，因为LISTAGG可以接受您选择的分隔符。

试一下，

LISTAGG
(
  CASE
  WHEN ROW_NUMBER() OVER (PARTITION BY product_id,
                                       product_detail_set_id,
                                       registration_id,
                                       product_family_id,
                                       application_id,
                                       package_Set_id, 
                                       legal_status 
                                       order by packset_country)=1 THEN
    legal_status
  ELSE
    NULL
  END), ',') WITHIN GROUP (ORDER BY required_col)

另外，我想解释一下为什么您需要在12c中迁移到LISTAGG。因为已经从最新的12c版本中删除了t。因此，任何一直依赖WM_CONCAT函数的应用程序一旦升级到12c就不能工作。阅读

对于11g版本2之前的版本，您不能使用LISTAGG。有很多字符串聚合技术，请看我的答案。

有关的更多详细信息

票数 5

Stack Overflow用户

发布于 2015-10-20 13:21:46

    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 ); 
/ 
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;
/ 
CREATE or replace FUNCTION stragg(input varchar2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; 
/


with t as ( select 'a1' val from dual union all select 'b2' val from dual ) select stragg(val) as val from t; val --------------------------- a1,b2

票数 0

Stack Overflow用户

发布于 2017-11-14 22:17:48

以下方法的动机

我们在使用wm_concat (已知错误)或wm_concat-to-list_agg-conversion-for-12c-use.时遇到了几个问题

(更不用说在其他解决方案可能相当复杂的许多场景中使用wm_concat的出色的紧凑/声明性用法。)

(有时必须使用xmlagg，因为> 4000 chars/clob问题或wm_concat( distinct ... )仿真的regexp_replace变通方法)，例如like here。

策略

因此，在我看来，最后一个相当好的策略(可能根据您的环境/需求/策略而有所不同)是

创建两个函数
- create function wm_concat_32767(... (从Oracle12c开始就可以在varchar(32767)上使用)和
  - ，具体取决于数据库的MAX_STRING_SIZE您可能希望将其应用到"4000“或db中

- **`create function  wm_concat_clob(...`** in your db in the sys-schema first.
- They should be based on code like provided by [the answer from _Dart XKey_](https://stackoverflow.com/a/33228581/1915920) (maybe copied from [_asktom/Tom Kyte_](https://web.archive.org/web/20150906040401/https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402))

create public synonym wm_concat for sys.wm_concat_32767
- (我通常会让wm_concat指向可能更快的wm_concat_32767，而不是wm_concat_clob)
- this允许基于< 11.2g的现有wm_concat(varchar(4000))的轻松重用/迁移)
- it可能已经知道函数的名称，因此很容易使用
- (有关它的在线文档可能具有误导性的问题是我可以忍受它的好处大于我的缺点)
- it与前面提到的listagg/xmlagg/regexp_replace-based变通方法相反，above

甚至允许使用带有紧凑声明语法的wm_concat( distinct ... )查询

到wm_concat_*functions的

create public synonym wm_concat_clob for sys.wm_concat_clob
give 正确的公共执行权限

代码

所以最后我们使用的代码(也许我会随着时间的推移更新上面提到的所有内容)：

wm_concat_32767(...)创建：

取决于您的数据库的MAX_STRING_SIZE，您可能希望将其设置为"4000“或其他值

create or replace type  string_agg_type  as object ( 
  total varchar2(32767), 
  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 
); 
/ 
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;
/ 
CREATE or replace FUNCTION  wm_concat_32767(input varchar2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; 
/

基于this code from Michel Cadot的wm_concat_clob(...)创建

create or replace type  stragg_type4  as object (
  result CLOB,
  static function ODCIAggregateInitialize (sctx IN OUT stragg_type4) return number,
  member function ODCIAggregateIterate (self IN OUT stragg_type4, value IN varchar2) return number,
  member function ODCIAggregateTerminate (self IN stragg_type4, returnValue OUT CLOB, flags IN number) return number,
  member function ODCIAggregateMerge (self IN OUT stragg_type4, ctx2 IN stragg_type4) return number
);
/

create or replace type body  stragg_type4  is

  static function ODCIAggregateInitialize (sctx IN OUT stragg_type4) return number is begin
    sctx := stragg_type4 (null);
    dbms_lob.createtemporary (lob_loc => sctx.result, cache   => TRUE, dur => dbms_lob.call);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate (self IN OUT stragg_type4, value IN varchar2) return number is begin
    self.result := self.result || ',' || value;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate (self IN stragg_type4, returnValue OUT CLOB, flags IN number) return number is begin
    returnValue := ltrim (self.result, ',');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge (self IN OUT stragg_type4, ctx2 IN stragg_type4) return number is begin
    self.result := self.result || ctx2.result;
    return ODCIConst.Success;
  end;

end;
/

sho err

CREATE or replace FUNCTION wm_concat_clob(input varchar2) RETURN CLOB PARALLEL_ENABLE AGGREGATE USING stragg_type4;
/

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/33212899

复制

相似问题

问将WM_CONCAT转换为Listagg
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将WM_CONCAT转换为ListaggEN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将WM_CONCAT转换为Listagg
EN