(1)定义split_type类型:CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000) / (2)定义split函数:
CREATE OR REPLACE FUNCTION split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) 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; / (3)存储过程中,使用类似 For T In ( select a,b,c,d from table (split(1,2,3,4,,)) ) Loop –注意下面的inserti语句,varchar类型的值需要补充引号上去 Execute Immediate insert into tableName set fieldName = ||T.a ; Execute Immediate commit; End Loop; 的查询语句,把分开的结果拼成sql语句并写入到表中。
如果觉得《oracle端怎么把字符串分割成数组 – java – 前端》对你有帮助,请点赞、收藏,并留下你的观点哦!