mysql 存储过程 实例

USE mydatebase;
DROP PROCEDURE IF EXISTS check_amount;
DELIMITER >>
CREATE PROCEDURE check_amount ()
BEGIN
DECLARE _product_id VARCHAR(255);
DECLARE _amount INT(10) DEFAULT 0;
DECLARE _demand_amount INT(10) DEFAULT 0;
DECLARE _on_produce INT(10) DEFAULT 0;
DECLARE count INT(10) DEFAULT 0;
DECLARE n_index INT(10) DEFAULT 0;

DECLARE cur_stock CURSOR FOR SELECT product_id, amount, on_produce FROM stock;          /*查询库存表中的产品id，产品数量，生产在途数量*/
SELECT COUNT(*) FROM stock INTO count;                                                  /*查询产品种数*/

OPEN cur_stock;
loop_stock: LOOP
    SET n_index = n_index + 1;                                                          /*循环控制*/
    IF n_index > count THEN
        LEAVE loop_stock;
    END IF;
    FETCH cur_stock INTO _product_id, _amount, _on_produce;
                                                                                        /*查询订单明细中对应产品的订单数量减去已完成数量*/                
    SELECT SUM(demand_amount - finished_amount) FROM order_details WHERE product_id = _product_id INTO _demand_amount;
    IF _amount + _on_deliver < _demand_amount THEN                                      /*如果库存数+生产在途数<订单需求数 则还需要生产*/
        SELECT _product_id, _demand_amount, _amount, _on_produce;                       /*显示需要生产的产品id，需求数和生产在途数*/
    END IF;
END LOOP loop_stock;    
CLOSE cur_stock;
END; >>
DELIMITER ;
CALL check_amount();