最近在写一个 spring mvc + mybatis 调用mysql 存储过程, 进行批量添加 ,返回了条数 但是没有执行添加操作 , 不知道哪里出错了 , 希望各位大神帮忙看看
mapper
service
dao
controller
存储过程 testPro
BEGIN
DECLARE id varchar(36);
DECLARE goodsId varchar(36);
DECLARE done INT DEFAULT FALSE;
declare fetchSeqCursor cursor for SELECT * FROM tmp_print;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
declare CONTINUE HANDLER for sqlexception
create temporary table if not exists promGoods (
ID VARCHAR(36),
PROMOTION_ID VARCHAR(36),
GOODS_ID VARCHAR(36)
);
START TRANSACTION;
open fetchSeqCursor;
seq_loop:loop
fetch fetchSeqCursor into id,goodsId;
IF done THEN
LEAVE seq_loop;
END IF;
SET @resaultCount = (SELECT count(*) FROM promGoods WHERE PROMOTION_ID = promotionId AND GOODS_ID = goodsId);
IF @resaultCount = 0 THEN
INSERT INTO promGoods(ID,PROMOTION_ID,GOODS_ID) VALUES (id,promotionId,goodsId);
END IF;
END LOOP;
CLOSE fetchSeqCursor;
SET resaultCount = (SELECT count(*) FROM promGoods WHERE PROMOTION_ID = promotionId );
IF resaultCount = 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
存储过程 sp_print_result:
BEGIN
-- Get the separated string.
declare cnt int default 0;
declare i int default 0;
set cnt = func_get_split_string_total(goodsId,',');
drop table if exists tmp_print;
create temporary table tmp_print (id VARCHAR(36) not null,goodsId VARCHAR(36) not null);
while i < cnt
do
set i = i + 1;
insert into tmp_print(id,goodsId) values (func_get_split_string(id,',',i),func_get_split_string(goodsId,',',i));
end while;
SET resaultCount = cnt;
END
目前 问题 :controller 两个resault 可以接受返回参数, 但是临时表中数据不执行添加操作