ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高
ORA-06512: 在 line 20
各位能帮帮我吗?困扰我2个多小时了declare
cursor my_cursor(c_tm_intrvl_cd number) is(
select to_number(to_char(sysdate,'yyyyMMddhh24')) deal_date,t.tm_intrvl_cd, t.road_cd,t.hr_cd,
round(nvl(avg(t.Road_Avg_Spd),15),2) as ROAD_AVG_SPD,
round(avg(t.Road_Avg_Time),2) as Road_Avg_Time,round(avg(t.samp_num),2) as samp_num
from zhnj_eds.DM_FCT_ROAD_AVGSPD_DAY t
where t.tm_intrvl_cd in(
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24'),'yyyyMMddhh24')),
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24')-7,'yyyyMMddhh24')),
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24')-7*2,'yyyyMMddhh24')),
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24')-7*3,'yyyyMMddhh24')))
group by road_cd,t.tm_intrvl_cd,t.hr_cd);
my_cursor_row DM_FCT_ROAD_AVGSPD_DAY%ROWTYPE;
begin
for rec_tmp in (select tm_intrvl_cd from DM_FCT_ROAD_AVGSPD_DAY group by tm_intrvl_cd order by tm_intrvl_cd)
loop
--dbms_output.put_line('时间为' || rec_tmp.tm_intrvl_cd);
open my_cursor(rec_tmp.tm_intrvl_cd);
LOOP
FETCH my_cursor INTO my_cursor_row;
EXIT WHEN my_cursor%NOTFOUND;
dbms_output.put_line('时间为' || rec_tmp.tm_intrvl_cd || my_cursor_row.ROAD_AVG_SPD);
--insert into dm_fct_road_avgspd_day_spcl values(my_cursor_row.deal_date,my_cursor_row.road_cd,my_cursor_row.tm_intrvl_cd,my_cursor_row.hr_cd,my_cursor_row.ROAD_AVG_SPD,my_cursor_row.Road_Avg_Time,my_cursor_row.samp_num);
END LOOP;
CLOSE my_cursor;
end loop;
end;DM_FCT_ROAD_AVGSPD_DAY结构
create table DM_FCT_ROAD_AVGSPD_DAY
(
DEAL_DATE NUMBER(8) not null,
ROAD_CD VARCHAR2(50),
TM_INTRVL_CD NUMBER(10),
HR_CD NUMBER(2),
ROAD_AVG_SPD NUMBER(5,2),
ROAD_AVG_TIME NUMBER(10,2),
SAMP_NUM NUMBER(5)
);
多谢各位
ORA-06512: 在 line 20
各位能帮帮我吗?困扰我2个多小时了declare
cursor my_cursor(c_tm_intrvl_cd number) is(
select to_number(to_char(sysdate,'yyyyMMddhh24')) deal_date,t.tm_intrvl_cd, t.road_cd,t.hr_cd,
round(nvl(avg(t.Road_Avg_Spd),15),2) as ROAD_AVG_SPD,
round(avg(t.Road_Avg_Time),2) as Road_Avg_Time,round(avg(t.samp_num),2) as samp_num
from zhnj_eds.DM_FCT_ROAD_AVGSPD_DAY t
where t.tm_intrvl_cd in(
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24'),'yyyyMMddhh24')),
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24')-7,'yyyyMMddhh24')),
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24')-7*2,'yyyyMMddhh24')),
to_number(to_char(to_date(c_tm_intrvl_cd,'yyyyMMddhh24')-7*3,'yyyyMMddhh24')))
group by road_cd,t.tm_intrvl_cd,t.hr_cd);
my_cursor_row DM_FCT_ROAD_AVGSPD_DAY%ROWTYPE;
begin
for rec_tmp in (select tm_intrvl_cd from DM_FCT_ROAD_AVGSPD_DAY group by tm_intrvl_cd order by tm_intrvl_cd)
loop
--dbms_output.put_line('时间为' || rec_tmp.tm_intrvl_cd);
open my_cursor(rec_tmp.tm_intrvl_cd);
LOOP
FETCH my_cursor INTO my_cursor_row;
EXIT WHEN my_cursor%NOTFOUND;
dbms_output.put_line('时间为' || rec_tmp.tm_intrvl_cd || my_cursor_row.ROAD_AVG_SPD);
--insert into dm_fct_road_avgspd_day_spcl values(my_cursor_row.deal_date,my_cursor_row.road_cd,my_cursor_row.tm_intrvl_cd,my_cursor_row.hr_cd,my_cursor_row.ROAD_AVG_SPD,my_cursor_row.Road_Avg_Time,my_cursor_row.samp_num);
END LOOP;
CLOSE my_cursor;
end loop;
end;DM_FCT_ROAD_AVGSPD_DAY结构
create table DM_FCT_ROAD_AVGSPD_DAY
(
DEAL_DATE NUMBER(8) not null,
ROAD_CD VARCHAR2(50),
TM_INTRVL_CD NUMBER(10),
HR_CD NUMBER(2),
ROAD_AVG_SPD NUMBER(5,2),
ROAD_AVG_TIME NUMBER(10,2),
SAMP_NUM NUMBER(5)
);
多谢各位
--这是你游标的定义
to_number(to_char(sysdate,'yyyyMMddhh24')) deal_date--这是你行变量的定义
my_cursor_row DM_FCT_ROAD_AVGSPD_DAY%ROWTYPE;--这是你的表结构定义
DEAL_DATE NUMBER(8) not null,--这是你报错的地方
FETCH my_cursor INTO my_cursor_row;--很明显,一个yyyymmddhh24 10位的数字放进 8位的表字段类型的变量里面,肯定不行--其余类似,自行检查一下。--ps.把你行变量的定义改成下面这样,目前的错误肯定能过,但是insert的时候可能会报错,原因同上。
my_cursor_row my_cursor%ROWTYPE;