(mysql.info.gz) Stored Procedures
Info Catalog
(mysql.info.gz) Spatial extensions in MySQL
(mysql.info.gz) Top
(mysql.info.gz) Triggers
19 Stored Procedures and Functions
**********************************
Stored procedures and functions are a new feature in MySQL version 5.0.
A stored procedure is a set of SQL statements that can be stored in the
server. Once this has been done, clients don't need to keep reissuing
the individual statements but can refer to the stored procedure instead.
Some situations where stored procedures can be particularly useful:
* When multiple client applications are written in different
languages or work on different platforms, but need to perform the
same database operations.
* When security is paramount. Banks, for example, use stored
procedures for all common operations. This provides a consistent
and secure environment, and procedures can ensure that each
operation is properly logged. In such a setup, applications and
users would not get any access to the database tables directly,
but can only execute specific stored procedures.
Stored procedures can provide improved performance because less
information needs to be sent between the server and the client. The
tradeoff is that this does increase the load on the database server
system because more of the work is done on the server side and less is
done on the client (application) side. Consider this if many client
machines (such as Web servers) are serviced by only one or a few
database servers.
Stored procedures also allow you to have libraries of functions in the
database server. This is a feature shared by modern application
languages that allow such design internally, for example, by using
classes. Using these client application language features is beneficial
for the programmer even outside the scope of database use.
MySQL follows the SQL:2003 syntax for stored procedures, which is also
used by IBM's DB2.
The MySQL implementation of stored procedures is still in progress. All
syntax described in this chapter is supported and any limitations and
extensions are documented where appropriate.
Stored procedures require the `proc' table in the `mysql' database.
This table is created during the MySQL 5.0 installation procedure. If
you are upgrading to MySQL 5.0 from an earlier version, be sure to
update your grant tables to make sure that the `proc' table exists.
Upgrading-grant-tables.
Beginning with MySQL 5.0.3, the grant system has been modified to take
stored routines into account as follows:
* The `CREATE ROUTINE' is needed to create stored routines.
* The `ALTER ROUTINE' privilege is needed to alter or drop stored
routines. This privilege is granted automatically to the creator
of a routine.
* The `EXECUTE' privilege is required to execute stored routines.
However, this privilege is granted automatically to the creator of
a routine. Also, the default `SQL SECURITY' characteristic for a
routine is `DEFINER', which allows users who have access to the
database with which the routine is associated to execute the
routine.
Menu
* Stored Procedure Syntax Stored Procedure Syntax
Info Catalog
(mysql.info.gz) Spatial extensions in MySQL
(mysql.info.gz) Top
(mysql.info.gz) Triggers
automatically generated byinfo2html