表shop里的内容如下!
+--------+--------+---------+---------------+
| shopId | cityId | tradeId | shopName |
+--------+--------+---------+---------------+
| UA0AHA | UA | AH | gosure s shop |
+--------+--------+---------+---------------+其中cityId是shop的城市编号,tradeId是行业编号;
shopId的第12位是城市编号,45位是行业编号
现在要写一个存储过程在shop表里添加内容,
存储过程的输入参数是cityId ,tradeId , shopName
shopId 要在存储过程根据(cityId ,tradeId)自动生成,比如现在有一个shopId 为UA0AHA的记录,如果再插入一个cityId = UA,tradeId=AH的shop 那么它的shopId 就应该是UA0AHB了
现在我写的存储过程如下,(存储过程我刚学!见笑了!)DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`insertIntoShop`$$
CREATE PROCEDURE `test`.`insertIntoShop` (
in cityId varchar(2),
in tradeId varchar(2),
in shopName varchar(45)
)
BEGIN
DECLARE shopId varchar(6);
DECLARE lastShopId varchar(6);
DECLARE stopFlag int;
DECLARE cursor_shopId CURSOR
FOR select shopId
from `shop`
where `cityId` = cityId and `tradeId` = tradeId
group by shopId desc limit 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; OPEN cursor_shopId;
REPEAT
FETCH cursor_shopId into lastShopId;
UNTIL stopFlag = 1
end REPEAT;
CLOSE cursor_shopId; select lastShopId;
#问题在这里出现
#明明有一条记录,可是lastShopId 为NULL
END$$DELIMITER ;
+--------+--------+---------+---------------+
| shopId | cityId | tradeId | shopName |
+--------+--------+---------+---------------+
| UA0AHA | UA | AH | gosure s shop |
+--------+--------+---------+---------------+其中cityId是shop的城市编号,tradeId是行业编号;
shopId的第12位是城市编号,45位是行业编号
现在要写一个存储过程在shop表里添加内容,
存储过程的输入参数是cityId ,tradeId , shopName
shopId 要在存储过程根据(cityId ,tradeId)自动生成,比如现在有一个shopId 为UA0AHA的记录,如果再插入一个cityId = UA,tradeId=AH的shop 那么它的shopId 就应该是UA0AHB了
现在我写的存储过程如下,(存储过程我刚学!见笑了!)DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`insertIntoShop`$$
CREATE PROCEDURE `test`.`insertIntoShop` (
in cityId varchar(2),
in tradeId varchar(2),
in shopName varchar(45)
)
BEGIN
DECLARE shopId varchar(6);
DECLARE lastShopId varchar(6);
DECLARE stopFlag int;
DECLARE cursor_shopId CURSOR
FOR select shopId
from `shop`
where `cityId` = cityId and `tradeId` = tradeId
group by shopId desc limit 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1; OPEN cursor_shopId;
REPEAT
FETCH cursor_shopId into lastShopId;
UNTIL stopFlag = 1
end REPEAT;
CLOSE cursor_shopId; select lastShopId;
#问题在这里出现
#明明有一条记录,可是lastShopId 为NULL
END$$DELIMITER ;
在存储过程中定义的变量(shopId)和数据库里的字段有冲突!回贴者有分!