CREATE PROCEDURE

Description

The CREATE PROCEDURE statement is used to create a stored procedure.

Privilege requirements

To use the CREATE PROCEDURE statement, you must have the CREATE PROCEDURE privilege.

Example:

After granting the CREATE ROUTINE privilege to a user, the user can create a PROCEDURE.

GRANT CREATE ROUTINE ON my.* TO mingye;
CREATE PROCEDURE p1()
    -> BEGIN
    -> SELECT 1 FROM dual;
    -> END;
    -> /

By default, after a PROCEDURE is created, seekdb automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine (stored procedure or function) creator. If the DEFINER clause is present, the required privileges depend on the value of user.

Syntax

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

By default, a stored procedure is associated with the default database. To associate a stored procedure with a specified database, use database_name.sp_name to specify the name.

To call a stored procedure, use the CALL statement. For more information, see CALL.

The DEFINER and SQL SECURITY clauses specify the security context to use when checking access privileges during routine execution.

The IF NOT EXISTS clause prevents the creation of a routine with the same name that already exists.

The proc_parameter parameter list in parentheses must always be present. If no parameters are specified, use empty parentheses () to indicate an empty parameter list. Parameter names are not case-sensitive. By default, each parameter is an IN parameter. To specify other values for the parameters of a stored procedure, use the OUT or INOUT keyword before the parameter name.

An IN parameter passes a value to a stored procedure. The stored procedure may modify this value, but the caller does not see the modification when the stored procedure returns. An OUT parameter passes a value from the stored procedure back to the caller. The initial value of an OUT parameter within the stored procedure is NULL, and the caller can see the value of the OUT parameter when the procedure returns. An INOUT parameter is initialized by the caller and can be modified by the procedure. Any changes made to an INOUT parameter by the stored procedure are visible to the caller when the procedure returns.

To pass a user-defined variable to a stored procedure, use the CALL statement for each OUT or INOUT parameter, so that you can obtain the value when the procedure returns. If you call the procedure from another stored procedure or function, you can also pass routine parameters or local routine variables as OUT or INOUT parameters. If you call the procedure from a trigger, you can also pass NEW.column_name as an OUT or INOUT parameter.

You can declare the parameter type as any valid data type. If you specify CHARACTER SET, you can also use the COLLATE attribute.

The routine_body consists of valid SQL statements. An SQL statement can be a simple statement, such as SELECT or INSERT, or a compound statement written using BEGIN and END. A compound statement can contain declarations, loops, and other control structure statements.

Stored routines can contain DDL statements such as CREATE and DROP. Stored procedures can contain SQL transaction statements such as COMMIT. Stored functions cannot contain statements that explicitly or implicitly commit or roll back transactions. SQL standards do not require support for these statements, and each DBMS vendor can decide whether to allow these statements.

Statements that return result sets can be used in stored procedures, but not in stored functions. This includes SELECT statements without the INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. If a stored function uses these statements, an error is returned. If a stored procedure cannot return a result set when using these statements, an error is also returned.

The USE statement cannot be used in stored routines. When a routine is called, the USE database_name statement is implicitly executed (and revoked when the routine terminates), specifying the default database for the routine. If you need to reference objects in a database other than the default database of the routine, you can qualify the object name with the database name.

If a routine is defined in strict SQL mode but is called in non-strict mode, the parameters of the routine are not assigned values in strict mode. If expressions are assigned to the parameters of a routine in strict SQL mode, the routine must be called in strict mode.

The COMMENT characteristic can be used to describe stored routines. You can display comment information using the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.

The LANGUAGE characteristic specifies the language used to write the routine. This characteristic is applicable only to SQL routines. The server ignores this characteristic for other types of routines.

A routine is considered "deterministic" if it always produces the same result for the same input parameters. Otherwise, it is considered "non-deterministic". If neither the DETERMINISTIC nor the NOT DETERMINISTIC characteristic is specified in the routine definition, the default is NOT DETERMINISTIC.

The following table describes the characteristics of stored routines in terms of how they use data:

• CONTAINS SQL indicates that the stored routine does not contain statements that read or write data. This is the default value. For example, SET@x=1 or DO RELEASE_LOCK('abc') are executed but do not read or write data.

• NO SQL indicates that the stored routine does not contain SQL statements.

• READS SQL DATA indicates that the stored routine contains statements that read data (such as SELECT) but do not write data.

• MODIFIES SQL DATA indicates that the stored routine contains statements that may write data (such as INSERT or DELETE).

The SQL SECURITY clause can be DEFINER or INVOKER, specifying whether the routine is executed using the privileges of the specified account (which must have access to the database associated with the routine) or the privileges of the caller. The default value is DEFINER. The user calling the stored routine must have the EXECUTE privilege on the routine.

The DEFINER clause specifies the account used to check access privileges when the routine is executed. If the DEFINER clause is present, the user value must be an account specified as 'user_name'@'host_name' (such as 'admin'@'oblocalhost') or an account obtained using the CURRENT_USER() function. If the DEFINER clause is omitted, the default definer is the user who executed the CREATE PROCEDURE statement. In the body of a stored routine defined with the SQL SECURITY DEFINER characteristic, you can use the CURRENT_USER function to return the DEFINER value of the stored routine.

The following example uses the SQL SECURITY INVOKER characteristic. However, the procedure has a DEFINER of 'admin'@'localhost'. In this case, the stored procedure is executed using the privileges of the caller. Whether the execution is successful depends on whether the caller has the EXECUTE privilege and the SELECT privilege on ob.user.

CREATE DEFINER = 'admin'@'oblocalhost' PROCEDURE account_num()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM ob.users;
END;

The server handles the data types of routine parameters, local routine variables created using DECLARE, and function return values as follows:

• It checks for data type mismatches and overflows. Conversion and overflow issues can generate warnings or errors in strict SQL mode.

• Only scalar values can be specified. For example, the SET val=(SELECT 1, 2) statement is invalid.

• For character data types, if the declaration includes CHARACTER SET, the specified character set and its default collation are used. If the COLLATE attribute is used, the specified collation is used instead of the default collation.

  If neither CHARACTER SET nor COLLATE is specified, the character set and collation of the database at the time the routine was created are used. To prevent the server from using the database's character set and collation, explicitly specify the CHARACTER SET and COLLATE characteristics for the parameter.

  If you want to change the default character set or collation of the database, you must drop and recreate the stored routine to apply the new database default values.

  You can use the character_set_database and collation_database system variables to specify the database character set and collation.

For detailed examples of creating stored procedures, see Stored procedures.