--个人留作笔记
create or replace procedure ykt_exception(
p_jyrq in varchar2 ,p_jysj in varchar2,p_kh in varchar2, p_wid in varchar2, p_xh in varchar2 ,p_jyje in NUMBER)
as
S_TIME varchar2(50); --开始时间
a_count int; --总记录数
a_amout NUMBER; --当天消费总金额
BEGIN
--查询当天消费总金额
select sum(JYJE) into a_amout from t_ykt_grjyjl where KH = p_kh and JYLX =0 and JYRQ = p_jyrq;
a_amout := a_amout+p_jyje;
--如果当天消费金额超过50,那么不执行查找的功能
if a_amout>50 then
commit;
return;
end if;
--查询十分钟前的时间
select TO_CHAR((to_date(p_jyrq||' '||p_jysj ,'yyyy-mm-dd,hh24:mi:ss') - 10/(60*24)),'hh24:mi:ss') into S_TIME from dual;
--查询十分钟符合条件消费的记录数
select count(wid) into a_count from t_ykt_grjyjl a
where jylx =0
and kh = p_kh
and jyrq = p_jyrq
and jysj > S_TIME
and instr(jydd,'浴室')=0;
if a_count >= 2 then
--插入异常表
insert into T_YKT_EXCEPTION values (p_wid, p_kh,p_xh,p_jyrq,p_jysj,0);
end if ;
commit;
End;