我采用For loop的游标循环方式进行表的记录插入,发现游标读不到记录,但是我测试直接赋值例如
for cur_fg_qi_qty in (select distinct pn
from a
where f_term = 2012
and f_month = 3
) loop
、
就可以读到值,请问是不是for loop游标不能这样动态使用declare
v_year varchar2(8);
v_month varchar2(8);begin
v_year :=2012;
v_month :=3; for cur_fg_qi_qty in (select distinct pn
from a
where f_term = v_year
and f_month = v_month
) loop insert into b
(select pn,
hs_name,
qty3,
qty4,
from ( pn,
hs_name,
qty3,
qty4
from a
where f_term = v_year
and f_month = v_month
)
where rownum < 2);
end loop;
end;
for cur_fg_qi_qty in (select distinct pn
from a
where f_term = 2012
and f_month = 3
) loop
、
就可以读到值,请问是不是for loop游标不能这样动态使用declare
v_year varchar2(8);
v_month varchar2(8);begin
v_year :=2012;
v_month :=3; for cur_fg_qi_qty in (select distinct pn
from a
where f_term = v_year
and f_month = v_month
) loop insert into b
(select pn,
hs_name,
qty3,
qty4,
from ( pn,
hs_name,
qty3,
qty4
from a
where f_term = v_year
and f_month = v_month
)
where rownum < 2);
end loop;
end;
declare
v_year varchar2(8);
v_month varchar2(8);
TYPE c_type IS REF CURSOR;
my_cur c_type;
begin
v_year :=2012;
v_month :=3;
OPEN my_cur FOR 'select distinct pn from a where f_term = :1 and f_month = :2'
using v_year,v_month; loop
insert into b
(select pn,
hs_name,
qty3,
qty4,
from ( pn,
hs_name,
qty3,
qty4
from a
where f_term = v_year
and f_month = v_month
)
where rownum < 2);
end loop;
end;
loop
fetch cursor into v;
……
end loop;
close test_cursor;
这样的行不行?
第一个语句oracle隐士的将字符型03转成了数值型3.
循环中的语句由于变量是字符型,所以不进行转换,03<>3查不出数据。
declare
v_year varchar2(8);
v_month varchar2(8);
cursor cur is select distinct pn
from a
where f_term = v_year
and f_month = v_month ;
begin
v_year :=2012;
v_month :=3; for cur_fg_qi_qty in cur loop insert into b
(select pn,
hs_name,
qty3,
qty4,
from ( pn,
hs_name,
qty3,
qty4
from a
where f_term = v_year
and f_month = v_month
)
where rownum < 2);
end loop;
end;
还可以用while进行循环