自己写的短信指令查询燃气费oracle触发器

最近做了个天然气的短信平台项目，以下内容是通过短信指令实现该用户的费用反馈信息。
希望对大家有帮助，也方便自己以后使用。。

CREATE OR REPLACE TRIGGER "TRI_SMS_RECEIVE"
BEFORE insert on sms_receive
FOR EACH ROW
Declare
  phone number(11);/*手机（查询条件用）*/
  phone_gr number(11);/*居民手机*/
  phone_dw number(11);/*单位手机*/
  dy_phone number(11);/*订阅手机*/
  detail varchar2(500);/*内容*/
  v_yhzh varchar2(36);/*用户号（截取用）*/
  v_yhzh_gr varchar2(36);/*居民用户号*/
  v_yhzh_dw varchar2(36);/*单位用户号*/
  v_zl varchar2(36);/*短信指令*/
  orgId_gr varchar2(36);/*居民区域码*/
  orgId_dw varchar2(36);/*单位区域码*/
  tq_text varchar2(1000);/*停气信息*/
  fee number(11,2);/*当月费用*/
  fee_yu number(11,2);/*可用余额*/
  fee_qf number(11,2);/*目前欠费总额*/
  time_month varchar2(50); /*用于定义当前的年月*/
  time_js varchar2(50); /*用于接受用户发送的年月*/
Begin
  begin
    select sms_receive_sid_seq.nextval
    into :new.receive_sid
    from dual;
    end;
  time_month := to_char(sysdate,'yyyymm'); /*赋值：当前的年月*/

  /*获得手机号码和短信内容*/
  begin
   select :new.sm_org_mobile,:new.sm_content into phone,detail from dual ;
   end;
    v_zl := substr(detail,1,4);/*赋值：截取前四位指令*/
    v_yhzh := substr(detail,6,10);/*赋值：截取用户号*/
    time_js := substr(detail,17,6);/*赋值：截取年月*/
    dbms_output.put_line('得到手机和用户号'||phone||'--'||v_yhzh);
    begin
       /*查询出居民的区域码,手机,用户号*/
       select hnpy.g.org_id,hnpy.g.gr_sj,hnpy.s.yqdz_userid_old into orgId_gr,phone_gr,v_yhzh_gr
       from hnpy.v_yongqidizhi s, hnpy.v_gerenkehu g
       where hnpy.s.yqdz_kh_id=hnpy.g.gr_id and hnpy.s.yqdz_kh_lx=2
       and hnpy.g.gr_sj=phone and s.yqdz_userid_old=v_yhzh;
       exception
         WHEN NO_DATA_FOUND THEN
           orgId_gr:=NULL;
           phone_gr:=null;
           v_yhzh_gr:=null;
     end;
     dbms_output.put_line('居民查询结束！得到手机phone_gr'||phone_gr||'用户号v_yhzh_gr'||v_yhzh_gr||'orgId_gr'||orgId_gr);
     dbms_output.put_line('条件'||phone||'-'||v_yhzh);
     begin
       /*查询出单位的区域码，手机，用户号*/
       select hnpy.g.org_id,hnpy.g.kh_dh_bg,hnpy.s.yqdz_userid_old into orgId_dw,phone_dw,v_yhzh_dw
       from hnpy.v_yongqidizhi s, hnpy.v_danweikehu g
       where hnpy.s.yqdz_kh_id=hnpy.g.kh_id and hnpy.s.yqdz_kh_lx=1
       and g.kh_dh_bg=phone and s.yqdz_userid_old=v_yhzh;
       exception
         when no_data_found then
           orgId_dw:=null;
           phone_dw:=null;
           v_yhzh_dw:=null;
      end;
     dbms_output.put_line('单位查询结束！得到手机phone_dw'||phone_dw||'用户号v_yhzh_dw'||v_yhzh_dw||'orgId_dw'||orgId_dw);
if v_zl='cxfy' or v_zl='CXFY' then /*查询费用-------*/
      dbms_output.put_line('进入cxfy！');
       dbms_output.put_line('phone_dw '||phone_dw);
        dbms_output.put_line('v_yhzh_dw '||v_yhzh_dw);
        dbms_output.put_line('用户号v_yhzh--'||v_yhzh);
      begin
       /*北京方面提供的sql语句beijing---------*/
       select nvl(a.费用总金额, 0), NVL(c.余额, 0), NVL(b.总欠费金额, 0) into fee,fee_yu,fee_qf
       from (select cbjl_yqzh, SUM(nvl(cbjl.cbjl_sjjfe, 0)) 费用总金额
          from hnpy.v_chaobiao_jilu cbjl
         where cbjl_cbqijian like time_month||'%' --日期格式YYYYMM 如:201101%
           and cbjl_yqzh = v_yhzh --用户号
         group by cbjl_yqzh) a
  left join
