Oracle jobs脚本、调用存储过程
Oracle jobs脚本、调用存储过程
今天要做一个每间隔5秒钟就要更新一下状态字段的功能、老大建议用jobs条用存储过程的方式来实现。然而自己从来没有弄过什么jobs跟Oralce的存储过程。杯具就这样发生了、弄了一天才弄好。
我要实现的功能就是查询会议室资源、看看是否被占用、或者是被锁定。被占用时显示已占用图标跟信息、被锁定时显示已锁定图标跟信息。
存储过程:
- CREATE OR REPLACE PROCEDURE hzwstatusupdate is
- meetId number; --会议室编号
- begin
- meetId := 0 ;
- for varCode in (select huiyishi from Tbmeetmgr where
- to_date(stoptime,'yyyy-MM-dd') = to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')
- and to_date(to_char(sysdate,'hh24:mi'),'hh24:mi') between to_date(starttime,'hh24:mi')
- and to_date(endtime,'hh24:mi'))
- loop
- meetId:=varCode.Huiyishi;
- update tbmeetroom set status = 1 where id = meetId ;
- commit;
- end loop;
- if meetId <= 0 then
- update tbmeetroom set status = 0 where status = 1 ;
- commit;
- end if ;
- for varCode in (select meetroomid from Tbmeetroomlock t where
- to_date(to_char(sysdate,'yyyy-MM-dd hh24:mi'),'yyyy-MM-dd hh24:mi')
- between to_date(lockstarttime,'yyyy-MM-dd hh24:mi')
- and to_date(lockendtime,'yyyy-MM-dd hh24:mi'))
- loop
- meetId:=varCode.Meetroomid;
- update tbmeetroom set status = 2 where id = meetId ;
- commit;
- end loop;
- if meetId <= 0 then
- update tbmeetroom set status = 0 where status = 2 ;
- commit;
- end if ;
- end;
jobs脚本:
页面代码:
view plaincopy to clipboardprint?
- function lockDiaoYong(){
- var idArr = "";
- jQuery("input[id^='hiddMentId']").each(function(i) {
- var idName = jQuery(this).val();
- idArr = idArr+ idName +",";
- });
- if(idArr !="" && idArr != null){
- var urlL = "<%=path%>/platform/mgrmeet!getLockandMgr.action?idArr="+idArr;
- $.ajax({
- type: "get", //用get方式传输
- url:urlL, //目标地址
- async:false,
- success:function(data) {
- if(data != "没有" && data != "" && data != null){
- if(data.indexOf("|") != -1){
- var strarr = data.split("|");
- var arrId = strarr[0].split(",");
- var arrSta = strarr[1].split(",");
- for(var i = 0 ; i < arrId.length; i++){
- if(arrSta[i] == 1 || arrSta[i] == "1" ){
- document.getElementById("showImg"+arrId[i]).src = "<%=path%>/images/syz.jpg";
- }
- if(arrSta[i] == 2 || arrSta[i] == "2" ){
- document.getElementById("showImg"+arrId[i]).src = "<%=path%>/images/sd.jpg";
- }
- }
- }
- }
- }
- });
- }
- }
- function timeDiaoYong(){
- var date = new Date(); //日期对象
- lockDiaoYong();
- setTimeout("timeDiaoYong()",60000); //设置过1000毫秒就是1秒,调用timeDiaoYong()方法
- }
- <mce:script type="text/javascript"><!--
- window.onload=function(){
- timeDiaoYong();
- }
- // --></mce:script>
java后台代码:
- public String getLockandMgr() throws IOException{
- request.setCharacterEncoding("UTF-8");
- response.setContentType("text/html;charset=UTF-8");
- String idarr = request.getParameter("idArr");
- List list = new ArrayList();
- String resultId = "";
- String resultSt = "";
- if (idarr != null && !idarr.equals("")) {
- String[] siptarr = idarr.split(",");
- int len = siptarr.length ;
- Long[] lo = new Long[len];
- for (int i = 0; i < len; i++) {
- lo[i] = Long.valueOf(siptarr[i]);
- }
- list = mgrService.getMeetById(lo);
- }
- int siz = list.size();
- PrintWriter out = response.getWriter();
- if(list != null && siz > 0){
- for (int i = 0; i < siz ; i++) {
- Object[] obj = (Object[]) list.get(i);
- resultId += obj[0] +",";
- resultSt += obj[1] +",";
- }
- String resultNum = resultId+"|"+resultSt;
- out.print(resultNum);
- }else{
- out.print("没有");
- }
- return null;
- }
|
评论暂时关闭