create or replace procedure PRC_FactIncomeByWaterKind(
GYear in varchar2,
GMonth in varchar2
) isstr_Year varchar2(100);
str_Month varchar2(100);
str_SQL varchar2(3000);begin
case when GYear is not null then
str_Year:='and ChargeAndIncomeYear like '''||GYear|| '''';else null; end case;
case when GMonth is not null then
str_Month:='and ChargeAndIncomeMonth like '''||GMonth|| '''';else null; end case;str_SQL:='select ... from table1 where 1=1 '|| str_Year || str_Month
|| ' group by ..... ';cursor c_s is str_SQL;
cc c_s%rowtype;begin
open c_s; --打开游标
loop
exit when c_s%notfound;
insert into T_PRT_FactIncomeByWaterKind values
(
....
);
end loop;
close c_s;--关闭游标
end;end PRC_FactIncomeByWaterKind;
执行一个查询,里面带有参数年.月,查询出来后插入到另外一个表,怎么实现。。
刚开始用oracle..
GYear in varchar2,
GMonth in varchar2
) isstr_Year varchar2(100);
str_Month varchar2(100);
str_SQL varchar2(3000);begin
case when GYear is not null then
str_Year:='and ChargeAndIncomeYear like '''||GYear|| '''';else null; end case;
case when GMonth is not null then
str_Month:='and ChargeAndIncomeMonth like '''||GMonth|| '''';else null; end case;str_SQL:='select ... from table1 where 1=1 '|| str_Year || str_Month
|| ' group by ..... ';cursor c_s is str_SQL;
cc c_s%rowtype;begin
open c_s; --打开游标
loop
exit when c_s%notfound;
insert into T_PRT_FactIncomeByWaterKind values
(
....
);
end loop;
close c_s;--关闭游标
end;end PRC_FactIncomeByWaterKind;
执行一个查询,里面带有参数年.月,查询出来后插入到另外一个表,怎么实现。。
刚开始用oracle..
if (GYear is not null && Gmonth is not null and Gyear != '' and Gmonth !='')
then str_SQL := Gyear || '-' || Gmonth || '00';
insert into table... valus...;
end if;提醒:
没必要使用游标,记住游标是很消耗资源的,能不用则不用。(当然了,你得需要学会使用它,之后再是怎么使用它)。不知道我的解释对你有没有帮助。
fetch c_s into cc;
然后使用cc中的各字段的值即可。
create or replace procedure PRC_FactIncomeByWaterKind(
GYear in varchar2,
GMonth in varchar2
) isstr_Year varchar2(100);
str_Month varchar2(100);
str_SQL varchar2(3000);
c_s sys_refcursor;
cc table1%rowtype;
begin
case when GYear is not null then
str_Year:='and ChargeAndIncomeYear like '''||GYear|| '''';else null; end case;
case when GMonth is not null then
str_Month:='and ChargeAndIncomeMonth like '''||GMonth|| '''';else null; end case;str_SQL:='select ... from table1 where 1=1 '|| str_Year || str_Month
|| ' group by ..... ';open c_s for str_SQL;
fetch c_s into cc;
loop
if c_s%found then
insert into T_PRT_FactIncomeByWaterKind values
(
cc.col1,cc.col2
);
else exit;
end if;
end loop;close c_s;--关闭游标
end PRC_FactIncomeByWaterKind;