--创建OBJECT类型,包含三个字段
CREATE OR REPLACE TYPE OBJ_V_TABLE AS OBJECT
(
ID VARCHAR2(50),
NAME VARCHAR2(50),
DESCP VARCHAR2(50)
);
--创建一个表类型
CREATE OR REPLACE TYPE V_TABLE IS TABLE OF OBJ_V_TABLE;
--创建拆分函数,分隔符每三个对应三个表字段
CREATE OR REPLACE FUNCTION SPLIT(P_STR VARCHAR2,
P_DELIMITER VARCHAR2 DEFAULT ',')
RETURN V_TABLE
IS
RS V_TABLE := V_TABLE();
L_STR VARCHAR2(4000) := '';
L_LEN NUMBER := 0;
ID VARCHAR2(50);
NAME VARCHAR2(50);
DESCP VARCHAR2(50);
BEGIN
L_STR := P_STR;
L_LEN := LENGTH(P_DELIMITER);
WHILE LENGTH(L_STR) > 0 LOOP
RS.EXTEND;
IF INSTR(L_STR, P_DELIMITER,1,3) > 0 THEN
ID := SUBSTR(L_STR, 1, INSTR(L_STR, P_DELIMITER) - 1);
L_STR := SUBSTR(L_STR, INSTR(L_STR, P_DELIMITER) + L_LEN);
NAME := SUBSTR(L_STR, 1, INSTR(L_STR, P_DELIMITER) - 1);
L_STR := SUBSTR(L_STR, INSTR(L_STR, P_DELIMITER) + L_LEN);
DESCP := SUBSTR(L_STR, 1, INSTR(L_STR, P_DELIMITER) - 1);
L_STR := SUBSTR(L_STR, INSTR(L_STR, P_DELIMITER) + L_LEN);
RS(RS.COUNT) := OBJ_V_TABLE(ID, NAME, DESCP);
ELSIF INSTR(L_STR, P_DELIMITER,1,2) > 0 THEN
ID := SUBSTR(L_STR, 1, INSTR(L_STR, P_DELIMITER) - 1);
L_STR := SUBSTR(L_STR, INSTR(L_STR, P_DELIMITER) + L_LEN);
NAME := SUBSTR(L_STR, 1, INSTR(L_STR, P_DELIMITER) - 1);
L_STR := SUBSTR(L_STR, INSTR(L_STR, P_DELIMITER) + L_LEN);
DESCP := L_STR;
RS(RS.COUNT) := OBJ_V_TABLE(ID, NAME, DESCP);
EXIT;
ELSIF INSTR(L_STR, P_DELIMITER,1,1) > 0 THEN
ID := SUBSTR(L_STR, 1, INSTR(L_STR, P_DELIMITER) - 1);
NAME := SUBSTR(L_STR, INSTR(L_STR, P_DELIMITER) + L_LEN);
RS(RS.COUNT) := OBJ_V_TABLE(ID, NAME, '');
EXIT;
ELSE
RS(RS.COUNT) := OBJ_V_TABLE(L_STR, '', '');
EXIT;
END IF ;
END LOOP ;
RETURN RS;
END ;
--运行
SELECT * FROM TABLE(SPLIT('a,张山,1,b,李四,2,c,王五,3,5,六七'));
运行结果: