回覆列表
  • 1 # 使用者5329335765427

    用指定分隔符切割輸入的字串,返回一維陣列,每個陣列元素為一個子串。

    原始碼:

    CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

    CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

    RETURN ty_str_split

    IS

    j INT := 0;

    i INT := 1;

    len INT := 0;

    len1 INT := 0;

    str VARCHAR2 (4000);

    str_split ty_str_split := ty_str_split ();

    BEGIN

    len := LENGTH (p_str);

    len1 := LENGTH (p_delimiter);

    WHILE j = len

    THEN

    EXIT;

    END IF;

    ELSE

    str := SUBSTR (p_str, i, j - i);

    i := j + len1;

    str_split。

    EXTEND;

    str_split (str_split。COUNT) := str;

    END IF;

    END LOOP;

    RETURN str_split;

    END fn_split;

    /

    CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

    CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

    RETURN ty_str_split

    IS

    j INT := 0;

    i INT := 1;

    len INT := 0;

    len1 INT := 0;

    str VARCHAR2 (4000);

    str_split ty_str_split := ty_str_split ();

    BEGIN

    len := LENGTH (p_str);

    len1 := LENGTH (p_delimiter);

    WHILE j = len

    THEN

    EXIT;

    END IF;

    ELSE

    str := SUBSTR (p_str, i, j - i);

    i := j + len1;

    str_split。

    EXTEND;

    str_split (str_split。COUNT) := str;

    END IF;

    END LOOP;

    RETURN str_split;

    END fn_split;

    /

    測試:

    DECLARE

    CURSOR c

    IS

    SELECT *

    FROM TABLE (CAST (fn_split (’1;;12;;123;;1234;;12345’, ’;;’) AS ty_str_split

    )

    );

    r c%ROWTYPE;

    BEGIN

    OPEN c;

    LOOP

    FETCH c INTO r;

    EXIT WHEN c%NOTFOUND;

    DBMS_OUTPUT。

    put_line (r。column_value);

    END LOOP;

    CLOSE c;

    END;

    /

    結果:

    1

    12

    123

    1234

    12345。

  • 中秋節和大豐收的關聯?
  • 粵媒認為換帥能奪冠中超未免太簡單,施密特只是替罪羊,你認為呢?