17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure tb_proce_TingCheFei
(
date_1 date,
date_5 date
)
as
hour_1 number;
hour_2 number;
date_3 date;
date_4 date;
date_2 date;
money number;
begin
money:=0;
date_2:=date_5;
if date_2-date_1>=1 then
money:=round(date_2-date_1-0.499)*119.5;
date_2:=date_2-round(date_2-date_1-0.499);
end if;
hour_1:=to_number(to_char(date_1,'HH24'));
hour_2:=to_number(to_char(date_2,'HH24'));
date_3:=TO_DATE(TO_CHAR(TRUNC(date_1,'DD'),'YYYY-MM-DD')||'21:00:00','YYYY-MM-DD HH24:MI:SS');
date_4:=TO_DATE(TO_CHAR(TRUNC(date_2,'DD'),'YYYY-MM-DD')||'07:00:00','YYYY-MM-DD HH24:MI:SS');
if hour_1>=7 and hour_2<=20 and hour_1<=20 then
money:=Round((date_2-date_1)*24*60/15+0.499)*2;
elsif hour_1>20 and hour_2<=7 then
money:=Round((date_2-date_1)*24/2+0.499)*1.5;
elsif hour_1<=20 and hour_1>=7 then
money:=money+Round((date_3-date_1)*24*60/15+0.499)*2;
money:=money+Round((date_2-date_3)*24/2+0.499)*1.5;
elsif hour_1<7 or hour_1>20 then
money:=money+Round((date_4-date_1)*24/2+0.499)*1.5;
money:=money+Round((date_2-date_4)*24*60/15+0.499)*2;
end if;
dbms_output.put_line(money);
end;
--写个函数,传入SRSJ和KCSJ,返回应结算金额
--以下代码可参考
SELECT SRSJ,KCSJ,
TO_NUMBER(TO_CHAR(SRSJ,'HH24')) HOUR,
CASE WHEN TO_NUMBER(TO_CHAR(SRSJ,'HH24')) BETWEEN 7 AND 20 THEN 'DAY' ELSE 'NIGTH' END DATE_TYPE,
(KCSJ - SRSJ)*24*60*60 SECOND_DIFF,
CASE WHEN TO_NUMBER(TO_CHAR(SRSJ,'HH24')) BETWEEN 7 AND 20
THEN TO_DATE(TO_CHAR(TRUNC(SRSJ,'DD'),'YYYY-MM-DD')||'21:00:00','YYYY-MM-DD HH24:MI:SS')
WHEN TO_NUMBER(TO_CHAR(SRSJ,'HH24')) <= 6
THEN TO_DATE(TO_CHAR(TRUNC(SRSJ,'DD'),'YYYY-MM-DD')||'07:00:00','YYYY-MM-DD HH24:MI:SS')
ELSE TO_DATE(TO_CHAR(TRUNC(SRSJ,'DD')+1,'YYYY-MM-DD')||'07:00:00','YYYY-MM-DD HH24:MI:SS')
END NEXT_DATE_TYPE
FROM A_TABLE;