mysql存储过程获取返回值

Kylin_Shaw 发布于 2015/04/29 18:14
阅读 9K+
收藏 2
DROP PROCEDURE
IF EXISTS addvoucher;

CREATE PROCEDURE addvoucher (
	IN userid INT,
	IN voucherid INT,
	OUT result INT
)
BEGIN
	SELECT
		@endate_a := endate ,@batch_a := batch ,@c_count_a := c_count,
		@isdead_a := isdead
	FROM
		t_voucher
	WHERE
		id = voucherid;


SET autocommit = 0;
IF EXISTS (
	SELECT
		*
	FROM
		t_user_voucher tuv,
		t_voucher tv
	WHERE
		tv.id = tuv.voucherid
	AND tv.batch =@batch_a
) THEN

SET result = 1;-- 已存在

SELECT
	result;


ELSE

IF @c_count_a > 0 THEN

IF (
	TO_DAYS(@endate_a) - TO_DAYS(NOW())
) > 0 THEN

IF @isdead_a = 1 THEN
	INSERT INTO t_user_voucher (userid, voucherid, isdead)
VALUES
	(userid, voucherid, 1);
	UPDATE t_voucher SET c_count = c_count-1 where id = voucherid;

SET result = 0;-- 成功

SELECT
	result;

ELSE 
	SET result=4;
	SELECT result;

END
IF;


ELSE

SET result = 2;

SELECT
	result;


END
IF;


ELSE

SET result = 3;

SELECT
	result;


END
IF;


END
IF;

COMMIT;

END;


上面是我的存储过程


公司使用的是springMVC+mybatis


我想获取result的值,但是在变量赋值的过程中,


用的是


SELECT
 @endate_a := endate ,@batch_a := batch ,@c_count_a := c_count,
 @isdead_a := isdead
 FROM
 t_voucher
 WHERE
 id = voucherid;
这种赋值,然后call出来后,返回


mysql> call addvoucher(1,1,@result);
+---------------------+-------------------+-----------------------+---------------------+
| @endate_a := endate | @batch_a := batch | @c_count_a := c_count | @isdead_a := isdead |
+---------------------+-------------------+-----------------------+---------------------+
| 2015-04-15 16:41:49 | 1_1               |                     1 |                   1 |
+---------------------+-------------------+-----------------------+---------------------+
1 row in set




+--------+
| result |
+--------+
|      2 |
+--------+
1 row in set




Query OK, 0 rows affected




我该怎么获取到result的值

加载中
0
Kylin_Shaw
Kylin_Shaw

引用来自“诺维茨基”的评论

分享下答案吧
<parameterMap id="swapParameters" class="map" >     
 		<parameter property="userid" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>     
 		<parameter property="voucherid" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>       
 		<parameter property="result" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>     
		</parameterMap>     
	<procedure id="addvoucher" parameterMap="swapParameters" >     
		{call addvoucher (?,?,?)}      
	</procedure>

这个是XML

Java中

Map<Object, Object> map = new HashMap<Object, Object>();
		map.put("userid", 1);
		map.put("voucherid", 1);
		map.put("result", -1);
		int result = -1;
		try {
			this.sqlMapClient.queryForObject("Voucher.addvoucher", map);
			result = (Integer) map.get("result");
			System.out.println(result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;



这个就能拿到我指定的result了



0
Kylin_Shaw
Kylin_Shaw
我自己已经解决了....
0
诺维茨基
诺维茨基
分享下答案吧
返回顶部
顶部