05.SQL函数与存储过程

12a2024/10/3大约 20 分钟

05.SQL函数与存储过程

SQL存储函数用于完成特定计算，返回一个值，在SQL中，存储函数可以分为聚合函数和单行函数，不同的DBMS之间的差异很大，只有很少的函数是被DBMS同时支持的
- 聚合函数是对一组数据进行汇总的函数，输入是多行值，输出单个值，在之前聚合查询中已经介绍过了，接下来的内容不包含聚合函数
- 单行函数只对一行数据进行变换，每行返回一个结果，支持嵌套
另外，函数类型还可以按用途分为流程控制、数值、字符串、时间等类型

每个数据库支持的函数实在是太多了，下面只介绍一些常用的函数，具体的函数可在数据库教程网查看

5.1 流程控制函数

分支语句

MySQL

-- 逻辑分支，IF判断
SELECT IF(1 > 2, 'NO', 'YES'), IF(2 > 1, 'NO', 'YES');

-- IFNULL可以用于替换NULL，相当于IF可以结合ISNULL函数使用
SELECT IFNULL(NULL, 'HELLO WORLD'), IF(ISNULL(NULL), NULL, 'HELLO WORLD');

-- 多逻辑分支
SELECT CASE WHEN level = 0 THEN 'Junior'
            WHEN level = 1 THEN 'Senior'
            ELSE 'Error'
      END FROM students;

-- 如果相等返回NULL，否则返回参数1
SELECT NULLIF(1, 1), NULLIF(1, 2);

5.2 数值函数

基本数学运算

MySQL

-- 取绝对值、整除、取模
SELECT ABS(-1), 7 DIV 5, 7 MOD 5, 7 % 5;

-- 向上、下取整
SELECT CEILING(1.5), FLOOR(1.5);
-- 四舍五入、按位截断，比如保留1个小数位
-- 如果为保留位数为负数，对应整数位会被0替换
SELECT ROUND(123.456, 1), TRUNCATE(123.456, 1);

-- 基本运算
-- 小数点后6位圆周率和弧度角度互换
SELECT PI(), RADIANS(45), DEGREES(PI()/2);
-- 4的平方根、自然底数的2次方、2的3次方、正弦函数等三角函数
SELECT SQRT(4), EXP(2), POWER(2, 3), SIN(PI()), ACOS(1);
-- log函数
-- LOG()函数包含两个参数，第一个为log的底数，如果省略为自然底数
-- ln为自然底数对数函数，LOG10和LOG2分别表示以10和2为底的对数
SELECT LOG(1), LOG(2, 4), LN(2), LOG10(10), LOG2(2);

-- 返回列表中的最大值、最小值，支持能够比较的文本字符串或数字列表
SELECT GREATEST(2, 1, 5, '0'), LEAST('HELLO', 'WORLD');

-- 数字符号判断，返回结果为1/-1/0，如果数字为NULL会返回NULL
SELECT SIGN(-1.5);

-- 0-1之间的随机数，可传入种子
SELECT RAND(), RAND(999);

5.3 字符串函数

基本字符串函数

MySQL

-- 整数和字符转换, 第一个得到第一个字符ASCII码值, 第二个转换为字符
SELECT ASCII('A'), CHAR(65, 66, 67);

-- 字符串与16进制字符流转换, HEX函数还支持传入数字
SELECT HEX(255), HEX('abc'), UNHEX(HEX('Hello'));

-- 字符串截取、拼接
-- 截取按顺序传入index和length, index从1开始
-- 如果希望从左侧或右侧截取可以使用LEFT(str, len)或RIGHT(str, len)
-- 拼接不支持null输入, 如果希望忽略null请使用CONCAT_WS
SELECT SUBSTRING('Hello', 1, 2), CONCAT('Hello', 'World');

-- 在指定位置替换字符, 下方结果为'Hello World'
SELECT INSERT('Hello_World', 6, 1, ' ');

-- 获取字符串字节长度和字符长度，分别有别名 OCTET_LENGTH 和 CHARACTER_LENGTH
SELECT LENGTH('HELLO'), CHAR_LENGTH('HELLO');

