[mysql]存储过程/存储函数

[Stored Procedure /Stored Function]

存储过程

定义

语法分析：

调试

效果比较

存储函数

应用

语法分析：

两者对比

存储过程和函数的查看、修改、删除

查看

修改

删除

拓展

优点

缺点

---

存储过程

定义

一组预先经过编译的SQL语句：

执行过程：存储过程预先存储在 MySQL 服务器上。执行时，客户端向服务器端发出调用命令，服务器端会将这一系列 Stored SQL 语句全部执行。

语法分析：

创建：

 CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) 
 [characteristics ...] 
 BEGIN
 存储过程体
 END

分类：

• 没有参数（无参数无返回）

• 仅仅带 IN 类型（有参数无返回）

• 仅仅带 OUT 类型（无参数有返回）

• 既带 IN 又带 OUT（有参数有返回）

• 带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

1. 参数意义

   • IN ：当前参数为输入参数，也就是表示入参；

     读取参数值。 默认为 IN ，表示输入参数。

   • OUT：当前参数为输出参数，也就是表示出参；

     执行完成后，调用客户端可以读取参数返回值。

   • INOUT ：当前参数既可以为输入参数，也可以为输出参数。

2. 形参类型：可以是 MySQL数据库中的任意类型。

3. 约束条件：characteristics 创建存储过程时指定的约束条件

    LANGUAGE SQL | [NOT] DETERMINISTIC |
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
    | SQL SECURITY { DEFINER | INVOKER } 
    | COMMENT 'string'

   • LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

   • [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。

     1.DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同输入得到相同输出。

     2.NOT DETERMINISTIC表示结果是不确定 的，相同输入可能得到不同输出。

     (如果没有指定任意一个值，默认为NOT DETERMINISTIC)

   • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。

     1.CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；

     2.NO SQL表示当前存储过程的子程序中不包含任何SQL语句；

     3.READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；

     4.MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。

   (默认情况下，系统会指定为CONTAINS SQL)

   • SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

     1.DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；

     2.INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

     (如果没有设置相关的值，则MySQL默认指定值为DEFINER)

   • COMMENT 'string'：注释信息，用来描述存储过程

   1. 省略：如果函数体里仅仅一条SQL 语句，则可以省略 BEGIN 和 END

   2. 结束标记：DELIMITER

       DELIMITER $ # look at this!
       CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
       [characteristics ...] 
       BEGIN
       sql语句1; #为了避免和函数体里的分号混淆
       sql语句2; 
       END $ # Wowwwww!

      避免使用反斜杠（\）字符 ：因为反斜线是MySQL的转义字符。

调用

存储过程必须使用CALL语句调用，并且存储过程和数据库相关，

执行其他数据库中的存储过程，需要指定数据库名称，例如 CALL dbname.procname。

 CALL 存储过程名(实参列表)

• IN 场景

 CALL sp1('值');

• OUT 场景

 SET @name;
 CALL sp1(@name);
 SELECT @name;

注意：OUT 输出参数，还要配合 SELECT [求得的字段] INTO @变量 FROM...... 来给参数变量赋值，

同时在调用时给 OUT 参数 赋值。

• INOUT 场景

 SET @name=值;
 CALL sp1(@name);
 SELECT @name;

示例：

1. 创建调用过程

 DELIMITER // CREATE PROCEDURE `add_num`(IN n INT) 
 BEGIN 
 #定义+赋值
 DECLARE i INT; 
 DECLARE sum INT;
 SET i = 1; SET sum = 0; 
 #进入循环
 WHILE i <= n DO 
 SET sum = sum + i; 
 SET i = i +1; 
 END WHILE; 
 #产出结果
 SELECT sum;
 END //
 ​
 DELIMITER ;
 ​
 #第2个存储过程
 CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
 ...

1. CALL调用过程

 CALL CountProc (101, @num);
 CALL add_num(50);

1. 查看发挥结果

 SELECT @num;

如果使用了DBMS： Navicat ，那么在编写存储过程的时候，Navicat 会自动设置 DELIMITER 为其他符号， 不需要再进行 DELIMITER 的操作。

调试

SQL没有IDE，你只能通过SELECT一个一个实验排查。

效果比较

好处：

1. 简化操作，提高了sql语句的重用性，减少了开发程序员的压力

2. 减少操作过程中的失误，提高效率

3. 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）

4. 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

和视图、函数的对比：

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。

• 和视图的不同

视图是虚拟表 ，通常不对底层数据表直接操作，

存储过程是程序化的 SQL，可以直接操作底层数据表,相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

• 和函数的不同

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于

函数，存储过程是 没有返回值 的。

存储函数

应用

语法分析：

创建：

 CREATE FUNCTION 函数名(参数名 参数类型,...) 
 RETURNS 返回值类型 
 [characteristics ...] 
 BEGIN
 函数体 #函数体中肯定有 RETURN 语句 
 END

1. 参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数RETURNS type 语句表示函数返回数据的类型；

2. RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。

3. characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。

4. 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

调用

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。

换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。

区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

 SELECT 函数名(实参列表)

示例

1. 创建

 CREATE FUNCTION email_by_name()
 RETURNS VARCHAR(25) 
 DETERMINISTIC CONTAINS SQL 
 BEGIN
 ​
 RETURN (SELECT email 
         FROM employees 
         WHERE last_name = 'Abel');
 ​
 END // 
 DELIMITER ;

 DELIMITER // 
 CREATE FUNCTION count_by_id(dept_id INT) 
 RETURNS INT 
     LANGUAGE SQL 
     NOT DETERMINISTIC 
     READS SQL DATA 
     SQL SECURITY DEFINER 
     COMMENT '查询部门平均工资' 
 BEGIN
 ​
 RETURN (SELECT COUNT(*) 
         FROM employees 
         WHERE department_id = dept_id); 
 ​
 END // 
 DELIMITER ;

1. 调用

 SELECT email_by_name()
 ​
 SET @dept_id = 50; SELECT count_by_id(@dept_id);

注意：若在创建存储函数中报错

you might want to use the less safe log_bin_trust_function_creators variable

有两种处理方法：

方式1：加上必要的函数特性

 [NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}

方式2：

 mysql> SET GLOBAL log_bin_trust_function_creators = 1;

两者对比

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	0或多个	更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	返回一个查询结果

• 存储函数可以放在查询语句中使用，存储过程不行。

• 存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，存储函数不行。

存储过程和函数的查看、修改、删除

查看

MySQL存储了存储过程和函数的状态信息，用户可以通过3种方法查看：

1. SHOW CREATE语句

    SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
    #举例
    SHOW CREATE FUNCTION test_db.CountProc \G

2. SHOW STATUS语句

    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

   这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

3. 在information_schema.Routines表中查看

    SELECT * 
    FROM information_schema.Routines 
    WHERE ROUTINE_NAME='存储过程或函数的名' 
    [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
    #数据库中存在（函数和过程）名称相同的情况，指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。 

修改

这里的修改指：不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

 ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

characteristic 指定存储过程或函数的特性，其取值信息与[创建存储过程、函数时的取值信息]

不同。

 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
 | SQL SECURITY { DEFINER | INVOKER } 
 | COMMENT 'string'

• CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。

• NO SQL ，表示子程序中不包含SQL语句。

• READS SQL DATA ，表示子程序中包含读数据的语句。

• MODIFIES SQL DATA ，表示子程序中包含写数据的语句。

• SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。

• DEFINER ，表示只有定义者自己才能够执行。

• INVOKER ，表示调用者可以执行。

• COMMENT 'string' ，表示注释信息

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。

两个语句的结构一样，语句中的所有参数也一样。

示例：

 ALTER PROCEDURE CountProc
 MODIFIES SQL DATA
 SQL SECURITY INVOKER ;

修改后查询

 SELECT specific_name,sql_data_access,securtiy_type
 FROM information_schema.'ROUTINES'
 WHERE routine_name = 'CountProc' AND routine_type = 'PROCEDURE';

查询结果：访问数据权限和安全类型改变。

删除

 DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
 #IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告

拓展

对于存储过程的使用争议：

优点

1. 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译， 这就提升了 SQL 的执行效率。

2. 可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题 拆解成不同的模块，然后模块之间可以 重复使用 ，在减少开发工作量的同时，还能保证代码的结构清 晰。

3. 存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限 ，这样就和视图一样具 有较强的安全性。

4. 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减 少了网络传输量。

5. 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接 多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可 。

缺点

基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？

阿里开发规范 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。 存储过程虽然有诸如上面的好处，但缺点也是很明显的。

1. 可移植性差：存储过程不能跨数据库移植

   比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

2. 调试困难：只有少数 DBMS 支持存储过程的调试。

   对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。

3. 存储过程的版本管理很困难：比如数据表索引发生变化了，可能会导致存储过程失效。

   我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

4. 它不适合高并发的场景：高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方 式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就 不适用了。