[Oracle]: Oracle 分隔字符串方法

1. Function

/** *  Split the string according to the separator. * *  @param I_VALUES: String is divided *  @param I_SEPARATOR: Separator *  @return ARRAY_STRING: String array */CREATE OR REPLACEFUNCTION FN_SPLIT(    I_VALUES     IN VARCHAR2,    I_SEPARATOR  IN VARCHAR2)   RETURN ARRAY_STRING AS    V_STR VARCHAR2(200);    V_SEPARATOR VARCHAR2(10);    V_LENGTH NUMBER;    V_START NUMBER := 1;    V_END   NUMBER;    V_INDEX NUMBER := 1;    O_ARRAY_STRING ARRAY_STRING;BEGIN    O_ARRAY_STRING := ARRAY_STRING();    V_SEPARATOR := NVL(I_SEPARATOR, ',');    V_STR := LTRIM(RTRIM(I_VALUES));    V_LENGTH := LENGTH(V_STR);    WHILE (V_START <= V_LENGTH)      LOOP        V_END := INSTR(V_STR, V_SEPARATOR, V_START);        IF V_END = 0 THEN            IF LTRIM(RTRIM(SUBSTR(V_STR, V_START))) IS NOT NULL THEN                O_ARRAY_STRING.extend;                O_ARRAY_STRING(V_INDEX) := LTRIM(RTRIM(SUBSTR(V_STR,V_START)));                V_INDEX := V_INDEX + 1;            END IF;            V_START := V_LENGTH + 1;        ELSE            IF LTRIM(RTRIM(SUBSTR(V_STR, V_START, V_END - V_START))) IS NOT NULL THEN                 O_ARRAY_STRING.extend;                O_ARRAY_STRING(V_INDEX) := LTRIM(RTRIM(SUBSTR(V_STR, V_START, V_END - V_START)));                V_INDEX := V_INDEX + 1;            END IF;            V_START := V_END + 1;        END IF;      END LOOP;END FN_SPLIT

1. Test

/*********** FN_SPLIT TEST *************/DECLARE   I_VALUES VARCHAR2(200);  I_SEPARATOR VARCHAR2(10);  V_Return ARRAY_STRING;BEGIN  I_VALUES := 'AAA,  BB  , CCC';  I_SEPARATOR := '';  V_Return := FN_SPLIT(    I_VALUES => I_VALUES,    I_SEPARATOR => I_SEPARATOR  );  FOR i IN 1 .. V_Return.count LOOP    DBMS_OUTPUT.PUT_LINE(i || ': ' || V_Return(i));  END LOOP;END;