Database Reference
In-Depth Information
As already explained, writing a stored procedure is not trivial. To give you a
taste for what is involved, let's look at a simple example—a stored procedure
that returns the average product price. Here is the code:
▼
Input
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
▼
Analysis
The stored procedure is named
productpricing
and is thus defined with the
statement
CREATE PROCEDURE productpricing()
. Had the stored pro-
cedure accepted parameters, these would have been enumerated between the
(
and
)
. This stored procuedure has no parameters, but the trailing
()
is still
required.
BEGIN
and
END
statements are used to delimit the stored procedure
body, and the body itself is just a simple
SELECT
statement (using the
Avg()
function learned back in Chapter 12, “Summarizing Data”).
When MariaDB processes this code it creates a new stored procedure named
productpricing
. No data is returned because the code does not call the
stored procedure, it simply creates it for future use.
Note
mysql
Command Line Client Delimiters If you are using the
mysql
command line
utility, pay careful attention to this note.
The default MariaDB statement delimiter is
;
(as you have seen in all the SQL state-
ments used thus far). However, the
mysql
command line utility also uses
;
as a delim-
iter. If the command line utility were to interpret the
;
characters inside of the stored
procedure itself, those would not end up becoming part of the stored procedure, and
that would make the SQL in the stored procedure syntactically invalid.
The solution is to temporarily change the command line utility delimiter, as seen here:
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;
