Batch Oracle insert records

The combined arrays of PL/SQL are used here.

I want to insert a group of records separated by '_' into the database. Of course, this is just to facilitate batch insertion from Java calling the stored procedure.

 

First define the array type and a tool function (used to break down strings ):

1 create or replace package qtone is
2 type substr_array is table of varchar2 (100 char) index by binary_integer;
3 end;
4
5 create or replace function split_str (STR in varchar2, delimiter in varchar2) return qtone. substr_array is
6 src_str varchar2 (300 char): = '';
7 prev_index INTEGER: = 1;
8 next_index INTEGER: = 1;
9
10 retval qtone. substr_array;
11 cnt integer: = 1;
12 begin
13 -- remove the start and end delimiters
14 src_str: = trim (both delimiter from Str );
15 While next_index! = 0 Loop
16 next_index: = instr (src_str, delimiter, prev_index, 1 );
17
18 if (next_index = 0) then
19 retval (CNT): = substr (src_str, prev_index );
20 CNT: = CNT + 1;
21 else
22 retval (CNT): = substr (src_str, prev_index, next_index-prev_index );
23 CNT: = CNT + 1;
24 end if;
25
26 prev_index: = next_index + 1;
27 end loop;
28
29 return retval;
30 end;

 

Then, perform a simple test:

1 Create Table Test (
2 Name varchar2 (20 char ),
3 address varchar2 (50 char)
4 );
5
6 declare
7 names varchar2 (100 char): = 'ungshow _ sweet_ting ';
8 addrs varchar2 (100 char): = 'guangdong _ zhejiang_hubei ';
9
10 addr_array qtone. substr_array;
11 name_array qtone. substr_array;
12
13 stmt varchar2 (100 char): = '';
14 begin
15 name_array: = split_str (names ,'_');
16 addr_array: = split_str (addrs ,'_');
17
18 stmt: = 'insert into test (name, address) values (: name,: ADDR )';
19 if (name_array.count <> 0 and name_array.count = addr_array.count) then
20 For I in name_array.first... name_array.last Loop
21 execute immediate stmt using name_array (I), addr_array (I );
22 end loop;
23
24 commit;
25 end if;
26 end;