您当前的位置: 首页 > 数据库教程 > Oracle教程 > 关于oracle字符串汇总

关于oracle字符串汇总

作者:不详 来源:网络 发布时间: 2014-09-01 11:48 点击:
使用的代码 代码如下 -- 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

关于oracle字符串汇总

  使用的代码

  代码如下

  -- 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;

  /

  最后给个参考表

  在数据量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
分享到:
本文"关于oracle字符串汇总"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4