mysql存储过程错误（1172，“结果由多个行组成”）

本文介绍了mysql存储过程错误（1172，“结果由多个行组成”）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

当尝试从django运行以下存储过程时，我会得到一个OperationError（1172，Result由多个行组成）任何想法我可能做错了什么？

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?

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

推荐答案

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

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 int parementer to another name. For example - IN storeId_param int

查询将如下 -

SELECT S1000IncreaseChoices INTO changeType FROM store_store WHERE StoreID = storeId_param;

这篇关于mysql存储过程错误（1172，“结果由多个行组成”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

storeid