我的存储过程:
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 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;后面的循环插入的写法是错误的,请各位高手教教正确的写法是怎样的?
解决方案 »
- 在存储过程中使用游标的问题(pl/sql)
- oracle 月初日
- 字符串操作求助
- oracle客户端sqlplus问题.高手请进,没遇到过的错误提示
- oracle rman备份
- Oracle下面的提示符问题
- oracle9 下的TNSLSNR.EXE进程问题
- 怎样显示某个触发器或database link的内容?比如我原来可以用的database link,由于密码改动,用不了了,在知道database link名字的情况下
- 如何替换字符的问题
- 大家看看,这算不算倒分!
- oracle中用ed命令打开编辑器
- pb9+sqlserver2000转到oracle9i ,应该学习什么书,推荐本经典的。
从外面传入的参数a 和EDate 的值分别是:'<','2007-01-01'
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
and to_char(I_DATE, ''yyyy-MM-dd'') '|| a || EDate ||'
group by PRODUCT_NUMBER, a.INCEPT_ROOM, b.PLACE';
end;
str varchar(2000);
str:= '
insert into table_insert
(USERID, PRODUCT_ID, ROOMID, PLACEID, AMOUNT)
select :p1,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
and to_char(I_DATE, ''yyyy-MM-dd'') '|| a || EDate ||'
group by PRODUCT_NUMBER, a.INCEPT_ROOM, b.PLACE';execute immediate str using 变量;