Databases Reference
In-Depth Information
Nonetheless, phpMyAdmin has a few features that permit a developer to create such
routines, save them, recall them to make some modifications, and delete them.
Procedures are accessed by a
CALL
statement to which we can pass parameters (more
details at
http://dev.mysql.com/doc/refman/5.5/en/call.html
). On the other
hand, functions are accessed from SQL statements (for example,
SELECT
), and are
similar to other MySQL internal functions, thus returning a value.
The
CREATE ROUTINE
and
ALTER ROUTINE
privileges are needed to be able to create,
see, and delete a stored procedure or function. The
EXECUTE
privilege is needed
to run the routine, although the privilege is normally granted automatically to the
routine's creator.
Creating a stored procedure
We will create a procedure to change the page count for a specific book, by adding a
speciic number of pages. The topic's ISBN and the number of pages to be added will
be the input parameters to this procedure. We are using the SQL query box (refer to
Chapter 11
) to enter this procedure.
Changing the delimiter
The standard SQL delimiter is the semicolon, and this character will be used
inside our procedure to delimit SQL statements. However, the
CREATE PROCEDURE
statement is by itself a SQL statement; hence, we must come up with a way to
indicate to the MySQL parser where this statement ends. The query box has a
Delimiter
input box, which contains a semicolon by default. Therefore, we change it
to another string, which, by convention, is a double slash "
//
".
Search WWH ::
Custom Search