Create Stored Procedure

• Tweet
• 

delimiter |

DROP PROCEDURE IF EXISTS CHECK_STOCKS |
CREATE PROCEDURE CHECK_STOCKS(p_order_id INT)
BEGIN
	DECLARE v_done INT DEFAULT 0;
	DECLARE v_o_p_id, v_quantity, v_stock INT;
	DECLARE v_cur1 CURSOR FOR
		SELECT opt.o_p_id , opt.o_p_quantity , p.product_stock
		FROM order_products_tmp opt , products p
		WHERE opt.order_id = p_order_id AND p.product_id = opt.product_id;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = 1;

	OPEN v_cur1;

	REPEAT
		FETCH v_cur1 INTO v_o_p_id, v_quantity, v_stock;
		IF NOT v_done THEN
			IF v_stock <= 0 THEN
				DELETE FROM order_products_tmp WHERE o_p_id = v_o_p_id LIMIT 1;
			ELSEIF v_quantity > v_stock THEN
				UPDATE order_products_tmp SET o_p_quantity = v_stock WHERE o_p_id = v_o_p_id LIMIT 1;
			END IF;
		END IF;
	UNTIL v_done END REPEAT;

	CLOSE v_cur1;
END
|

<a href="http://www.ab-d.fr/date/2010-03-17/">Source: Comment créer une procédure stockée ? ( MySQL, SQL )</a><a href="http://www.chemise-milano.fr">(chemise milano)</a>    

Tags:
• MySQL
• SQL