Oracle function 触发器

老火、又要把上一篇的存储过程转移到触发器里面来！！！而且上一篇中有很多bug！！！见 http://www.linuxidc.com/Linux/2011-05/36357.htm

create or replace trigger biufer_tbmeetmgr_CHANGETIME
--after插入之后
--before 插入之前
before insert or update or delete
on tbmeetmgr
for each row
declare
TYPE c_time IS REF CURSOR; --创建游离标记
vrec c_time;
varInt number ;
strOverSql varchar2（30）;
roomname varchar2（100）;
roomcnt number;
v_usercount number;
v_username varchar2（50）;
begin
--调用存储过程
if inserting then
select count（0） into roomcnt from tbmeetroom t where t.id = :NEW.Huiyishi;
select count（0） into v_usercount from tbsySUSErinfo t where t.userid = :NEW.Creator;
if roomcnt > 0 then
select t.name into roomname from tbmeetroom t where t.id = :NEW.Huiyishi;
end if;
if roomcnt <= 0 then
roomname := :NEW.Huiyishi;
end if;
if v_usercount > 0 then
select t.username into v_username from tbsysuserinfo t where t.userid = :NEW.Creator;
end if;
--一次性会议
if :NEW.huiyimoshi = 1 then
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）;
end if;
--单周会议
varInt := 0;
if :NEW.xunhuaimoshi = 1 then
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;
OPEN vrec for SELECT to_char（next_day（to_date（substr（:NEW.Createtime,1,10）,"yyyy-mm-dd"）-1,
ceil（substr（:NEW.Xunhuairiqi,3）））+1+（rownum-1）*7 , "yyyy-MM-dd"）
from dual connect by rownum<=varInt ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line（"----+++++单周循环日期++++++-----:"||strOverSql）;
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）;
-- commit;
end loop;
end if;
--每月循环
varInt := 0;
if :NEW.xunhuaimoshi = 3 then
select ceil（months_between（to_date（:NEW.stoptime,"yyyy-mm-dd"）,
to_date（substr（:NEW.createtime, 1, 10）,"yyyy-mm-dd"））） into varInt from dual;
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"）
from dual connect by rownum<= ceil（varInt） ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line（"月度时间:"||strOverSql）;
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）;
-- commit;
end loop;
end if;
--季度循环
varInt := 0;
if :NEW.xunhuaimoshi = 4 then
select ceil（（to_date（:NEW.stoptime,"yyyy-mm-dd"）-to_date（substr（:NEW.createtime, 1, 10）,"yyyy-mm-dd"））/90）
into varInt from dual;
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"）
from dual connect by rownum<= ceil（varInt） ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line（"月度时间:"||strOverSql）;
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）;
-- commit;
end loop;
end if;
end if;
if deleting then
delete from tbmeetmgrinfo t where t.mid = :OLD.Id;
end if;
if updating then
delete from tbmeetmgrinfo t where t.mid = :OLD.Id;
select count（0） into roomcnt from tbmeetroom t where t.id = :NEW.Huiyishi;
if roomcnt > 0 then
select t.name into roomname from tbmeetroom t where t.id = :NEW.Huiyishi;
end if;
if roomcnt <= 0 then
roomname := :NEW.Huiyishi;
end if;
--一次性会议
if :NEW.huiyimoshi = 1 then
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）;
end if;
--单周会议
varInt := 0;
if :NEW.xunhuaimoshi = 1 then
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;
OPEN vrec for SELECT to_char（next_day（to_date（substr（:NEW.Createtime,1,10）,"yyyy-mm-dd"）-1,
ceil（substr（:NEW.Xunhuairiqi,3）））+1+（rownum-1）*7 , "yyyy-MM-dd"）
from dual connect by rownum<=varInt ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line（"----+++++单周循环日期++++++-----:"||strOverSql）;
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）;
-- commit;
end loop;
end if;
--每月循环
varInt := 0;
if :NEW.xunhuaimoshi = 3 then
select ceil（months_between（to_date（:NEW.stoptime,"yyyy-mm-dd"）,
to_date（substr（:NEW.createtime, 1, 10）,"yyyy-mm-dd"））） into varInt from dual;
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"）
from dual connect by rownum<= ceil（varInt） ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line（"月度时间:"||strOverSql）;
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）;
-- commit;
end loop;
end if;
--季度循环
varInt := 0;
if :NEW.xunhuaimoshi = 4 then
select ceil（（to_date（:NEW.stoptime,"yyyy-mm-dd"）-to_date（substr（:NEW.createtime, 1, 10）,"yyyy-mm-dd"））/90）
into varInt from dual;
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"）
from dual connect by rownum<= ceil（varInt） ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line（"月度时间:"||strOverSql）;
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）;
-- commit;
end loop;
end if;
end if;
end;

Oracle存储过程、触发器实现获取时间段内周、月、季度的具体时间Oracle jobs脚本、调用存储过程相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）