CREATE DEFINER=`root`@`%` PROCEDURE `p_handle_yw_standard_info00801`(IN pa_ext_uuid VARCHAR ( 50 ),
IN action VARCHAR ( 50 ),
IN i_zspm_dm VARCHAR ( 50 ),
IN i_zszm_dm VARCHAR ( 50 ),
OUT ErrCode VARCHAR ( 20 ),
OUT ErrMsg VARCHAR ( 200 ))
proc_label:BEGIN
DECLARE i_count_field INT (2) DEFAULT 0;
DECLARE i_found INT(2) DEFAULT 0;
DECLARE v_err VARCHAR(500) DEFAULT '';
declare v_sfxmlx VARCHAR(100) CHARACTER SET utf8;
-- 定义游标遍历结束标志
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
/*异常处理机制*/
DECLARE EXIT HANDLER FOR 1062
BEGIN
set ErrCode='99';
set ErrMsg='保存费源信息失败,id已存在';
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
set ErrCode='99';
set ErrMsg='数据处理异常';
END;
-- 查询费源信息是否存在
select count(*) into i_count_field from yw_008_pw_info i where i.fyxx_uuid = pa_ext_uuid;
IF i_count_field = 0 THEN
set ErrCode = '99';
set ErrMsg = '费源信息不存在';
LEAVE proc_label;
END IF;
IF action = 'save' THEN
-- 插入费源信息
INSERT INTO fssr_swsb_pw
(uuid, xmmc, htqdsj, pc, crf, crfssqx, crsr, jmje, swbmzszje, szqxdm, szqx, szs, mc, jfrmc, jfrlx, shxydm, yx, dz, lxr, lxfs, bz, zspm_dm, zszm_dm)
select t.fyxx_uuid, t.xmmc, t.htqdsj, t.pc, t.crf, t.admindivcode, t.crsr, t.jmje, t.swbmzszje,t.admindivcode, t.admindivname, t.szsbm, t.mc, t.jfrmc, t.jfrlx, t.shxydm,t.yx, t.dz, t.lxr, t.lxfs, t.bz , r.zspm_dm, r.zszm_dm from yw_008_pw_info t
left join xm_item x on x.admindivcode = t.admindivcode and x.entercode = t.entercode and x.ywcode = t.sfxmlx and x.itemcode = t.itemcode and
x.is_deleted =2 and x.is_enabled=1 and x.status = '2'
left join xm_item_relation r on x.xm_uuid = r.xm_uuid and r.zspm_dm = i_zspm_dm and r.zszm_dm = i_zszm_dm
where t.fyxx_uuid = pa_ext_uuid;
END IF;
set ErrCode = '00';
END
-- IN代表输入,是可以在存储过程中使用的变量.OUT代表输出,将存储过程的结果,或者运行结果信息输出.DECLARE为在存储过程中定义变量,可以通过select...set 变量为其赋值.定义存储过程的异常处理机制,并定义返回00为成功,99为失败,通过输入的参数action来判断要执行操作的类型.
Session session = baseDao.getSession();//获取session对象,可以通过各种手段获取
String procedureName = "p_handle_yw_standard_info00801" ;//存储过程的名称
session.doWork(connection -> {
CallableStatement cs = null;
try {
cs = connection.prepareCall("{call "+ procedureName + "(?,?,?,?,?,?)}");//有几个参数就有几个问号
cs.setString(1, fyxxuuid);
cs.setString(2, "save");
cs.setString(3, zspm_dm);
cs.setString(4, zszm_dm);//输入的四个参数
cs.execute();
String code = cs.getString(5);
String result = cs.getString(6);//输出的两个参数
if (!"00".equals(code)) {//上文存储过程执行成功会返回00
throw new ApiBzException(code, result);
}
baseDao.executeBySql("update yw_008_pw_info t left join fssr_swsb_pw f on f.uuid = t.fyxx_uuid set t.bz = f.bz where t.fyxx_uuid = ?", Collections.singletonList(fyxxuuid));
} catch (SQLException throwables) {
throwables.printStackTrace();
throw new RuntimeException(throwables);//异常处理机制
} finally {
IoUtil.close(cs);//java中自带的工具类
}
});