MySQL里，可以将多条SQL语句封装在一个存储程序里，这些SQL语句共同完成一项 功能，调用时只需调用这个存储程序，而不是书写若干条又臭又长的SQL语句。存储程序又可以分为存储例程、触发器和事件，存储例程又可以分为存储函数和存储过程。存储例程是需要使用者手动调用的，而触发器和事件是MySQL在特定条件下自动调用的，关系如下图：
存储函数和存储过程 - 图1

1、存储函数

存储函数更贴近编程语言里函数的概念，有入参、返回值和函数体的概念，函数体由若干SQL语句和控制语句（比如循环语句、判断语句等）组成。

1.1 创建存储函数

格式：

  1. CREATE FUNCTION 存储函数名称([参数列表])
  2. RETURNS 返回值类型
  3. BEGIN
  4.     函数体内容
  5. END

举例：

  1. delimiter $
  3. # 存储函数，计算科目的平均分，入参为科目名称，返回值为平均分
  4. CREATE FUNCTION avg_subject_score(s VARCHAR(30))
  5. RETURNS DOUBLE
  6. BEGIN
  7.     RETURN (SELECT AVG(score) FROM student_score WHERE s= subject);
  8. END $
  10. delimiter ;

注意：

  • 是RETURNS而不是RETURN；
  • 存储函数因为有返回值，因此函数体里一定要有RETURN语句；
  • 函数体里有多条SQL语句且用；分割开时;需要用delimiter重新定义语句结束分隔符，DataGrip里可以不用；

  • 存储函数有且仅有一个返回值。

    1.2 使用存储函数

    格式：

    1. 存储函数名[(入参列表，逗号分隔)];

    举例：

    1. SELECT avg_subject_score('母猪的产后护理');

    注意：

  • 使用存储函数，就跟我们之前介绍的系统内置的函数是一个用法；

  • 存储函数由于有且仅有一个返回值，因此存储函数可以用在查询列表、搜索条件和搜索表达式中。

    1.3 查看和删除存储函数

    1.3.1 查看存储函数

    格式： ```sql

    查看当前数据库里所有存储函数

    SHOW FUNCTION STATUS [LIKE 需要匹配的函数名];

查看指定的存储函数

SHOW CREATE FUNCTION 存储函数名;

  1. <a name="xxkc8"></a>
  2. ### 1.3.2 删除存储函数
  3. **格式：**
  4. ```sql
  5. DROP FUNCTION 存储函数名;

1.4 函数体的定义

1.4.1 定义局部变量

之前介绍了自定义变量，这里介绍局部变量。自定义变量的作用范围是整个数据库的生命周期，局部变量的作用范围是存储函数的生命周期。
在函数体里使用局部变量，必须先用DECLARE声明局部变量，再用SET对局部变量赋值；相比于自定义变量，使用局部变量时，变量名前面不加@前缀。

声明局部变量：
格式：

  1. DECLARE 变量名1, 变量名2, ... 数据类型 [DEFAULT 默认值];

举例：

  1. CREATE FUNCTION test_function(val INT)
  2. RETURNS INT
  3. BEGIN
  4.     DECLARE local_val INT DEFAULT 0;
  5.     SET local_val = val * val;
  6.     RETURN local_val;
  7. END;

注意：

  • 使用局部变量前，必须先用DECLARE声明；
  • 使用局部变量时用SET，且局部变量前不需要用@前缀。

    1.4.2 使用自定义变量

    存储函数的函数体里，不仅可以使用局部变量，还可以使用自定义变量，需要注意自定义变量使用时变量名前面有@前缀，局部变量没有。
    举例： ```sql SET @global_val = 123;

CREATE FUNCTION test_function_2() RETURNS INT BEGIN DECLARE local_val INT DEFAULT 0; SET local_val = @global_val * 2; RETURN local_val; END;

  1. <a name="gtmVq"></a>
  2. ### 1.4.3 存储函数的入参
  3. **注意：**
  5. - 存储函数的入参可以是一个，也可以是多个，多个入参间用逗号分隔；
  6. - 存储函数的入参不可以和函数体语句中的其他变量名、列名等冲突；
  7. - 调用函数的时候，必须显式的指定所有的参数，并且参数类型也一定要匹配。
  8. <a name="iBBYt"></a>
  9. # 2、存储过程
  10. 存储过程和存储函数都属于存储例程，同样可以将若干`SQL`语句封装在存储过程中供外部一次性调用。不同于存储函数，存储过程更倾向于单纯地批量执行`SQL`语句，因此存储过程里不需要`RETURN`返回值，存储过程中也可以一次性给多个变量赋值。
  11. <a name="nqHQD"></a>
  12. ## 2.1 创建存储过程
  13. **格式：**
  14. ```sql
  15. CREATE PROCEDURE 存储过程名称([参数列表])
  16. BEGIN
  17.     需要执行的语句
  18. END

举例：

  1. CREATE PROCEDURE test_procdure(id INT, name VARCHAR(30))
  2. BEGIN
  3.     SELECT * FROM test_table;
  5.     INSERT INTO test_table VALUES (id, name);
  6.     SELECT * FROM test_table;
  7. END;

2.2 使用存储过程

格式：

  1. CALL 存储过程([参数列表]);

与调用存储函数不同，调用存储过程需要使用CALL关键字。

