我在 MySQL 中創建了一個存盤程序。它必須獲取 5 個不同列的值并根據輸入引數執行計算并將計算值更新到表中。我能夠創建存盤程序，但是在執行它時，它拋出一個錯誤，指出“MYSQL 說：#1172 - 結果包含多于一行”。
下面是我的存盤程序
DELIMITER $$
CREATE DEFINER=`andrew`@`localhost` PROCEDURE `st_update_userpoints`(
in point int,
in gold int,
in silver int,
in bronze int,
in userId int)
BEGIN
DECLARE _gold int default 0;
DECLARE _silver int default 0;
DECLARE _bronze int default 0;
DECLARE _points int default 0;
DECLARE _score int default 0;
select `goldMedal`,`silverMedal`,`bronzeMedal`,`totalPoints`,`score` into @_gold,@_silver,@_bronze,@_points,@_score from `userpoints` where `userId`=userId;
set _gold:=(@_gold gold);
set _silver:=(@_silver silver);
set _bronze:=(@_bronze bronze);
set _points:=(@_points points);
set _score = (@_gold*5) (@_silver 3) (@_bronze) (@_points);
update `userpoints` set `goldMedal`=@_gold, `silverMedal`=@_silver, `bronzeMedal`=@_bronze, `totalPoints`=@_points,
`score`=@_score where `userId`=userId;
END$$
DELIMITER ;
uj5u.com熱心網友回復：
where `userId`=userIdin userId int對左右部分使用輸入引數值。因此，除了userpoints.userId為 NULL的行（在這種情況下查詢回傳多行）之外，它始終為 TRUE，如果提供的引數值為 NULL（并且查詢不回傳行），則它始終為 NULL==FALSE。
使用where userpoints.userId=userId- 在這種情況下，左部分值是列值，右部分值是提供引數值。我會檢查提供的 NULL 值...
uj5u.com熱心網友回復：
最后修正存盤程序
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `st_update_userpoints`(
in points int,
in gold int,
in silver int,
in bronze int,
in userId int)
BEGIN
DECLARE _gold int default 0;
DECLARE _silver int default 0;
DECLARE _bronze int default 0;
DECLARE _points int default 0;
DECLARE _score int default 0;
select `goldMedal`,`silverMedal`,`bronzeMedal`,`totalPoints`,`score` into _gold,_silver,_bronze,_points,_score from `userpoints` where `userpoints`.`userId`=userId;
set _gold:=(_gold gold);
set _silver:=(_silver silver);
set _bronze:=(_bronze bronze);
set _points:=(_points points);
set _score = (_gold*5) (_silver*3) (_bronze*1) (_points);
update `userpoints` set `goldMedal`=_gold, `silverMedal`=_silver, `bronzeMedal`=_bronze, `totalPoints`=_points,
`score`=_score where `userpoints`.`userId`=userId;
END $$
DELIMITER ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/377401.html