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非常有用。