我要写一个触发器,根据求出两个时间的差来调整另一个表的一个时间字段,但是表的字段是timstemp类型,因为有多条记录,所以要将求出的多个时间差相加,请问,如何才能做到呢?要怎样转换为number类型?
原文如下:
create or replace trigger TUE_FAS_ATND_REC
after insert on fas_atnd_rec
for each row
--declare numrows INTEGER;
-- maxhours INTEGER;
-- local variables here
declare numrows number;
maxhours number;
cursor attendanceCur is select * from FAS_ATND_REC where
to_char(FAS_ATND_REC.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD') = to_char(:old.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD');
pragma autonomous_transaction;
begin
for cur_result in attendanceCur loop
begin
numrows:=numrows+to_number(to_char((cur_result.atnd_rec_wkr_out_date - cur_result.atnd_rec_wkr_in_date)));
end ;
end loop;
select WKR_DLY_MAX_WORK_HOURS into maxHours from Fas_Wkr
where WKER_PK = :old.ATRC_WKER_FK_IX01;
if numrows > maxHours then
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(:old.ATND_REC_WKR_OUT_DATE,maxHours,:old.ATRC_WKER_FK_IX01);
end;
else
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WKHR_PK,FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(SEQ_WORK_HOUR_REC_ID.NEXTVAL,:old.ATND_REC_WKR_OUT_DATE,numrows,:old.ATRC_WKER_FK_IX01);
end;
end if;
commit;
end TU_FAS_ATND_REC;
其中的 numrows:=numrows+to_number(to_char((cur_result.atnd_rec_wkr_out_date - cur_result.atnd_rec_wkr_in_date)));
在触发后会提示不将char 转为number不成功,因为我要求的是小时之差hour放在numrows中,可能不是整数。请问有什么好办法解决吗,拜谢。
原文如下:
create or replace trigger TUE_FAS_ATND_REC
after insert on fas_atnd_rec
for each row
--declare numrows INTEGER;
-- maxhours INTEGER;
-- local variables here
declare numrows number;
maxhours number;
cursor attendanceCur is select * from FAS_ATND_REC where
to_char(FAS_ATND_REC.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD') = to_char(:old.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD');
pragma autonomous_transaction;
begin
for cur_result in attendanceCur loop
begin
numrows:=numrows+to_number(to_char((cur_result.atnd_rec_wkr_out_date - cur_result.atnd_rec_wkr_in_date)));
end ;
end loop;
select WKR_DLY_MAX_WORK_HOURS into maxHours from Fas_Wkr
where WKER_PK = :old.ATRC_WKER_FK_IX01;
if numrows > maxHours then
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(:old.ATND_REC_WKR_OUT_DATE,maxHours,:old.ATRC_WKER_FK_IX01);
end;
else
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WKHR_PK,FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(SEQ_WORK_HOUR_REC_ID.NEXTVAL,:old.ATND_REC_WKR_OUT_DATE,numrows,:old.ATRC_WKER_FK_IX01);
end;
end if;
commit;
end TU_FAS_ATND_REC;
其中的 numrows:=numrows+to_number(to_char((cur_result.atnd_rec_wkr_out_date - cur_result.atnd_rec_wkr_in_date)));
在触发后会提示不将char 转为number不成功,因为我要求的是小时之差hour放在numrows中,可能不是整数。请问有什么好办法解决吗,拜谢。
Create or replace function
Datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
l_result:=null;
if (upper(p_what) = 'HOUR') then
l_result:=((p_d2-p_d1)*24);
end if;
if (upper(p_what) = 'DAY') then
l_result:=(p_d2-p_d1);
end if;
if (upper(p_what) = 'MONTH') then
l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);
end if;
if (upper(p_what) = 'QUARTER') then
l_result:=((floor(MONTHS_BETWEEN(p_d2,TRUNC(p_d2,'YEAR'))/3)+1) -
(floor(MONTHS_BETWEEN(p_d1,TRUNC(p_d1,'YEAR'))/3)+1) + (((to_char(p_d2, 'yyyy')) - (to_char(p_d1, 'yyyy')))*4));
end if;
l_result:=floor(l_result);
return l_result;
end;--然后就可以求得,写为
numrows :=numrows+Datediff('hour',cur_result.atnd_rec_wkr_in_date,
cur_result.atnd_rec_wkr_out_date);
myvalue := to_number(
to_date(to_char(cur_result.atnd_rec_wkr_out_date,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
-
to_date(to_char(cur_result.atnd_rec_wkr_in_date,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
);