Oracle function 触发器


老火、又要把上一篇的存储过程转移到触发器里面来！！！

而且上一篇中有很多bug！！！见

  1. create or replace trigger biufer_tbmeetmgr_CHANGETIME   
  2.  --after插入之后   
  3.  --before 插入之前   
  4.  before insert or update or delete   
  5.   on tbmeetmgr   
  6.  for each row   
  7.      
  8. declare   
  9.           TYPE c_time IS REF CURSOR;  --创建游离标记   
  10.           vrec c_time;   
  11.           varInt number ;   
  12.           strOverSql varchar2(30);   
  13.           roomname varchar2(100);   
  14.           roomcnt number;   
  15.           v_usercount number;   
  16.           v_username varchar2(50);   
  17. begin   
  18.   --调用存储过程   
  19.   if inserting then   
  20.     select count(0) into roomcnt from tbmeetroom t where t.id = :NEW.Huiyishi;   
  21.     select count(0) into v_usercount from tbsySUSErinfo t where t.userid = :NEW.Creator;   
  22.     if roomcnt > 0 then   
  23.       select t.name into roomname from tbmeetroom t where t.id = :NEW.Huiyishi;   
  24.     end if;   
  25.     if roomcnt <= 0 then   
  26.       roomname := :NEW.Huiyishi;   
  27.     end if;   
  28.     if v_usercount > 0 then   
  29.       select t.username into v_username from tbsysuserinfo t where t.userid = :NEW.Creator;   
  30.     end if;   
  31.     --一次性会议   
  32.     if :NEW.huiyimoshi = 1 then   
  33.       insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),:NEW.Stoptime,0,:NEW.ecid, :NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  34.     end if;   
  35.        
  36.       
  37.        
  38.     --单周会议   
  39.     varInt := 0;   
  40.     if :NEW.xunhuaimoshi = 1 then   
  41.           select ceil(( to_date(:NEW.Stoptime,'yyyy-mm-dd') - next_day(to_date(substr(:NEW.Createtime, 1, 10),'yyyy-mm-dd')-1, ceil(substr(:NEW.Xunhuairiqi,3)))+1  )/7) into varInt from dual;   
  42.                 OPEN vrec for SELECT to_char(next_day(to_date(substr(:NEW.Createtime,1,10),'yyyy-mm-dd')-1,   
  43.                 ceil(substr(:NEW.Xunhuairiqi,3)))+1+(rownum-1)*7 , 'yyyy-MM-dd')   
  44.                 from dual connect by rownum<=varInt ;     
  45.                   LOOP     
  46.                     FETCH vrec INTO strOverSql;  --入库终止日期   
  47.                     exit when vrec%notfound;   
  48.                     --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql);   
  49.                     insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,1,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  50.                    -- commit;   
  51.                   end loop;   
  52.             
  53.     end if;   
  54.        
  55.     --每月循环   
  56.     varInt := 0;   
  57.     if :NEW.xunhuaimoshi = 3 then   
  58.     select ceil(months_between(to_date(:NEW.stoptime,'yyyy-mm-dd'),   
  59.                      to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))) into varInt from dual;   
  60.          OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,8))||(substr(:NEW.Xunhuairiqi,3,4)),'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd')   
  61.             from dual connect by rownum<= ceil(varInt) ;   
  62.                LOOP     
  63.                 FETCH vrec INTO strOverSql;  --入库终止日期   
  64.                 exit when vrec%notfound;   
  65.                 --dbms_output.put_line('月度时间:'||strOverSql);   
  66.                 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,3,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  67.                -- commit;   
  68.                end loop;               
  69.               
  70.     end if;   
  71.        
  72.        
  73.     --季度循环   
  74.     varInt := 0;   
  75.     if :NEW.xunhuaimoshi = 4 then   
  76.     select ceil((to_date(:NEW.stoptime,'yyyy-mm-dd')-to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))/90)   
  77.                into varInt from dual;   
  78.          OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,5))||(substr(:NEW.Xunhuairiqi,3,7)),'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd')   
  79.             from dual connect by rownum<= ceil(varInt) ;   
  80.                LOOP     
  81.                 FETCH vrec INTO strOverSql;  --入库终止日期   
  82.                 exit when vrec%notfound;   
  83.                 --dbms_output.put_line('月度时间:'||strOverSql);   
  84.                 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,4,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  85.                -- commit;   
  86.                end loop;               
  87.               
  88.     end if;       
  89.        
  90.   end if;    
  91.   if deleting then   
  92.     delete from tbmeetmgrinfo t where t.mid = :OLD.Id;   
  93.   end if;   
  94.   if updating then   
  95.     delete from tbmeetmgrinfo t where t.mid = :OLD.Id;   
  96.     select count(0) into roomcnt from tbmeetroom t where t.id = :NEW.Huiyishi;   
  97.     if roomcnt > 0 then   
  98.       select t.name into roomname from tbmeetroom t where t.id = :NEW.Huiyishi;   
  99.     end if;   
  100.     if roomcnt <= 0 then   
  101.       roomname := :NEW.Huiyishi;   
  102.     end if;   
  103.     --一次性会议   
  104.     if :NEW.huiyimoshi = 1 then   
  105.       insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),:NEW.Stoptime,0,:NEW.ecid, :NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  106.     end if;   
  107.        
  108.       
  109.        
  110.     --单周会议   
  111.     varInt := 0;   
  112.     if :NEW.xunhuaimoshi = 1 then   
  113.           select ceil(( to_date(:NEW.Stoptime,'yyyy-mm-dd') - next_day(to_date(substr(:NEW.Createtime, 1, 10),'yyyy-mm-dd')-1, ceil(substr(:NEW.Xunhuairiqi,3)))+1  )/7) into varInt from dual;   
  114.                 OPEN vrec for SELECT to_char(next_day(to_date(substr(:NEW.Createtime,1,10),'yyyy-mm-dd')-1,   
  115.                 ceil(substr(:NEW.Xunhuairiqi,3)))+1+(rownum-1)*7 , 'yyyy-MM-dd')   
  116.                 from dual connect by rownum<=varInt ;      
  117.                   LOOP     
  118.                     FETCH vrec INTO strOverSql;  --入库终止日期   
  119.                     exit when vrec%notfound;   
  120.                     --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql);   
  121.                     insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,1,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  122.                    -- commit;   
  123.                   end loop;   
  124.             
  125.     end if;   
  126.        
  127.     --每月循环   
  128.     varInt := 0;   
  129.     if :NEW.xunhuaimoshi = 3 then   
  130.     select ceil(months_between(to_date(:NEW.stoptime,'yyyy-mm-dd'),   
  131.                      to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))) into varInt from dual;   
  132.          OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,8))||(substr(:NEW.Xunhuairiqi,3,4)),'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd')   
  133.             from dual connect by rownum<= ceil(varInt) ;   
  134.                LOOP     
  135.                 FETCH vrec INTO strOverSql;  --入库终止日期   
  136.                 exit when vrec%notfound;   
  137.                 --dbms_output.put_line('月度时间:'||strOverSql);   
  138.                 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,3,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  139.                -- commit;   
  140.                end loop;               
  141.               
  142.     end if;   
  143.        
  144.        
  145.     --季度循环   
  146.     varInt := 0;   
  147.     if :NEW.xunhuaimoshi = 4 then   
  148.     select ceil((to_date(:NEW.stoptime,'yyyy-mm-dd')-to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))/90)   
  149.                into varInt from dual;   
  150.          OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,5))||(substr(:NEW.Xunhuairiqi,3,7)),'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd')   
  151.             from dual connect by rownum<= ceil(varInt) ;   
  152.                LOOP     
  153.                 FETCH vrec INTO strOverSql;  --入库终止日期   
  154.                 exit when vrec%notfound;   
  155.                 --dbms_output.put_line('月度时间:'||strOverSql);   
  156.                 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,:NEW.Huiyishi,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,4,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username);   
  157.                -- commit;   
  158.                end loop;               
  159.               
  160.     end if;       
  161.   end if;   
  162. end;  
  • 1
  • 2
  • 下一页

相关内容