-- 全大写、全小写
SELECT UPPER('hEllO'), LOWER('hEllO');

-- 去除两边的指定字符、替换字符串中指定字符
-- TRIM中'_' FROM可省略，默认为空格
-- 可以通过{BOTH | LEADING | TRAILING}控制删除哪边，默认BOTH
-- LEADING为只删除左边，TRALING为只删除右边
SELECT TRIM(BOTH '_' FROM '__hello__');
-- REPLACE(str, from_str, to_str)函数
-- 返回 str 中的所有 from_str 被 to_str 替换后的结果
SELECT REPLACE('Hello World', 'l', 'L');

5.4 时间类型函数

基本时间类型函数

MySQL

-- 获取时间类型对应的部分
-- 下列函数也可以通过EXTRACT(YEAR FROM '2022-02-28')完成
-- 年月日
SELECT YEAR('2022-02-28'), MONTH('2022-02-28'), DAY('2022-02-28');
-- 时分秒
SELECT HOUR('10:11:12'), MINUTE('10:11:12'), SECOND('10:11:12');
-- 周，第二个参数是模式信息，和周有关的函数都支持输入模式信息，0表示从星期天开始一周，且对第一周没有额外要求
SELECT WEEK('2022-01-01', 0);
-- 季度数字，从1开始
SELECT QUARTER('2022-02-28');

-- 获取周几、是一年中的第几天、一周的第几天(从星期日开始)
SELECT DAYNAME('2022-02-28'), DAYOFYEAR('2022-02-28'), DAYOFWEEK('2022-02-28');
-- 获取月份名
SELECT MONTHNAME('2022-08-31');
-- 获取是一年第几周
SELECT WEEKOFYEAR('2022-02-28', 0);

-- 计算时间差值返回一个 HH:MM:SS 格式的时间值
SELECT TIMEDIFF('2022-02-28 12:12:12', '2022-02-21 10:10:10');
-- 计算时间差值，返回对应的时间间隔
TIMESTAMPDIFF(YEAR, '2021-12-28', '2022-02-28');

-- 增加日期, 支持设置增加的单位, 默认为DAY
SELECT DATE_ADD('2020-06-10', 10), DATE_ADD('2020-06-10', INTERVAL 10 DAY);

-- 转字符串、转时间
-- GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})可以获取格式
-- 也可以通过%设置格式
SELECT SELECT DATE_FORMAT('2022-02-28', '%Y'), STR_TO_DATE('2022/02/01', '%Y/%m/%d');

-- 时区转换，支持时区名称和时区偏移量，比如：+8:00
SELECT CONVERT_TZ('2021-12-01 12:00:00', 'GMT', 'MET');

系统时间函数

MySQL

-- 这些函数可以用于加减法，得到对应的数字结果

-- 获取当前日期
SELECT CURRENT_DATE, CURRENT_DATE();

-- 返回系统时间，不包含日期
SELECT CURRENT_TIME;

-- 返回系统时间，包含日期，UTC_TIMESTAMP支持毫秒
SELECT UTC_TIMESTAMP(1), UTC_TIMESTAMP(6), NOW();

5.5 JSON函数

JSON函数用于处理JSON数据或是可转成JSON的字符串
在JSON官方文档中，JSON数据包括对象、数组、字符串、数值、布尔值和null

创建JSON：

MySQL

--
-- 创建一个 JSON 对象，参数以键值对的形式传入，键重复时会覆盖之前的值
SELECT JSON_OBJECT('id', 1, 'name', 'Alice');
-- 结果为 {"id": 1, "name": "Alice"}

-- 创建一个 JSON 数组，数组中可以包含各种 JSON 数据类型
SELECT JSON_ARRAY(1, 'abc', TRUE, NULL);
-- 结果为 [1, "abc", true, null]

修改JSON数据：

MySQL

-- JSON 数据指定
-- $ 表示根节点，$.属性名 用于表示指定节点
-- 属性名[数字] 用于表示指定数组下标的元素

