Routines: Stored Procedures and
Functions
It took a while before phpMyAdmin started to include support for these features.
The reason is that stored procedures and functions are blocks of code (like a
subprogram) that are kept as part of the database and phpMyAdmin, being a web
interface, is more oriented towards operations that are quickly done with a mouse.
Nonetheless, in version 2.11, there are 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,
whereas functions are accessed from SQL statements (for example a SELECT) like
other MySQL internal functions 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, but it is normally granted
automatically to its creator.
Chapter 18
[ 271 ]
Creating a Stored Procedure
We'll create a procedure that has the effect to change the page count for a specific
book, adding a specific number of pages. The book's ISBN, and number of pages to
add, will be the input parameters to 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.
Pages:
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248