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;
自定义Oracle数据类型
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;