[Stored Procedure /Stored Function]

[TOC]

存储过程

定义

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

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

语法分析：

创建：

1
2
3
4
5
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 创建存储过程时指定的约束条件

1
2
3
4
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

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

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

调用

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

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

1
CALL 存储过程名(实参列表)
  • IN 场景
1
CALL sp1('值');
  • OUT 场景
1
2
3
SET @name;
CALL sp1(@name);
SELECT @name;

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

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

  • INOUT 场景
1
2
3
SET @name=值;
CALL sp1(@name);
SELECT @name;

示例：

  1. 创建调用过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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调用过程
1
2
CALL CountProc (101, @num);
CALL add_num(50);
  1. 查看发挥结果
1
SELECT @num;

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

调试

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

效果比较

好处：

  1. 简化操作，提高了sql语句的重用性，减少了开发程序员的压力
  2. 减少操作过程中的失误，提高效率
  3. 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
  4. 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

和视图、函数的对比：

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

  • 和视图的不同

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

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

  • 和函数的不同

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

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

存储函数

应用

语法分析：

创建：

1
2
3
4
5
6
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的开发者定义的。

1
SELECT 函数名(实参列表)

示例

  1. 创建
1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25) 
DETERMINISTIC CONTAINS SQL 
BEGIN

RETURN (SELECT email 
        FROM employees 
        WHERE last_name = 'Abel');

END // 
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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. 调用
1
2
3
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：加上必要的函数特性

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

方式2： 

1
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

两者对比

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

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

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

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

查看

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

  1. SHOW CREATE语句

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

  2. SHOW STATUS语句

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

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

  3. 在information_schema.Routines 表中查看

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

修改

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

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

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

不同。

1
2
3
{ 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语句。

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

示例：

1
2
3
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

修改后查询

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

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

删除

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

快来参与讨论吧~