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

页面放几个按钮：

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

然后，对应 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">
	begin
		update 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
begin
  v_res := 1;
  begin
    --学年学期
    if v_lx = 'xn' then
      begin
        MERGE INTO dm_xnxqb t1 USING dm_xnxqb@bh_dblink t2 ON (t1.xn = t2.xn and t1.xq = t2.xq)
        WHEN MATCHED THEN
          UPDATE
             SET t1.xnmc   = t2.xnmc,
                 t1.xqmc   = t2.xqmc,
                 t1.kyf    = t2.kyf,
                 t1.sfdqxq = t2.sfdqxq
        WHEN NOT MATCHED THEN
          insert
            (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
      --年级
      begin
        MERGE INTO dm_njb t1 USING dm_njb@bh_dblink t2 ON (t1.nj = t2.nj)
        WHEN MATCHED THEN
          UPDATE
             SET t1.njmc   = t2.njmc,
                 t1.kyf    = t2.kyf
        WHEN NOT MATCHED THEN
          insert
            (nj, njmc, kyf)
          values
            (t2.nj, t2.njmc, t2.kyf);
        commit;
      end;
    
    elsif v_lx = 'xiaoqu' then
      --校区
      begin
        MERGE INTO dm_xiaoqub t1 USING dm_xiaoqub@bh_dblink t2 ON (t1.dm = t2.dm)
        WHEN MATCHED THEN
          UPDATE
             SET t1.mc   = t2.mc,
                 t1.kyf  = t2.kyf
        WHEN NOT MATCHED THEN
          insert
            (dm, mc, kyf)
          values
            (t2.dm, t2.mc, t2.kyf);
        commit;
      end;
   
    else
      begin
        select * from dual;
        commit;
      end;
    end if;
  end;
exception
  when others then
    rollback;
    DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);
    DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);
    v_res := 0;
end KCSB_XXTBBYLX;