MariaDB PROCEDURE
(jlive)[crashcourse]>CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|
+--------------+
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 |
+------------+-----------+---------------+
|
+------------+-----------+---------------+
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 |
+---------+--------+--------+
|
+---------+--------+--------+
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)
) --
BEGIN
-- Declare variable for total
-- Get the order total
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
-- Is this taxable?
-- Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO
total;
END;//
DELIMITER ;
说明:COMMENT
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 ***************************
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
*************************** 2. row ***************************
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
2 rows in set (0.00 sec)
(jlive)[crashcourse]>SHOW CREATE PROCEDURE ordertotal\G
*************************** 1. row ***************************
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
1 row in set (0.00 sec)