-- 使用这些添加函数时，应该使用 JSON 格式
-- 不然会出现字符串直接插入
SELECT JSON_SET('{"a":1}', '$.a', '[1,2]');
-- 结果为 {"a":"[1,2]"}
-- 需要使用CAST函数
SELECT JSON_SET('{"a":1}', '$.a', CAST('[1,2]' AS JSON));


-- JSON 数据修改函数
-- 如果指定路径存在则更新，不存在则添加新键值
SELECT JSON_SET('{"a":1}', '$.a', 100, '$.b', 'new');

-- 仅当指定路径不存在时才插入数据
SELECT JSON_INSERT('{"a":1}', '$.b', 2);

-- 仅当指定路径存在时替换数据
SELECT JSON_REPLACE('{"a":1}', '$.a', 200);

-- 删除指定路径的数据
SELECT JSON_REMOVE('{"a":1,"b":2}', '$.b');


-- JSON 数组操作
-- 在 JSON 数组指定路径的末尾追加元素，支持批量添加
-- 如果指定的路径不指向数组，则操作失败
SELECT JSON_ARRAY_APPEND('{"a":[1,2]}', '$.a', 3, 4);
-- 结果为 {"a":[1,2,3,4]}

-- 在 JSON 数组中插入新元素，指定数组下标的位置(默认为数组末尾)
-- 如果对应位置超过数组长度，将插入到末尾
SELECT JSON_ARRAY_INSERT('{"a":[1,3]}', '$.a[1]', 2, 4);
-- 结果为 {"a":[1,2,4,3]}


-- 合并 JSON 数据
-- 使用“替换合并”策略，后面的 JSON 会替换前面 JSON 中相同键的值
-- 如果相邻的合并的 JSON 数据类型不同，合并结果为第二个数据
SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2}');

-- 使用“保留合并”策略，相同键的值合并为数组
-- 对象和数组合并，会将对象添加到数组中(如果是对象在前面，对象将作为第一个元素)
SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"a": [2,3]}');

从JSON中提取数据：

MySQL

-- JSON 字符串数据提取和去引号
-- 用于从 JSON 文档中提取指定路径的数据
SELECT JSON_EXTRACT('{"id":1,"name":"Alice"}', '$.name');
-- 结果为 "Alice"

-- 用于去除提取结果中的额外双引号，常与 JSON_EXTRACT 联合使用返回纯文本结果
-- 对应 JSON_QUOTE 函数，为字符串添加""包装，如果其中有 " 和特殊字符会被转义
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"id":1,"name":"Alice"}', '$.name'));
-- 结果为 Alice

-- 返回特定格式的数据，路径不对返回 NULL
-- 在路径后可以使用 RETURNING 语句修改返回的数据类型
-- CHAR、INT、DECIMAL、BOOLEAN、DATE、TIME、DATETIME、YEAR
-- NULL 值处理 [{NULL | ERROR | DEFAULT value} ON EMPTY]
-- 错误处理 [{NULL | ERROR | DEFAULT value} ON ERROR]
SELECT JSON_VALUE('{"id":1,"name":"Alice"}',
                  '$.name' RETURNING CHAR DEFAULT "John" ON ERROR);

-- 返回最顶层成员数组，第二个参数为路径，默认为 $
SELECT JSON_KEYS('[{"id":1,"name":"Alice"}]', '$[0]');
-- 结果为 ["id", "name"]

-- 对字符串进行 JSON 格式化
SELECT JSON_PRETTY('["Hello World"]');

-- 将 JSON 转换为表
-- 参数1为 JSON 字符串
-- 参数2为 需要转换的路径，用*匹配所有的数组元素
-- 参数3为 列属性，数据来源可以是对应元素中的路径或 ORDINALITY 自增
SELECT *
FROM JSON_TABLE('[{"id":1,"name":"John"}, {"id":2,"name":"Jane"}]'
  , '$[*]'
  COLUMNS(col_id FOR ORDINALITY,
          id INT PATH '$.id',
          name VARCHAR(20) PATH '$.name')
          );

相关检查以及判断函数：

MySQL

