oracle问题处理记录-长字符串逗号分隔刷新

邮件项目中密送人，抄送人用逗号分隔保存为blob存在邮件实体类中

id（邮件ID）	subject（邮件标题）	cc（抄送人工号）
xxx	欢迎访问xx系统，为你导航	d001，d002，d003

后来实在影响效率和太不科学，决定把密送人抄送人和邮件做一个关联表：

id	mailID	type	userID
1	xxx	cc	d001
2	xxx	cc	d002

所以需要把原有的表存储的抄送人刷到新表中，以下为操作：

1.编写逗号分隔字符串函数。

https://www.cnblogs.com/dshore123/p/7805050.html

https://www.cnblogs.com/soundcode/p/6145216.html

使用上面的函数前需要建立一个类型

CREATE OR REPLACE TYPE strsplit_type IS TABLE OF VARCHAR2 (4000)；

2.编写存储过程-先将存在抄送人的整体联合邮件ID存入新表

create or replace PROCEDURE A_UPDATE_RECIPIENT AS 

 Cursor RECIPIENT is  select bcc,id from MAIL where bcc is not null;
  t RECIPIENT%ROWTYPE;
BEGIN
    for t in RECIPIENT loop
      insert into MAIL_RECIPIENT (mail_box_id,ACCOUNT,type) values(t.id,t.bcc,'BCC');
    end loop;
    --去掉最后一位的分号
    update MAIL_RECIPIENT set account =  substr(account,0,length(account)-1)   where substr(account,length(account),1)=';';
  commit;
END A_UPDATE_RECIPIENT;

3.编写存储过程-将逗号分隔后的存入新表

CREATE OR REPLACE PROCEDURE A_UPDATE_FENHAO AS 
 Cursor RECIPIENT is  select account,mail_box_id,type from MAIL_RECIPIENT where account like '%;%';
  t RECIPIENT%ROWTYPE;
BEGIN
   for t in RECIPIENT loop
      insert into MAIL_RECIPIENT (mail_box_id,ACCOUNT,type)   select t.mail_box_id,column_value,t.type from table(split(t.account));
    end loop;
    --删除还有逗号的记录
    delete from MAIL_RECIPIENT where account like '%;%';
    --删除重复数据
    delete from MAIL_RECIPIENT where rowid not in (select min(rowid) from MAIL_RECIPIENT group by mail_box_id,ACCOUNT,type );
END A_UPDATE_FENHAO;