CREATE PROCEDURE

Purpose

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.

By default, after a PROCEDURE is created, OceanBase Database automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine 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

Parameters

By default, a stored procedure is associated with the default database. To associate a stored procedure with a specified database, specify the database name in the format of database_name.sp_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 be used when checking access privileges during the execution of the routine.

The IF NOT EXISTS clause prevents the creation of a routine with the same name as an existing routine.

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

An IN parameter passes a value to the stored procedure. The stored procedure may modify the value, but the caller cannot see the modification when the stored procedure returns. An OUT parameter passes the value of the stored procedure back to the caller. The initial value of an OUT parameter in the stored procedure is NULL. When the stored procedure returns, the caller can see the value of the OUT parameter. An INOUT parameter is initialized by the caller and can be modified by the stored procedure. When the stored procedure returns, the caller can see any changes made to the INOUT parameter by the stored procedure.

To call a stored procedure with an OUT or INOUT parameter, use the CALL statement to pass a user-defined variable. When the stored procedure returns, you can obtain the value of the OUT or INOUT parameter. If you call the stored 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 stored procedure from a trigger, you can also pass NEW.column_name as an OUT or INOUT parameter.

The parameter type can be any valid data type. If you specify the CHARACTER SET option, you can also specify 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.

A stored routine can contain DDL statements such as CREATE and DROP. A stored procedure can contain SQL transaction statements such as COMMIT. A stored function 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 the use of 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 will be returned. If a stored procedure cannot return a result set when using these statements, an error will be 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 execution of the routine. If you want to reference other database objects outside the default database of the routine, you can specify 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 an expression is assigned to a routine in strict SQL mode, the routine must be called in strict mode.

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

The LANGUAGE characteristic specifies the language used to write the routine. This option is applicable only to SQL routines, and the server will ignore this option.

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 option is specified in the routine definition, the default is NOT DETERMINISTIC.

The following table describes the characteristics of data usage in stored routines:

• CONTAINS SQL: 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') will be executed but will not read or write data.

• NO SQL: the stored routine does not contain SQL statements.

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

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

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

The DEFINER option specifies the account to be used when checking access privileges during the execution of the routine. If the DEFINER option is specified, the user value must be an account in the format of 'user_name'@'host_name' (such as 'admin'@'oblocalhost') or an account obtained by using the CURRENT_USER() function. If the DEFINER option 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 option, you can use the CURRENT_USER function to return the DEFINER value of the stored routine.

The following example uses the SQL SECURITY INVOKER option. The stored 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 the ob.user table.

obclient> 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:

• Checks for data type mismatches and overflows. Conversions and overflows can generate warnings or errors in strict SQL mode.

• Scalar values only. 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 specified, 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 of routine creation are used. To prevent the server from using the database's character set and collation, explicitly specify the CHARACTER SET and COLLATE attributes for the parameters.

  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 defaults.

  The system variables character_set_database and collation_database can be used to specify the database character set and collation.

Examples

Example 1: Create a simple stored procedure.

obclient> CREATE TABLE employee_info(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary DECIMAL(10,2)
);

obclient> INSERT INTO employee_info VALUES 
    (1, 'Alice', 8000.00),
    (2, 'Bob', 12000.00),
    (3, 'Charlie', 9500.00);

obclient> DELIMITER //

obclient> CREATE PROCEDURE get_employee_list()
    BEGIN
        SELECT emp_id, emp_name, salary FROM employee_info ORDER BY salary DESC;
    END //

obclient> DELIMITER ;

Call the stored procedure:

obclient> CALL get_employee_list();

The query result is as follows:

+--------+----------+----------+
| emp_id | emp_name | salary   |
+--------+----------+----------+
|      2 | Bob      | 12000.00 |
|      3 | Charlie  |  9500.00 |
|      1 | Alice    |  8000.00 |
+--------+----------+----------+
3 rows in set

Example 2: Create a stored procedure with an IN parameter.

obclient> DELIMITER //

obclient> CREATE PROCEDURE get_employee_by_id(IN emp_id_param INT)
    BEGIN
        SELECT emp_id, emp_name, salary FROM employee_info WHERE emp_id = emp_id_param;
    END //

obclient> DELIMITER ;

Call the stored procedure:

obclient> CALL get_employee_by_id(1);

The query result is as follows:

+--------+----------+----------+
| emp_id | emp_name | salary   |
+--------+----------+----------+
|      1 | Alice    |  8000.00 |
+--------+----------+----------+
1 row in set

Example 3: Create a stored procedure with an OUT parameter.

obclient> DELIMITER //

obclient> CREATE PROCEDURE calculate_total_salary(OUT total_salary DECIMAL(10,2))
    BEGIN
        SELECT SUM(salary) INTO total_salary FROM employee_info;
    END //

obclient> DELIMITER ;

Call the stored procedure:

obclient> SET @total = 0;

obclient> CALL calculate_total_salary(@total);

obclient> SELECT @total;

The query result is as follows:

+----------+
| @total   |
+----------+
| 29500.00 |
+----------+
1 row in set

Example 4: Create a stored procedure with an INOUT parameter.

obclient> DELIMITER //

obclient> CREATE PROCEDURE update_salary(INOUT salary_amount DECIMAL(10,2), IN increase_rate DECIMAL(3,2))
    BEGIN
        SET salary_amount = salary_amount * (1 + increase_rate);
    END //

obclient> DELIMITER ;

Call the stored procedure:

obclient> SET @current_salary = 8000.00;

obclient> CALL update_salary(@current_salary, 0.1);

obclient> SELECT @current_salary;

The query result is as follows:

+------------------+
| @current_salary   |
+------------------+
|          8800.00 |
+------------------+
1 row in set

Example 5: Use the DEFINER and SQL SECURITY clauses.

obclient> DELIMITER //

obclient> CREATE DEFINER='admin'@'localhost' PROCEDURE process_order_data()
    SQL SECURITY INVOKER
    COMMENT 'Process order data with invoker security'
    BEGIN
        SELECT COUNT(*) as order_count FROM order_table;
    END //

obclient> DELIMITER ;

References

• Stored procedures
• ALTER PROCEDURE
• DROP PROCEDURE
• CALL
• SHOW CREATE PROCEDURE