背景:
存储过程
为明白意图我先用SQL语句示意!
1、创建一张表
create table a(
kpi_id number(6),
kpi_value(32,2)
)
2、在info表中有一些数据
kpi_id kpi_value
98 0.01
98 0.02
98 0.91
98 -0.02
98 0.02
98 -0.01
98 0.11--------------存储过程中3、拼接语句
v_insert_str := 'insert into a(kpi_id,kpi_value)';
v_select_str :='select kpi_id,kpi_value from info';4、执行
execute immediate v_insert_str || v_select_str;5、查询 a 表的数据
kpi_id kpi_value
98 0.00
98 0.00
98 0.00
98 0.00
98 0.00
98 0.00
98 0.01为什么插入到另一个表的结果集全部大部分数值都为0.00了???奇怪!
存储过程
为明白意图我先用SQL语句示意!
1、创建一张表
create table a(
kpi_id number(6),
kpi_value(32,2)
)
2、在info表中有一些数据
kpi_id kpi_value
98 0.01
98 0.02
98 0.91
98 -0.02
98 0.02
98 -0.01
98 0.11--------------存储过程中3、拼接语句
v_insert_str := 'insert into a(kpi_id,kpi_value)';
v_select_str :='select kpi_id,kpi_value from info';4、执行
execute immediate v_insert_str || v_select_str;5、查询 a 表的数据
kpi_id kpi_value
98 0.00
98 0.00
98 0.00
98 0.00
98 0.00
98 0.00
98 0.01为什么插入到另一个表的结果集全部大部分数值都为0.00了???奇怪!
----------------------------出现问题的SQL----------------------------------
for x in 98..99 loop
v_kpi_id :=x;
if v_kpi_id = 98 then
v_dmsChar :='dms02';
else
v_dmsChar :='dms01';
end if;
v_insert_str:='insert into bam_baln_cube_bill_lv3(
surr_key
,bill_month
,time_period
,time_val
,dms_cl
,dms01
,dms01_cht01
,dms01_cht02
,dms02
,dms02_cht01
,dms02_cht02
,kpi_id
,kpi_value)';
v_select_str:='select surr_key
,bill_month
,time_period
,time_val
,dms_cl
,dms01
,dms01_cht01
,dms01_cht02
,dms02
,dms02_cht01
,dms02_cht02
,kpi_id
,to_char(kpi_value,''fm9999990.9999'') from
(select
surr_key
, bill_month
, 4 time_period
, 96 time_val
, dms_cl
, dms01, dms01_cht01, dms01_cht02
, dms02, dms02_cht01, dms02_cht02
--,sum(decode(kpi_id,95,kpi_value)) curr_sheet_cnt
--,sum(decode(kpi_id,96,kpi_value)) last_sheet_cnt
--,sum(decode(kpi_id,97,kpi_value)) rate
,:1 kpi_id
,case
when sum(decode(kpi_id,95,kpi_value))=0 and sum(decode(kpi_id,96,kpi_value)) =0 then 0
when sum(decode(kpi_id,95,kpi_value)) is null then 0
else
round(sum(decode(kpi_id,97,kpi_value))*(sum(decode(kpi_id,95,kpi_value))+sum(decode(kpi_id,96,kpi_value)))/2 /decode(sum(sum(decode(kpi_id,97,kpi_value))*(sum(decode(kpi_id,95,kpi_value))+sum(decode(kpi_id,96,kpi_value)))/2) over(partition by bill_month, dms_cl, :2),0,1,sum(sum(decode(kpi_id,97,kpi_value))*(sum(decode(kpi_id,95,kpi_value))+sum(decode(kpi_id,96,kpi_value)))/2) over(partition by bill_month, dms_cl, :3)),2)
end kpi_value
from bam_baln_cube_bill_lv3
where bill_month = :4
and dms_cl = 10100010102
group by surr_key
,bill_month
, dms_cl
, dms01, dms01_cht01, dms01_cht02
, dms02, dms02_cht01, dms02_cht02)';
(v_kpi_id||' '||v_dmsChar||' '||v_dmsChar||' '||p_curr_date);
execute immediate v_insert_str || v_select_str using v_kpi_id,v_dmsChar,v_dmsChar,p_curr_date; --这里赋值
end loop;
v_kpi_id :=x;
if v_kpi_id = 98 then
v_dmsChar :='dms02';
else
v_dmsChar :='dms01';
end if;
v_insert_str:='insert into bam_baln_cube_bill_lv3(
surr_key
,bill_month
,time_period
,time_val
,dms_cl
,dms01
,dms01_cht01
,dms01_cht02
,dms02
,dms02_cht01
,dms02_cht02
,kpi_id
,kpi_value)';
v_select_str:='select surr_key
,bill_month
,time_period
,time_val
,dms_cl
,dms01
,dms01_cht01
,dms01_cht02
,dms02
,dms02_cht01
,dms02_cht02
,kpi_id
,to_char(kpi_value,''fm9999990.9999'') from
(select
surr_key
, bill_month
, 4 time_period
, 96 time_val
, dms_cl
, dms01, dms01_cht01, dms01_cht02
, dms02, dms02_cht01, dms02_cht02
--,sum(decode(kpi_id,95,kpi_value)) curr_sheet_cnt
--,sum(decode(kpi_id,96,kpi_value)) last_sheet_cnt
--,sum(decode(kpi_id,97,kpi_value)) rate
,'||v_kpi_id||' kpi_id
,case
when sum(decode(kpi_id,95,kpi_value))=0 and sum(decode(kpi_id,96,kpi_value)) =0 then 0
when sum(decode(kpi_id,95,kpi_value)) is null then 0
else
round(sum(decode(kpi_id,97,kpi_value))*(sum(decode(kpi_id,95,kpi_value))+sum(decode(kpi_id,96,kpi_value)))/2 /decode(sum(sum(decode(kpi_id,97,kpi_value))*(sum(decode(kpi_id,95,kpi_value))+sum(decode(kpi_id,96,kpi_value)))/2) over(partition by bill_month, dms_cl, '||v_dmsChar||'),0,1,sum(sum(decode(kpi_id,97,kpi_value))*(sum(decode(kpi_id,95,kpi_value))+sum(decode(kpi_id,96,kpi_value)))/2) over(partition by bill_month, dms_cl, '||v_dmsChar||')),2)
end kpi_value
from bam_baln_cube_bill_lv3
where bill_month = :1
and dms_cl = 10100010102
group by surr_key
,bill_month
, dms_cl
, dms01, dms01_cht01, dms01_cht02
, dms02, dms02_cht01, dms02_cht02)';(v_kpi_id||' '||v_dmsChar||' '||v_dmsChar||' '||p_curr_date);
execute immediate v_insert_str || v_select_str using p_curr_date;
end loop;
比如:
declare
v_kpi_id number(6);
v_dmsChar varchar2(20); begin
for x 98..99 loop
if v_kpi_id :=x then
v_dmsChar :='dms01';
else
v_dmsChar :='dms02';
end if;
--这里导致了错误(正确如下边的拼接语句)
v_insert_str :='insert into source_table(kpi_id,dms)'
v_select_str :='select kpi_id,:1 from temp_xxx where bill_month = :2';
execute immediate v_insert_str || v_select_str using v_dmsChar,p_curr_date;
--采用拼接方式
v_insert_str :='insert into source_table(kpi_id,dms)'
v_select_str :='select kpi_id,'||v_dmsChar||' from temp_xxx where bill_month = :1';
execute immediate v_insert_str || v_select_str using p_curr_date;
end loop;
end;
kpi_id number(6),
kpi_value(32,2)
)
============>改成 create table a(
kpi_id number(6,4),
kpi_value(32,2)
)字段类型的问题