mysql报错1172该怎样解决,mysql存储过程错误(1172,’结果由多行组成’)

当试图从django运行以下存储过程时,我得到一个OperationError(1172,’结果由多行组成’)任何想法我可能做错了什么？

-- --------------------------------------------------------------------------------

-- 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

我不确定它是否重要,但我启动存储过程如下：

def priceupdate(request, store_id):

cursor = connection.cursor()

cursor.callproc("UpdatePrices", (store_id, '1000 ML'))

cursor.close()

return HttpResponseRedirect(request.META.get('HTTP_REFERER'))

解决方法:

SELECT … INTO查询为结果集提供多条记录. WHERE过滤器不正确 – 它们比较两个相同的值StoreID = storeId.将storeId int parementer重命名为另一个名称.例如 – IN storeId_param int

查询将是这样的 –

SELECT S1000IncreaseChoices INTO changeType FROM store_store WHERE StoreID = storeId_param;

标签：mysql,django,stored-procedures

来源： https://codeday.me/bug/20191007/1866881.html