oracle用分隔符解析字符串6,oracle以分隔符回循环字符串

oracle以分隔符来循环字符串

CREATE OR REPLACE PROCEDURE TEST(P_DETAIL_RESULT_COUNT IN VARCHAR2) AS

V_DETAIL_TYPE  VARCHAR2(64);

V_RESULT_COUNT NUMBER(12);

V_LEN          NUMBER;

V_LAST_POS     NUMBER;

V_POS          NUMBER;

V_NUM          NUMBER;

V_SUB_SIGN     VARCHAR2(1) := ',';

V_EQUAL_SIGN   VARCHAR2(1) := '=';

V_EQUAL_POS    NUMBER;

V_DETAIL       VARCHAR2(128);

BEGIN

V_LEN      := LENGTH(V_SUB_SIGN);

V_LAST_POS := 1 - V_LEN;

LOOP

V_POS := INSTR(P_DETAIL_RESULT_COUNT, V_SUB_SIGN, V_LAST_POS + V_LEN);

IF V_POS > 0 THEN

--FOUND

V_NUM := V_POS - (V_LAST_POS + V_LEN);

ELSE

--NOT FOUND

V_NUM := LENGTH(P_DETAIL_RESULT_COUNT) + 1 - (V_LAST_POS + V_LEN);

END IF;

V_DETAIL := SUBSTR(P_DETAIL_RESULT_COUNT, V_LAST_POS + V_LEN, V_NUM);

V_EQUAL_POS:=INSTR(V_DETAIL,V_EQUAL_SIGN);

V_DETAIL_TYPE := SUBSTR(V_DETAIL,1,V_EQUAL_POS-1);

V_RESULT_COUNT := SUBSTR(V_DETAIL,V_EQUAL_POS+1);

EXIT WHEN V_POS = 0;

V_LAST_POS := V_POS;

END LOOP;

COMMIT;

--发生异常直接捕获

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

RAISE;

END;