存储过程实现同步数据功能,从前端到数据库

页面放几个按钮:

 点击上面的每个按钮,都会在数据库里执行 存储过程 进行同步数据。具体的实现方法及步骤如下:

然后,对应 html 的书写是:

<div class="address">当前位置:申报设置 >> 信息同步</div>
<div class="clr"></div>
<div class="butsea"><table  border="0" align="left" cellpadding="0" cellspacing="0"  ><tr><td height="35px"><div class="addlist_button"> <a href="javascript:xxtb('js');" id='jstb'><span>教师同步</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('yx');" ><span>院系同步</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('zy');" ><span>专业同步</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('xn');" ><span>学年学期</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('nj');" ><span>年级</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('skyy');" ><span>授课语言</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('pycc');" ><span>培养层次</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('kclb');" ><span>课程类别</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('xiaoqu');" ><span>校区</span></a> </div></td></tr></table>
</div>

然后,html对应的 js 方法是:

function  xxtb(lx){BOX_show('loadDiv');var url="<%=path%>/xxtb/XxtbByLx"var param={'lx':lx};$.post(url,param,function(result){if(result){alert("信息同步成功");}else{alert("信息同步失败");}BOX_remove('loadDiv');},'json')
}

 然后,对应的 controller 方法是:

@ResponseBody
@RequestMapping("XxtbByLx")
//信息同步
public List<Map<String, Object>> XxtbByLx(String lx){return xxtbService.XxtbByLx(lx);
}

 然后,对应的 service 实现类:

//信息同步  lx:js=教师,yx=院系,kc=课程,xn=学年学期
@Override
public String XxtbByLx(String lx){Map<String,String> paraMap = new HashMap<String, String>();paraMap.put("lx", lx);paraMap.put("res", "0");dbDao.update("xxtb.XxtbByLx",paraMap);dbDao.update("xxtb.cleanString","");//清除特殊字符return new ArrayList<Map<String,Object>>();
}

然后,xml 中相关的方法以及 xml 的sql 处理脚本:

<!-- 去除oracle中字符串中的特殊字符  -->
<update id="xxtb.cleanString">beginupdate dm_yxb set yxywmc = replace(yxywmc, chr(9), '');update dm_yxb set yxywmc = replace(yxywmc, chr(10), '');update dm_yxb set yxywmc = replace(yxywmc, chr(13), '');update dm_yxb set yxywmc = replace(yxywmc, chr(32), '');update dm_yxb set yxywmc = replace(yxywmc, chr(34), '');update dm_yxb set yxywmc = replace(yxywmc, chr(63), '');update dm_zyb set zyywmc = replace(zyywmc, chr(9), '');update dm_zyb set zyywmc = replace(zyywmc, chr(10), '');update dm_zyb set zyywmc = replace(zyywmc, chr(13), '');update dm_zyb set zyywmc = replace(zyywmc, chr(32), '');update dm_zyb set zyywmc = replace(zyywmc, chr(34), '');update dm_zyb set zyywmc = replace(zyywmc, chr(63), '');end;
</update>

 

<!-- 信息同步 -->
<parameterMap class="java.util.HashMap" id="xxbMap" ><!-- 需要传入的参数 --><parameter property="lx"   javaType="String"    jdbcType="VARCHAR"  mode="IN"/><!-- 可以返回的参数 --><parameter property="res" javaType="String"    jdbcType="VARCHAR"  mode="OUT"/>
</parameterMap>
<procedure id="xxtb.XXXXXXXXXX" parameterMap="xxbMap"><!-- 调用数据库存储过程 -->{call XXXXXXXXXXXX_BY_LX(?,?)}
</procedure>

然后,对应的存储过程 KCSB_XXTBBYLX :

create or replace procedure KCSB_XXTBBYLX(v_lx  in VARCHAR2, -- 入参v_res out VARCHAR2 -- 返回值
) is
beginv_res := 1;begin--学年学期if v_lx = 'xn' thenbeginMERGE INTO dm_xnxqb t1 USING dm_xnxqb@bh_dblink t2 ON (t1.xn = t2.xn and t1.xq = t2.xq)WHEN MATCHED THENUPDATESET t1.xnmc   = t2.xnmc,t1.xqmc   = t2.xqmc,t1.kyf    = t2.kyf,t1.sfdqxq = t2.sfdqxqWHEN NOT MATCHED THENinsert(xn, xq, xnmc, xqmc, kyf, sfdqxq)values(t2.xn, t2.xq, t2.xnmc, t2.xqmc, t2.kyf, t2.sfdqxq);commit;end;elsif v_lx = 'nj' then--年级beginMERGE INTO dm_njb t1 USING dm_njb@bh_dblink t2 ON (t1.nj = t2.nj)WHEN MATCHED THENUPDATESET t1.njmc   = t2.njmc,t1.kyf    = t2.kyfWHEN NOT MATCHED THENinsert(nj, njmc, kyf)values(t2.nj, t2.njmc, t2.kyf);commit;end;elsif v_lx = 'xiaoqu' then--校区beginMERGE INTO dm_xiaoqub t1 USING dm_xiaoqub@bh_dblink t2 ON (t1.dm = t2.dm)WHEN MATCHED THENUPDATESET t1.mc   = t2.mc,t1.kyf  = t2.kyfWHEN NOT MATCHED THENinsert(dm, mc, kyf)values(t2.dm, t2.mc, t2.kyf);commit;end;elsebeginselect * from dual;commit;end;end if;end;
exceptionwhen others thenrollback;DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);v_res := 0;
end KCSB_XXTBBYLX;

 

存储过程实现同步数据功能,从前端到数据库

