CREATE PROCEDURE

You can use the CREATE PROCEDURE statement to create a stored procedure.

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, stored procedures are associated with the default database. To associate a stored procedure with a specified database, use database_name.sp_name to specify the database name.

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

To use the CREATE PROCEDURE statement, you must have the CREATE PROCEDURE privilege. By default, OceanBase Database automatically grants the ALTER ROUTINE and EXECUTE privileges to the creator of a stored routine such as a stored procedure or a stored function. If the DEFINER clause is used, the required privileges depend on the value of user.

The DEFINER and SQL SECURITY clauses specify the security context used for checking the access privileges when the routine is executed.

IF NOT EXISTS avoids creating a routine that already exists.

The proc_parameter parameter list must always exist in the parentheses. If no parameter exists, use empty parentheses () to indicate an empty parameter list. The parameter names are case-insensitive. By default, each parameter is an IN parameter. To specify another value for a parameter of a stored procedure, use the OUT or INOUT keyword prior to the parameter name.

The IN parameter passes a value to a stored procedure. The stored procedure may change this value. However, when the stored procedure returns a value, the change is invisible to the invoker. The OUT parameter passes a value in the stored procedure back to the invoker. The initial value of the OUT parameter in the stored procedure is NULL. When the stored procedure returns a value, the value of the OUT parameter is visible to the invoker. The INOUT parameter is initialized by the invoker and can be modified by the stored procedure. When the stored procedure returns a value, any changes made by the stored procedure are visible to the invoker.

For each OUT or INOUT parameter, the CALL statement is used to pass a user-defined variable to call the procedure. In this way, a value returned by the procedure can be obtained. If a stored procedure has been called from another stored procedure or function, you can pass a routine parameter or local routine variable as an OUT or INOUT parameter. If the procedure is called from a trigger, NEW.column_name can be passed as an OUT or INOUT parameter.

The parameter type can be declared as any valid data type. If the parameter type is preceded by CHARACTER SET, the COLLATE attribute can be used.

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

A stored routine can contain DDL statements such as CREATE and DROP. A stored procedure can contain SQL statements such as COMMIT. A stored function cannot contain statements for explicitly or implicitly committing or rolling back transactions. The SQL standard does not require support for such statements. The standard stipulates that each DBMS vendor can decide whether to support the use of these statements.

Statements that return a result set can be used in stored procedures but not in stored functions, including the SELECT statement and other statements without the INTO var_list clause, the SHOW statement, the EXPLAIN statement, and the CHECK TABLE statement. If any of these statements is used in a stored function, an error is returned. If a stored procedure that uses these statements cannot return a result set, 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 to specify the default database for executing the routine. This statement will be revoked when the routine ends. To reference objects in a database other than the default database of the routine, you can use the database name.

If you define a routine in strict SQL mode but call it in non-strict mode, values will not be assigned to parameters of the routine in strict mode. If an expression passed to the routine is assigned in strict SQL mode, the routine must be called in strict mode.

The COMMENT attribute can be used to describe a stored routine. You can execute the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement to show the comments.

The LANGUAGE attribute indicates the language used to write the routine. This attribute applies only to SQL routines and is ignored by servers.

A routine is considered deterministic if it always generates the same result for the same input parameters, and indeterministic if otherwise. If the routine definition does not specify the DETERMINISTIC or NOT DETERMINISTIC keyword, the default value is NOT DETERMINISTIC.

The following characteristics provide information about how the routine uses data:

• CONTAINS SQL indicates that the stored routine does not contain data read/write statements. This is the default value. For example, SET@x=1 or DO RELEASE_LOCK('abc') will be executed but it does 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 data read statements such as SELECT but does not contain data write statements.

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

SQL SECURITY can be set to DEFINER or INVOKER to specify whether the stored routine is executed by using the privileges of the specified account that has the privileges to access the database associated with the routine, or by using the privileges of the invoker. The default value is DEFINER. To call a stored routine, you must have the EXECUTE privilege on the routine.

The DEFINER clause specifies the account used to check the access privileges on the routine when the routine is executed. If the DEFINER clause exists, you must set user to an account in the 'user_name'@'host_name' format, such as 'admin'@'oblocalhost', or to the account obtained by using the CURRENT_USER() function. By default, if the DEFINER clause is omitted, the definer is the user who executes the CREATE PROCEDURE statement. In the body of a stored routine that is defined by using SQL SECURITY DEFINER, you can use the CURRENT_USER function to return the DEFINER value of the stored routine.

In the following example, SQL SECURITY INVOKER is used. The stored procedure has a DEFINER of 'admin'@'localhost'. In this case, the stored procedure is executed by using the privileges of the invoker. The execution result depends on whether the invoker 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;

A server processes the data types of routine parameters, local variables created by using the DECLARE statement, and return values of functions as follows:

• Check for data type mismatch and overflow. Conversion and overflow will cause warnings or errors in strict SQL mode.

• Specify only scalar values. For example, the SET val=(SELECT 1, 2) statement is invalid.

• Use the specified character set and its default collation for the character data types if the declaration contains CHARACTER SET. If the COLLATE attribute is used, use the specified collation instead of the default collation.

  If CHARACTER SET and COLLATE are not used, use the character set and collation of the database specified when the routine was created. To prevent the server from using the character set and collation of the database, explicitly specify the CHARACTER SET and COLLATE attributes.

  To change the default database character set or collation, delete the stored routine and create one again to apply the new default values of the database.

  The character_set_database and collation_database system variables specify the character set and collation for the database.

For examples of creating stored procedures, see Stored procedures.