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

描述

用于创建一个存储过程stored procedure。默认情况下,存储程序是关联到其默认数据库中的。要想将存储程序关联到一个给定的数据库中,可以在创建时指定完整的存储程序名称db_name.sp_name。

当调用存储程序时,将隐含了USE db_name动作(且程序执行结束时不会再返回)。这使得当执行存储程序时可以直接调用到其关联的数据库中去。在存储程序内部,不能USE语句。

当创建了存储过程后,可以使用CALL语句来调用它(见CALL)。

CREATE PROCEDURE语句需要有CREATE ROUTINE权限。默认情况下,MariaDB自动授予ALTER ROUTINEEXECUTE权限给程序的创建者。更多内容参见:Stored Routine Privileges

DEFINERSQL SECURITY子句指定程序执行时,检查权限时所使用的上下文,下文将描述它们。

如果程序的名称和内置的SQL函数同名,则必须在创建存储过程时,过程的名称和括号之间使用一个空格隔开,否则语法错误。同样,在调用存储过程时也如此。基于此,我们建议尽量避免存储程序的名称和内置函数重名。

IGNORE_SPACE SQL模式应用于内置函数,而不是存储过程。它允许在存储程序名称后使用空格,而不管是否启用了IGNORE_SPACE

允许使用空参数列表。如果给定了参数,则参数名不区分大小写。

每个参数都可以声明为任意有效的数据类型,但不能使用COLLATE属性。

IN/OUT/INOUT

默认每个参数都是IN。要指定其他类型的参数,可以在参数名前面使用关键字OUTINOUT

IN参数类型表示将调用者给定的值传递给存储过程。存储过程可能会修改这个值,但是对于调用者来说,在存储过程返回结果时,所做的修改是不可见的。

OUT参数类型表示将存储过程的返回值传递给调用者。其初始值为NULL,当存储过程返回时,这个值对调用者来说是可见的。

INOUT参数类型表示由调用者传递值给存储过程,存储过程可能会修改这个值,当存储过程返回的时候,所做的修改对调用者来说是可见的。

对于每个OUTINOUT类型的参数,当调用者在CALL语句中调用存储过程时,所传递的每个变量都可以在存储过程返回的时候获取其值。如果你在其他存储过程或存储函数中调用存储过程,你也可以传递ININOUT类型的参数。

DETERMINISTIC/NOT DETERMINISTIC

DETERMINISTICNOT 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语句,但它们不读、不写数据库中的数据。例如函数中使用了SETDO语句。

NO SQL不意味着任何事,因为MariaDB目前支持的语言只有SQL语言。

routine_body部分包含有效的SQL语句。这可以是一个简单的SELECT或INSERT语句,也可以是使用BEGIN and END的语句块。语句块中可以包含变量声明、循环以及其他流程控制语句。详细语法信息见:Programmatic and Compound Statements

MariaDB中允许存储过程中使用DDL语句,例如CREATEDROP。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)

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.