ORACLE学习之实现字符串聚合和截断的多种方法的超详细解析

一、小数据量的字符串聚合——wm_concat()函数

1、建表clob_table(id,position_clob,strain_clob)，其中除了id是number类型，其他都是clob类型；

建表 straconstantinfo(id,position,strain);

2、建立序列id_seq，初始为0，自增为1，nocycle，nocache

3、建立字符串聚合的存储过程

--本存储过程功能是将实时表中的position和strain两个字段的所有数据都合并起来。
--该代码适合数据量不大的情况，如果合并起来的字符串过长需要使用自定义函数的方法来解决
create or replace procedure number_merge_char(v_seqname in varchar2)  as
  n  number;
  p_clob clob;
  s_clob clob;
  amount_p int;
  amount_s int;
  offset_p int;
  offset_s int;
  vchar_position varchar2(32767);
  vchar_strain  varchar2(32767);
begin
  --execute immediate 一般用于在存储过程中执行动态sql，它可以把执行sql所获得数据传输到存储过程中以进行进一步的计算
  --这里首先向clob_table表中插入了一条记录，但clob字段里的都是空的，相当于初始化，需要后续把数据存入空的clob中
  execute immediate 'insert into clob_table(id,position_clob,strain_clob) values('|| v_seqname ||'.nextval,empty_clob(),empty_clob())';
  execute immediate 'select id_seq.currval from dual' into n;
  --使用wm_concat的作用是以逗号为分隔符，把括号内字段的一列数据聚合成一行的一个长字符串，即列转行,此处合并了position字段的列的值
  execute immediate 'select wm_concat(position) from (select id,position,strain from straconstantinfo order by id)' into vchar_position;
  dbms_output.put_line('合并后vchar_position：'|| vchar_position ||'。');
  --此处合并了straconstantinfo表中的strain字段的列值
  execute immediate  'select wm_concat(strain) from (select id,position,strain from straconstantinfo order by id)' into vchar_strain;
  dbms_output.put_line('合并后vchar_strain：'|| vchar_strain ||'。');
  --这里先把clob_table表中的两个clob字段的值赋给该存储过程中定义的clob中
  select position_clob,strain_clob into p_clob,s_clob from clob_table where id = n for update;
  --然后对clob进行操作，首先打开clob对象
 dbms_lob.open(p_clob,dbms_lob.lob_readwrite);
  dbms_lob.open(s_clob,dbms_lob.lob_readwrite);
  --获取完成聚合的文本的长度
  amount_p:=length(vchar_position);
  amount_s:=length(vchar_strain);
  --获取定位器引用的lob数据的长度，即写入的起始位置
  offset_p:=dbms_lob.getlength(p_clob)+1;
  offset_s:=dbms_lob.getlength(s_clob)+1;  
  --接着这里把聚合后的数据写入clob对象中去，格式如下：
  --dbms_lob.write(被写入lob, 写入长度(指写入lob数据)，写入起始位置(指被写入lob)，写入lob数据);
 dbms_lob.write(p_clob,amount_p,offset_p,vchar_position);
  dbms_lob.write(s_clob,amount_s,offset_s,vchar_strain);
  --写入完成后，关闭lob指针
  dbms_lob.close(p_clob);
  dbms_lob.close(s_clob);
  --提交修改
  commit;
exception
  when others then
  --出现错误，提示失败
  dbms_output.put_line('数据存入失败！');
  rollback;
end number_merge_char;

4、至此，执行了该存储过程后就把straconstantinfo表中的position字段和strain字段两个列中的数据全部聚合成字符串并存入了clob_table表中的position_clob和strain_clob中。

5、分析：

（1）wm_concat()函数是一个undocument函数，在后续的版本中无法确保还是否会提供该函数的支持。

（2）vm_concat函数是无序的，使用该函数聚合后的字符串可能并没有按照顺序进行聚合，可能是无序的。

 

 

二、小数据量的字符串截取——substr()函数

1、建表strainfo(id,position_clob,strain_clob)，其中除了id是number类型，其他都是clob类型；

--本存储过程功能为将straininfo表中strain_clob长字段的clob字符串进行截取分割，并还原回原来的数据形式，
--存入数组并输出来（对应一中的字符串聚合）。
create or replace procedure infoclob_substr_strain(s_out_array out array.filename_array) 
as
  --定义一个clob接收表中的clob数据
 sclob clob;
  s_read_lenth number;
  s_out varchar2(32767);
  v_clob1 varchar2(32767);
  str1 varchar2(32767);
  l_len integer;
  i integer;
