使用游標插入另一個表的過程

我正在使用游標創建一個過程，如果 5 個產品的目前數量低於其最小數量，它會檢查不同的表。如果目前數量低於我創建一個採購訂單（表）。我很難從課程中的不同表格中獲取資訊來創建採購訂單。

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 ;

編輯：我是這樣理解的。有沒有不使用游標的替代方法？

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

“創建採購訂單”需要什麼？如果是MySQL之外的東西，那麼處理流程是不可能的。

相反，努力收集採購訂單所需的數據，將該數據返回到您的應用程序，然後該應用程序將“創建採購訂單”。

盡量不要使用“游標”。它們效率低下、笨拙，並且與 SQL 對“數據集”進行操作的原則格格不入。這似乎接近循環正在做的事情：

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;

而且您可能也想獲取product_id，以便知道要重新排序的內容。

引用自：https://dba.stackexchange.com/questions/296119