mybatis调用mysql存储过程（返回参数，单结果集，多结果集）
图1.png
这里IN表示输入的值，OUT表示输出的值。（一个输入值两个输出值）
输出的二个值
RETCODE、RETDESC在xxMapper.xml中要输出，图1中有几个值图2这里就有几个，使用驼峰
图2.png
注：RETCODE、RETDESC这里的输出值不用在函数里SELECT RETCODE;，因为即使没有，当执行成功后会自动返回在对象里（Object/Map），所以在函数里不用SELECT一下了。
Navicat中执行函数需要输入参数值，也是对应图1，@A@B表示输出值。（一个输入值两个输出值）
image.png
函数p_duplicate_enroll中
1. 标注一二三四五处如果存在，则执行成功后返回结果如下
p_duplicate_enroll执行结果.png
标注一对应结果1
标注二对应结果2
标注三对应结果3
标注四对应结果4
标注五对应结果5
其中结果3返回的是集合。
存在这么多结果，想要取结果3中的集合是不好取的，只有像下图一样只有一个结果才好取出集合。
2. 标注一二四五处如果不存在，则执行成功后返回结果如下
p_duplicate_enroll_copy执行结果.png
只有结果1，对应的是标注三处的值。可以取出集合
函数p_duplicate_enroll
BEGIN
/*
参数格式：
SUBJECTID ： 输入参数：科目ID
RETCODE： 输出参数：返回码，0 为成功，其他是失败
RETDESC： 输出参数：返回内容描述
结果集： 输出结果集：id,subjectid,idcard,name,genre,relate_subjectid,re_marks,re_idcard,re_id
*/
/* 定义I_SUBJECT 关联科目ID */
DECLARE RELATE_SUBJECT_ID BIGINT(50) DEFAULT -1;
/* 捕捉任何异常,异常后退出 */
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DELETE FROM T_SYN_LOG WHERE TASK_NAME = 'P_DUPLICATE_ENROLL_EXCEPTION';
INSERT INTO T_SYN_LOG(TASK_NAME,OPERATE_CONTENT) VALUES ('P_DUPLICATE_ENROLL_EXCEPTION','EXCEPTION');
SET RETCODE = 99;
SET RETDESC = '重复报名数据异常';
COMMIT;
ROLLBACK;
END;
/* 初始化返回码 */
SET RETCODE = -1;
SET RETDESC = '';
IF I_SUBJECT_ID IS NOT NULL || I_SUBJECT_ID <> '' THEN
SELECT IFNULL(T.relate_subjectid,0) INTO RELATE_SUBJECT_ID FROM t_auth_subject T WHERE T.id = I_SUBJECT_ID;
ELSE
SET RELATE_SUBJECT_ID = -1;
END IF;
/* 调试语句 */
SELECT I_SUBJECT_ID; //标注一
SELECT RELATE_SUBJECT_ID; //标注二
IF RELATE_SUBJECT_ID <> 0 AND RELATE_SUBJECT_ID <> -1 THEN
//标注三
SELECT MM.id ,MM.subjectid,MM.idcard,MM.`name`,'理论报名重复' as genre,MM.relate_subjectid, EE.thery_marks as re_marks,EE.idcard AS re_idcard ,ee.id as re_id FROM
(SELECT e.id,e.idcard,se.subjectid,se.thery_marks FROM t_auth_score se
LEFT JOIN t_auth_enroll E on(se.eid = e.id and e.`status`=101001)
LEFT JOIN t_auth_subject s on (s.id = se.subjectid and s.`status` = 101001)
where se.`status` = 101001 AND E.is_pass_verifty = 2402 and se.subjectid = RELATE_SUBJECT_ID
AND se.thery_marks >= s.thery_marks AND E.genre LIKE '理论%'
) EE
LEFT JOIN (
SELECT e1.id,e1.subjectid,e1.idcard,e1.name,s1.relate_subjectid FROM t_auth_enroll e1
LEFT JOIN t_auth_subject s1 on(e1.subjectid = s1.id and s1.`status` = 101001)
where e1.subjectid = I_SUBJECT_ID AND e1.status = 101001 AND e1.genre LIKE '理论%' AND e1.is_pass_verifty <> 2403
AND s1.relate_subjectid IS NOT NULL) MM
ON (EE.idcard = MM.idcard AND EE.subjectid = MM.relate_subjectid)
WHERE MM.id IS NOT NULL
UNION ALL
//标注三
SELECT MM.id,MM.subjectid,MM.idcard,MM.`name`,'操作报名重复' as genre,MM.relate_subjectid, EE.operation_marks as re_marks,EE.idcard AS re_idcard ,ee.id as re_id FROM
(SELECT e.id,e.idcard,se.subjectid,se.operation_marks FROM t_auth_score se
LEFT JOIN t_auth_enroll E on(se.eid = e.id and e.`status`=101001)
LEFT JOIN t_auth_subject s on (s.id = se.subjectid and s.`status` = 101001)
where se.`status` = 101001 AND E.is_pass_verifty = 2402 and se.subjectid = RELATE_SUBJECT_ID
AND se.operation_marks >= s.operation_marks AND E.genre LIKE '%操作'
) EE
LEFT JOIN (
SELECT e1.id,e1.subjectid,e1.idcard,e1.name,s1.relate_subjectid FROM t_auth_enroll e1
LEFT JOIN t_auth_subject s1 on(e1.subjectid = s1.id and s1.`status` = 101001)
where e1.subjectid = I_SUBJECT_ID AND e1.status = 101001 AND e1.genre LIKE '%操作' AND e1.is_pass_verifty <> 2403
AND s1.relate_subjectid IS NOT NULL) MM
ON (EE.idcard = MM.idcard AND EE.subjectid = MM.relate_subjectid)
WHERE MM.id IS NOT NULL ;
SET RETCODE = 0;
SET RETDESC = '返回重复报名人员信息' ;
ELSE
SET RETCODE = 1;
SET RETDESC = '当前科目无关联科目，未检测到重复报名信息' ;
END IF;
/* 调试语句 */
SELECT RETCODE; //标注四
SELECT RETDESC; //标注五
/* 记录日志 */
DELETE FROM T_SYN_LOG WHERE TASK_NAME = 'P_DUPLICATE_ENROLL_END';
INSERT INTO T_SYN_LOG(TASK_NAME,OPERATE_CONTENT) VALUES ('P_DUPLICATE_ENROLL_END','END');
COMMIT;
END
网友评论