(select cbjl_yqzh,SUM(nvl(cbjl.cbjl_yje + cbjl.cbjl_gwf, 0)) 总欠费金额
    from hnpy.v_chaobiao_jilu cbjl
   where cbjl_yqzh = v_yhzh --用户号
     and cbjl_zt = '4'
   group by cbjl_yqzh) b
    on a.cbjl_yqzh = b.cbjl_yqzh
  left join (select yqdz_userid_old, zjzh.khzj_keyong_yue 余额
               from hnpy.v_zijinzhanghu zjzh
               left join hnpy.v_yongqidizhi yqdz
                 on yqdz.yqdz_id = zjzh.khzj_yqdz_id
              where yqdz_userid_old = v_yhzh) c --用户号
    on a.cbjl_yqzh = c.yqdz_userid_old
    left join (select yqdz_userid_old, dwzjzh.dwzj_keyong_yue 余额
               from hnpy.v_danweizijinzhanghu dwzjzh
               left join hnpy.v_yongqidizhi yqdz
                 on yqdz.yqdz_kh_id = dwzjzh.kh_id
              where yqdz_userid_old = v_yhzh) d --用户号
              on a.cbjl_yqzh = d.yqdz_userid_old; /*北京方面提供的sql语句-----------beijing*/
              exception
                when no_data_found then
                  fee := null;
                  fee_qf := null;
                  fee_yu := null;
              end;
     dbms_output.put_line(time_month);
     dbms_output.put_line('得到费用'||fee||' '||fee_yu||' '||fee_qf);
     /*验证手机号码是否是登记时的手机号码*/
     if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
        begin
           /*查找停气信息*/
          select gas.info_content
          into tq_text
          from dx.gas_info gas
          where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
          and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
          exception
             when no_data_found then
               tq_text := null;
           end;
           dbms_output.put_line('停气内容：'||tq_text);
           /*将停气信息发送至用户手机*/
          if tq_text is not null then/*2--*/
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone,tq_text,sysdate,sysdate);
          end if;/*--2*/
          dbms_output.put_line('手机phone_gr '||phone_gr||'phone_dw '||phone_dw);
          /*发送费用信息*/
          if phone_gr is not null and fee is not null then/*1--*/
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone_gr,'尊敬的居民用户!您本月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
          elsif phone_dw is not null and fee is not null then
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone_dw,'尊敬的单位用户!您本月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
          else
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone_dw,'尊敬的用户!暂时没有当月的相关信息!如需帮助请回复BZXX!',sysdate,sysdate);
          end if;/*--1*/
     else
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
         values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
     end if;
elsif v_zl='cxwy' or v_zl='CXWY' then /*查询往月费用-------*/
      dbms_output.put_line('进入cxwy！');
      begin
       /*北京方面提供的sql语句beijing---------*/
       select nvl(a.费用总金额, 0), NVL(c.余额, 0), NVL(b.总欠费金额, 0) into fee,fee_yu,fee_qf
       from (select cbjl_yqzh, SUM(nvl(cbjl.cbjl_sjjfe, 0)) 费用总金额
          from hnpy.v_chaobiao_jilu cbjl
         where cbjl_cbqijian like time_js||'%' --日期格式YYYYMM 如:201101%
           and cbjl_yqzh = v_yhzh --用户号
         group by cbjl_yqzh) a
  left join
(select cbjl_yqzh,SUM(nvl(cbjl.cbjl_yje + cbjl.cbjl_gwf, 0)) 总欠费金额
    from hnpy.v_chaobiao_jilu cbjl
   where cbjl_yqzh = v_yhzh --用户号
     and cbjl_zt = '4'
   group by cbjl_yqzh) b
    on a.cbjl_yqzh = b.cbjl_yqzh
  left join (select yqdz_userid_old, zjzh.khzj_keyong_yue 余额
               from hnpy.v_zijinzhanghu zjzh
               left join hnpy.v_yongqidizhi yqdz
                 on yqdz.yqdz_id = zjzh.khzj_yqdz_id
              where yqdz_userid_old = v_yhzh) c --用户号
    on a.cbjl_yqzh = c.yqdz_userid_old
    left join (select yqdz_userid_old, dwzjzh.dwzj_keyong_yue 余额
               from hnpy.v_danweizijinzhanghu dwzjzh
               left join hnpy.v_yongqidizhi yqdz
                 on yqdz.yqdz_kh_id = dwzjzh.kh_id
              where yqdz_userid_old = v_yhzh) d --用户号
              on a.cbjl_yqzh = d.yqdz_userid_old; /*北京方面提供的sql语句-----------beijing*/
              exception
                when no_data_found then
                  fee := null;
                  fee_qf := null;
                  fee_yu := null;
              end;
              dbms_output.put_line('time_js--'||time_js);
     /*验证手机号码是否是登记时的手机号码*/
     if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
         begin
          /*查找停气信息*/
          select gas.info_content into tq_text
           from dx.gas_info gas
           where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
           and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
           exception
             when no_data_found then
               tq_text := null;
           end;
           /*将停气信息发送至用户手机*/
          if tq_text is not null then/*2--*/
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone,tq_text,sysdate,sysdate);
          end if;/*--2*/
          /*发送费用信息*/
          if phone_gr is not null and fee is not null then/*1--*/
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone_gr,'尊敬的用户!您'||time_js||'月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
          elsif phone_dw is not null and fee is not null then
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone_dw,'尊敬的用户!您'||time_js||'月的天燃气费共'||fee||'元,当前余额'||fee_yu||'元,目前共欠费'||fee_qf||'元!如需帮助请回复BZXX!',sysdate,sysdate);
          else
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone_dw,'尊敬的用户!您没有该月的相关信息!如需帮助请回复BZXX!',sysdate,sysdate);
          end if;/*--1*/
     else
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
         values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
     end if;
