MySQL.PROCEDURE.使用存储过程

DELIMITER // mysql> CREATE PROCEDURE productpricing() -> BEGIN -> SELECT AVG(prod_price) AS priceaverage -> FROM products; -> END// Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL productpricing(); +--------------+ | priceaverage | +--------------+ | 16.133571 | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> DROP PROCEDURE productpricing; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE productpricing( -> OUT pl DECIMAL(8,2), -> OUT ph DECIMAL(8,2), -> OUT pa DECIMAL(8,2) -> ) -> BEGIN -> SELECT MIN(prod_price) -> INTO pl -> FROM products; -> SELECT MAX(prod_price) -> INTO ph -> FROM products; -> SELECT AVG(prod_price) -> INTO pa -> FROM products; -> END// Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL productpricing(@pricelow, @pricehigh, @priceaverage); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT @priceaverage; +---------------+ | @priceaverage | +---------------+ | 16.13 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT @pricehigh, @pricelow, @priceaverage; +------------+-----------+---------------+ | @pricehigh | @pricelow | @priceaverage | +------------+-----------+---------------+ | 55.00 | 2.50 | 16.13 | +------------+-----------+---------------+ mysql> DROP PROCEDURE productpricing; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE ordertotal( -> IN onumber INT, -> OUT ototal DECIMAL(8,2) -> ) -> BEGIN -> SELECT SUM(item_price * quantity) -> FROM orderitems -> WHERE order_num = onumber -> INTO ototal; -> END// Query OK, 0 rows affected (0.00 sec) or mysql> DELIMITER // mysql> CREATE PROCEDURE ordertotal( -> IN onumber INT, -> OUT ototal DECIMAL(8,2) -> ) -> BEGIN -> SELECT SUM(item_price * quantity) -> INTO ototal -> FROM orderitems -> WHERE order_num = onumber; -> END// Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL ordertotal(20005, @total); Query OK, 1 row affected (0.00 sec) mysql> SELECT @total; +--------+ | @total | +--------+ | 149.87 | +--------+ 1 row in set (0.00 sec) mysql> CALL ordertotal(20009, @total); Query OK, 1 row affected (0.00 sec) mysql> SELECT @total; +--------+ | @total | +--------+ | 38.47 | +--------+ mysql> DROP PROCEDURE ordertotal; Query OK, 0 rows affected (0.00 sec) mysql> -- Name; order total mysql> -- Parameters: onumber = order number mysql> -- taxable = 0 if not taxable, 1 if taxable mysql> -- ototal = order total variable mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE ordertotal( -> IN onumber INT, -> IN taxable BOOLEAN, -> OUT ototal DECIMAL(8,2) -> ) COMMENT ‘Obtain order total, optionally adding tax‘ -> BEGIN -> -- Declare variable for total -> DECLARE total DECIMAL(8,2); -> -- Declare tax percentage -> 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 -> -- Yes, so add taxrate to the total -> SELECT total + (total / 100 * taxrate) INTO total; -> END IF; -> -- And finally, save to out variable -> SELECT total INTO ototal; -> END// Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL ordertotal(20005, 0, @total); Query OK, 1 row affected (0.00 sec) mysql> SELECT @total; +--------+ | @total | +--------+ | 149.87 | +--------+ mysql> CALL ordertotal(20005, 1, @total); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT @total; +--------+ | @total | +--------+ | 158.86 | +--------+

 

SHOW CREATE PROCEDURE ordertotal;
+------------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode                                   | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                      | character_set_client | collation_connection | Database Collation |
+------------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| ordertotal | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)
    COMMENT ‘Obtain order total, optionally adding tax‘
BEGIN

DECLARE total DECIMAL(8,2);

DECLARE taxrate INT DEFAULT 6;


SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;


IF taxable THEN
   
   SELECT total + (total / 100 * taxrate) INTO total;
END IF;
   
   SELECT total INTO ototal;
END | utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+------------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)


mysql> SHOW PROCEDURE STATUS LIKE ‘ordertotal‘;
+------------+------------+-----------+----------------+---------------------+---------------------+---------------+-------------------------------------------+----------------------+----------------------+--------------------+
| Db         | Name       | Type      | Definer        | Modified            | Created             | Security_type | Comment                                   | character_set_client | collation_connection | Database Collation |
+------------+------------+-----------+----------------+---------------------+---------------------+---------------+-------------------------------------------+----------------------+----------------------+--------------------+
| learnmysql | ordertotal | PROCEDURE | root@localhost | 2015-06-09 11:42:48 | 2015-06-09 11:42:48 | DEFINER       | Obtain order total, optionally adding tax | utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+------------+------------+-----------+----------------+---------------------+---------------------+---------------+-------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

 

MySQL.PROCEDURE.使用存储过程

标签：