2015.1.15 利用函数实现将一行记录拆分成多行记录 （多年想要的效果）

将一行转换为多列的函数

首先定义一个行类型:            

CREATE OR REPLACE TYPE "SPLIT_ARR"  AS OBJECT(nowStr varchar2(18))

其次以此行类型定义一个表类型:

CREATE OR REPLACE TYPE "SPLIT_TAB" AS TABLE of split_arr;

定义函数(此函数完成字符串拆分功能):

 

CREATE OR REPLACE FUNCTION GetSubStr(

                   str in varchar2, --待分割的字符串

                   splitchar in varchar2 --分割标志

            )

            return split_tab

            IS

              restStr varchar2(2000) default GetSubStr.str;--剩余的字符串

              thisStr varchar2(18);--取得的当前字符串

              indexStr int;--临时存放分隔符在字符串中的位置

            

              v split_tab := split_tab(); --返回结果

 

            begin

                 dbms_output.put_line(restStr);

                 while length(restStr) != 0

                   LOOP

                     <<top>>

                     indexStr := instr(restStr,splitchar); --从子串中取分隔符的第一个位置

 

                     if indexStr = 0 and length(restStr) != 0  then--在剩余的串中找不到分隔符

                        begin

                          v.extend;

                          v(v.count) := split_arr(Reststr);

                          return v;

                        end;

                     end if;

                   

                     if indexStr = 1 then---第一个字符便为分隔符,此时去掉分隔符

                        begin

                             restStr := substr(restStr,2);

                             goto   top;

                        end;

                     end if;

                   

                     if length(restStr) = 0 or restStr is null then

                        return v;

                     end if;

                  

                     v.extend;

                     thisStr := substr(restStr,1,indexStr - 1); --取得当前的字符串

                     restStr := substr(restStr,indexStr + 1);---取剩余的字符串

 

                     v(v.count) := split_arr(thisStr);

                   END LOOP;

                 return v;

            end;