[Oracle] string parsing to generate associated data

[Oracle] string parsing generates associated data because the project function sometimes needs to be provided by other departments in Excel... in other documents, some data is inserted into the database tables without interruption, so a simple parsing process is written to avoid repeated work; sort out and share the SQL code CREATE OR REPLACE PROCEDURE tables (DATA_NAMES VARCHAR2, DATA_NUMBERS VARCHAR2, REAMK VARCHAR2 DEFAULT 'Pro _ CREATE ') AS -- group code NEW_GROUP_CODE VARCHAR2 (20 ); -- TYPE VAR20 is table of VARCHAR2 (20); -- DATA (the number OF bytes must be extended when the string IS too long) CNAME VARCHAR2 (1024); CNUMBER VARCHAR2 (1024 ); -- initialize NAMES VAR20: = VAR20 (); NUMBERS VAR20: = VAR20 (); I INTEGER DEFAULT 0; DATA_LEN INTEGER DEFAULT 0; BEGIN -- DATA format/* CNAME: = ', wang jianbing, Liu Yulong, Wang Jianhua, '; CNUMBER: =', 15895985619,159 51902106, 18951853833, '; */CNAME: = REPLACE (DATA_NAMES, CHR (10 ), ''),'', ''); CNUMBER: = REPLACE (DATA_NUMBERS, CHR (10 ),''),'',''); -- Data split count DATA_LEN: = LENGTHB (REGEXP_REPLACE (CNUMBER, '[^,] +', ''); -- insert NEW_GROUP_CODE: = TEST_TABLE1_SEQ.NEXTVAL; insert into TEST_TABLE_1 (ID, CODE) VALUES (GID, NEW_GROUP_CODE); -- parse and generate the contact information IF DATA_LEN <1 then rollback; RETURN; end if; loop I: = I + 1; exit when I> DATA_LEN; -- add the set length NAMES. EXTEND; NUMBERS. EXTEND; -- SUBSTR start position intercepts the corresponding number of characters. REPLACE replaces the space NAMES (I): = SUBSTR (CNAME, INSTR (CNAME, ',', 1, I) + 1, (INSTR (CNAME, ',', 1, I + 1)-2)-(INSTR (CNAME, ',', 1, I) + 1 )); NUMBERS (I): = SUBSTR (CNUMBER, INSTR (CNUMBER, ',', 1, I) + 1, (INSTR (CNUMBER, ',', 1, I + 1) -2)-INSTR (CNUMBER, ',', 1, I) + 1); -- join table INSERT INTO TEST_TABLE_GROUP (ID, TEST_TABLE1_ID contact_name, mobile_number) VALUES (SYS_GUID (), NEW_GROUP_CODE NAMES (I), NUMBERS (I); END LOOP; COMMIT; END;