begin
  --数组初始化
  s_out_array := array.filename_array(); 
  --数组扩展到10000个元素，也可以按具体需求修改这个数组的大小
  s_out_array.extend(10000); 
  select strain_clob into sclob from straininfo where id = '1';
  --获取该clob对象字符串的长度
 s_read_lenth :=length(sclob);
  --从lob数据中读取指定长度数据到缓冲区：dbms_lob.read(lob数据，指定长度，起始位置，存储返回lob类型值变量);
  dbms_lob.read(sclob,s_read_lenth,1,s_out);
  --以逗号为分隔符截取读取到s_out中的字符串
  str1 := ','||s_out||',';
  --计算出有几个“,”(数字)
  l_len := length(translate(str1, ',0123456789 ', ','));
  for i in 1..l_len-1 loop
  --把数字都放数组里
    select substr(str1,instr(str1,',',1,i)+1 ,instr(str1,',',1,i+1)-instr(str1,',',1,i)-1 ) 
      into s_out_array(i) from dual;
      --dbms_output.put_line(t(i));
  end loop;
  --使用 隐式游标 判断数组的数据是否有效，当有数据返回时候该属性为true
  if sql%found then 
  --打印数组
  dbms_output.put_line('游标打开！数组数据写入成功！如下：');
  for i in 1..l_len-1 loop
    dbms_output.put_line(s_out_array(i));
  end loop;
  else
  dbms_output.put_line('游标未打开！');
  end if;
  --下面注释掉的这部分与上面的截取分割输出字符串的部分是一样的功能，也可以选择这种方法
 --截取clob部分字符串,注意substr是一个函数而不是一个存储过程，不修改clob变量的值
  --v_amount:=11;  --截取长度
  --v_offset:=1;    --开始截位置
  --截取s_out中的v_amount长度的字符串赋值给v_clob1
  --v_clob1 := dbms_lob.substr(s_out,v_amount,v_offset);
  --dbms_output.put_line(v_clob1);
  exception
  when too_many_rows then
  dbms_output.put_line('too_many_rows');
  when others then dbms_output.put_line('others unknown errors !');
end infoclob_substr_strain;

 

三、自定义字符串聚合函数

1、自定义聚集函数的介绍——相关资料来源于oracle文档的翻译

由于一中的wm_concat()函数的局限性，以及无法处理超长字符串，它的最大处理长度仅能达到32767，即varchar2的最大定义长度，当超过该长度便无可奈何，因此通过资料查找，找到了这种使用自定义函数的方式来完成字符串聚合的功能。

oracle提供了很多预定义好的聚集函数，比如max(), sum(), avg()， 但是这些预定义的聚集函数基本上都是适应于标量数据(scalar data)， 对于复杂的数据类型，比如说用户自定义的object type, clob等， 是不支持的。

但是，幸运的是，用户可以通过实现oracle的extensibility framework中的odciaggregateinterface来创建自定义聚集函数，而且自定义的聚集函数跟内建的聚集函数用法上没有差别。odci是oracle datacartridge interface 几个单词的首字母缩写。

2、用户自定义的聚合函数概述

通过实现odciaggregate rountines来创建自定义的聚集函数。可以通过定义一个对象类型（object type），然后在这个类型内部实现odciaggregate 接口函数(routines)，可以用任何一种oracle支持的语言来实现这些接口函数，比如c/c++, java, pl/sql等。在这个object type定义之后，相应的接口函数也都在该object type body内部实现之后，就可以通过create function语句来创建自定义的聚集函数了。

每个自定义的聚集函数需要实现4个odciaggregate 接口函数，这些函数定义了任何一个聚集函数内部需要实现的操作，这些函数分别是 initialization, iteration, merging 和 termination。

①odciaggregateinitialize 这个函数用来执行初始化操作(initialization). oracle会调用这个函数来初始化自定义函数计算。 初始化的聚集环境(aggregationcontext)会以对象实例(object type instance)传回给oracle.

②odciaggregateiterate 这个函数用来遍历需要处理的数据，被oracle重复调用。每次调用的时候，当前的aggreation context 和新的（一组）值会作为传入参数。这个函数会处理这些传入值，然后返回更新后的aggregation context. 这个函数对每一个non-null的值都会被执行一次。null值不会被传递个聚集函数。

③odciaggregatemerge 这个函数用来把两个aggregationcontext整合在一起，一般用来并行计算中（当一个函数被设置成enable parallel 处理的时候）。

④odciaggregateterminate 这个函数是oracle调用的最后一个函数。它接收aggregationcontext作为参数，返回最后的aggregate value.

