Database Reference
In-Depth Information
Tip
Drop Only If It Exists
DROP PROCEDURE
throws an error if the named procedure
does not actually exist. To delete a procedure if it exists (and not throw an error if it
does not), use
DROP PROCEDURE IF EXISTS
.
productpricing
is a really simple stored procedure—it simply displays the
results of a
SELECT
statement. Typically stored procedures do not display
results; rather, they return them into variables that you specify.
New Term
Variable A named location in memory, used for temporary storage of data.
Here is an updated version of
productpricing
(you'll not be able to create
the stored procedure again if you did not previously drop it):
▼
Input
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;
▼
Analysis
This stored procedure accepts three parameters named
pl
to store the lowest
product price,
ph
to store the highest product price, and
pa
to store the aver-
age product price (and thus the variable names). Each parameter must have
its type specified; here a decimal value is used. The keyword
OUT
is used to
specify that this parameter is used to send a value out of the stored procedure
(back to the caller). MariaDB supports parameters of types
IN
(those passed to
stored procedures),
OUT
(those passed from stored procedures, as used here),
and
INOUT
(those used to pass parameters to and from stored procedures). The
