(mysql.info.gz) Stored Procedures

(mysql.info.gz) Spatial extensions in MySQL
 
 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.
(mysql.info.gz) Stored Procedures

Menu
