【Oracle】实现函数返回值为数据集合

创建自定义类型

CREATE OR REPLACE TYPE OBJ_SPLIT_STR AS OBJECT (
	SPLIT_STR VARCHAR2(32)
);

创建嵌套表

CREATE OR REPLACE TYPE TYPE_SPLIT_STR AS TABLE OF OBJ_SPLIT_STR;

创建函数

CREATE OR REPLACE FUNCTION F_SPLIT_STR (
	str   varchar2,		--待分拆的字符串
	split varchar2		--数据分隔符
)
RETURN TYPE_SPLIT_STR PIPELINED
IS
	-- 定义表类型
	v_temp_error_group OBJ_SPLIT_STR;
	splitLen number(10) := LENGTH(split);
	startIndex number(10);
	endIndex number(10);
BEGIN
	startIndex := 1 - splitLen;
	endIndex := INSTR(str,split);
		
	IF endIndex = 0 THEN
		v_temp_error_group := OBJ_SPLIT_STR(str);
		PIPE ROW (v_temp_error_group);
	END IF;

	WHILE endIndex > 0 LOOP 
		endIndex := INSTR(str, split, startIndex + splitLen);
		
		IF endIndex = 0 THEN
			v_temp_error_group := OBJ_SPLIT_STR(SUBSTR(str, startIndex + splitLen, LENGTH(str) - startIndex));
			PIPE ROW (v_temp_error_group);
		ELSE
			v_temp_error_group := OBJ_SPLIT_STR(SUBSTR(str, startIndex + splitLen, endIndex - startIndex - splitLen));
			PIPE ROW (v_temp_error_group);
		END IF;
		
		startIndex := endIndex;
	END LOOP;

END;

调用

SELECT * FROM TABLE(F_SPLIT_STR('12|13|14|15'), '|');

运行结果：