1、定义一个自定义类型
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);2、定义一个函数
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split PIPELINED IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); 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); PIPE ROW (str); IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; PIPE ROW (str); END IF; END LOOP; RETURN; END fn_split;3、字符串数组的应用
/* description:车机联控下发到责任单位 */ PROCEDURE CJLK_UNIT_DOWN( P_ERRCD IN OUT NUMBER, P_ERRMSG IN OUT VARCHAR2, P_CJLK_XH IN VARCHAR2, P_JSDWBH IN VARCHAR2, P_JSDWMC IN VARCHAR2, P_ZT IN VARCHAR DEFAULT 'A' )AS CURSOR L_JSDWBH IS SELECT * FROM TABLE (CAST (FN_SPLIT(P_JSDWBH,';') AS ty_str_split)); BEGIN P_ERRCD:=0; P_ERRMSG:=''; UPDATE AQGL.CJLK SET JSDWBH=P_JSDWBH,JSDWMC=P_JSDWMC,ZT='B' WHERE XH=P_CJLK_XH; FOR I IN L_JSDWBH LOOP INSERT INTO AQGL.CJLK_DWFK (XH,PID,JSDWBH,JSDWMC,ZT) VALUES (sys_guid(),P_CJLK_XH,I.COLUMN_VALUE,GET_DWMC(i.column_value),P_ZT); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; P_ERRCD:=SQLCODE; P_ERRMSG:=SQLERRM; END;其中P_JSDWBH包含了多个用;分隔的字符串。
posted on 2012-05-09 11:41 蓝红石 阅读( ...) 评论( ...) 编辑 收藏转载于:https://www.cnblogs.com/liuweihua/archive/2012/05/09/2491480.html
相关资源：oracle遍历数据库查找字符串