在存储过程内的查询中使用值进行操作（mysql）

i have a stored procedure in mysql with a couple of queries and i need to perform some operations with that query.

我在mysql中有一个存储过程,有几个查询,我需要用该查询执行一些操作。

This is some the code from the stored procedure:

这是存储过程中的一些代码:

    BEGIN
    SET @@session.collation_connection = @@global.collation_connection;

    DROP TEMPORARY TABLE IF EXISTS innerContainers;
    CREATE TEMPORARY TABLE `innerContainers` (
        `id_container` INT(10) NOT NULL,
        `display_name` VARCHAR(100) NOT NULL,
        PRIMARY KEY (`id_container`)
        )
        ENGINE = memory;

    INSERT INTO innerContainers(id_container, display_name)
    (SELECT c1.id_container, c1.display_name 
     FROM container_presentation cp
     LEFT JOIN presentation p USING(id_presentation)
     LEFT JOIN container c1 ON p.id_container = c1.id_container
     WHERE c1.isDeleted = 0 AND c1.isActive = 1 AND
             cp.id_container = in_id_container)
    UNION
    (SELECT c1.id_container, c1.display_name 
     FROM container_assembly_item cp
     LEFT JOIN presentation p USING(id_presentation)
     LEFT JOIN container c1 ON p.id_container = c1.id_container
     WHERE c1.isDeleted = 0 AND c1.isActive = 1 AND
           cp.id_container = in_id_container);

    SELECT mad.id_container,
             mat.sign_stock, 
             ma.id_management_start_point,  
             ma.id_management_end_point,
             mad.quantity
    FROM management_activity ma
    LEFT JOIN management_activity_type mat ON ma.id_management_activity_type = mat.id_management_activity_type
    LEFT JOIN management_activity_detail mad ON ma.id_management_activity = mad.id_management_activity
    LEFT JOIN management_stock_point msp ON ma.id_management_end_point = msp.id_management_stock_point
    LEFT JOIN management_stock_point msp1 ON ma.id_management_start_point = msp1.id_management_stock_point
    WHERE mad.id_container IN (SELECT id_container FROM innerContainers)
    ORDER BY mad.id_container ASC;
END

Now, after the last query.. i need to do some operations and return a value for each id_container inside the temporary table depending on the values in the second query. Something like this:

现在,在最后一次查询之后..我需要做一些操作,并根据第二个查询中的值为临时表中的每个id_container返回一个值。像这样的东西:

foreach id_container in the second query i have a resultValue and i need to:

foreach id_container在第二个查询中我有一个resultValue,我需要:

if the sign_stock == 1 and some other conditions then resultValue -= quantity and if sign_stock == 2 and some other conditions then resultValue += quantity. And the final resultValue after iterating over the id_container lines will be the one i want for that id_container in the temporary table. I dont know how to do that operation.. can some one help me with that?

如果sign_stock == 1和其他一些条件,则resultValue - = quantity,如果sign_stock == 2和其他一些条件,则resultValue + = quantity。迭代id_container行之后的最终resultValue将是我想要的临时表中的id_container。我不知道怎么做这个操作..有人可以帮助我吗?

1 个解决方案

#1

Don't create a temporary table unless you need the data after the procedure call. Either way, in order to iterate over the results of a SELECT query, use a CURSOR.

除非在过程调用后需要数据,否则不要创建临时表。无论哪种方式,为了迭代SELECT查询的结果,使用CURSOR。

A simple example is provided in the linked manual page.

链接手册页中提供了一个简单示例。

#1