Chiriri's blog
Iekr
2021-08-15
目录

MySQL 存储过程和函数

# MySQL 存储过程和函数

存储过程是一组经过预先编译的 SQL 语句的封装，它由声明式的 SQL 语句（例如 DDL 语句和 DML 语句）以及过程式的 SQL 语句（例如分支语句和循环语句）组成。

相对应函数 / 方法，存储函数必须有返回值，存储过程可以没有返回值

# 存储过程

存储过程可以没有返回值

-- 修改结束分隔符这里指定为$ 可以自定义
delimiter $

-- 创建存储过程
create procedure 存储过程名称([参数列表[,...]])
[特性...]
begin
	sql 语句;
end$
                        
-- 恢复结束分割符为分号
delimiter ;
           
-- 调用存储过程
call 存储过程名称(参数);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

存储过程的特性有以下 5 种取值：

  1. LANGUAGE SQL ：指明使用 SQL 语句来组成 routine_body 部分，当前系统仅支持 SQL 。
  2. [NOT] DETERMINISTIC ：指明存储过程执行的结果是否是 “确定的”。 DETERMINISTIC 表示结果是确定的，即每次执行存储过程时，对于相同的输入参数产生相同的结果； NOT DETERMINISTIC 表示结果是不确定的，即每次执行存储过程时，对于相同的输入参数产生不同的结果。系统默认指定为 NOT DETERMINISTIC 。
  3. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指定使用 SQL 语句时的限制。CONTAINS SQL 表示包含 SQL 语句，但是这些语句中不包括读写数据的语句； NO SQL 表示不包含 SQL 语句； READS SQL DATA 表示包含读数据的 SQL 语句，但不包含写入数据的 SQL 语句； MODIFIES SQL DATA 表示包括写入数据的 SQL 语句。系统默认指定为 CONTAINS SQL 。
  4. SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行该存储过程。 DEFINER 表示只有存储过程的创建者才可以执行； INVOKER 表示拥有权限的调用者才可以执行。系统默认指定为 DEFINER 。
  5. SCOMMENT 'string' ：提供存储过程的注释或描述信息。
-- 查询数据中所有的存储 过程   必须是root权限用户
select * from mysql.proc where db='数据库名称';
-- 删除存储过程
drop procedure [if exists] 存储过程名称;
-- 查看存储过程状态
SHOW PROCEDURE STATUS [LIKE '存储过程名'];
-- 查看存储过程
SHOW CREATE PROCEDURE 存储过程名;
-- 修改存储过程
ALTER PROCEDURE 存储过程名 [特性...];
1
2
3
4
5
6
7
8
9
10

# 变量

  • 局部变量：在 SQL 语句块的 BEGIN...END 中定义，并且作用域仅限于该语句块
  • 用户变量：以 @ 符号开头定义的，作用范围为当前会话（连接）中的所有 SQL 语句
  • 会话变量和全局变量：由 MYSQLD 服务器维护，其作用范围分别为当前会话和整个服务器
-- 定义变量
declare 变量名 数据类型 [default 默认值];
-- 赋值
set 变量名 = 值;
set 变量名 := 值;
select 变量名 := 值;
-- 赋值
select 列名 into 变量名 from 表名 [where 条件];
-- 给会话变量赋值
SET SESSION 变量名称 = 参数;
SET @@SESSION.变量名称 = 参数;
-- 查看会话变量
SHOW SESSION VARIABLES like ' 变量名称 ';
SHOW SESSION VARIABLES;
-- 给全局变量赋值
SET GLOBAL 变量名称 = 参数;
SET @@GLOBAL.变量名称;
-- 查看全局变量
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES like '变量名称';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 游标

在存储过程或函数中，可以使用游标来逐个读取查询结果。通过控制游标的移动，用户可以按需要查看和操作这些数据，但不能跳过任何记录。

游标的声明必须在变量和条件声明语句之后，在声明处理程序之前

-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询条件语句;
--  打开先前声明的游标
OPEN 游标名称;
-- 使用游标,FETCH 语句每执行一次，然后游标指针就会后移一行
FETCH 游标名称 INTO 参数1 [,参数2...];
-- 关闭游标
CLOSE 游标名称
1
2
3
4
5
6
7
8

