可以将数组用作表,用一条SQL语句将其值插入到表中;例如:
declare
vArray integer_varray;
begin
-- some code to populate vArray
insert into someTable(col)
select column_value from table(vArray);
end;
如果可以用查询填充数组,则不需要数组,只需将查询用作INSERT语句的数据源即可;例如:
insert into someTable(col)
select something
from someOtherTable
如果你需要一种方法来创建一组数字,比如1,2,…3500,这是一种常用的方法:
select level
from dual
connect by level <= 3500
关于从字符串生成一组数字的方法,这是一种非常常见的方法:
SQL> create or replace type integer_varray as varray (4000) of int
2 /
Type created.
SQL> create table someTable(n number);
Table created.
SQL> declare
2 vString varchar2(32000) := '1,10,11,10,20,0,0,0,1,10';
3 vArray integer_varray;
4 begin
5 insert into someTable(n)
6 select to_number(regexp_substr(vString, '[^,]+', 1, level))
7 from dual
8 connect by instr(vString, ',', 1, level - 1) > 0;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select * from someTable;
N
----------
1
10
11
10
20
1
10
10 rows selected.
SQL>