oracle使用split_type分割字符串
CREATE OR REPLACE FUNCTION A_Test1(I_VAL VARCHAR2) RETURN VARCHAR2 is v_batch_col split_type; v_item_batch VARCHAR2(100); BEGIN select SPLIT(I_VAL,'@') into v_batch_col from dual; select column_value into v_item_batch from(select rownum rn,t1.column_value from table(v_batch_col) t1) where rn = 1; RETURN v_item_batch; EXCEPTION WHEN OTHERS THEN RETURN '错误'; END A_Test1;
1、定义一个 split_type 类型的变量
2、使用split函数分割字符串并赋值给 v_batch_col --select SPLIT(I_VAL,'@') into v_batch_col from dual;
3、使用变量接收分割后的值 --select column_value into v_item_batch from(select rownum rn,t1.column_value from table(v_batch_col) t1) where rn = 1;
split函数
CREATE OR REPLACE FUNCTION "SPLIT" ( p_str IN VARCHAR2, p_delimiter IN VARCHAR2 default(',') --分隔符,默认逗号 ) RETURN split_type IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); my_split split_type := split_type (); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter); WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i); IF j = 0 THEN j := len; str := SUBSTR (p_str, i); my_split.EXTEND; my_split (my_split.COUNT) := str; IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; my_split.EXTEND; my_split (my_split.COUNT) := str; END IF; END LOOP; RETURN my_split; END split;