oracle如何循环的从一个表里取值,在存储过程中建一个动态游标后如何循环取游标指向的记录并插入另一个表中...

当前位置:我的异常网» Oracle开发 » 在存储过程中建一个动态游标后如何循环取游标指向的

在存储过程中建一个动态游标后如何循环取游标指向的记录并插入另一个表中

www.myexceptions.net  网友分享于：2013-12-12  浏览：757次

在存储过程中建一个动态游标后怎么循环取游标指向的记录并插入另一个表中？

我的存储过程：

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

文章评论