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

本文目录：
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程序。

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

1.创建存储过程、函数

在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 |
+---------+------+

1.1 存储过程的IN、OUT和INOUT

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

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 |
+------+------+

2.修改和删除存储过程、函数

可以使用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

3.查看存储过程、函数信息

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

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                                |
+-----+----------------------------------------+

其中第一列是从0开始的序列值，表示存储过程中所执行的动作先后顺序。第二列是mariadb要执行的动作，这些动作是基于存储过程中的源语句进行设置的。