oracle触发器文字与格式字符串不匹配
ORA-06512: 在 "HEALTHW.TRIGGER_INS_T_PEDOMETER", line 27
ORA-04088: 触发器 'HEALTHW.TRIGGER_INS_T_PEDOMETER' 执行过程中出错
ORA-06512: 在 "HEALTHW.TRIGGER_INS_DEVICEDETAILLIST", line 39
ORA-04088: 触发器 'HEALTHW.TRIGGER_INS_DEVICEDETAILLIST' 执行过程中出错
-----------------------------------------------------
以下是触发器代码，报上述错误，帮忙看一下是哪里的问题呢？
CREATE OR REPLACE TRIGGER trigger_INS_T_PEDOMETER
BEFORE INSERT ON healthw.T_PEDOMETER
FOR EACH ROW
DECLARE
v_totalsteps varchar2(10); --今日总步数
v_faststeps varchar2(10); --今日快步数
v_slowsteps varchar2(10); --今日慢步数
v_stride number(10); -- 步幅
v_calorie varchar2(10);
v_distance varchar2(10);
v_deviceid varchar2(20); --设备号
-- v_mobilephone varchar2(21);
-- cursor c_1 is select tp.totalsteps,tp.faststeps,tp.slowsteps,tp.distance,tp.calorie from T_PEDOMETER tp where tp.userid = :new.userid and tp.receivetime = to_date(''||sysdate||'','yyyy-mm-dd');
-- r c_1%rowtype;
BEGIN
v_stride := 35;
-- 可能发生异常
begin
select tum.imsi into v_deviceid from T_USER_MSISDN tum where tum.userid = :new.userid;
EXCEPTION WHEN NO_DATA_FOUND THEN
null;
end;
if(v_deviceid is not null) then
-- 查询用户的手机号
--select tub.mobilephone into v_mobilephone from T_USER_BASE tub where tub.userid = :new.userid;
-- 今日完成步数
--select sum(tp.totalsteps) into v_totalsteps,sum(tp.faststeps) into v_faststeps,sum(tp.slowsteps) into v_slowsteps,sum(tp.distance) into v_distance,sum(tp.calorie) into v_calorie from T_PEDOMETER tp where tp.userid = :new.userid and tp.receivetime = to_date(''||sysdate||'','yyyy-mm-dd');
select steps,faststeps,slowsteps, distance, calorie
into v_totalsteps ,
v_faststeps ,
v_slowsteps,
v_distance,
v_calorie
from (select sum(tp.totalsteps) as steps,
sum(tp.faststeps) as faststeps,
sum(tp.slowsteps) as slowsteps,
sum(tp.distance) as distance,
sum(tp.calorie) as calorie
from T_PEDOMETER tp
where tp.userid = :new.userid
and tp.receivetime =
to_date('' || sysdate || '', 'yyyy-mm-dd'));
insert into T_PEDOMETER_STAT(userid,Msisdn,TODAYACTUALSTEPS,TODAYFASTSTEPS,TODAYSLOWSTEPS,TODAYDISTANCE,TODAYCALARIE,TODAYDATE,BATTERY,STRIDE) values(:new.userid,''||v_deviceid||'',v_totalsteps,v_faststeps,v_slowsteps,v_distance,v_calorie,to_date(''||sysdate||'','yyyy-mm-dd'),20,v_stride);
--insert into T_PEDOMETER_STAT values(v_userid,v_totalsteps,v_faststeps,v_slowsteps,:new.hour,v_calorie,v_distance,sysdate,to_date(''||:new.today||'','yyyy-mm-dd'),20,v_stride);
end if;
END;
最新回答
谁难过我的难过
2024-10-24 07:51:22
比如看到你有个比较可疑的语句:to_date(''||:new.today||'','yyyy-mm-dd'),20,v_stride);
这里的new.today参数传进来的值是不是10位的日期.
下次请我
2024-10-24 07:44:40
select steps, faststeps, slowsteps, distance, calorie
into v_totalsteps, v_faststeps, v_slowsteps, v_distance, v_calorie
from (select sum(tp.totalsteps) as steps,
sum(tp.faststeps) as faststeps,
sum(tp.slowsteps) as slowsteps,
sum(tp.distance) as distance,
sum(tp.calorie) as calorie
from T_PEDOMETER tp
where tp.userid = :new.userid
and tp.receivetime = to_date('' || sysdate || '', 'yyyy-mm-dd'));最后这个条件能取到数据吗？是不是应该在一个范围内！
热门标签
