创建存储过程并在java代码中使用

最新推荐文章于 2023-03-23 08:50:41 发布

leese233

最新推荐文章于 2023-03-23 08:50:41 发布

阅读量428

点赞数 1

分类专栏：试错文章标签： java 开发语言 sql

本文链接：https://blog.csdn.net/leese233/article/details/126776330

版权

MySQL 存储过程异常处理数据操作 Java调用

关键词由CSDN通过智能技术生成

试错专栏收录该内容

60 篇文章 0 订阅

订阅专栏

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中自带的工具类
    }
});