oracle变量问题
我在写一个存储过程。要求根据指标周期，以及规定的公式进行累计。
create or replace procedure pr_dm_lcgl_distpctstat_1_sig(in_shenqing_year_month number)
is
/**********************************************************************
*function :
*author :
*createdate : 20120507
*updatedate :
**********************************************************************/
v_ex_code number;
v_gg_pct_jrcs_001 number(8,2);--T≤1
v_gg_pct_jrcs_002 number(8,2);--1<T≤3
v_gg_pct_jrcs_003 number(8,2);--3<T≤6
v_gg_pct_jrcs_004 number(8,2);--6<T≤12
v_gg_pct_jrcs_005 number(8,2);--t>12
v_seq_gg_pct_jrcs_001 number;
v_seq_gg_pct_jrcs_002 number;
v_seq_gg_pct_jrcs_003 number;
v_seq_gg_pct_jrcs_004 number;
v_seq_gg_pct_jrcs_005 number;
v_shenqing_year_month number(6):=in_shenqing_year_month;
v_shenqing_year number(4):= in_shenqing_year_month/100;
v_shenqing_month number(2):= mod(in_shenqing_year_month,100);
v_target varchar2(30);
begin
---获取指标
for i in 5..8 loop
v_target:='tar.pct_02_00'||i;
select sum(decode(sign(v_target-30),1,1,0,1,-1,0)) as gg_pct_jrcs_001,
sum(decode(sign(v_target-30),1,decode(sign(v_target-90),1,0,0,1,-1,1),0,0,-1,0)) as gg_pct_jrcs_002,
sum(decode(sign(v_target-90),1,decode(sign(v_target-180),1,0,0,1,-1,1),0,0,-1,0)) as gg_pct_jrcs_003,
sum(decode(sign(v_target-180),1,decode(sign(v_target-360),1,0,0,1,-1,1),0,0,-1,0)) as gg_pct_jrcs_004,
sum(decode(sign(v_target-360),1,1,0,0,-1,0)) as gg_pct_jrcs_005
into v_gg_pct_jrcs_001,
v_gg_pct_jrcs_002,
v_gg_pct_jrcs_003,
v_gg_pct_jrcs_004,
v_gg_pct_jrcs_005
from dm_sc_pct_target tar inner join dm_lcgl_distpctstat_index idx on tar.shenqingh=idx.shenqingh
where idx.shenqing_year_month=v_shenqing_year_month;
---获取序列
select seq_dm_lcgl_distpctstat.nextval into v_seq_gg_pct_jrcs_001 from dual;
select seq_dm_lcgl_distpctstat.nextval into v_seq_gg_pct_jrcs_002 from dual;
select seq_dm_lcgl_distpctstat.nextval into v_seq_gg_pct_jrcs_003 from dual;
select seq_dm_lcgl_distpctstat.nextval into v_seq_gg_pct_jrcs_004 from dual;
select seq_dm_lcgl_distpctstat.nextval into v_seq_gg_pct_jrcs_005 from dual;
---同步数据
delete from dm_lcgl_distpctstat d where d.periodcode=v_target and d.year=v_shenqing_year and d.month=v_shenqing_month;
insert into dm_lcgl_distpctstat(id,periodcode,targetcode,year,month,factvalue,import_time)
select v_seq_gg_pct_jrcs_001,v_target,'gg_pct_jrcs_001',v_shenqing_year,v_shenqing_month,v_gg_pct_jrcs_001,sysdate from dual
union
select v_seq_gg_pct_jrcs_002,v_target,'gg_pct_jrcs_002',v_shenqing_year,v_shenqing_month,v_gg_pct_jrcs_002,sysdate from dual
union
select v_seq_gg_pct_jrcs_003,v_target,'gg_pct_jrcs_003',v_shenqing_year,v_shenqing_month,v_gg_pct_jrcs_003,sysdate from dual
union
select v_seq_gg_pct_jrcs_004,v_target,'gg_pct_jrcs_004',v_shenqing_year,v_shenqing_month,v_gg_pct_jrcs_004,sysdate from dual
union
select v_seq_gg_pct_jrcs_005,v_target,'gg_pct_jrcs_005',v_shenqing_year,v_shenqing_month,v_gg_pct_jrcs_005,sysdate from dual;
--更新索引表
update dm_lcgl_distpctstat_index idx
set idx.status='1',
idx.ex_code=0,
idx.update_time=sysdate
where idx.shenqing_year_month=v_shenqing_year_month;
commit;
end loop;
exception
when others then
rollback;
v_ex_code:=sqlerrm;
dbms_output.put_line(v_ex_code);
update dm_lcgl_distpctstat_index idx
set idx.status='1',
idx.ex_code=v_ex_code,
idx.update_time=sysdate
where idx.shenqing_year_month=v_shenqing_year_month;
dbms_output.put_line(v_sql);
commit;
end;
sign函数中的 v_target 原本应该是 pct_02_005 到 pct_02_008
我想用 FOR 循环的方式去替换 这样我就不用 写 很多SELECT 或者 写同样的 存储过程了。
可是 实际上是不行的 。
用过替换变量 可是重视要手动输入 。
希望高手解答 小弟感谢~
[解决办法]
用动态sql来做
execute immediate 'select sum(decode(sign('||v_target||'-30),1,1,0,1,-1,0)) ........... from ..... where .... ' into v_gg_pct_jrcs_001,
v_gg_pct_jrcs_002,
v_gg_pct_jrcs_003,
v_gg_pct_jrcs_004,
v_gg_pct_jrcs_005 ;