第23章 使用存储过程
DELIMITER ;; CREATE PROCEDURE ordertotal( IN onumber INT, IN taxable DECIMAL(8,2), OUT ototal DECIMAL(8,2) ) COMMENT 'Obtain order total, optionally adding tax' BEGIN -- Declare variable for total DECLARE total DECIMAL(8,2); -- Declare tax percantage DECLARE taxrate INT DEFAULT 6; -- Get the order total SELECT SUM(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total; -- Is this taxable? IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF; SELECT total INTO ototal; END;; DELIMITER ; CALL ordertotal(20005, 1, @total); SELECT @total;
第24章
DELIMITER ;; CREATE PROCEDURE processorders() BEGIN -- Declare local variables DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); -- Declare the cursor DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; -- Declare continue handler DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; -- Create a table to store the results CREATE TABLE IF NOT EXISTS ordertotals( order_num INT, total DECIMAL(8,2) ); -- Open the cursor OPEN ordernumbers; -- Loop through all rows REPEAT -- Get order number FETCH ordernumbers INTO o; -- Get the total for this order CALL ordertotal(o, 1, t); -- Insert order and total into ordertotals INSERT INTO ordertotals(order_num, total) VALUES(o, t); UNTIL done END REPEAT; -- Close the cursor CLOSE ordernumbers; END;; DELIMITER ; CALL processorders(); SELECT * FROM ordertotals;