-- 检查 JSON 是否包含指定的 JSON 数据，可以在最后给出具体子路径进行查询
-- 如果存在返回 1，不存在返回 0
SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a');

-- 批量检查指定的 JSON 数据是否存在，无法设置子路径查询
-- 参数 'one' 或 'all' 分别表示检查至少一个或全部路径存在
SELECT JSON_CONTAINS_PATH('{"a":1, "b":2}', 'one', '$.a', '$.c');

-- 在 JSON 字符串中搜索指定的属性，根据查找模式返回匹配值所在的路径
-- 支持通配符 % 和 _，像 LIKE 查询一样，后面还可添加转义符参数和搜索路径参数
SELECT JSON_SEARCH('{"a":"hello","b":"world"}', 'one', 'world');
-- 结果为 "$.b"

-- 检查某个值是否是 JSON 数组的元素，如果是返回 1
-- 如果是纯值且相等，也返回 1
SELECT 1 MEMBER OF '[1,2,3]';

-- 检查字符串是否为有效的 JSON 格式，如果是返回 1
-- 大部分 JSON 函数都要求传入的数据是有效的 JSON 格式
SELECT JSON_VALID('{"a":1}');

-- 检查字符串是否满足模式，满足返回 1
-- 模式需要查看 https://json-schema.org/learn/getting-started-step-by-step
SELECT JSON_SCHEMA_VALID('{"type":"object"}','{"a":1}');
-- 返回验证报告
SELECT JSON_PRETTY(
  JSON_SCHEMA_VALIDATION_REPORT('{"type":"object"}','{"a":1}')
) AS REPORT;

-- 检查两个 JSON 数据是否有相同键值对或重复元素，如果有返回 1
-- 纯值参数如 1 被视为 [1]
SELECT JSON_OVERLAPS('{"a":1,"b":2}', '{"a":1,"c":3}');

获取相关属性函数：

MySQL

-- JSON 结构和格式化函数
-- 获取 JSON 中指定路径下的元素数量，默认路径是 $
-- 对于数组返回元素个数，对于对象返回为对象属性个数
SELECT JSON_LENGTH('{"a":[1,2,3]}', '$.a');

-- 返回 JSON 文档的最大嵌套深度，如果是 NULL 返回 NULL
SELECT JSON_DEPTH('{"a":[{"b":2}]}');

-- 返回 JSON 数据的类型，如 'OBJECT'、'ARRAY'、'INTEGER' 等
SELECT JSON_TYPE('{"a":1}');

-- 返回 JSON 列的存储空间和更新释放了的空间
SELECT col,
       JSON_STORAGE_SIZE(col),
       JSON_STORAGE_FREE(col);

5.6 加密函数

加密相关函数

MySQL

-- 散列函数
SELECT MD5('Hello World'), SHA('Hello World');

-- 在5版本还支持加解密函数，但现在已经不支持了，推荐通过后端加密再存储
SELECT ENCODE('pwd', 'seed'), DECODE(ENCODE('pwd', 'seed'), 'seed');

5.7 系统信息函数

5.8 其他函数

随机数据函数

MySQL

-- 仅提供生成随机小数的函数
SELECT RAND();

-- 随机字符串生成
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) -- 该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100)DEFAULT
'abcdefghijk1mnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255)DEFAULT'';
DECLARE i INT DEFAULT 0;
WHILE i<n DO
  SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  SET i=i+1;
END WHILE;
RETURN return_str;
END //
DELIMITER;

-- 随机数整生成
DELIMITER //
CREATE FUNCTION rand_num (from_num INT to_num INT)RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i FLOOR(from_num +RAND()*(to_num from_num+1));
RETURN i;
END //
DELIMITER ;

5.8.1 正则匹配

MySQL

-- 正则匹配，也支持使用 RLIKE 和 REGEXP_LIKE() 函数
SELECT 'hello' REGEXP '^[a-z]+$';

