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