# 条件分支

# if 语句

if 判断条件1 then sql语句;
 [elseif 判断条件2 then sql语句;]
 [else sql语句;]
end if;
1
2
3
4

# case 语句

-- 方式一
CASE 条件表达式
    WHEN 参数表达式 1 THEN SQL 语句 1
    [WHEN 参数表达式 2 THEN SQL 语句 2]...
    [ELSE SQL 语句 3]
END CASE;
-- 案例
CASE val
    WHEN ' 男 ' THEN ' 输入的性别是男性 ';
    WHEN ' 女 ' THEN ' 输入的性别是女性 ';
    ELSE SELECT ' 输入有误 ';
END CASE;

-- 方式二
CASE
    WHEN 参数表达式 1 THEN SQL 语句 1
    [WHEN 参数表达式 2 THEN SQL 语句 2] ...
    [ELSE SQL 语句 3]
END CASE;
-- 案例
CASE
    WHEN val = 0 THEN SELECT 'val 的值为 0';
    WHEN val > 0 THEN SELECT 'val 的值大于 0';
    WHEN val < 0 THEN SELECT 'val 的值小于 0';
    ELSE SELECT ' 输入有误 ';
END CASE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 传参

  • IN 默认值 输入参数关键字 由调用者传递实参
  • OUT 输出参数，作为返回值返回
  • INOUT 即可以为输入也可以为输出