-- 正则替换
-- 匹配到的空格修改为-
SELECT REGEXP_REPLACE('123 abc 456 def', '\\s+', '-');
-- 支持设置替换起始位置、替换次数和匹配模式
SELECT REGEXP_REPLACE('123 abc 456 def', '\\d+', 'X', 1, 2, 'm');

5.9 流程控制

在数据库中支持类似C等编程语言的简单流程编写方法，实现简单的控制逻辑
变量
MySQL
- 在MySQL中，变量有两种类别，系统变量和用户自定义变量
- 系统变量主要是来自my.ini配置文件中的变量或编译MySQL时使用的默认值，常用的系统变量在官方文档有说明
  - 系统变量分为全局变量(global关键字)和会话变量(session关键字)，默认是会话变量
  - 全局变量对之后所有新建的连接都有效，也就是修改之后建立的连接会话的值；而会话变量不能跨会话生效，仅影响当前会话。它们在数据库重启后都会失效
  - 存在一些系统变量只能是全局的，比如最大连接数max_connections；也存在一些系统变量只能是会话级的，比如当前会话的连接标识pseudo_thread_id
  -- 查看全局变量 SELECT @@global.max_connections; SHOW GLOBAL VARIABLES LIKE 'admin_%'; -- 设置全局变量 SET GLOBAL max_connections=1000; SET @@global.max_connections=1000; -- 查看会话变量 SELECT @@session.character_set_client; SELECT @@character_set_client; SHOW VARIABLES LIKE 'character_%'; -- 设置会话变量 SET @@session.tx_isolation='read-uncommited';
- 用户自定义变量是由用户定义的，有两种情况，对当前会话有效和在BEGIN和END块中生效
  - 会话用户变量：在当前会话有效，根据规范，会话用户变量应该以@开头，不需要指定类型
    -- 使用 := 或 = 赋值 SET @a := 1; SET @b = 1; -- 使用 := 或 INTO 通过查询赋值 SELECT @c := COUNT(*) FROM user; SELECT COUNT(*) INTO @d FROM user;
  - 局部变量：在BEGIN与END块中有效，只在存储过程或函数中使用，一般不加@，需要指定类型
    BEGIN -- 声明，必须在块的起始部分 -- default部分可以省略，默认初始为NULL DECLARE a, b, c int default 1; DECLARE d VARCHAR(25); -- 赋值，可以使用会话用户变量的赋值方法 SET a = 2; SELECT 3 INTO b; -- 查询值 SELECT a, b, c; END

分支选择IF：在数据库中也有可以完成简单判断的语句

MySQL

在MySQL中，判断是通过IF语句或CASE语句完成的，需要用于BEGIN ... END块中

-- if判断
-- 条件一般有是否为空，比较大小等
IF 条件1 THEN 操作1
[ELSEIF 条件2 THEN 操作2]...
[ELSE 操作N]
END IF;

-- case值判断，相当于switch判断
CASE 表达式
WHEN 值1 THEN 结果1或者语句1(如果是语句需要加分号)
WHEN 值2 THEN 结果2或者语句2(如果是语句需要加分号)
...
ELSE 结果n或者语句n(如果是语句需要加分号)
END [CASE](如果放在begin...end中需要加CASE)

-- case条件判断，相当于多重IF
CASE
WHEN 条件1 THEN 结果1或者语句1(如果是语句需要加分号)
WHEN 条件2 THEN 结果2或者语句2(如果是语句需要加分号)
...
ELSE 结果n或者语句n(如果是语句需要加分号)
END [CASE](如果放在begin...end中需要加CASE)

循环结构LOOP

MySQL

在MySQL中循环有三种实现方法，LOOP、WHILE和REPEAT，需要用于BEGIN ... END块中

-- 循环标签label用于标记循环，在退出循环时使用
-- 循环标签可不写两遍
-- LOOP 循环
l_label: LOOP
  IF 循环条件 THEN LEAVE l_label;
  循环体
END LOOP l_label;

-- WHILE 循环
w_label: WHILE 循环条件 DO
  循环体
END WHILE w_label;

-- REPEAT 循环
r_label: REPEAT
  循环体
UNTIL 结束循环表达式(满足退出循环)
END REPEAT r_label;

