Oracle 根据分隔符分隔字符串

为了让 PL/SQL 函数返回数据的多个行,必须通过返回一个 REF CURSOR 或一个数据集合来完成.REF CURSOR 的这种情况局限于可以从查询中选择的数据,而整个集合在可以返回前,必须进行具体化.Oracle 9i 通过引入的管道化表函数纠正了后一种情况.表函数是返回整个行的集(通常作为一个集合)的函数,可以直接从 SQL 语句中进行查询,就好像它是一个真正的数据库表一样.管道化表函数与之相似,但是它像在构建时一样返回数据,而不是一次全部返回.管道化表函数更加有效,因为数据可以尽可能快地返回.

•    管道化表函数必须返回一个集合.在函数中,PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的 RETURN 语句结束,以表明它已经完成.一旦我们创建了上述函数,我们就可以使用 TABLE 操作符从 SQL 查询中调用它.

     管道化表函数经常被用来把数据从一种类型转化成另一种类型.

   

  实现根据分隔符分隔字符串函数的例子:

  CREATE OR REPLACE TYPE t_Table IS TABLE OF VARCHAR2(4000);

  CREATE OR REPLACE FUNCTION FN_SplitStringByDelimiter(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
  RETURN t_Table PIPELINED AS
         v_delimiter_index INTEGER;
         v_start_index     INTEGER := 1;
         v_table           VARCHAR2(4000);
         v_delimiter_length INTEGER := LENGTH(p_delimiter);
  /*
    功能:把某一个字符串按照指定的分隔符分隔,并以Table形式返回
  */
  BEGIN
       LOOP
           -- 查找当前分隔符的位置
           v_delimiter_index := INSTR(p_string || p_delimiter, p_delimiter, v_start_index);
           -- 如果在字符串找不到字串则返回0,退出循环
           EXIT WHEN v_delimiter_index = 0;
           -- 得到分隔符前的值
           v_table := SUBSTR(p_string, v_start_index, v_delimiter_index - v_start_index);
           IF v_table IS NOT NULL THEN
              -- 返回该集合的单个元素
              PIPE ROW(v_table);
           END IF;
           -- 检索位置设置为当前分隔符的下一个
           v_start_index := v_delimiter_index + v_delimiter_length;
       END LOOP;
       -- 必须以一个空的 RETURN 语句结束
       RETURN;
  END FN_SplitStringByDelimiter;

   

  调用方法:SELECT * FROM TABLE(FN_SplitStringByDelimiter ('a.b.c.d.e', '.'));

  SELECT * FROM TABLE(FN_SplitStringByDelimiter ('123-;456-;789-;000', '-;'));

  返回结果: