我的存储过程:
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;后面的循环插入的写法是错误的,请各位高手教教正确的写法是怎样的?
解决方案 »
- RBO优化方式下VIEW的内部执行计划为什么会改变?
- 求救下载官方网站oracle
- 求Oracle 10g Web开发书籍!
- oracle 11g在windows 2008上安装实例的时候到了85%以后就不动了
- 数据库服务器速度变慢的如何解决 100分在线等
- 请问在SQL PLUS中执行一个存储过程,其中有问题,请问用什么命令现实是多少行出错,或者错误信息?
- 请帮我看看这个SQL错在哪?
- date类型如何求和?
- 菜鸟问题:如何往Oracle表里输入数据(不用Insert语句)...
- 关于ORACLE REPORT 6i 的问题
- 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 变量;