CREATE PROCEDURE
语法
CREATE [OR REPLACE] [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type type: Any valid MariaDB data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement
Contents
描述
用于创建一个存储过程stored procedure。默认情况下,存储程序是关联到其默认数据库中的。要想将存储程序关联到一个给定的数据库中,可以在创建时指定完整的存储程序名称db_name.sp_name。
当调用存储程序时,将隐含了USE db_name
动作(且程序执行结束时不会再返回)。这使得当执行存储程序时可以直接调用到其关联的数据库中去。在存储程序内部,不能USE
语句。
当创建了存储过程后,可以使用CALL
语句来调用它(见CALL)。
CREATE PROCEDURE
语句需要有CREATE ROUTINE
权限。默认情况下,MariaDB自动授予ALTER ROUTINE
和EXECUTE
权限给程序的创建者。更多内容参见:Stored Routine Privileges。
DEFINER
和SQL SECURITY
子句指定程序执行时,检查权限时所使用的上下文,下文将描述它们。
如果程序的名称和内置的SQL函数同名,则必须在创建存储过程时,过程的名称和括号之间使用一个空格隔开,否则语法错误。同样,在调用存储过程时也如此。基于此,我们建议尽量避免存储程序的名称和内置函数重名。
IGNORE_SPACE SQL
模式应用于内置函数,而不是存储过程。它允许在存储程序名称后使用空格,而不管是否启用了IGNORE_SPACE
。
允许使用空参数列表。如果给定了参数,则参数名不区分大小写。
每个参数都可以声明为任意有效的数据类型,但不能使用COLLATE属性。
IN/OUT/INOUT
默认每个参数都是IN
。要指定其他类型的参数,可以在参数名前面使用关键字OUT
或INOUT
。
IN
参数类型表示将调用者给定的值传递给存储过程。存储过程可能会修改这个值,但是对于调用者来说,在存储过程返回结果时,所做的修改是不可见的。
OUT
参数类型表示将存储过程的返回值传递给调用者。其初始值为NULL,当存储过程返回时,这个值对调用者来说是可见的。
INOUT
参数类型表示由调用者传递值给存储过程,存储过程可能会修改这个值,当存储过程返回的时候,所做的修改对调用者来说是可见的。
对于每个OUT
或INOUT
类型的参数,当调用者在CALL
语句中调用存储过程时,所传递的每个变量都可以在存储过程返回的时候获取其值。如果你在其他存储过程或存储函数中调用存储过程,你也可以传递IN
或INOUT
类型的参数。
DETERMINISTIC/NOT DETERMINISTIC
DETERMINISTIC
和NOT DETERMINISTIC
只适用于存储函数,将其指定在存储过程中是无意义的。 见CREATE FUNCTION。
CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA
CONTAINS SQL
, NO SQL
, READS SQL DATA
以及MODIFIES SQL DATA
是告诉服务器该过程是做什么的信息类子句。但MariaDB不会根据其内容对子句是否符合此处的定义做任何检查。如果没有指定这些参数,则默认使用CONTAIN SQL
。
MODIFIES SQL DATA
意味着函数包含了要修改数据库中数据的语句。例如,在函数中使用了类似于 DELETE, UPDATE, INSERT, REPLACE或DDL类的语句。
READS SQL DATA
意味着函数将会从数据库中读取数据,但不会修改任何数据。例如,在函数中使用了不带任何写操作的SELECT语句。
CONTAINS SQL
意味着函数包含了至少一条SQL语句,但它们不读、不写数据库中的数据。例如函数中使用了SET或DO语句。
NO SQL
不意味着任何事,因为MariaDB目前支持的语言只有SQL语言。
routine_body部分包含有效的SQL语句。这可以是一个简单的SELECT或INSERT语句,也可以是使用BEGIN and END的语句块。语句块中可以包含变量声明、循环以及其他流程控制语句。详细语法信息见:Programmatic and Compound Statements。
MariaDB中允许存储过程中使用DDL语句,例如CREATE
和DROP
。MariaDB同样允许存储过程包含SQL事务类语句(但存储函数不允许),例如COMMIT
语句。
关于更多的存储程序中不允许使用的语法,见Stored Routine Limitations。
在程序中调用存储过程
关于从其他语言接口上调用MariaDB/MySQL的存储过程,见CALL。
OR REPLACE
MariaDB starting with 10.1.3
如果使用了OR REPLACE
子句,它的行为等价于
DROP PROCEDURE IF EXISTS name; CREATE PROCEDURE name ...;
但不会删除该函数已有的权限privileges。
sql_mode
MariaDB会在创建存储过程时保留系统变量sql_mode的值。无论以后该系统变量修改为何值,都会使用此时保留的值作为存储过程执行的上下文环境。
字符集和排序规则
存储过程的参数可以声明为任意有效的字符集和排序规则。如果没有指定字符集和排序规则,将在存储过程创建时使用数据库默认的字符集和排序规则。如果之后数据库的字符集和排序规则发生了改变,存储过程的字符集和排序规则不会随之改变。这种情况下,应该删除存储过程并重现创建存储过程,保证存储过程的字符集和排序规则和数据库的设置保持一致。
示例
下面是一个使用OUT
参数类型的简单存储过程。它使用了DELIMITER
命令设置了语句结束符为//
。见Delimiters in the mysql client。
DELIMITER // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END; // DELIMITER ; CALL simpleproc(@a); SELECT @a; +------+ | @a | +------+ | 1 | +------+
字符集和排序规则:
DELIMITER // CREATE PROCEDURE simpleproc2 ( OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' ) BEGIN SELECT CONCAT('a'),f1 INTO param1 FROM t; END; // DELIMITER ;
CREATE OR REPLACE:
DELIMITER // CREATE PROCEDURE simpleproc2 ( OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' ) BEGIN SELECT CONCAT('a'),f1 INTO param1 FROM t; END; // ERROR 1304 (42000): PROCEDURE simpleproc2 already exists DELIMITER ; DELIMITER // CREATE OR REPLACE PROCEDURE simpleproc2 ( OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' ) BEGIN SELECT CONCAT('a'),f1 INTO param1 FROM t; END; // ERROR 1304 (42000): PROCEDURE simpleproc2 already exists DELIMITER ; Query OK, 0 rows affected (0.03 sec)