一个字段的值 1,2,3,4。。。 要输出一列
因为不知道多少个,所以 substr instr 用起来比较麻烦
自己写一个函数 类似于split
CREATE OR REPLACE
FUNCTION FSS_SPLIT(
P_LIST NVARCHAR2,
P_SEP NVARCHAR2 := ','
) RETURN TYPE_SPLIT PIPELINED
IS
L_IDX PLS_INTEGER;
V_LIST NVARCHAR2(32767) := P_LIST;
BEGIN
LOOP
L_IDX := INSTR(V_LIST,P_SEP);
IF L_IDX > 0 THEN
IF SUBSTR(V_LIST,1,L_IDX-1) IS NOT NULL THEN
PIPE ROW(SUBSTR(V_LIST,1,L_IDX-1));
END IF;
V_LIST := SUBSTR(V_LIST,L_IDX+LENGTH(P_SEP));
ELSE
IF V_LIST IS NOT NULL THEN
PIPE ROW(V_LIST);
END IF;
EXIT;
END IF;
END LOOP;
RETURN;
END;