(mysql.info) stored-procedure-syntax

Info Catalog

(mysql.info) stored-procedure-privileges

(mysql.info) stored-procedures

(mysql.info) stored-procedure-replication-faq

 
 17.2 Stored Procedure Syntax
 ============================
 

Menu

 
* create-procedure             `CREATE PROCEDURE' and `CREATE FUNCTION' Syntax
* alter-procedure              `ALTER PROCEDURE' and `ALTER FUNCTION' Syntax
* drop-procedure               `DROP PROCEDURE' and `DROP FUNCTION' Syntax
* call                         `CALL' Statement Syntax
* begin-end                    `BEGIN ... END' Compound Statement Syntax
* declare                      `DECLARE' Statement Syntax
* variables-in-stored-procedures  Variables in Stored Routines
* conditions-and-handlers      Conditions and Handlers
* cursors                      Cursors
* flow-control-constructs      Flow Control Constructs
 
 A stored routine is either a procedure or a function. Stored routines
 are created with `CREATE PROCEDURE' and `CREATE FUNCTION' statements. A
 procedure is invoked using a `CALL' statement, and can only pass back
 values using output variables. A function can be called from inside a
 statement just like any other function (that is, by invoking the
 function's name), and can return a scalar value.  Stored routines may
 call other stored routines.
 
 As of MySQL 5.0.1, a stored procedure or function is associated with a
 particular database. This has several implications:
 
    * When the routine is invoked, an implicit `USE DB_NAME' is
      performed (and undone when the routine terminates). `USE'
      statements within stored routines are disallowed.
 
    * You can qualify routine names with the database name. This can be
      used to refer to a routine that is not in the current database.
      For example, to invoke a stored procedure `p' or function `f' that
      is associated with the `test' database, you can say `CALL
      test.p()' or `test.f()'.
 
    * When a database is dropped, all stored routines associated with it
      are dropped as well.
 
 (In MySQL 5.0.0, stored routines are global and not associated with a
 database. They inherit the default database from the caller. If a `USE
 DB_NAME' is executed within the routine, the original default database
 is restored upon routine exit.)
 
 MySQL supports the very useful extension that allows the use of regular
 `SELECT' statements (that is, without using cursors or local variables)
 inside a stored procedure. The result set of such a query is simply
 sent directly to the client.  Multiple `SELECT' statements generate
 multiple result sets, so the client must use a MySQL client library that
 supports multiple result sets. This means the client must use a client
 library from a version of MySQL at least as recent as 4.1.  The client
 should also specify the `CLIENT_MULTI_STATEMENTS' option when it
 connects. For C programs, this can be done with the
 `mysql_real_connect()' C API function (see  mysql-real-connect).
 
 The following sections describe the syntax used to create, alter, drop,
 and invoke stored procedures and functions.
 

Info Catalog

(mysql.info) stored-procedure-privileges

(mysql.info) stored-procedures

(mysql.info) stored-procedure-replication-faq