MariaDB Crash Course - Ben Forta [72]
Input
SELECT @priceaverage;
Output
+---------------+
| @priceaverage |
+---------------+
| 16.133571428 |
+---------------+
To obtain all three values, you can use the following:
Input
SELECT @pricehigh, @pricelow, @priceaverage;
Output
+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
| 55.00 | 2.50 | 16.133571428 |
+------------+-----------+---------------+
Here is another example, this time using both IN and OUT parameters. ordertotal accepts an order number and returns the total for that order:
Input
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;
Analysis
onumber is defined as IN because the order number is passed in to the stored procedure. ototal is defined as OUT because the total is to be returned from the stored procedure. The SELECT statement uses both of these parameters, the WHERE clause uses onumber to select the right rows, and INTO uses ototal to store the calculated total.
To invoke this new stored procedure you can use the following:
Input
CALL ordertotal(20005, @total);
Analysis
Two parameters must be passed to ordertotal; the first is the order number and the second is the name of the variable that will contain the calculated total.
To display the total you can then do the following:
Input
SELECT @total;
Output
+--------+
| @total |
+--------+
| 149.87 |
+--------+
Analysis
@total has already been populated by the CALL statement to ordertotal, and SELECT displays the value it contains.
To obtain a display for the total of another order, you would need to call the stored procedure again, and then redisplay the variable:
Input
CALL ordertotal(20009, @total);
SELECT @total;
Building Intelligent Stored Procedures
All the stored procedures used thus far have basically encapsulated simple MariaDB SELECT statements. And while they are all valid examples of stored procedures, they really don’t do anything more than what you could do with those statements directly (if anything, they just make things a little more complex). The real power of stored procedures is realized when business rules and intelligent processing are included within them.
Consider this scenario. You need to obtain order totals as before, but also need to add sales tax to the total, but only for some customers (perhaps the ones in your own state). Now you need to do several things:
• Obtain the total (as before).
• Conditionally add tax to the total.
• Return the total (with or without tax).
That’s a perfect job for a stored procedure:
Input
-- 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;
Analysis
The stored procedure has changed dramatically. First of all, comments were added throughout (preceded by -- ). This is important as stored procedures increase in complexity. An additional parameter was added—taxable is a BOOLEAN (specify true if taxable, false if not). Within the stored procedure body, two local variables are defined using DECLARE statements. DECLARE requires that a variable name and a datatype be specified, and also supports optional default values (taxrate in this example is set to 6%). The SELECT has changed so the result is stored in total (the local