(mysql.info.gz) CREATE PROCEDURE

(mysql.info.gz) Maintaining Stored Procedures
 
 19.1.1.1 `CREATE PROCEDURE' and `CREATE FUNCTION'
 .................................................
 
      CREATE PROCEDURE SP_NAME ([PARAMETER[,...]])
          [CHARACTERISTIC ...] ROUTINE_BODY
 
      CREATE FUNCTION SP_NAME ([PARAMETER[,...]])
          RETURNS TYPE
          [CHARACTERISTIC ...] ROUTINE_BODY
 
      PARAMETER:
          [ IN | OUT | INOUT ] PARAM_NAME TYPE
 
      TYPE:
          ANY VALID MYSQL DATA TYPE
 
      CHARACTERISTIC:
          LANGUAGE SQL
        | [NOT] DETERMINISTIC
        | SQL SECURITY {DEFINER | INVOKER}
        | COMMENT 'STRING'
 
      ROUTINE_BODY:
          VALID SQL PROCEDURE STATEMENTS OR STATEMENTS
 
 These statements create a stored routine. As of MySQL 5.0.3, to create a
 routine, it is necessary to have the `CREATE ROUTINE' privilege for it,
 and the `ALTER ROUTINE' and `EXECUTE' privileges are granted
 automatically to its creator.
 
 By default, the routine is associated with the current 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 will need to use a space between the name and the following
 parenthesis when defining the routine, or a syntax error will occur.
 This is also true when you invoke the routine later.
 
 The `RETURNS' clause may be specified only for a `FUNCTION', for which
 it is mandatory.  It is used to indicate the return type of the
 function, and the function body must contain a `RETURN value' statement.
 
 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 `IN', `OUT', or `INOUT' is only valid for a
 `PROCEDURE'.
 
 The `CREATE FUNCTION' statement is used in earlier versions of MySQL to
 support UDFs (User Defined Functions).   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 framework for external stored procedures will be introduced in the
 near future. This will allow you to write stored procedures in
 languages other than SQL.  Most likely, one of the first languages to
 be supported will be PHP because the core PHP engine is small,
 thread-safe, and can easily be embedded. Because the framework will be
 public, it is expected that many other languages will also be supported.
 
 A function is considered "deterministic" if it always returns the same
 result for the same input parameters, and "not deterministic" otherwise.
 Currently, the `DETERMINISTIC' characteristic is accepted, but not yet
 used by the optimizer.
 
 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 will always execute the routine
 with this setting in force.
 
 The `COMMENT' clause is a MySQL extension, and may be used to describe
 the stored procedure. 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') and SQL transaction statements (such as `COMMIT'). This is not
 required by the standard and therefore is implementation-specific.
 
 * Currently, stored functions created with `CREATE FUNCTION' may
 not contain references to tables.  Please note that this includes some
 `SET' statements, but excludes some `SELECT' statements.  This
 limitation will be lifted as soon as possible.
 
 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)
 
 The following is an example of a function that takes a parameter,
 performs an operation using an SQL function, and returns the result:
 
      mysql> delimiter //
 
      mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
          -> RETURN CONCAT('Hello, ',s,'!');
          -> //
      Query OK, 0 rows affected (0.00 sec)
 
      mysql> delimiter ;
 
      mysql> SELECT hello('world');
      +----------------+
      | hello('world') |
      +----------------+
      | Hello, world!  |
      +----------------+
      1 row in set (0.00 sec)
Info Catalog
(mysql.info.gz) Maintaining Stored Procedures
(mysql.info.gz) ALTER PROCEDURE
automatically generated byinfo2html