Database Reference
In-Depth Information
▼
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
