学步园

欢迎技术交流。 QQ：138986722

晕~~~自己写的代码被改了、不知道！！！

Oracle中判断是否数字function：

CREATE OR REPLACE FUNCTION ISNUMBER(MyStr VARCHAR2) RETURN NUMBER<br /> IS<br /> STR VARCHAR2(400);<br /> ISNUM NUMBER;<br /> NUM NUMBER;<br /> BEGIN<br /> --返回0，不是数字。1，是数字<br /> ISNUM:=0;<br /> STR:=TRIM(MyStr);<br /> --如果输入值为空，就返回不是数字。<br /> IF TRIM(STR) IS NOT NULL THEN<br /> BEGIN<br /> NUM:=TO_NUMBER(STR);<br /> ISNUM:=1;<br /> EXCEPTION<br /> WHEN INVALID_NUMBER THEN<br /> NULL;<br /> WHEN OTHERS THEN<br /> NULL;<br /> END;<br /> END IF;<br /> RETURN ISNUM;<br /> END;<br />

触发器：

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

o(︶︿︶)o ~~~一段代码改来改去的、烦！！！