页面放几个按钮:

 点击上面的每个按钮,都会在数据库里执行 存储过程 进行同步数据。具体的实现方法及步骤如下:

然后,对应 html 的书写是:

<div class="address">当前位置:申报设置 >> 信息同步</div>
<div class="clr"></div>
<div class="butsea"><table  border="0" align="left" cellpadding="0" cellspacing="0"  ><tr><td height="35px"><div class="addlist_button"> <a href="javascript:xxtb('js');" id='jstb'><span>教师同步</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('yx');" ><span>院系同步</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('zy');" ><span>专业同步</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('xn');" ><span>学年学期</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('nj');" ><span>年级</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('skyy');" ><span>授课语言</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('pycc');" ><span>培养层次</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('kclb');" ><span>课程类别</span></a> </div><div class="addlist_button  ml15"> <a href="javascript:xxtb('xiaoqu');" ><span>校区</span></a> </div></td></tr></table>
</div>

然后,html对应的 js 方法是:

function  xxtb(lx){BOX_show('loadDiv');var url="<%=path%>/xxtb/XxtbByLx"var param={'lx':lx};$.post(url,param,function(result){if(result){alert("信息同步成功");}else{alert("信息同步失败");}BOX_remove('loadDiv');},'json')
}

 然后,对应的 controller 方法是:

@ResponseBody
@RequestMapping("XxtbByLx")
//信息同步
public List<Map<String, Object>> XxtbByLx(String lx){return xxtbService.XxtbByLx(lx);
}

 然后,对应的 service 实现类:

//信息同步  lx:js=教师,yx=院系,kc=课程,xn=学年学期
@Override
public String XxtbByLx(String lx){Map<String,String> paraMap = new HashMap<String, String>();paraMap.put("lx", lx);paraMap.put("res", "0");dbDao.update("xxtb.XxtbByLx",paraMap);dbDao.update("xxtb.cleanString","");//清除特殊字符return new ArrayList<Map<String,Object>>();
}

然后,xml 中相关的方法以及 xml 的sql 处理脚本:

<!-- 去除oracle中字符串中的特殊字符  -->
<update id="xxtb.cleanString">beginupdate dm_yxb set yxywmc = replace(yxywmc, chr(9), '');update dm_yxb set yxywmc = replace(yxywmc, chr(10), '');update dm_yxb set yxywmc = replace(yxywmc, chr(13), '');update dm_yxb set yxywmc = replace(yxywmc, chr(32), '');update dm_yxb set yxywmc = replace(yxywmc, chr(34), '');update dm_yxb set yxywmc = replace(yxywmc, chr(63), '');update dm_zyb set zyywmc = replace(zyywmc, chr(9), '');update dm_zyb set zyywmc = replace(zyywmc, chr(10), '');update dm_zyb set zyywmc = replace(zyywmc, chr(13), '');update dm_zyb set zyywmc = replace(zyywmc, chr(32), '');update dm_zyb set zyywmc = replace(zyywmc, chr(34), '');update dm_zyb set zyywmc = replace(zyywmc, chr(63), '');end;
</update>

 

<!-- 信息同步 -->
<parameterMap class="java.util.HashMap" id="xxbMap" ><!-- 需要传入的参数 --><parameter property="lx"   javaType="String"    jdbcType="VARCHAR"  mode="IN"/><!-- 可以返回的参数 --><parameter property="res" javaType="String"    jdbcType="VARCHAR"  mode="OUT"/>
</parameterMap>
<procedure id="xxtb.XXXXXXXXXX" parameterMap="xxbMap"><!-- 调用数据库存储过程 -->{call XXXXXXXXXXXX_BY_LX(?,?)}
</procedure>

然后,对应的存储过程 KCSB_XXTBBYLX :

create or replace procedure KCSB_XXTBBYLX(v_lx  in VARCHAR2, -- 入参v_res out VARCHAR2 -- 返回值
) is
beginv_res := 1;begin--学年学期if v_lx = 'xn' thenbeginMERGE INTO dm_xnxqb t1 USING dm_xnxqb@bh_dblink t2 ON (t1.xn = t2.xn and t1.xq = t2.xq)WHEN MATCHED THENUPDATESET t1.xnmc   = t2.xnmc,t1.xqmc   = t2.xqmc,t1.kyf    = t2.kyf,t1.sfdqxq = t2.sfdqxqWHEN NOT MATCHED THENinsert(xn, xq, xnmc, xqmc, kyf, sfdqxq)values(t2.xn, t2.xq, t2.xnmc, t2.xqmc, t2.kyf, t2.sfdqxq);commit;end;elsif v_lx = 'nj' then--年级beginMERGE INTO dm_njb t1 USING dm_njb@bh_dblink t2 ON (t1.nj = t2.nj)WHEN MATCHED THENUPDATESET t1.njmc   = t2.njmc,t1.kyf    = t2.kyfWHEN NOT MATCHED THENinsert(nj, njmc, kyf)values(t2.nj, t2.njmc, t2.kyf);commit;end;elsif v_lx = 'xiaoqu' then--校区beginMERGE INTO dm_xiaoqub t1 USING dm_xiaoqub@bh_dblink t2 ON (t1.dm = t2.dm)WHEN MATCHED THENUPDATESET t1.mc   = t2.mc,t1.kyf  = t2.kyfWHEN NOT MATCHED THENinsert(dm, mc, kyf)values(t2.dm, t2.mc, t2.kyf);commit;end;elsebeginselect * from dual;commit;end;end if;end;
exceptionwhen others thenrollback;DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);v_res := 0;
end KCSB_XXTBBYLX;