Oracle中函数返回数据集的方法（拆分表字符串函数，返回数据集）

 

--1、创建表结构对象

/*创建表结构类型 */
CREATE OR REPLACE TYPE TY_TABLE_TYPE AS OBJECT ( key_column VARCHAR2 (1000), split_column VARCHAR2 (1000));

--2、定义嵌套表类型

/*创建嵌套表类型*/
CREATE OR REPLACE TYPE TY_TABLE_LIST AS TABLE OF TY_TABLE_TYPE;

---3、创建函数

/*创建函数*/

CREATE OR REPLACE FUNCTION split_tbcolumn (inSql VARCHAR2, delimiter varchar2)
  RETURN TY_TABLE_LIST PIPELINED IS
/*
拆分表字符串函数，返回数据集
创建此函数前必须先执行下面两步：
--1、创建表结构对象
\*创建表结构类型 *\
CREATE OR REPLACE TYPE TY_TABLE_TYPE AS OBJECT ( key_column VARCHAR2 (1000), split_column VARCHAR2 (1000));
--2、定义嵌套表类型
\*创建嵌套表类型*\
CREATE OR REPLACE TYPE TY_TABLE_LIST AS TABLE OF TY_TABLE_TYPE;
调用此函数方法：
select * from TABLE(split_tbcolumn('select wf_serial_no, deal_person from bomc_itil.dwfformf1002003 where rownum < 20',','));
*/
  v_test_type TY_TABLE_TYPE;
  cnt         integer;
  I_str       VARCHAR2(4000);
  I_num       integer;
  str1        VARCHAR2(4000);
  i           integer;
  i1          integer;
  type curs  is ref  cursor;
  cur curs;
  v_key_column VARCHAR2 (4000);
  v_split_column VARCHAR2 (4000);
BEGIN

  OPEN  cur for inSql;
  LOOP
  FETCH cur INTO v_key_column,v_split_column;
  EXIT  WHEN   cur%NOTFOUND;
    I_str       := v_split_column;
    select nvl(length(I_str) - length(replace(I_str, delimiter, '')),0) into cnt from dual;
    --DBMS_OUTPUT.PUT_LINE('cnt:' || cnt);
    if cnt = 0 then
      --DBMS_OUTPUT.PUT_LINE(v_key_column || ':' || I_str);
      /* 赋值到临时变量中 */
      v_test_type := TY_TABLE_TYPE(v_key_column, I_str);
      /* 将临时变量放入返回的结果中 */
      PIPE ROW(v_test_type);
    else
      FOR I_num in 1 .. cnt+1 LOOP
        begin
          str1 := ' ';
          if I_num = 1 then
            i := 0;
          else
            i := instr(I_str, delimiter, 1, I_num - 1);
          end if;
          i1 := instr(I_str, delimiter, 1, I_num);
          if i >= length(I_str) or i1 > length(I_str) then
            str1 := ' ';
          else
            select substr(I_str, i + 1, decode(i1, 0, length(I_str) + 1, i1) - i - 1) into str1  from dual;
          end if;
        exception
          when others then
            null;
        end;
        --DBMS_OUTPUT.PUT_LINE(v_key_column || ':' || str1);
        /* 赋值到临时变量中 */
        v_test_type := TY_TABLE_TYPE(v_key_column, str1);
        /* 将临时变量放入返回的结果中 */
        PIPE ROW(v_test_type);
      END LOOP;
    end if;
  END  LOOP;
  CLOSE cur;
  RETURN;
END;

--4、调用函数

/*调用函数*/

select * from TABLE(split_tbcolumn ('select wf_serial_no, deal_person from bomc_itil.dwfformf1002003 where rownum < 20',','));