Procedure insert into another table using cursor

cursorsMySQLstored-procedures

I'm creating a procedure, using cursor, which checks in different tables if the current quantity of 5 products are below their minimum quantity. If the current quantity is below I create a PURCHASE ORDER (table). I'm having difficulty capturing the information from the different tables in the course to create the PURCHASE ORDER.

CREATE TABLE IF NOT EXISTS `purchase_order`(
    `id_purchase` INT(11) NOT NULL AUTO_INCREMENT,
    `description` VARCHAR(200) NOT NULL,
    `total_consolidated` DECIMAL NOT NULL,
    `was_canceled` SET('Y','N'),
    `branch_number` INT(14) NOT NULL,
    `date_accomplished` DATE NOT NULL,
    PRIMARY KEY(`id_purchase`),
    FOREIGN KEY(`branch_number`)
    REFERENCES `branch` (`branch_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `item_product`(
    `lot_number` INT(11) NOT NULL AUTO_INCREMENT,
    `id_purchase` INT(11) NOT NULL,
    `quantity_requested` INT(11) NOT NULL,
    PRIMARY KEY(`lot_number`),
   FOREIGN KEY(`id_purchase`)
   REFERENCES `purchase_order` (`id_purchase`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `product`(
    `id_product`INT(11) NOT NULL AUTO_INCREMENT,  
    `minimum_quantity` INT(11) NOT NULL,    
    `lot_number` INT(11) NOT NULL,     
    PRIMARY KEY(`id_product`),
    FOREIGN KEY(`lot_number`)
    REFERENCES `item_product` (`lot_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)   
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `stock_product`(
    `id_stock_product`INT(11) NOT NULL AUTO_INCREMENT,    
    `current_quantity`INT(11) NOT NULL,   
    `id_product`INT(11) NOT NULL,    
    PRIMARY KEY(`id_stock_product`),
    FOREIGN KEY(`id_product`)
    REFERENCES `product` (`id_product`)    
    ON UPDATE CASCADE)
ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE sp_generate_purchase_order()
BEGIN
    DECLARE countN INT DEFAULT 0;
    DECLARE minimumQuantity INT DEFAULT 0;
    DECLARE currentQuantity INT DEFAULT 0;
    DECLARE curs CURSOR FOR SELECT  P.minimum_quantity, SP.current_quantity  from produc P inner join stock_product as SP
on P.id_product = SP.id_product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
    OPEN curs;    
    FETCH curs INTO minimumQuantity, currentQuantity;
    WHILE countN != 5
        FETCH curs INTO minimumQuantity, currentQuantity;
        IF currentQuantity < minimumQuantity
        THEN /*create the purchase order*/;
        END IF;
        set countN = countN + 1;
    END WHILE;
    CLOSE curs;
    
END$$
DELIMITER ;

EDIT: I got it that way. Is there any alternative way without using cursor?

DELIMITER $$
CREATE PROCEDURE sp_generate_purchase_order()
BEGIN
    DECLARE countN INT DEFAULT 0;
    DECLARE minimumQuantity INT DEFAULT 0;
    DECLARE currentQuantity INT DEFAULT 0;
    DECLARE idProduct INT DEFAULT 0;
    DECLARE numberBranch VARCHAR(200);   
    DECLARE total INT;
    DECLARE done BOOLEAN;
    DECLARE curs CURSOR FOR SELECT  P.id_product, P.minimum_quantity, SP.current_quantity, C.branch_number from product P inner join stock_product as SP
    on P.id_product = SP.id_product
    inner join item_product IP on P.lot_number = IP.lot_number
    inner join purchase_order PO on IP.id_purchase = PO.id_purchase
    inner join branch C on PO.branch_number = C.branch_number;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
    OPEN curs;    
    WHILE (countN != 5 or done != true)DO
        FETCH curs INTO idProduct, minimumQuantity, currentQuantity, numberBranch;
        SET total = 0;
        IF currentQuantity < minimumQuantity         
         THEN SET countN = countN + 1;
              SET total = minimumQuantity + currentQuantity;
              INSERT INTO purchase_order VALUES(NULL, 'for review',total,'N', numberBranch, CURDATE());
        END IF;        
    END WHILE;
    CLOSE curs;
    
END$$

Best Answer

What does it take to "create a purchase order"? If it is something outside MySQL, then the process flow is impossible.

Instead, work on gathering the data needed for the purchase order, return that data to your application, which will then "create the purchase order(s)".

Try not to use "cursors". They are inefficient, clumsy, and alien to SQL's principle of acting on 'sets' of data. This seems to be close to what the loop is doing:

INSERT INTO purchase_order
        (description, total_consolidated, was_canceled,
         branch_number, date_accomplished)
    SELECT  'for review',
            minimumQuantity + currentQuantity,
            'N',
            numberBranch,
            CURDATE()
        FROM product P
        JOIN stock_product as SP  on P.id_product = SP.id_product
        inner join item_product IP on P.lot_number = IP.lot_number
        inner join purchase_order PO on IP.id_purchase = PO.id_purchase
        inner join branch C on PO.branch_number = C.branch_number
        WHERE currentQuantity < minimumQuantity
        LIMIT 5;

And you probably want to fetch the product_id, too, so that you know what to reorder.

Related Question