oracle存储过程拆分字符串函数,Oracle中利用存储过程来分割字符串

CREATE OR REPLACE PROCEDURE split_string(V_STR_1 IN VARCHAR2)

AS

TYPE C_STR_SP IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

C1 C_STR_SP;

V_START NUMBER :=1;

V_LOCATE NUMBER;

V_STR VARCHAR2(100):=V_STR_1;

V_INDEX NUMBER:=1;

BEGIN

WHILE INSTR(V_STR,’,’)>0 LOOP

V_LOCATE :=INSTR(V_STR,’,’);

C1(V_INDEX) :=SUBSTR(V_STR,V_START,V_LOCATE-V_START);

DBMS_OUTPUT.PUT_lINE(C1(V_INDEX));

V_STR :=SUBSTR(V_STR,V_LOCATE+1);

V_INDEX :=V_INDEX+1;

END LOOP;

DBMS_OUTPUT.PUT_lINE(V_STR);

commit;

END;

实现效果如下图: