有表test1, test2,两个表的结构一样,test2为空表,test1的内容为:
id name
1 aa
2 bb现在用动态sql使用forall将test1的数据插入到test2,代码如下:declare
type dr_type is table of test1%ROWTYPE index by binary_integer;
dr_table dr_type;begin
select * BULK COLLECT into dr_table from test1;
forall i in dr_table.first .. dr_table.last
execute immediate 'insert into test2 values dr_table(i)';
end;
/
-----forall如果按上面的写法,会报错: DML statement without BULK In-BIND cannot be used inside FORALL
大意是没有bulk绑定不能用在forall中这样,我又改用下面的方法来使用forall
execute immediate
'begin
forall i in :first .. :last
insert into test2 values (:3(i));
end;' USING dr_table.first,dr_table.last,dr_table;
commit;
----这里using 会报错:expressions have to be of SQL types,表示dr_table不是sql的类型变量。
我用的是oralce 9i,难道不支持动态sql 中使用forall么??
id name
1 aa
2 bb现在用动态sql使用forall将test1的数据插入到test2,代码如下:declare
type dr_type is table of test1%ROWTYPE index by binary_integer;
dr_table dr_type;begin
select * BULK COLLECT into dr_table from test1;
forall i in dr_table.first .. dr_table.last
execute immediate 'insert into test2 values dr_table(i)';
end;
/
-----forall如果按上面的写法,会报错: DML statement without BULK In-BIND cannot be used inside FORALL
大意是没有bulk绑定不能用在forall中这样,我又改用下面的方法来使用forall
execute immediate
'begin
forall i in :first .. :last
insert into test2 values (:3(i));
end;' USING dr_table.first,dr_table.last,dr_table;
commit;
----这里using 会报错:expressions have to be of SQL types,表示dr_table不是sql的类型变量。
我用的是oralce 9i,难道不支持动态sql 中使用forall么??
declare
type dr_type is table of test1%ROWTYPE index by binary_integer;
dr_table dr_type; begin
select * BULK COLLECT into dr_table from test1;
forall i in dr_table.first .. dr_table.last
insert into test2 values dr_table(i);
end;
这样就可以了
declare
type dr_type is table of test1%ROWTYPE index by binary_integer;
dr_table dr_type; begin
select * BULK COLLECT into dr_table from test1;
forall i in dr_table.first .. dr_table.last
execute immediate 'insert into test2 values(:1)' using dr_table(i);
end;
type dr_type is table of niu_1%ROWTYPE index by binary_integer;
dr_table dr_type; begin
execute IMMEDIATE 'declare
type dr_type is table of niu_1%ROWTYPE index by binary_integer;
dr_table dr_type; begin select * BULK COLLECT into dr_table from niu_1;
forall i in dr_table.first .. dr_table.last
insert into niu_2 values dr_table(i);
end; ';
end;
type dr_type is table of test1%ROWTYPE index by binary_integer;
dr_table dr_type; begin
execute IMMEDIATE 'declare
type dr_type is table of test1%ROWTYPE index by binary_integer;
dr_table dr_type;
begin
select * BULK COLLECT into dr_table from test1;
forall i in dr_table.first .. dr_table.last
insert into test2 values dr_table(i);
end; ';
end;