Oracle SQL insert into table a user ID and a value of a list of strings
Here’s a (PL/)SQL option you might want to consider.
For testing purposes, I’ve created a
TEST table which will contain the
VALUE pairs. The procedure accepts two parameters, both are strings:
IDwill be common for all values
VALUEis a comma-separated values list. Although you can pass a collection, I’d suggest you to use VARCHAR2 as it is quite simple to maintain
SELECT within the procedure uses a hierarchical query with regular expressions; its purpose is to split that comma-separated values string into rows so that you could insert each value into its own row. Doing so, you don’t even need a loop. Besides, that
SELECT would work even if you run it standalone, but – you want a procedure.
OK, here it goes:
SQL> create table test (id varchar2(10), value varchar2(20)); Table created. SQL> create or replace procedure p_ins (par_id in varchar2, 2 par_value in varchar2) 3 is 4 begin 5 insert into test (id, value) 6 select par_id, 7 trim(regexp_substr(par_value, '[^,]+', 1, level)) 8 from dual 9 connect by level <= regexp_count(par_value, ',') + 1; 10 end; 11 / Procedure created.
SQL> begin 2 p_ins('A', 'The, quick, brown fox, runs, or, whatever, it does'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select * from test; ID VALUE ---------- -------------------- A The A quick A brown fox A runs A or A whatever A it does 7 rows selected.