• Oracle进行字符串汇总


    对字符进行汇总(把相关的字符连接成一个串,以符号分隔),有三种常用方法(10g中增加了内置的WMSYS.WM_CONCAT()函数)。在不同数据量下进行测试,运行时间如下表:

    在数据量10000的表上进行三种方法的测试,运行时间单位秒

    数据量PCT Collect SYS_Connect UDF
    1% 0.017 0.018 0.017
    10% 0.026 0.050 0.029
    50% 0.057 2.45 0.065
    100% 0.090 5.00 1.06

    使用的代码

    -- sample data
    drop table t_collect purge;
    create table t_collect as
    select mod(rownum,30) as flag, lpad(dbms_random.string('l',3),4,' ') as val
    from dual connect by rownum<=10000;

    collect函数(Oracle10g)
    -- 1: use collect funtion
    select flag,
    my_tk.f_list2str(cast(collect(trim(val)) as my_tk_str_tab_type)) as ename
    from t_collect sample(10)
    group by flag
    order by 1;

    sys_connect_by_path
    -- 2: use sys_connect_by_path and row_number function
    select t1.flag, substr(max(sys_connect_by_path(t1.val, ',')), 2) q
    from (select a.flag,
    trim(a.val) as val,
    row_number() over(partition by a.flag order by a.val) rn
    from t_collect sample(10) a) t1
    start with t1.rn = 1
    connect by t1.flag = prior t1.flag
    and t1.rn - 1 = prior t1.rn
    group by t1.flag
    order by 1;

    user-defined-function
    -- 3: use user-defined-function
    select flag,
    string_agg(trim(val)) as ename
    from t_collect sample(10)
    group by flag
    order by 1;


    辅助用的my_tk包代码片段

    create or replace type my_tk_str_tab_type is table of varchar2(100);


    ---------------------------------------------------------------------
    function f_list2str
    (
    p_list my_tk_str_tab_type,
    p_separator varchar2 default ',',
    p_sort integer default 1
    ) return varchar2 is
    l_idx pls_integer := 0;
    l_str varchar2(32767) := null;
    l_spt varchar2(10) := null;
    l_list my_tk_str_tab_type := p_list;
    begin
    if p_sort = 1 then
    l_list := f_sort_list(p_list);
    end if;

    l_idx := l_list.first;
    while l_idx is not null loop
    l_str := l_str || l_spt || l_list(l_idx);
    l_spt := p_separator;
    l_idx := l_list.next(l_idx);
    end loop;

    return l_str;
    end;


    自定义聚合函数

    -------------------------------------------------------------------
    --
    user-defined-function
    CREATE OR REPLACE TYPE t_string_agg AS OBJECT
    (
    g_string VARCHAR2(32767),

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
    RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
    value IN VARCHAR2 )
    RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
    returnValue OUT VARCHAR2,
    flags IN NUMBER)
    RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
    ctx2 IN t_string_agg)
    RETURN NUMBER
    );
    /
    CREATE OR REPLACE TYPE BODY t_string_agg IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
    RETURN NUMBER IS
    BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
    value IN VARCHAR2 )
    RETURN NUMBER IS
    BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
    returnValue OUT VARCHAR2,
    flags IN NUMBER)
    RETURN NUMBER IS
    BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
    ctx2 IN t_string_agg)
    RETURN NUMBER IS
    BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
    END;
    END;
    /
    CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
    RETURN VARCHAR2
    PARALLEL_ENABLE AGGREGATE USING t_string_agg;
    /




    作者:wait4friend
    Weibo:@wait4friend
    Twitter:@wait4friend
    本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
  • 相关阅读:
    一个微信小程序跳转到另一个微信小程序
    微信小程序 canIUse
    微信小程序 点击事件 传递参数
    jquery 在将对象作为参数传递的时候要转换成 JSON
    微擎后台进行GET提交
    微信小程序添加底部导航栏
    jquery 中 html与text函数的区别
    C++ int与char[]的相互转换
    Qt error: LNK1158 无法运行rc.exe解决办法
    Qt error C3615: constexpr 函数 "qCountLeadingZeroBits" 不会生成常数表达式
  • 原文地址:https://www.cnblogs.com/wait4friend/p/2335941.html
Copyright © 2020-2023  润新知