elsif v_zl='cxtq' or v_zl='CXTQ' then /*查询停气-------*/
      dbms_output.put_line('进入cxtq！');
      /*验证手机号码是否是登记时的手机号码*/
      if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
           /*查找停气信息内容*/
          begin
            select gas.info_content into tq_text
             from dx.gas_info gas
             where rownum<2 and sysdate>=gas.start_date and sysdate<=gas.end_date
             and gas.area_sid=orgId_gr or gas.area_sid=orgId_dw;
           exception
             when no_data_found then
               tq_text := null;
           end;
           /*将停气信息发送至用户手机*/
          if tq_text is not null then/*2--*/
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone,tq_text,sysdate,sysdate);
          else /*没有停气信息*/
            insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
            values (1,phone,'没有找到近期停气信息!如需帮助请回复BZXX!',sysdate,sysdate);
          end if;/*--2*/
      else
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
         values (1,phone,'指令有误!请用您登记的手机号码来查询!如需帮助请回复BZXX!',sysdate,sysdate);
      end if;
elsif v_zl='dyxx' or v_zl='DYXX' then/*订阅-------*/
      dbms_output.put_line('进入dyxx！');
   /*验证手机号码是否是登记时的手机号码*/
   if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
        /*查询出是否订阅过*/
       begin
        select r.user_mobile into dy_phone from dx.dy_order r where r.user_mobile=phone ;
       exception
             when no_data_found then
               dy_phone := null;
           end;
       /*判断是否订阅*/
       if dy_phone is not null then
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
          values (1,phone,'您已经订阅!请不要重复订阅!如需帮助请回复BZXX!',sysdate,sysdate);
       else/*添加到订阅表，同时发送通知短信*/
         insert into dy_order (user_mobile,user_sid,create_date,add_staff)
         values (phone,v_yhzh,sysdate,'admin') ;
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
         values (1,phone,'订阅成功!系统将每月定时向您发送您的费用信息!如需帮助请回复BZXX!',sysdate,sysdate);
       end if;
    else/*不是登记时的手机号码*/
      insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
      values (1,phone,'指令有误!请用您登记的手机号码查询!如需帮助请回复BZXX!',sysdate,sysdate);
    end if;
elsif v_zl='tdxx' or v_zl='TDXX' then /*退订-------*/
      dbms_output.put_line('进入tdxx！');
   /*验证手机号码是否是登记时的手机号码*/
   if phone_gr is not null or v_yhzh_gr is not null or phone_dw is not null or v_yhzh_dw is not null then
      /*查询出是否订阅过*/
      begin
        select r.user_mobile into dy_phone from dx.dy_order r where r.user_mobile=phone and r.user_sid=v_yhzh ;
      exception
             when no_data_found then
               dy_phone := null;
      end;
      if dy_phone is null then
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
         values (1,phone,'未发现您有订阅信息!如需帮助请回复BZXX!',sysdate,sysdate);
      else
        /*从订阅表里删除,同时发送通知短信*/
         delete from dx.dy_order d where d.user_mobile=phone and d.user_sid=v_yhzh ;
         insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
         values (1,phone,'退订成功!欢迎再次使用!如需帮助请回复BZXX!',sysdate,sysdate);
      end if;
   else /*不是登记时的手机号码*/
     insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
     values (1,phone,'指令有误!请用您登记的手机号码查询!如需帮助请回复BZXX!',sysdate,sysdate);
   end if;
elsif v_zl='bzxx' or v_zl='BZXX' then /*帮助-------*/
      dbms_output.put_line('进入bzxx！');
    insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
    values (1,phone,'欢迎使用查询帮助指令！1.查询费用:CXFY#用户号 2.查询往月费用:CXWY#用户号#年年年年月月 3.查询停气信息:CXTQ#用户号 4.订阅信息:DYXX#用户号 5.退订信息:TDXX#用户号',sysdate,sysdate);
else /*其他的指令-------*/
  dbms_output.put_line('进入其他！');
    insert into dx.sms_task (mt_group_id,mt_dst_num,mt_content,mt_add_time,mt_at_time)
    values (1,phone,'指令有误!如需帮助请回复BZXX!',sysdate,sysdate);
end if;

end;