--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',','));