在MySQL中，LEAVE 标签名相当于break语句；如果希望实现continue语句的效果，可以使用ITERATE 标签名

错误处理：

MySQL

在MySQL中，对错误进行处理主要有定义错误和定义处理方式两个步骤

定义错误：定义某个MySQL错误码或SQL错误条件代码的错误名称，方便进行处理

-- 定义错误名称，如果是存储程序，需要用于存储程序开头
DECLARE 错误名称 CONDITION FOR [MySQL错误码|SQLSTATE '错误条件代码'];

-- 比如：ERROR 1048(23000)
DECLARE Field_Not_Be_Null CONDITION FOR 1048;
DECLARE Field_Not_Be_Null CONDITION FOR SQLSTATE '23000';

-- 直接抛出错误
SIGNAL 错误名称 [MySQL错误码|SQLSTATE '错误条件代码'] SET MESSAGE_TEXT = '自定义错误消息';

定义处理方式：定义某个错误名称对应的处理程序，需要指定处理方式，错误类型和处理语句
- 处理方式：表示应该怎么处理错误
- 错误类型：表示应该匹配哪些错误
- 处理语句：应该做哪些处理

-- 声明
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

-- 处理方式，规范中还包括Undo，但是MySQL还不支持
-- * Continue：忽略错误，继续执行后续语句
-- * Exit：退出当前语句块(默认)

-- 错误类型
-- * SQLWARNING：捕获所有警告(错误条件代码以01开头)
-- * NOT FOUND：捕获未找到数据的情况(错误条件代码以02开头)
-- * SQLEXCEPTION：捕获除01和02开头的所有其他类型错误
-- * 特定错误，[MySQL错误码|定义的错误名称|SQLSTATE 错误条件]

-- 处理语句
-- 可以是SET 变量=值的赋值语句
-- 也可以是BEGIN ... END的复合语句

SQL错误条件说明

SQL标准定义了一系列的错误码和条件名称，用于描述不同的错误类型。以下是部分常见的规范值

错误代码	条件名称	描述
`00000`	`successful_completion`	成功完成
`01000`	`warning`	警告
`02000`	`no_data`	无数据
`21000`	`cardinality_violation`	基数违规
`22000`	`data_exception`	数据异常
`22012`	`division_by_zero`	除以零
`23000`	`integrity_constraint_violation`	完整性约束违规
`28000`	`invalid_authorization_specification`	无效的授权规范
`42000`	`syntax_error_or_access_rule_violation`	语法错误或访问规则违规

动态语句执行

MySQL

-- 动态 SQL 需要拼接语句，通常是通过 concat() 函数拼接字符串完成
-- 首先需要创建一个会话变量或全局变量
-- ! 注意：如果是 Begin 和 end 中 Declare 的局部变量不可用于动态语句执行
SET @sql = concat('SELECT * FROM user WHERE id = ', id);

-- 三步执行
-- 预编译语句
PREPARE stmt FROM @sql;
-- 执行预编译语句
EXECUTE stmt;
-- 释放预编译语句
DEALLOCATE PREPARE stmt;

