MySQL stored procedure with no parameters can't recognize datatypes

Question

can You help.

This code should update quantity of product in packages to by equal to minimum stock of product that are forming the package. My stocks are updated based on data form Oracle ERP every 5 minutes, but ERP does not know abort packages - they exists only in Prestashop, and they have to be updated independently in cycles (job). I try to do it by procedure.

CREATE OR REPLACE PROCEDURE B2C_P_QUANTYTY_UPDATE 
BEGIN
 FOR i IN 
  (SELECT ps_pack.id_product_pack, min(ps_stock_available.quantity) min_quantity
     FROM ps_pack, ps_stock_available
    WHERE ps_pack.id_product_item = ps_stock_available.id_product
    GROUP BY ps_pack.id_product_pack)
 LOOP
  UPDATE ps_stock_available 
     SET ps_stock_available.quantity = i.min_quantity 
   WHERE ps_stock_available.id_product = i.id_product_pack ; 
 END LOOP ; 
END;

2 errors has been found in analysis.

Unrecognized data type. (near "ps_pack" at position 81)
Unrecognized data type. (near "(" at position 109)

MySQL returned:

#1064 - Something is wrong in your syntax near 'BEGIN
FOR i IN
  (SELECT ps_pack.id_product_pack, min(ps_stock_available.qua' in line 2

I don't understand why, the select query works fine. But wrapped inside procedure stops recognizing data types.

score 0 · Accepted Answer · answered Aug 22 '20 at 20:51

Thanks to @Barranka answer to post SQL - Looping through ever row of table in mysql? i was able to do it.

And the code looks like that:

DELIMITER $$
CREATE OR REPLACE PROCEDURE B2C_P_QUANTYTY_UPDATE ()
BEGIN
    DECLARE c_product int;
    DECLARE c_min_quantity int;
    DECLARE done int default false;
    DECLARE quantity_cursor cursor FOR SELECT ps_pack.id_product_pack AS product , MIN(ps_stock_available.quantity) min_quantity 
                                         FROM ps_pack, ps_stock_available 
                                        WHERE ps_pack.id_product_item = ps_stock_available.id_product
                                        GROUP BY ps_pack.id_product_pack;
    DECLARE continue handler FOR not found  
        SET done = true;            
OPEN quantity_cursor;
    quantity_loop: LOOP
        FETCH quantity_cursor INTO c_product, c_min_quantity;
            IF done THEN 
                leave quantity_loop;
            END IF;
        UPDATE ps_stock_available 
           SET ps_stock_available.quantity = c_min_quantity 
         WHERE ps_stock_available.id_product = c_product; 
    END loop ; 
CLOSE  quantity_cursor;
COMMIT;
END$$
DELIMITER ;

MySQL stored procedure with no parameters can't recognize datatypes

1 Answers1