oracle一个表拆成多个表 oracle拆分函数 将字符串拆分成多行多字段表数据

--创建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,六七'));

运行结果: