oracle 字符串字段分成多个字段后创建视图

/**

情景：某表有一字段为字符串类型，字段中有1~2个英文分号或者中文的分号

      现要根据该字段的分号把该字段划分为2~3个字段，并组成与表中其他字段组成视图。

     

解决方法：

      1）创建table类型，table记录类型跟视图记录类型一样，然后创建返回该table类型的方法。

         方法用于处理str字段，将其分成多个字段，然后生成table记录类型的数据，并填充table，

         最后将数据返回。

         创建视图时直接根据方法返回的table来创建视图。

        

      2）直接用select创建视图，配合case when。

个人推荐使用方法2.

**/

-----------------------------------创建例子用表------------------

create table test_3(

V_NAME VARCHAR2( 10),                       

STR    VARCHAR2(100 )

);

insert into test_3 values( 'aa','q123；df345；我是谁' );

insert into test_3 values( 'bb','aaabbbb；123456' );

insert into test_3 values( 'cc','adbs;123456；kyleming' );

commit;

------------------------------------------------

--创建记录对象

CREATE OR REPLACE TYPE my_substr  AS OBJECT(

v_name varchar2( 18),

v_str1 varchar2( 30),

v_str2 varchar2( 30),

v_str3 varchar2( 30)

);

---根据记录类型创建表对象

create or replace type my_substr_tab as table of my_substr;

-----------------------------------------------function begin---------------------

-----------------创建方法------------------

create or replace function split_str_tab

 return my_substr_tab

is

v_str_split my_substr_tab := my_substr_tab();

str1 varchar2( 30);

str2 varchar2( 30);

str3 varchar2( 30);

sp_ind  number := 0;

rest_str varchar2( 100);

sp_count number;

cursor t3_cur is select * from test_3;

v_row t3_cur%rowtype;

begin

  open t3_cur;

  fetch t3_cur into v_row;

  while t3_cur% found loop

  --清空str1、str2、str3字符串---

  str1 := '';

  str2 := '';

  str3 := '';

  ----------------判断有多少个分号--------------

  sp_count := length(v_row.str) - length (regexp_replace(v_row.str,'[;|；]', ''));

  if(sp_count = 1) then               ----一个分号的处理

     sp_ind := regexp_instr(v_row.str, '[;|；]');

     str1 := substr(v_row.str,1 ,sp_ind-1);

     str2 := substr(v_row.str,sp_ind+1 );

     v_str_split.extend;             ---扩充表格记录

     v_str_split(v_str_split.count) := my_substr(v_row.v_name,str1,str2,str3); ---填充扩充的记录

  elsif(sp_count = 2) then       -----两个分号的处理

     sp_ind := regexp_instr(v_row.str, '[;|；]');

     str1 := substr(v_row.str,1 ,sp_ind-1);

     rest_str := substr(v_row.str,sp_ind+1 );

     sp_ind := regexp_instr(rest_str, '[;|；]');

     str2 := substr(rest_str,1 ,sp_ind-1);

     str3 := substr(rest_str,sp_ind+1 );

     v_str_split.extend;

     v_str_split(v_str_split.count) := my_substr(v_row.v_name,str1,str2,str3);

   else

     null;

   end if;

   fetch t3_cur into v_row;

   end loop;

   close t3_cur;

    return v_str_split;

end;

--------------------------------function end---------------------------

----create view from function----------------

create view v_test_3 as select * from table(split_str_tab());

select * from v_test_3;

---------------end--------------------

------------------------------- 直接用select语句创建视图----------------------

create or replace view v_t3 as

select v_name,

      ( case when length(str) - length(regexp_replace(str,'[;|；]' ,'')) >=1 then

         regexp_substr(str, '[^;|；]+',1 ,1, 'i') else '' end) str1,

      ( case when length(str) - length(regexp_replace(str,'[;|；]' ,'')) >=1 then

         regexp_substr(str, '[^;|；]+',1 ,2, 'i') else '' end) str2,

      ( case when length(str) - length(regexp_replace(str,'[;|；]' ,'')) >=2 then

         regexp_substr(str, '[^;|；]+',1 ,3, 'i') else '' end) str3

from test_3;

select * from v_t3;