Oralce自定义函数实现字符串分割

1.定义自定义类型，函数返回自定义类型
SQL> create or replace type str_split is table of varchar2(1000);
  2  /
 
Type created
 
2.用一个Function来实现字符串分割的功能。
/*
* 功能：字符串分割。
* 参数：p_str 分割的字符串
*       p_delimiter 分隔符
*/
create or replace function fun_str_split(p_str       in varchar2,
                                         p_delimiter in varchar2)
    return str_split
    pipelined is
    v_start  number := 1; --开始查找的位置
    v_index  number := 0; --查找到的索引
    v_length number := length(p_str); --字符串长度
begin
   
    while (v_start <= v_length) loop
        --从开始位置开始查找，每次查找完毕更新开始位置为查找到的位置。
        v_index := instr(p_str, p_delimiter, v_start);
       
        if v_index = 0 then
            --未找到，说明结束了
            pipe row(substr(p_str, v_start));
            v_start := v_length + 1;
        else
            pipe row(substr(p_str, v_start, v_index - v_start));
            v_start := v_index + 1;--查找到的索引 + ','占的长度
        end if;
   
    end loop;
   
    return;
end;

3.测试
SQL> select * from table(fun_str_split('hello,world,abc',','));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
hello
world
abc

//注意上面使用了column_value，这是一个PIPELINED function返回的默认列的名字。

关于pipelined（摘自http://www.linuxidc.com/Linux/2011-05/35797.htm）
pipelined是Oracle的一个关键字。
普通函数的结尾加一个pipelined关键字，就是管道函数。
这个函数的返回参数类型为集合，这是为了使其能作为表函数使用。
表函数就是在from子句中以table(v_resultset)调用的，v_resultset就是一个集合类型的参数。
管道化表函数必须返回一个集合。
在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。
一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
//典型的Pipelined例子  
//当使用PL/SQL表函数时，下面是典型的步骤  
//生产函数必须在其定义中使用PIPELINED关键字  
//生产函数必须使用一个out参数，这个参数是一个集合，对应到返回的结果集  
//一旦有结果产生，通过使用PIPE ROW关键字将其输送给消费函数  
//生产函数必须以RETURN语句结束，但是不需要制定返回值  
//消费函数必须使用TABLE关键字，将从PIPELINE function返回的行集当成一个常规表  
//第一步.定义一个返回的行集的格式。  
//在这个实例中，我们返回这三个类型的值:int,date,varchar2(25)  
CREATE OR REPLACE TYPE myObjectFormat   
AS OBJECT  
(  
  A   INT,  
  B   DATE,  
  C   VARCHAR2(25)  
)  
/  

//下一步，为第一步定义的类型定义一个集合类型  
CREATE OR REPLACE TYPE myTableType  
   AS TABLE OF myObjectFormat  
/  

//最后，生产函数被打包到一个包里面，它是一个pipelined function就像被pipelined关键字所标记一样。  
CREATE OR REPLACE PACKAGE myDemoPack  
AS  
      FUNCTION prodFunc RETURN myTableType PIPELINED;  
END;  
/  

CREATE OR REPLACE PACKAGE BODY myDemoPack AS  
FUNCTION prodFunc RETURN myTableType PIPELINED IS  
BEGIN  
  FOR i in 1 .. 5  
    LOOP  
      PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));  
    END LOOP;  
    RETURN;  
  END;  
END;  
/  

//测试结果：  
alter session set nls_date_format='yyyy-mm-dd';  
SELECT * FROM TABLE(myDemoPack.prodFunc());  
                A B           C  
----------------- ----------- ------------  
                1 2011-05-05  Row 1  
                2 2011-05-06  Row 2  
                3 2011-05-07  Row 3  
                4 2011-05-08  Row 4  
                5 2011-05-09  Row 5  
//结论：  
//在一个select语句里面，我们需要一个数据源，而不是一张表的话，Pipelined functions非常有用。