--example: 下面介绍自定义聚集函数是如何工作的
select avg(t.sales)from annualsales tgroupby t.state;
--为了完成求平均值的计算，avg函数经历下面几个步骤：
--(1) initializes： 初始化aggregationcontext:
runningsum = 0;  runningcount = 0;
--(2) iteratively 处理每个连续的输入，同时更新aggregation context:
runningsum += inputval; runningcount ++;
--(3) 【这步可选】merge 整合两个aggregationcontext 返回一个aggregation context. 如果需要这一步的话，它是在termination之前执行。
runningsum = runningsum1 + runningsum2;
runningcount = runningcount1 + runningcount2;
--(4) terminates 计算出最后的结果，通过最后的aggregation context来返回最后的aggregated value.
return (runningsum / runningcount);
--如果avg是自定义的聚集函数的话，与之相对应的对象类型(object type)需要实现对应的odciaggregate的接口函数。
--变量runningsum 和 runningcount 是对象类型中的属性(attribute). 

3、关于oracle中的自定义聚合函数的学习这里就不再赘述。

四、大数据量的字符串聚合——xmlagg()函数

1、建表vibrationinfo(v_id,curtime,time_clob,position_clob)，v_id是number类型，curtime是varchar2类型，其他都是clob类型；

建表 straconstantinfo(id,position,strain);

2、建立序列id_v_seq，初始为0，自增为1，nocycle，nocache

3、建立实现字符串聚合的存储过程

下面的存储过程实现了使用xmlagg()函数对一个字段的值进行合并，并且输出clob格式。

create or replace procedure v_constant_to_infoclob as
  n number;
  --实时表position,strain字段合并后存放在p_clob,s_clob中
  t_clob clob;
  p_clob clob;
  --存放一个字段拼接起来的一串字符
  lob_time clob;
  lob_position clob;
begin
  --先插入一条空clob的记录，后续再把数据存进去
 insert into vibrationinfo(v_id,curtime,time_clob,position_clob) 
      values(id_v_seq.nextval,to_char(sysdate,'yyyy-mm-dd hh24:mi'),empty_clob(),empty_clob());
  --将序列初始化后的当前值赋给n
  select id_v_seq.currval into n from dual;
  --得到clob的指针，并存放在变量x_clob中，这里要加上for update之句，表示事务的开始，否则dbms_lob.open 的执行会产生错误
  select time_clob,position_clob into t_clob,p_clob from vibrationinfo where v_id = n for update;
  --根据clob指针以读写方式打开clob
  dbms_lob.open(t_clob,dbms_lob.lob_readwrite);
  dbms_lob.open(p_clob,dbms_lob.lob_readwrite);
  --使用xmlagg进行对一个字段的值合并，并输出clob格式
  select xmlagg(xmlparse(content curtime || ',' wellformed)).getclobval() into 
      lob_time from (select v_id,curtime,position from vibraconstantinfo order by curtime);
  select xmlagg(xmlparse(content position || ',' wellformed)).getclobval() into 
      lob_position from (select v_id,curtime,position from vibraconstantinfo order by curtime);
  --append用于将一个大对象添加到另一个大对象中，此时是将源大对象的内容全部添加过去，即lob_position大对象的内容添加到p_clob大对象中
  dbms_lob.append(t_clob,lob_time);
  dbms_lob.append(p_clob,lob_position);
  --关闭lob指针
  dbms_lob.close(t_clob);
  dbms_lob.close(p_clob);
  --执行实时表的清空，这里是因为实时表的数据要实时存入，所以要把数据存入历史表后再把实时表的数据清空
  delete /*+ rule */ from vibraconstantinfo;
  --提交修改
  commit;
exception
  when others then
  --出现错误，提示失败
  dbms_output.put_line('数据合并的存储过程出现异常！将执行回滚。');
      rollback;
end v_constant_to_infoclob;

4、分析：这种方式的聚合优点很多，不仅可以解决其他函数字符串长度的限制，而且执行时间很快。

5、关于oracle中的xml函数的学习这里就不再赘述。

总结：以上三种字符串聚合的方式，我都使用过

1、wm_concat()函数主要缺陷就是字符串长度限制，如果有特殊需求，要处理超长字符串的话，该函数是无法满足需求的，不过如果是在一些普通长度的聚合等情况下，该函数的使用还是很方便的；

2、关于自定义聚合函数，它可以解决wm_concat()函数的缺陷。于是之后我使用这个自定义函数来进行数据处理，但是由于我的中表的数据量比较大，简单估算聚合后的字符串长度达到了25w，而且我在运行的过程中发现，它的执行速度很慢，就算最快的时候也有30s之久，所以为了解决这个速度慢的问题，在经过进一步的学习，我找到了使用xml函数来完成字符串聚合。

3、接着就引入了xmlagg函数，进过资料查找，发现在oracle中对xml块的操作，其速度会相当快，因此我最终改用了这个函数来实现我的数据库中的具体要求，最终达到了超长数据聚合，并且快速存入数据库表的功能。

注：主要的实现代码内容为原创，资料部分来源与oracle文档