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

存储过程中经常需要返回结果集。 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.csdn.net/rdarda/article/details/7881648/
有存储过程类型和变量命名不能和查询的字段重复
需要注意的

BEGINDECLARE        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 THENSELECT  count(*) INTO cntFROM    c_daily_user_positionWHERE    sys_id = sysidAND settle_date = settledateAND channel_id = channelidAND user_id = useridAND product_id = productid; if cnt =0 THENinsert 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