MariaDB PROCEDURE

MariaDB PROCEDURE

创建不带参数的PROCEDURE

DELIMITER //

CREATE PROCEDURE productpricing() 

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products; 

END;//

DELIMITER ;

(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)