Oracle自定义数据类型

 1 CREATE OR REPLACE FUNCTION split(p_str       IN clob,
 2                                      p_delimiter IN VARCHAR2 default (',') --分隔符，默认逗号
 3                                      ) RETURN split_type IS
 4   j        INT := 0;
 5   i        INT := 1;
 6   len      INT := 0;
 7   len1     INT := 0;
 8   str      clob;
 9   my_split split_type := split_type();
10 BEGIN
11   len  := LENGTH(p_str);
12   len1 := LENGTH(p_delimiter);
13
14   WHILE j < len LOOP
15     j := INSTR(p_str, p_delimiter, i);
16
17     IF j = 0 THEN
18       j   := len;
19       str := SUBSTR(p_str, i);
20       my_split.EXTEND;
21       my_split(my_split.COUNT) := str;
22
23       IF i >= len THEN
24         EXIT;
25       END IF;
26     ELSE
27       str := SUBSTR(p_str, i, j - i);
28       i   := j + len1;
29       my_split.EXTEND;
30       my_split(my_split.COUNT) := str;
31     END IF;
32   END LOOP;
33
34   RETURN my_split;
35 END split;

1 CREATE OR REPLACE TYPE "SPLIT_TYPE" IS TABLE OF VARCHAR2 (4000);
2 CREATE OR REPLACE TYPE "TYPE_STR_SPLIT" IS TABLE OF VARCHAR2 (4000);
3 CREATE OR REPLACE TYPE "TY_ROW_STR_SPLIT" as object (strValue VARCHAR2 (4000));
4 CREATE OR REPLACE TYPE "TY_TBL_STR_SPLIT" IS TABLE OF ty_row_str_split;