MariaDB/MySQL存储过程和函数学习心得

124次阅读

共计 5779 个字符，预计需要花费 15 分钟才能阅读完成。

本文目录：
1. 创建存储过程、函数
1.1 存储过程的 IN、OUT 和 INOUT
2. 修改和删除存储过程、函数
3. 查看存储过程、函数信息

在 MySQL/MariaDB 中，存储过程 (stored procedure)、存储函数(stored function)、触发器(trigger)、事件(event) 统称为存储程序(stored programs)。此外，存储过程和存储函数还合称为 stored routines。

对于函数来说，除了存储函数，还有用户自定义函数(UDF,user defined function)，在 MySQL/MariaDB 中，用户自定义函数是存储函数的扩展，它像一个小程序一样，需要编译、安装后才能运行。这和 SQL Server 对 UDF 的定义不同，倒是类似于 SQL Server 的 CLR 程序。

虽然存储过程和存储函数在功能实现上有些区别，但在使用方法上几乎一致。

在 MySQL/MariaDB 中创建存储过程、函数的语法如下：其中 OR REPLACE 是 MariaDB 10.1.3 版本中才有的，MySQL 中不支持OR REPLACE。

— 创建存储过程和函数
CREATE [OR REPLACE] PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body
CREATE [OR REPLACE] FUNCTION sp_name ([func_parameter[,…]])
RETURNS type
[characteristic …] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type
func_parameter:
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

在 MySQL/MariaDB 的存储过程和函数中，允许存在 DML 和 DDL 语句。且存储过程中还允许 (存储函数不允许) 使用 SQL 事务类型的语句，例如提交 commit。当然，肯定也支持嵌套其他存储过程或函数。

存储过程的参数有三种类型：IN、OUT 和 INOUT，下文将详细分析这三种类型参数。

在创建和修改的语法中，需要说明的就是 characteristic 部分，这部分基本没什么用，但可以了解下它们是干什么的。

language sql：表示后面的 body 部分使用标准 SQL 语句编写，这是默认的。该选项的作用是为了以后支持非 SQL 语句书写存储过程和函数的，例如 SQL Server 中就有使用.NET 写的 CLR 存储过程、函数、触发器等。但目前，这个还没有任何意义。
[not] deterministic：deterministic 的意思是确定的。这里的意思是函数返回值是明确的，而非具有随机性的值。例如，使用了随机数，使用了 now()这样的函数等。not deterministic 表示返回值是不确定的，这是系统默认值。当优化器知道函数返回值是确定值时，将选择一个更优化的执行计划。如果返回值是确定的，却定义为 not deterministic，则性能会降低，如果返回值是不确定的，却定义为 deterministic，则报错。
{contains sql|no sql|reads sql data|modifies sql data}：这些是提供给服务器的信息类子句，看上去是约束性语句，但 MariaDB 不会根据它们做任何检查。也就是说，这些提示符只是给人看的，没有任何作用。contains sql 表示 body 不包含读和写数据的语句，例如 SET 和 DO；no sql 表示 body 不包含 SQL 语句；reads sql data 表示 body 包含读数据的语句，但不包含写数据的语句，如 SELECT。modifies sql data 表示 body 包含写数据的语句，如 DELETE/UPDATE；如果没有指定这些特征值，则使用默认值 contains sql。
sql security：指明执行该程序时以谁的身份执行。definer 表示执行时获取创建者的权限，invoker 表示以调用者的身份执行，若调用该程序的用户对程序中涉及的对象没有对应的权限则会执行失败（如 lisa 用户有执行存储过程的权限，但是没有读取存储过程中涉及的表 a 的权限，那么执行存储过程时因为读表失败而导致执行被拒绝）。默认是 definer。
comment：程序的注释信息。

当要调用存储过程或函数时，可以使用 call 命令调用存储过程，如call sp_name();；而函数则可以当作表达式一样进行调用，例如使用 select 命令select func();，当作表达式赋值给变量set @a=func()。

需要注意的是，在 MySQL/MariaDB 中，因为语句的结束符是分号 ”;”，在存储过程或函数创建过程中直接使用分号会导致语句报错。所以当存储过程或存储函数中包含需要使用分号的语句时，应在创建存储过程或函数之前使用 delimiter 命令来暂时改变语句结束符，在创建完毕之后再改回结束符为分号 ”;”。

例如：

delimiter $$
create or replace procedure proc() — procedure name
begin — procedure body
select * from A;
end $$
delimiter ;
– 调用存储过程
call proc();

