Purpose
The CREATE PROCEDURE statement is used to create a stored procedure.
Privilege requirements
The CREATE PROCEDURE statement requires the CREATE PROCEDURE privilege.
By default, after a PROCEDURE is created, OceanBase AI Database automatically grants the ALTER ROUTINE and EXECUTE privileges to the creator of the routine (stored procedure or function). If a DEFINER clause is present, the required privileges depend on the value of the user clause.
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
Parameter |
Description |
|---|---|
| DEFINER | Specifies the definer (creator) of the stored procedure. If omitted, the default is the executor. CREATE PROCEDURE The user who executed the statement. You can specify as 'user_name'@'host_name' or CURRENT_USER. |
| IF NOT EXISTS | If the stored procedure already exists, no error is returned. |
| sp_name | The name of the stored procedure to be created. You can use database_name.sp_name Specify the database. |
| proc_parameter | List of stored procedure parameters. The parameter format is [ IN \| OUT \| INOUT ] param_name type. By default, each parameter is an IN parameter. |
| characteristic | Storage procedure features, which can include: - COMMENT 'string': comments on stored procedures- LANGUAGE SQL: The specified language (only for SQL statements. This parameter can be omitted).- [NOT] DETERMINISTIC: Specifies whether the stored procedure is deterministic.- CONTAINS SQL \| NO SQL \| READS SQL DATA \| MODIFIES SQL DATA: specifies the data access characteristics of the stored procedure.- SQL SECURITY { DEFINER \| INVOKER }: Specifies the execution privilege mode. |
| routine_body | The body of the stored procedure, which contains its specific implementation logic. |
By default, a stored procedure is associated with the current 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 be used when checking access privileges during routine execution.
IF NOT EXISTS prevents creating a routine with an existing name.
The proc_parameter parameter list within parentheses must always be present. If no parameters are specified, use empty parentheses () to indicate an empty parameter list. The parameter names here are case-insensitive. By default, each parameter is an IN parameter. To assign other values to the parameters of a stored procedure, use the keywords OUT or INOUT before the parameter name.
IN parameters pass values to the stored procedure. The stored procedure may modify these values, but the caller cannot see the changes when the stored procedure returns. OUT parameters pass values back from the stored procedure to the caller. The initial value of an OUT parameter inside the stored procedure is NULL. When the procedure returns, the caller can see the value of the OUT parameter. INOUT parameters are initialized by the caller and can be modified by the procedure. When the stored procedure returns, any changes made by the procedure are visible to the caller.
For each OUT or INOUT parameter, use the CALL statement to pass a user-defined variable to the procedure so that you can retrieve this value when the procedure returns. If the procedure is called from another stored procedure or function, routine parameters or local routine variables can also be passed as OUT or INOUT parameters. If the procedure is called from a trigger, NEW.column_name can also be passed as an OUT or INOUT parameter.
Parameter types can be declared as any valid data type. If preceded by CHARACTER SET, the COLLATE attribute can be used.
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 perform explicit or implicit commit or rollback. The SQL standard does not require support for these statements; it specifies that each DBMS vendor can decide whether to allow their use.
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, as well as statements such as SHOW, EXPLAIN, and CHECK TABLE. Using these statements in a stored function will cause an error. If a stored program cannot return a result set when using these statements, an error will also occur.
The USE statement is not allowed in stored routines. When a routine is called, USE database_name is implicitly executed (and undone when the routine terminates), specifying the default database for the routine's execution. If you need to reference database objects outside the routine's default database, you can qualify them with the database name.
If a routine is defined in strict SQL mode but called in non-strict mode, the parameters of the routine will not be assigned values in strict mode. If expressions are allocated to be passed to a routine in strict SQL mode, the routine should be called in strict mode.
The COMMENT clause can be used to describe stored routines. You can use the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements to view the comment information.
The LANGUAGE clause specifies the language used to write the routine. It applies only to SQL routines and is ignored by the server.
If a routine always produces the same result for the same input parameters, it is considered "deterministic"; otherwise, it is considered "non-deterministic". If the routine definition does not specify DETERMINISTIC or NOT DETERMINISTIC, the default is NOT DETERMINISTIC.
The following table describes the characteristics of data used by storage routines:
CONTAINS SQL: The stored routine does not contain statements for reading or writing data. This is the default value. For example, statements such asSET@x=1andDO RELEASE_LOCK('abc')are executed but neither read nor write data.NO SQLindicates that the stored routine does not contain SQL statements.READS SQL DATAindicates that the stored routine contains statements for reading data (such asSELECT) but no statements for writing data.MODIFIES SQL DATAindicates that the stored routine contains statements that may write data (such asINSERTorDELETE).
SQL SECURITY can be set to DEFINER or INVOKER to specify whether the stored routine is executed with the privileges of the specified account (which must have access permissions to the database associated with the routine) or with the privileges of the caller. The default value is DEFINER. The user calling the stored routine must have the EXECUTE privilege on that routine.
The DEFINER clause specifies the account used to check access privileges when executing the stored routine. If the DEFINER clause is present, the user value should be an account specified as 'user_name'@'host_name' (for example, 'admin'@'oblocalhost') or the account obtained by using the CURRENT_USER() function. If the DEFINER clause is omitted, the default definer is the user who executes the CREATE PROCEDURE statement. Within the body of a stored routine defined using the SQL SECURITY DEFINER feature, you can use the CURRENT_USER function to retrieve the DEFINER value of the stored routine.
The following example uses the SQL SECURITY INVOKER feature, but the procedure has a DEFINER of 'admin'@'localhost'. In this case, the stored procedure is executed with the privileges of the caller. Whether it can be executed successfully depends on whether the caller has the EXECUTE privilege and the SELECT privilege on ob.user.
obclient> CREATE DEFINER = 'admin'@'oblocalhost' PROCEDURE account_num()
SQL SECURITY INVOKER
BEGIN
SELECT 'Number of accounts:', COUNT(*) FROM ob.users;
END;
The server handles data types for routine parameters, local routine variables created using DECLARE, or function return values as follows:
Check for data type mismatches and overflow. Conversion and overflow issues can cause alerts or errors in strict SQL mode.
Only scalar values can be assigned. For example, the statement
SET val=(SELECT 1, 2)is invalid.For character data types, if the declaration includes
CHARACTER SET, the specified character set and its default collation are used. If theCOLLATEattribute is used, the specified collation is used instead of the default collation.If
CHARACTER SETandCOLLATEare not specified, the database character set and collation applied when the routine was created are used. To prevent the server from using the database character set and collation, explicitly specify theCHARACTER SETandCOLLATEattributes for these parameters.To change the database default character set or collation, you must drop and recreate the storage routine to apply the new database defaults.
The
character_set_databaseandcollation_databasesystem variables 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 IN parameters.
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 INOUT parameters.
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 ;