create procedure 存储过程名([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
			SQL 语句;
END$

-- 调用
call 存储过程名(@输出参数名);
-- 查询返回值
select @输出参数名
1
2
3
4
5
6
7
8
9

# 循环结构

# while 循环

[ 标注名称 :] while 判断语句 do
	循环体语句;
end while [标注名称];
1
2
3

# loop 语句

LOOP 语句可以使某些特定的语句重复执行，实现简单的循环功能。 LOOP 语句本身没有停止循环的语句，必须使用 LEAVE 语句等来停止循环并跳出循环过程。

[ 标注名称 :] LOOP
SQL 语句
END LOOP [ 标注名称 ];

-- 案例
DECLARE var INT DEFAULT 1;
num: # 标注循环开始
LOOP
    SELECT var;
    IF var >= 10
THEN LEAVE num;
    END IF;
    SET var = var + 1;
END LOOP
num;# 标注循环结束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# repeat 语句

REPEAT 语句是创建一个带条件判断的循环过程，当每次语句执行完毕后，会对条件表达式进行判断，如果表达式返回值为 TRUE ，则循环结束，否则重复执行循环中的语句。

[ 标注名称 :] REPEAT
SQL 语句
UNTIL 条件表达式
END REPEAT [ 标注名称 ];

-- 案例
DECLARE sum INT;
DECLARE num INT;
SET sum = 0;
SET num = 1;
REPEAT
    SET sum = sum + num;
    SET num = num + 1;
UNTIL num > 10 END REPEAT;# 判断 num的值是否满足循环继续条件
SELECT sum;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3 种循环的区别

  • LOOP 是一种非条件控制的循环结构，因此需要使用跳转语句结束循环。
  • WHILE/REPEAT 是条件控制的循环结构，无须使用跳转语句结束循环
  • WHILE 语句是当满足条件的情况下执行循环内的语句，否则退出循环。
  • REPEAT 语句则需要执行完循环中的语句后才对条件表达式进行判断。如果表达式返回值为 TRUE ，则循环结束，否则，继续执行循环中的语句。

# 跳转语句

# leave 语句

LEAVE 语句主要用于跳出所有被标注的流程控制构造

LEAVE 标注名称;
1

# iterate 语句

ITERATE 是 “重新开始循环” 的意思，它只能用在 LOOP 、 REPEAT 和 WHILE 语句内，用于跳过循环中余下的语句并将执行顺序转到语句段开头处，重新开始下一次循环。

iterate 标注名称;
1

# 存储函数

存储函数必须要有返回值 即有 out 参数

delimiter $
create function 函数名称(参数列表)
returns 返回值类型
[特性...]
begin
	sql语句;
	return 返回值;
end$
delimiter ;

-- 调用
select 函数名称(实际参数);
-- 查看存储函数
SHOW FUNCTION STATUS [LIKE ' 存储函数名 ']
SHOW CREATE FUNCTION 存储函数名;
-- 删除存储函数
drop function [if exists] 函数名;
-- 修改存储函数
ALTER FUNCTION 存储函数名 [特性...];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# MySQL 系统函数

MySQL 中包含了大量的系统函数，这些函数被称为内置函数或系统函数。它们是一种预定义的操作，可以在 SQL 语句中直接调用，并且提供了丰富的功能和广泛的支持。

# 字符串函数

函数名称 函数功能 应用举例 注意事项
ASCII (参数) 返回参数中第一个字符所对应的 ASCII 码。 SELECT ASCII ('MySQL'); 返回字符 “ M” 对应的数值为 77 。 如果参数为空字符串，则返回值为 0 。如果参数为 NULL ，则返回值为 NULL 。该函数常用于带有 ASCII 码值从 0 到 255 的字符。
CHAR_LENGTH (( 参数) ) 返回字符串参数所包含的字符个数。 SELECT CHAR_LENGTH ('MySQL'); 字符串 “MySQL” 所包含的字符个数为 5 。 一个多字节字符算作一个单字符。
CHARACTER_LENGTH (( 参数) ) 返回字符串参数所包含的字符个数。 SELECTCHARACTION_LENGTH ('MySQL'); 字符串 “ MySQL” 所包含的字符个数为 5 。 CHARACTER_LENGTH 函数是 CHAR_LENGTH 函数的同义词。
CONCAT (参数 1, 参数 2,..., 参数 n) 将所有参数连接成一个字符串并返回该字符串。 SELECTCONCAT ('I','Like','MySQL'); 将三个参数 “ I” 、“ Like” 和 “ MySQL” 组合成了一个字符如果有任意一个参数为 NULL ，则返回值为 NULL ； 如果变量中含有任意一个二进制字符串，则结果为一个二进制字符串。
CONCAT_WS (分 隔符，, 参数 1, 参数 2,...) 第一个参数是其他参数的分隔符。而分隔符的位置放在要连接的两个字符串之间。 SELECTCONCAT_WS ('+','I','like',NULL,'MySQL'); 使用了 “ +” 作为分隔符连接三个字符串，返回的结果为 “ I+like+MySQL” 。 如果分隔符为 NULL ，则结果也为 NULL 。CONCAT_WS 函数会忽略任何分隔符参数后的 NULL 值。
ELT (N, 参数 1, 参数 2, 参数 若 N 为 1 ，则返回值为参数 1 ；若 N 为 2 ，则返回值为参数 2 ，以此类推。 SELECT ELT (2,'a','b','c') ；返回第 2 个位置的参数 “ b”。 若 N 小于 1 或大于参数的数量，则返回值为 NULL
FIELD (参数 1, 参数 2, 参数 2...) 返回参数 1 在 (参数 2, 参数 3...) 中的位置。 SELECTFIELD ('d”','a','b','c','d”','e'); 在字符串列表中查找第一个参数 “ d”” 的位置，返回的结果为 4 。 在字符串列表中找不到参数 1 或者参数 1 为 NULL 的情况下，返回值为 0 。
FIND_IN_SET (参数 1, 参数 2) 返回在参数 2 中与参数 1 匹配的字符串的位置。 SELECTFIND_IN_SET ('My','MySQL,Like,You,My'); 返回的结果为 4 。 如果参数 1 不在参数 2 或参数 2 为空字符串，则返回值为 0 ；若任意一个参数为 NULL ，则返回值为 NULL ；这个函数在第一个参数包含一个逗号时将无法正常运行。
FORMAT (数字，N) 将数字设置为类似 “#,###,###.##” 的格式，以四舍五入的方式保留到小数点后 N 位。 SELECTFORMAT ('314159.265354',2); 将数字 314159.265354 按照 “#,###,###.##” 的格式保留了 2 位小数，并返回结果 “ 314,159.27” 。 FORMAT 函数返回的结果为字符串类型。
INSERT ( 参数 1,N, le n, 参数 将参数 1 从指定字符位置为 N 开始且长度为 len 的子字符串替换为参数 2 后组成的新字符串并返回。 SELECTINSERT ('MABCD',2,4,'ySQL'); 将字符串 “ MABCD” 从第 2 个字符开始且长度为 4 的子字符串替换为字符串 “ ySQL” ，返回结果为 “ MySQL” 。 如果 N 超过参数 1 的长度，则返回原始参数 1 。若任意一个参数为 NULL ，则返回结果为 NULL 。
INSTR (参数 1, 参数 2) 返回参数 2 在参数 1 中的开始位置。 SELECT INSTR ('MySQL','SQL'); 返回结果为 3 。 该函数支持多字节字元，并且只有当至少有一个参数是二进制字符串时区分大小写
POSITION (参数 2IN 参数 1) 返回参数 2 在参数 1 中的开始位置。 SELECT POSITION ('SQL' IN'MySQL'); 返回结果为 3 。 POSITION 函数是 LOCATE (参数 1, 参数 2) 同义词。
LCASE (参数) 将参数中的字母字符全部转换成小写字母。 SELECT LCASE ('FIRST'); 所有字母为大写的，全部转换成小写字母，如 “ FIRST” 转换为了 “ first” 。 LCASE 函数是 LOWER 函数的同义词。
LOWER (参数) 将参数中的字母字符全部转换成小写字母。 SELECT LOWER ('Second”'); 大小写混合的字符串，小写字母不变，大写字母全部变为小写字母，如 “ Second”” 转换为了 “ second”” 。 LOWER 函数是 LCASE 函数的同义词。
LEFT (参数，N) 返回参数最左边的 N 个字符。 SELECT LEFT ('MySQL',2) ：返回了左边 2 个字符 “ My” 。
RIGHT (参数，N) 返回参数最右边的 N 个字符。 SELECT RIGHT ('MySQL',3); 返回了右边 3 个字符 “SQL” 。
LPAD (参数 1, le n, 参数) 返回参数 1 ，其左边由参数 2 补全至 N 字符长度。 SELECTLPAD ('football',13,'=>'); 返回字符串 “ =>=>=football”，左侧填充 “ =>” 并让长度达到 13 。 若参数 1 的长度大于 len ，则返回值被缩短至 len 字符长度。
RPAD (参数 1,1en, 参数 2) 返回参数 1，其右边由参数 2 补全至 N 字符长度。 SELECTRPAD ('basketball',12,'<='); 返回字符串 “ basketball<=” ，其右边由参数 2 补全至 N 字符长度。 若参数 1 的长度大于 len ，则返回值被缩短至 len 字符长度。
LTRIM (参数) 返回参数，并将其左侧空格字符删除。 SELECT LTRIM (' water '); 删除其左侧所有空格并返回字符串 “ water ” 。
RTRIM (参数) 返回参数，并将其右侧空格字符删除。 SELECT RTRIM (' water '); 删除其右侧所有空格并返回字符串 “water” 。
TRIM (参数) 返回参数，并删除参数两侧的空格字符。 SELECT TRIM (' water '); 删除其两侧所有空格并返回字符串 “ water”。
LOCATE (参数 1, 参数 2) 返回参数 1 在参数 2 中第一次出现的位置。 SELECTLOCATE ('ball','football'); 返回的结果为 5 。
LOCATE (参数 1, 参数 2,N) 返回参数 1 在参数 2 中从第 N 字符开始，第一次出现的位置。 SELECTLOCATE ('ball','football',6); 返回结果为 0。 若起始位置超过查询的参数所在位置，结果返回 0 。
MAKE SET (N, 参数 1, 参数 2,..., 参数 n) 按照 N 的二进制数从参数 1 、参数 2 、 ... 、参数 n 中获取字符串。 SELECTMAKE_SET (2,'a','b','c'); 数字 2 对应的二进制数为 10 ，所以 “ MAKE_SET (2,'a','b','c')” 应选取参数 2 ，即 “ b”。 对于值为 NULL 的参数，其值不会被添加到结果中。
MID (参数，N,len) 从参数中返回一个长度为 len 的字符串子串，起始位置从第 N 个字符开始。 SELECTMID ('watermelon',6,5); 返回从第 6 个字符位置开始，长度为 5 的子字符串，结果为 “ melon” 。 MID 函数是 SUBSTRING 函数的同义词。
SUBSTRING (参数，N,len) 从参数中返回一个长度为 len 的字符串子串，起始位置从第 N 个字符开始。 SELECTSUBSTRING ('watermelon',-5,5); 返回从右数第 5 个字符位置开始，长度为 5 的子字符串，结果为 “melon” 。 若 N 的值为负数，则子字符串的位置起始于字符串结尾的第 N 个字符，而不是字符串的开头位置。
REPEAT (参数，N) 返回由 N 个参数组成的字符串。 SELECT REPEAT ('MySQL',3); 返回由 3 个重复字符串 “ MySQL” 组成的字符串 “ MySQLMySQLMySQL” 。 若 N 小于或等于 0 ，则结果为空字符串。若两个参数中含有 NULL ，则结果也为 NULL 。
REPLACE (参数 1, 参数 2, 参数 3) 使用参数 3 替代参数 1 中包含的所有参数 2 。 SELECTREPLACE ('MySQL','SQL','self'); 返回的结果是 “ Myself” 。 若所有字符串均相同，则返回 0 ；若参数 1 中某个字符小于参数 2 对应位置的字符，则返回 -1 ；否则返回 1 。
SPACE (N) 返回一个由 N 个空格字符组成的字符串。 SELECT SPACE (6); 返回了一个由 6 个空格组成的字符串。
STRCMP (参数 1, 参数 2） 用来比较两个字符串。 SELECT STRCMP ('acb','abc'); 所以返回结果为 1 。

# 数学函数

image-20231020092255692

image-20231020092332218

image-20231020092350199

image-20231020092401418

# 日期和时间函数

image-20231020092437529

image-20231020092452285

image-20231020092508234

image-20231020092519780

image-20231020092529169

image-20231020092543653

image-20231020092554112

image-20231020092610834

image-20231020092625933

# 日期和时间的格式

日期和时间的格式如下表所示，其中 TYPE 表示需要使用的格式类型，后面跟着的是预期的参数格式。

image-20231020094001036

image-20231020094019669

# format 的日期时间格式

image-20231020094051634

image-20231020094117059

# GET_FORMAT 返回的日期格式

该函数接受两个参数，第一个参数是数据类型，指定要格式化的日期或时间类型，第二个参数是格式类型，指定所需的日期或时间格式。

image-20231020094214956

image-20231020094224455

# 统计函数

image-20231020094249251

# 流程控制函数

# if

如果表达式中参数 1 的值为 TRUE ，则 IF 函数的返回值为参数 2 ；否则返回值为参数 3 。

if(参数1,参数2,参数3);
-- 案例
SELECT IF(1>2,'YES','NO');
1
2
3
# case
CASE 条件表达式
WHEN [参数表达式1] THEN 结果1
[WHEN[参数表达式2] THEN 结果2..]
[ELSE 结果3]
END;
-- 案例
SELECT CASE ' 男 '
WHEN ' 男 ' THEN 'BOY'
WHEN ' 女 ' THEN 'GIRL'
END;
1
2
3
4
5
6
7
8
9
10
# ifnull

若表达式中参数 1 的值不为 NULL ，则 IFNULL 函数的返回值为参数 1; 否则其返回值为参数 2

IFNULL(参数1,参数2);
1
# nullif

若表达式中参数 1 与参数 2 取值相同，则 NULLIF 函数的返回值为 NULL; 否则其返回值为参数 1 。

NULLIF(参数1,参数2);
1
编辑 (opens new window)
上次更新: 2025/01/01, 10:09:39
备份
触发器

← 触发器→

最近更新
01
k8s
06-06
02
进程与线程
03-04
03
计算机操作系统概述
02-26
更多文章>
Theme by Vdoing | Copyright © 2022-2025 Iekr | Blog