以下函数在package下实现:
包体:
TYPE TYPE1 IS TABLE OF VARCHAR2(10);
--分割字符串函数
FUNCTION SPLIT(P_ITEMID IN VARCHAR2, P_SPLIT IN VARCHAR2) RETURN TYPE1;
包内容:
--分割字符串函数
FUNCTION SPLIT(P_ITEMID IN VARCHAR2, P_SPLIT IN VARCHAR2) RETURN TYPE1 IS
V_ITEMID VARCHAR2(4000);
V_ITEM_IDS TYPE1;
V_IDX pls_integer;
V_CNT NUMBER;
V_ITEM VARCHAR2(10);
BEGIN
V_ITEM_IDS := TYPE1();
V_CNT := 0;
V_ITEMID := P_ITEMID;
LOOP
--初始化一个空间
V_ITEM_IDS.EXTEND(1);
V_CNT := V_CNT + 1;
V_IDX := INSTR(V_ITEMID, P_SPLIT);
IF V_IDX > 0 THEN
V_ITEM := SUBSTR(V_ITEMID, 1, V_IDX - 1);
DBMS_OUTPUT.put_line(V_ITEM || ' 网点ID ' || V_CNT);
V_ITEM_IDS(V_CNT) := SUBSTR(V_ITEMID, 1, V_IDX - 1);
V_ITEMID := SUBSTR(V_ITEMID, V_IDX + LENGTH(P_SPLIT));
ELSE
V_ITEM_IDS(V_CNT) := V_ITEMID;
EXIT;
END IF;
END LOOP;
RETURN V_ITEM_IDS;
END SPLIT;