(mysql.info) create-procedure

(mysql.info) stored-procedure-syntax
 
 17.2.1 `CREATE PROCEDURE' and `CREATE FUNCTION' Syntax
 ------------------------------------------------------
 
      CREATE PROCEDURE SP_NAME ([PROC_PARAMETER[,...]])
          [CHARACTERISTIC ...] ROUTINE_BODY
 
      CREATE FUNCTION SP_NAME ([FUNC_PARAMETER[,...]])
          RETURNS TYPE
          [CHARACTERISTIC ...] ROUTINE_BODY
 
      PROC_PARAMETER:
          [ IN | OUT | INOUT ] PARAM_NAME TYPE
 
      FUNC_PARAMETER:
          PARAM_NAME TYPE
 
      TYPE:
          ANY VALID MYSQL DATA TYPE
 
      CHARACTERISTIC:
          LANGUAGE SQL
        | [NOT] DETERMINISTIC
        | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
        | SQL SECURITY { DEFINER | INVOKER }
        | COMMENT 'STRING'
 
      ROUTINE_BODY:
          VALID SQL PROCEDURE STATEMENT
 
 These statements create stored routines. As of MySQL 5.0.3, to use
 them, it is necessary to have the `CREATE ROUTINE' privilege. If binary
 logging is enabled, these statements might may also require the `SUPER'
 privilege, as described in  stored-procedure-logging. MySQL
 automatically grants the `ALTER ROUTINE' and `EXECUTE' privileges to
 the routine creator.
 
 By default, the routine is associated with the default database.  To
 associate the routine explicitly with a given database, specify the
 name as DB_NAME.SP_NAME when you create it.
 
 If the routine name is the same as the name of a built-in SQL function,
 you must use a space between the name and the following parenthesis
 when defining the routine, or a syntax error occurs. This is also true
 when you invoke the routine later. For this reason, we suggest that it
 is better to avoid re-using the names of existing SQL functions for
 your own stored routines.
 
 The `IGNORE_SPACE' SQL mode applies to built-in functions, not to
 stored routines. it is always allowable to have spaces after a routine
 name, regardless of whether `IGNORE_SPACE' is enabled.
 
 The parameter list enclosed within parentheses must always be present.
 If there are no parameters, an empty parameter list of `()' should be
 used. Each parameter is an `IN' parameter by default. To specify
 otherwise for a parameter, use the keyword `OUT' or `INOUT' before the
 parameter name.
 
 * Specifying a parameter as `IN', `OUT', or `INOUT' is valid only
 for a `PROCEDURE'. (`FUNCTION' parameters are always regarded as `IN'
 parameters.)
 
 Each parameter can be declared to use any valid data type, except that
 the `COLLATE' attribute cannot be used.
 
 The `RETURNS' clause may be specified only for a `FUNCTION', for which
 it is mandatory. It indicates the return type of the function, and the
 function body must contain a `RETURN VALUE' statement.
 
 The ROUTINE_BODY consists of a valid SQL procedure statement. This can
 be a simple statement such as `SELECT' or `INSERT', or it can be a
 compound statement written using `BEGIN' and `END'. Compound statement
 syntax is described in  begin-end.  Compound statements can
 contain declarations, loops, and other control structure statements.
 The syntax for these statements is described later in this chapter.
 See, for example,  declare, and  flow-control-constructs.
 Some statements are not allowed in stored routines; see 
 routine-restrictions.
 
 The `CREATE FUNCTION' statement was used in earlier versions of MySQL
 to support UDFs (user-defined functions). See  adding-functions.
 UDFs continue to be supported, even with the existence of stored
 functions. A UDF can be regarded as an external stored function.
 However, do note that stored functions share their namespace with UDFs.
 
 A procedure or function is considered `deterministic' if it always
 produces the same result for the same input parameters, and `not
 deterministic' otherwise. If neither `DETERMINISTIC' nor `NOT
 DETERMINISTIC' is given in the routine definition, the default is `NOT
 DETERMINISTIC'.
 
 For replication purposes, use of the `NOW()' function (or its synonyms)
 or `RAND()' does not necessarily make a routine non-deterministic. For
 `NOW()', the binary log includes the timestamp and replicates
 correctly. `RAND()' also replicates correctly as long as it is invoked
 only once within a routine. (You can consider the routine execution
 timestamp and random number seed as implicit inputs that are identical
 on the master and slave.)
 
 Currently, the `DETERMINISTIC' characteristic is accepted, but not yet
 used by the optimizer. However, if binary logging is enabled, this
 characteristic affects which routine definitions MySQL accepts. See
  stored-procedure-logging.
 
 Several characteristics provide information about the nature of data
 use by the routine. `CONTAINS SQL' indicates that the routine does not
 contain statements that read or write data. `NO SQL' indicates that the
 routine contains no SQL statements. `READS SQL DATA' indicates that the
 routine contains statements that read data, but not statements that
 write data.  `MODIFIES SQL DATA' indicates that the routine contains
 statements that may write data. `CONTAINS SQL' is the default if none
 of these characteristics is given explicitly. These characteristics are
 advisory only. The server does not use them to constrain what kinds of
 statements a routine will be allowed to execute.
 
 The `SQL SECURITY' characteristic can be used to specify whether the
 routine should be executed using the permissions of the user who
 creates the routine or the user who invokes it. The default value is
 `DEFINER'.  This feature is new in SQL:2003. The creator or invoker must
 have permission to access the database with which the routine is
 associated. As of MySQL 5.0.3, it is necessary to have the `EXECUTE'
 privilege to be able to execute the routine. The user that must have
 this privilege is either the definer or invoker, depending on how the
 `SQL SECURITY' characteristic is set.
 
 MySQL stores the `sql_mode' system variable setting that is in effect
 at the time a routine is created, and always executes the routine with
 this setting in force.
 
 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.
 
 As of MySQL 5.0.18, the server uses the data type of a routine
 parameter or function return value as follows. These rules also apply
 to local routine variables created with the `DECLARE' statement (
 declare-local-variables).
 
    * Assignments are checked for data type mismatches and overflow.
      Conversion and overflow problems result in warnings, or errors in
      strict mode.
 
    * For character data types, if there is a `CHARACTER SET' clause in
      the declaration, the specified character set and its default
      collation are used. If there is no such clause, the database
      character set and collation are used. (These are given by the
      values of the `character_set_database' and `collation_database'
      system variables.)
 
    * Only scalar values can be assigned to parameters or variables. For
      example, a statement such as `SET x = (SELECT 1, 2)' is invalid.
 
 Before MySQL 5.0.18, parameters, return values, and local variables are
 treated as items in expressions, and are subject to automatic (silent)
 conversion and truncation. Stored functions ignore the `sql_mode'
 setting.
 
 The `COMMENT' clause is a MySQL extension, and may be used to describe
 the stored routine. This information is displayed by the `SHOW CREATE
 PROCEDURE' and `SHOW CREATE FUNCTION' statements.
 
 MySQL allows routines to contain DDL statements, such as `CREATE' and
 `DROP'. MySQL also allows stored procedures (but not stored functions)
 to contain SQL transaction statements such as `COMMIT'. Stored
 functions may not contain statements that do explicit or implicit
 commit or rollback.  Support for these statements is not required by
 the SQL standard, which states that each DBMS vendor may decide whether
 to allow them.
 
 Stored routines cannot use `LOAD DATA INFILE'.
 
 Statements that return a result set cannot be used within a stored
 function. This includes `SELECT' statements that do not use `INTO' to
 fetch column values into variables, `SHOW' statements, and other
 statements such as `EXPLAIN'. For statements that can be determined at
 function definition time to return a result set, a `Not allowed to
 return a result set from a function' error occurs
 (`ER_SP_NO_RETSET_IN_FUNC'). For statements that can be determined only
 at runtime to return a result set, a `PROCEDURE %s can't return a
 result set in the given context' error occurs (`ER_SP_BADSELECT').
 
 * Before MySQL 5.0.10, stored functions created with `CREATE
 FUNCTION' must not contain references to tables, with limited
 exceptions.  They may include some `SET' statements that contain table
 references, for example `SET a:= (SELECT MAX(id) FROM t)', and `SELECT'
 statements that fetch values directly into variables, for example
 `SELECT i INTO var1 FROM t'.
 
 The following is an example of a simple stored procedure that uses an
 `OUT' parameter. The example uses the `mysql' client `delimiter'
 command to change the statement delimiter from `;' to `//' while the
 procedure is being defined. This allows the `;' delimiter used in the
 procedure body to be passed through to the server rather than being
 interpreted by `mysql' itself.
 
      mysql> delimiter //
 
      mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
          -> BEGIN
          ->   SELECT COUNT(*) INTO param1 FROM t;
          -> END;
          -> //
      Query OK, 0 rows affected (0.00 sec)
 
      mysql> delimiter ;
 
      mysql> CALL simpleproc(@a);
      Query OK, 0 rows affected (0.00 sec)
 
      mysql> SELECT @a;
      +------+
      | @a   |
      +------+
      | 3    |
      +------+
      1 row in set (0.00 sec)
 
 When using the `delimiter' command, you should avoid the use of the
 backslash (‘`\'’) character because that is the escape character
 for MySQL.
 
 The following is an example of a function that takes a parameter,
 performs an operation using an SQL function, and returns the result. In
 this case, it is unnecessary to use `delimiter' because the function
 definition contains no internal `;' statement delimiters:
 
      mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
          -> RETURN CONCAT('Hello, ',s,'!');
      Query OK, 0 rows affected (0.00 sec)
 
      mysql> SELECT hello('world');
      +----------------+
      | hello('world') |
      +----------------+
      | Hello, world!  |
      +----------------+
      1 row in set (0.00 sec)
 
 A stored function returns a value of the data type specified in its
 `RETURNS' clause. If the `RETURN' statement returns a value of a
 different type, the value is coerced to the proper type. For example,
 if a function returns an `ENUM' or `SET' value, but the `RETURN'
 statement returns an integer, the value returned from the function is
 the string for the corresponding `ENUM' member of set of `SET' members.
Info Catalog
(mysql.info) stored-procedure-syntax
(mysql.info) alter-procedure
automatically generated byinfo2html