(jlive)[crashcourse]>CALL productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.00 sec)
不带参数的PROCEDURE直接CALL
删除PROCEDURE
(jlive)[crashcourse]>DROP PROCEDURE IF EXISTS productpricing;
Query OK, 0 rows affected (0.00 sec)
创建带参数的PROCEDURE
DELIMITER //
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;//
DELIMITER ;
(jlive)[crashcourse]>CALL productpricing(@pricelow, @pricehigh, @priceaverage);
Query OK, 1 row affected, 1 warning (0.00 sec)
(jlive)[crashcourse]>SELECT @pricehigh, @pricelow, @priceaverage;
+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
| 55.00 | 2.50 | 16.13 |
+------------+-----------+---------------+
1 row in set (0.00 sec)
(jlive)[crashcourse]>CALL productpricing(@Min, @Max, @Avg);
Query OK, 1 row affected, 1 warning (0.00 sec)
(jlive)[crashcourse]>SELECT @Avg AS Average, @Min Minium, @Max AS Maxium;
+---------+--------+--------+
| Average | Minium | Maxium |
+---------+--------+--------+
| 16.13 | 2.50 | 55.00 |
+---------+--------+--------+
1 row in set (0.00 sec)
productpricing()的三个参数依次为pl,ph,pa。 其值是BEGIN,END间的查询语句所查得,CALL的时候,名字随便取,但值的顺序是固定的。
参数中带有IN,OUT
DELIMITER //
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;//
DELIMITER ;
(jlive)[crashcourse]>CALL ordertotal(20005, @sum);
Query OK, 1 row affected (0.01 sec)
(jlive)[crashcourse]>SELECT @sum AS Total;
+--------+
| Total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
传入的形参为onumber这个IN变量,当去CALL的时候传入的值为20005,也就是过滤出orderitems表中order_num等于20005的总销售额,并将总销售额的值赋给形参ototal这个OUT变量
代条件的PROCEDURE
DELIMITER //
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
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;//
DELIMITER ;
说明:COMMENT ‘Obtain order total, optionally adding tax’,在MariaDB-10.1.10里报语法错误,这里就直接注释了
onumber,taxable是两个形参变量,最终显示的形参为ototal
当taxable = 0时,此时不没税率,那么total = 149.87 ==> ototal
(jlive)[crashcourse]>CALL ordertotal(20005, 0, @total);
Query OK, 1 row affected (0.00 sec)
(jlive)[crashcourse]>SELECT @total AS Total;
+--------+
| Total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
当taxable = 1时,total = 149.87(149.87/100*6)
(jlive)[crashcourse]>CALL ordertotal(20005, 1, @total);
Query OK, 1 row affected, 1 warning (0.00 sec)
(jlive)[crashcourse]>SELECT @total AS Total;
+--------+
| Total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)
查看PROCEDURE状态,支持通配符
(jlive)[crashcourse]>SHOW PROCEDURE STATUS LIKE '%'\G
*************************** 1. row ***************************
Db: crashcourse
Name: ordertotal
Type: PROCEDURE
Definer: root@localhost
Modified: 2016-03-20 20:04:34
Created: 2016-03-20 20:04:34
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: crashcourse
Name: productpricing
Type: PROCEDURE
Definer: root@localhost
Modified: 2016-03-20 19:59:20
Created: 2016-03-20 19:59:20
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
(jlive)[crashcourse]>SHOW CREATE PROCEDURE ordertotal\G
*************************** 1. row ***************************
Procedure: ordertotal
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` 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
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)