-- 例子
DELIMITER $$
CREATE PROCEDURE create_shard_tables(
    IN base_table VARCHAR(64),
    IN shard_count INT
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE table_name VARCHAR(64);

    WHILE i < shard_count DO
        SET table_name = CONCAT(base_table, '_', LPAD(i, 2, '0'));
        SET @sql_stmt = CONCAT(
            'CREATE TABLE IF NOT EXISTS `',
            table_name,
            '` LIKE `',
            base_table,
            '`'
        );
        PREPARE stmt FROM @sql_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER;

5.10 存储过程

存储过程：用于完成一次完整的业务处理，没有返回值，但可以传出多个值，就是一组经过预编译的语句封装

完整的存储过程架构如下：

MySQL

-- 定义结束符为$
-- 由于存储过程体中多语句要通过;结尾，和命令行结尾符;冲突
-- 更改默认语句结束符为$
DELIMITER $

-- 存储过程定义
-- 参数需要使用修饰符，默认为IN
-- IN：输入参数
-- OUT：返回参数
-- INOUT：输入和返回参数
CREATE PROCEDURE p_name(INOUT usr_id INT, OUT age INT)
-- 此部分是存储过程的约束条件
[characteristics ...]
BEGIN
  -- 存储过程体
  -- 支持各种流程控制和变量定义
END$

-- 还原结束符
DELIMITER ;

-- 约束包括
-- 说明存储过程执行体是由SQL语句组成的
LANGUAGE SQL
-- 指明存储过程执行的结果是否确定
| [NOT] DETERMINISTIC
-- 指明子程序使用SQL语句的限制
-- 分别为不包含读写数据的SQL语句、不包含任何SQL语句、包含读数据的SQL语句、包含写数据的SQL语句
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
-- 执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程
-- 分别为创建者和可访问者，默认指定值为DEFINER
| SQL SECURITY { DEFINER | INVOKER }
-- 注释信息
| COMMENT 'string'

使用存储过程

MySQL

-- 调用无参数的函数SELECT_EMPLOYEES
CALL SELECT_EMPLOYEES();

-- 调用有参数的函数ADD_SALARY
CALL ADD_SALARY('anna', 1000, @ms);
SELECT @ms;

其他对存储过程的操作
MySQL
```
-- 查询存储过程创建语句
SHOW CREATE PROCEDURE 存储过程名;
-- 查询相关信息
SHOW PROCEDURE STATUS LIKE '存储过程名';
-- 查询相关系统表获取相关信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储过程名' AND ROUTINE_TYPE = 'PROCEDURE';

-- 修改约束条件
ALTER PROCEDURE 存储过程名 [characteristics ...]

-- 删除
DROP PROCEDURE IF EXISTS 存储过程名
```
存储过程
不同公司对存储过程使用的看法不同，许多公司甚至禁止使用存储过程
- 存储过程具有许多优点，比如
  - 一次编写多次使用
  - 对操作封装提高安全性
  - 减少工作量
- 存储过程的缺点也很明显
  - 存储过程难以跨数据库系统移植、修改、调试
  - 如果数据表发生改变可能导致存储过程失效，却很难进行版本控制，维护不便
  - 不适合高并发分库分表存储场景

5.11 存储函数

许多数据库支持自定义函数，对数据进行各种处理，定义完成后可以和预定义函数一样调用它们

存储函数与存储过程对比

存储过程一般用于更新数据，可以有多个或0个返回值，通过CALL调用，存储过程可以对表进行增删等操作和事务操作
存储函数一般用于查询数据，必须只有一个返回值，通过SELECT调用，存储函数不能对表进行增删等操作和事务操作

MySQL

-- 定义
-- 参数列表支持使用修饰符IN/OUT/INOUT，默认为IN
CREATE FUNCTION 函数名(参数列表)
RETURNS 返回值类型
-- 支持和存储过程一样的约束条件
-- 有一个全局检查要求必须说明下面两个条件，否则不允许创建
-- * 结果是否确定 DETERMINISTIC
-- * 是否使用了读或者写SQL语句 CONTAINS SQL等
-- 即报错：you might want to use the less safe log_bin_trust_function_creators variable
-- 也可以通过设置log_bin_trust_function_creators=1取消
[characteristics ...]
BEGIN
  -- 函数体，必须含有RETURN语句
END

-- 示例
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(20)
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
  RETURN (SELECT email FROM employees WHERE id = emp_id);
END //
DELIMITER ;

-- 使用：返回101号员工对应的邮箱
SELECT email_by_id(101);

其他对存储函数的操作

MySQL

-- 查询存储函数创建语句
SHOW CREATE FUNCTION 存储函数名;
-- 查询相关信息
SHOW FUNCTION STATUS LIKE '存储函数名';
-- 查询相关系统表获取相关信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储函数名' AND ROUTINE_TYPE = 'FUNCTION';

-- 修改约束条件
ALTER FUNCTION 存储函数名 [characteristics ...];

-- 删除
DROP FUNCTION IF EXISTS 存储函数名;