mysql 存储过程错误（1172，'结果包含多于一行'）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19830053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql stored procedure error (1172, 'Result consisted of more than one row')
提问by RuSs
When trying to run the following stored procedure from django, I get an OperationError (1172, 'Result consisted of more than one row') Any idea what I might be doing wrong?
尝试从 django 运行以下存储过程时，出现 OperationError (1172, 'Result contains more than one row') 知道我做错了什么吗？
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdatePrices`(IN storeId int, IN bottleSize VARCHAR(50))
BEGIN
DECLARE amount DECIMAL(10,2); DECLARE isCustom INT DEFAULT 0;
DECLARE changeType VARCHAR(50) DEFAULT 'State'; DECLARE updateType INT DEFAULT 0;
IF bottleSize = '1000 Ml' THEN
SELECT S1000IncreaseChoices INTO changeType FROM store_store WHERE StoreID = storeId;
IF changeType = 'State' THEN
SELECT updateType = 0;
END IF;
IF changeType = 'Flat' THEN
SELECT S1000IncreaseAmount INTO amount FROM store_store WHERE StoreID = storeId;
SELECT updateType = 1;
END IF;
IF changeType = 'Percent' THEN
SELECT 1 - S1000IncreaseAmount/100 INTO amount FROM store_store WHERE StoreID = storeId;
SELECT updateType = 2;
END IF;
END IF;
IF updateType = 0 THEN
update store_storeliquor SL
inner join liquor_liquor LL
on liquorID_id = id
set StorePrice = ShelfPrice
where BottleSize = bottleSize
and storeID_id = storeId
and custom = 0;
END IF;
IF updateType = 1 THEN
update store_storeliquor SL
inner join liquor_liquor LL
on liquorID_id = id
set StorePrice = OffPremisePrice + amount
where BottleSize = bottleSize
and storeID_id = storeId
and custom = 0;
END IF;
IF updateType = 1 THEN
update store_storeliquor SL
inner join liquor_liquor LL
on liquorID_id = id
set StorePrice = OffPremisePrice / amount
where BottleSize = bottleSize
and storeID_id = storeId
and custom = 0;
END IF;
END
I'm not sure if it matters, but I initiate the stored procedure like so:
我不确定这是否重要，但我像这样启动存储过程：
def priceupdate(request, store_id):
cursor = connection.cursor()
cursor.callproc("UpdatePrices", (store_id, '1000 ML'))
cursor.close()
return HttpResponseRedirect(request.META.get('HTTP_REFERER'))
回答by Devart
Your SELECT...INTO queries give result sets with more then one record. The WHERE filters are incorrect - they compare two the same values StoreID = storeId. Rename IN storeId intparementer to another name. For example - IN storeId_param int
您的 SELECT...INTO 查询提供的结果集包含多个记录。WHERE 过滤器不正确 - 它们比较两个相同的值StoreID = storeId。将IN storeId intparementer重命名为另一个名称。例如 - IN storeId_param int
The query will be like this -
查询将是这样的 -
SELECT S1000IncreaseChoices INTO changeType FROM store_store WHERE StoreID = storeId_param;
