mysql 带入参和出参_mybatis结合mysql，调用含有入参和出参的存储过程的方式

先看看存储过程，存储过程名叫get_serial_no：

BEGIN

DECLARE v_category_code VARCHAR(20);

DECLARE v_serial_no INT(5);

DECLARE v_serial_code VARCHAR(20);

DECLARE v_existsed TINYINT(1);

START TRANSACTION;

SELECT serial_no,serial_code INTO v_serial_no,v_serial_code FROM t_bm_serial_no WHERE category_id = p_category_id FOR UPDATE;

SELECT category_code INTO v_category_code FROM t_bm_group_category WHERE category_id = p_category_id;

IF v_category_code IS NOT NULL THEN

IF v_serial_no IS NULL THEN

SET p_serial_code = CONCAT(v_category_code,'0001');

INSERT INTO t_bm_serial_no

(category_id,serial_no,serial_code,oper_id,oper_time)

VALUES

(p_category_id,1,p_serial_code,p_oper_id,NOW());

ELSE

SET v_existsed = 1;

cal_v_increase: WHILE v_existsed > 0 DO

SET v_serial_no = v_serial_no+1;

SELECT count(check_in_no) INTO v_existsed FROM t_bm_checkin c

WHERE c.DISEASE_CATEGORY_ID = p_category_id

AND c.CHECK_IN_STATUS = '200102'

AND RIGHT(check_in_no,5)+ 0 = v_serial_no;

END WHILE cal_v_increase;

SET p_serial_code = CONCAT(v_category_code,RIGHT(CONCAT('0000',v_serial_no),4));

UPDATE t_bm_serial_no

SET serial_no = v_serial_no,

serial_code = p_serial_code,

oper_id = p_oper_id,

oper_time = NOW()

WHERE category_id = p_category_id;

END IF;

END IF;

COMMIT;

END参数：IN p_category_id varchar(36),IN p_oper_id varchar(36),OUT p_serial_code varchar(20)

第一种实现方式，参数为map：

java实现：

HashMap paramMap = new HashMap();

paramMap.put("diseaseCategoryId",checkin.getDiseaseCategoryId());

paramMap.put("operId", checkin.getOperId());

checkInDAO.getCheckInNoByCheckIn(paramMap);//出参存放于paramMap中

checkin.setCheckInNo(checkin.getCheckInNo());

mybatis中配置实现：

配置paramMap，其中diseaseCategoryId和operId为入参，checkInNo为出参

调用sql语句，statementType为callable表示调用存储过程：

{call get_serial_no(?,?,?)}

第二种实现方式，参数为javabean：

这里javabean名为checkin，checkin中含有diseaseCategoryId、operId和checkInNo三个参数：

checkInDAO.getCheckInNoByCheckIn(checkin);这种方式，在mybatis中就不需要配置返回的结果集，同map方式一样，其中diseaseCategoryId和operId为入参，checkInNo为出参

{call get_serial_no(

#{diseaseCategoryId,jdbcType=VARCHAR,mode=IN},

#{operId,jdbcType=VARCHAR,mode=IN},

#{checkInNo,jdbcType=VARCHAR,mode=OUT}

)}