我的存储过程：
create or replace procedure test(a in string, EDate in string) is
type cur_t is ref cursor;
c_FACT_PATI cur_t;
str varchar2(5000);
rst xx.table_insert%rowtype;
begin
str := ' select PRODUCT_NUMBER, ';
str := str || ' a.INCEPT_ROOM as room_id, ';
str := str || ' b.PLACE, ';
str := str || ' sum(Amount) as NewSumAmount ';
str := str || ' from table1 a, ';
str := str || ' table2 b, ';
str := str || ' table3 c ';
str := str || ' where a.IN_ID = b.in_id ';
str := str || ' and a.AFFIRM_SIGN = 1 ';
str := str || ' and b.NUMBER = c.id ';
str := str || ' and to_char(I_DATE, ' || ' ' 'yyyy-MM-dd ' ' ' || ') ' || a || ' ' ' ' ||
EDate || ' ' ' ';
str := str || ' group by PRODUCT_NUMBER, a.INCEPT_ROOM, b.PLACE ';
open c_FACT_PATI for str;
loop
fetch c_FACT_PATI into rst;
exit when c_FACT_PATI%notfound;
insert into table_insert
(USERID, PRODUCT_ID, ROOMID, PLACEID, AMOUNT)
values
(8,
rst.PRODUCT_NUMBER,
rst.room_id,
rst.PLACE,
rst.NewSumAmount);
end loop;
close c_FACT_PATI;
end test;
后面的循环插入的写法是错误的，请各位高手教教正确的写法是怎样的？
------解决方案--------------------
create or replace procedure test(a in string, EDate in string) is
begin
execute immediate '
insert into table_insert
(USERID, PRODUCT_ID, ROOMID, PLACEID, AMOUNT)
select 8,PRODUCT_NUMBER,a.INCEPT_ROOM as room_id,b.PLACE,sum(Amount) as NewSumAmount
from table1 a,
table2 b,
table3 c
where a.IN_ID = b.in_id
and a.AFFIRM_SIGN = 1
and b.NUMBER = c.id