Database Reference
In-Depth Information
Here,
DELIMITER //
tells the command line utility to use
//
as the new end of
statement delimiter, and you will notice that the
END
that closes the stored proce-
dure is defined as
END //
instead of the expected
END;
. This way the
;
within the
stored procedure body remains intact and is correctly passed to the database engine.
And then, to restore things back to how they were initially, the statement closes with a
DELIMITER ;
.
Any character may be used as the delimiter except for
\
.
If you are using the
mysql
command line utility, keep this in mind as you work through
this chapter.
So how would you use this stored procedure? Like this:
▼
Input
CALL productpricing();
▼
Output
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
▼
Analysis
CALL productpricing();
executes the just created stored procedure and
displays the returned result. As a stored procedure is actually a type of func-
tion,
()
characters are required after the stored procedure name (even when no
parameters are being passed).
After they are created, stored procedures remain on the server, ready for use,
until dropped. The drop command (similar to the statement seen Chapter 21,
“Creating and Manipulating Tables”) removes the stored procedure from the
server.
To remove the stored procedure we just created, use the following statement:
▼
Input
DROP PROCEDURE productpricing;
▼
Analysis
This removes the just-created stored procedure. Notice that the trailing
()
is
not used; here just the stored procedure name is specified.
