mybatis调用mysql存储过程，带结果集

最新推荐文章于 2025-02-18 19:51:30 发布

上漂的程序猫狗

最新推荐文章于 2025-02-18 19:51:30 发布

阅读量2.9k

点赞数

分类专栏： mybatis

本文链接：https://blog.youkuaiyun.com/ggjlvzjy/article/details/49025327

版权

mybatis 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一个MySQL存储过程的实例，并演示了如何使用MyBatis框架调用该存储过程。此外，还详细解释了在服务层中正确配置事务的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

存储过程中经常需要返回结果集。 MySQL 中直接用 select 即可返回结果集。而 Oracle 则需要使用游标来返回结果集。这一点 MySQL 相对比较方便，如下代码即可实现输出结果集：

mybatis调用

service层需要注意增加 @Transactional(readOnly = false),放开事务写功能

service

@Transactional(readOnly = false)
    public List<DemoEntity> pc() {
        Map<String, String> map = new HashMap<String, String>() {
            {
                put("param", "1");
            }
        };
        return dao.pc(map);
    }

配置文件里面

<select id="pc" parameterType="java.util.Map" resultType="demoEntity" statementType="CALLABLE">
        {call pcTest(
        #{param,jdbcType=VARCHAR,mode=IN}
        )
        }
    </select>

mysql存储过程

DELIMITER $$ 
DROP procedure IF EXISTS pcTest $$  
CREATE procedure pcTest(in sear_name  varchar(2000))  
BEGIN  
  SELECT * FROM test_command;

END$$
DELIMITER;

一个mysql存储过程，可以参考博客：
http://blog.youkuaiyun.com/rdarda/article/details/7881648/
有存储过程类型和变量命名不能和查询的字段重复
需要注意的

BEGIN
DECLARE
        channelid VARCHAR (50);

DECLARE
    userid VARCHAR (50);

DECLARE
    productid VARCHAR (50);
DECLARE
    directions VARCHAR (50);

DECLARE
     no_more INT DEFAULT 0;

DECLARE
    volumes INT ;

DECLARE
    amounts double ;

DECLARE
    cnt INT DEFAULT 0;



DECLARE
    datacursor CURSOR FOR SELECT
        channel_id,
        user_id,
        product_id,
        direction,
        sum(volume),
        sum(amount) 
    FROM
        c_transaction_reporting
    WHERE
        sys_id = sysid
    AND settle_date = settledate
    GROUP BY
        channel_id,
        user_id,
        product_id,
        direction;

DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more = 1;

OPEN datacursor;
 FETCH datacursor INTO channelid,
 userid,
 productid,
 directions,
 volumes,
 amounts;



REPEAT

 IF  directions = 1 THEN
SELECT  count(*) INTO cnt
FROM
    c_daily_user_position
WHERE
    sys_id = sysid
AND settle_date = settledate
AND channel_id = channelid
AND user_id = userid
AND product_id = productid;



 if cnt =0 THEN

insert into c_daily_user_position ( sys_id, settle_date, channel_id, user_id, product_id, purchase_volume, purchase_amount ) values ( sysid, settledate, channelid, userid, productid, volumes, amounts );

   else 
update c_daily_user_position set purchase_volume = volumes and purchase_amount = amounts where sys_id = sysid and settle_date = settledate and channel_id = channelid and userid = userid and product_id = productid;

 END if;

else 
 select count(*) into cnt from c_daily_user_position where sys_id = sysid and settle_date = settledate and channel_id = channelid and user_id = userid and product_id = productid;
   if cnt =0 then
   insert into c_daily_user_position ( sys_id, settle_date, channel_id, user_id, product_id, redeem_volume, redeem_amount ) values ( sysid, settledate, channelid, userid, productid, volumes, amounts );

   else


    update c_daily_user_position set redeem_volume = volumes and redeem_amount = amounts where sys_id = sysid and settle_date = settledate and channel_id = channelid and userid = userid and product_id = productid;

   end if ;


 END  IF;


 FETCH datacursor INTO channelid,
 userid,
 productid,
 directions,
 volumes,
 amounts;

  UNTIL no_more=1
 END REPEAT;
 CLOSE  datacursor;  

END