举例：

  1. CALL test_procdure(20180104, 'TIM');

2.3 查看和删除存储过程

2.3.1 查看存储过程

格式：

  1. # 查看当前数据库中创建的存储过程
  2. SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]
  4. # 查看某个存储过程
  5. SHOW CREATE PROCEDURE 存储过程名称

2.3.2 删除存储过程

格式：

  1. DROP PROCEDURE 存储过程名称

2.4 存储过程的参数前缀

存储过程相比存储函数，可以在入参名加参数前缀修饰，有三种参数前缀，如下：

前缀 实际参数是否必须是变量 描述
IN 否 用于调用者向存储过程传递数据，如果IN参数在过程中被修改，调用者不可见。
OUT 是 用于把存储过程运行过程中产生的数据赋值给OUT参数，存储过程执行结束后，调用者可以访问到OUT参数。
INOUT 是 综合IN
和OUT
的特点，既可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用。

在DataGrip中，存储例程（存储过程 + 存储函数）存放在与tables目录并列的routines目录下，如图：
image.png

2.5 存储函数和存储过程的不同点

存储函数和存储过程虽然都属于存储例程，但有以下不同点：

  • 存储函数需要写RETURNS返回值类型，而且在函数体里要有RETURN语句，而存储过程没有；
  • 存储函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数；
  • 存储函数只能返回一个值，而存储过程可以通过设置多个OUT参数或者INOUT参数来返回多个结果；
  • 存储函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端；
  • 存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用。

    3、控制语句

和其他高级编程语言一样，MySQL也有判断、循环等控制语句。

3.1 判断语句

格式：

  1. IF 表达式 THEN
  2.     处理语句列表
  3. ELSEIF 表达式 THEN
  4.     处理语句列表
  5. ... # 这里可以有多个ELSEIF语句
  6. ELSE
  7.     处理语句列表
  8. END IF;

举例：

  1. CREATE FUNCTION test_function4(val INT)
  2. RETURNS VARCHAR(30)
  3. BEGIN
  4.     DECLARE result VARCHAR(30);
  6.     IF val > 60 THEN
  7.         SET result = '超过60';
  8.     ELSEIF val = 60 THEN
  9.         SET result = '等于60';
  10.     ELSEIF val < 60 THEN
  11.         SET result = '小于60';
  12.     ELSE
  13.         SET result = '非法输入';
  14.        END IF;
  16.     RETURN result;
  17. END;

3.2 循环语句

MySQL有三种循环语句写法：

  • WHILE DO;
  • REPEAT;
  • LOOP。

下面的例子都是用不同的循环语句格式计算前n个连续自然数之和。

3.2.1 WHILE DO

格式：

  1. WHILE 表达式 DO
  2.     处理语句列表
  3. END WHILE;

举例：

  1. CREATE FUNCTION cal_function(val INT)
  2. RETURNS INT
  3. BEGIN
  4.     DECLARE result INT DEFAULT 0;
  5.     DECLARE  i INT DEFAULT 1;
  7.     WHILE i <= val DO
  8.         SET result = result + i;
  9.         SET i = i + 1;
  10.     END WHILE;
  12.     RETURN result;
  13. END;

3.2.2 REPEAT

格式：

  1. REPEAT
  2.     处理语句列表
  3. UNTIL 表达式 END REPEAT;

举例：

  1. CREATE FUNCTION cal_function2(val INT)
  2. RETURNS INT
  3. BEGIN
  4.     DECLARE result INT DEFAULT 0;
  5.     DECLARE  i INT DEFAULT 1;
  7.     REPEAT
  8.         SET result = result + i;
  9.         SET i = i + 1;
  10.     UNTIL  i > val END REPEAT;
  12.     RETURN result;
  13. END;

3.2.3 LOOP

格式：

  1. LOOP
  2.     处理语句列表
  3. END LOOP;

举例：

  1. CREATE FUNCTION cal_function3(val INT)
  2. RETURNS INT
  3. BEGIN
  4.     DECLARE result INT DEFAULT 0;
  5.     DECLARE  i INT DEFAULT 1;
  7.     LOOP
  8.         IF i > val THEN
  9.             RETURN result;
  10.         END IF;
  12.         SET result = result + i;
  13.         SET i = i + 1;
  14.     END LOOP;
  15. END;

如果我们仅仅想结束循环break，而不是使用RETURN语句直接将函数返回，那么可以使用LEAVE语句。不过使用LEAVE时，需要先在LOOP语句前边放置一个所谓的标记flag，比方说我们使用LEAVE语句再改写sum_all函数：

  1. CREATE FUNCTION cal_function4(val INT)
  2. RETURNS INT
  3. BEGIN
  4.     DECLARE result INT DEFAULT 0;
  5.     DECLARE i INT DEFAULT 1;
  6.     flag:LOOP
  7.         IF i > val THEN
  8.             LEAVE flag;
  9.         END IF;
  10.         SET result = result + i;
  11.         SET i = i + 1;
  12.     END LOOP flag;
  13.     RETURN result;
  14. END

可以看到，我们在LOOP语句前加了一个flag:相当于为这个循环打了一个名叫flag的标记，然后在对应的END LOOP语句后边也把这个标记名flag给写上了。在存储函数的函数体中使用LEAVE flag语句来结束flag这个标记所代表的循环。