在上面的语句中，首先定义了结束符为 $$；然后判断了存储过程 proc()是否存在，存在则删除，之后才开始创建存储过程。这个存储过程很简单，只是一个 select 语句。创建语句结束之后，再次使用 delimiter 命令将结束符改回了分号 ”;”。最后使用 call 命令进行了存储过程的调用。

以下是一个函数的创建和使用示例：

delimiter $$
create or replace function func1()
returns int
return (select count(*) from t1);$$
delimiter ;
set @c=func1();
select func1(),@c;
+———+——+
| func1() | @c |
+———+——+
| 6 | 6 |
+———+——+

它们表示的是参数的类型。

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

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

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

对于每个 OUT 或 INOUT 类型的参数，当调用者在 CALL 语句中调用存储过程时，所传递的每个 用户变量 都可以在存储过程返回的时候获取其值。

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

(1).IN 参数类型。

IN 参数类型是指调用者将某个值传递给存储过程，存储过程借用这个值来完成某些操作。

以下是 IN 类型参数的示例。

create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc1(min int,max int)
begin
select * from t1 where t1.a >= min and t1.a <= max;
end$$
delimiter ;
call proc1(3,5);
+——+
| a |
+——+
| 3 |
| 4 |
| 5 |
+——+

(2).OUT 参数类型。

OUT 参数类型是指存储过程将某个值通过该参数返回给调用者。因此调用者必须传递一个用户变量给存储过程，用来记录存储过程 OUT 参数的值。这个用户变量在传递给存储过程之前，可以是一个已赋值的变量，但在传递给存储过程时，将自动初始化为 NULL 值。

以下是 OUT 类型参数的示例。在此示例中，传入 @a 给 proc()，最后将 count(*)赋值给 out 参数 cnt，cnt 代表的就是传入参数 @a。

delimiter $$
create or replace procedure proc2(out cnt int)
begin
select count(*) into cnt from A;
end$$
delimiter ;
call proc1(@a);
select @a as a;
a
——–
6

(3).INOUT 参数类型。

INOUT 参数类型指的是调用者和存储过程之间传递的内容可以互相赋值。INOUT 有两个过程，一个是 IN 的过程，这个过程是将调用者指定的值传递给存储过程，另一个是 OUT 的过程，这个过程是存储过程将某个返回值返回给调用者。因此，调用者传递 INOUT 参数时，也必须传递一个用户变量。但与 OUT 不同的是，INOUT 的用户变量有初始值，这个初始值会直接应用在存储过程中。而 OUT 的用户变量虽然也有初始值，但这个初始值会自动转换为 NULL。

以下是 INOUT 参数类型的示例。

/* procedure INOUT */
create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc3(INOUT cnt int,min int,max int)
begin
if cnt <5 then /* 直接用 cnt 这个 INOUT 参数来判断大小，因为它继承了传递时的值 */
select count(*) into cnt from t1 where t1.a >=min and t1.a <=max;
else
select count(*) into cnt from t1;
end if;
end$$
delimiter ;
set @a=3;
set @b=6;
call proc1(@a,3,5);
call proc1(@b,3,5);
select @a,@b;
+——+——+
| @a | @b |
+——+——+
| 3 | 6 |
+——+——+

可以使用 alter 语句修改存储过程、函数，但 alter 语句只能修改 characteristic 部分，不支持对 body 部分和参数部分修改。若要修改它们，只能先删除再创建。

— 修改存储过程和函数
ALTER {PROCEDURE | FUNCTION} proc_name [characteristic …]
characteristic:
COMMENT ‘string’
| LANGUAGE SQL
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}

删除存储过程和函数的方式很简单，直接使用 drop 命令，但要求有 alter 权限。

drop {procedure|function} [if exists] sp_name

查看存储过程和函数的信息。

show {procedure|function} status like ‘pattern’; — 查看 routine 的基本信息
show create {procedure|function} proc_name; — 查看 routine 的创建语句
show {procedure|function} code routine_name; — 查看 MariaDB 内部是如何操作 routine 中各语句的

从 information_schema.routines 中查看存储过程和函数的信息，这个比 show status 更详细一点。

select * from information_schema.routines where routines_name=’xxx’\G

这里说明下 show code 功能，这个功能必须要在支持 debug 的 MariaDB 上才能使用，可以在编译时使用选项 ”–with-debug” 启用该功能。

例如：

DELIMITER $$
CREATE PROCEDURE p1 ()
BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END$$
delimiter ;
SHOW PROCEDURE CODE p1;
+—–+—————————————-+
| Pos | Instruction |
+—–+—————————————-+
| 0 | set fanta@0 55 |
| 1 | stmt 9 “DROP TABLE t2” |
| 2 | stmt 5 “INSERT INTO t3 VALUES (fanta)” |
| 3 | jump 2 |
+—–+—————————————-+