可以将數組用作表,用一條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>