Posted 2020-08-31Development / SQL10 minutes read (About 1560 words)

Study Notes of MySQL 3 —— Variables, Procedures and Functions

Variables
Stored Procedures and Functions
Control Flow Functions

[toc]

Variables

系统变量

/*
分类：
	全局变量：作用域是全局设置
	会话变量：作用域仅仅是当前连接（会话）
注意：
	如果全局级别，则需要加 global，如果是会话级别，则需要加session，如果不写，则默认
*/

# 查看所有的系统变量
show global variables;

# 查看满足条件的部分系统变量
show global variables likee '%char%';

# 查看指定的某个系统变量的值
select @@global.系统变量名;

# 为某个系统变量赋值
set global.系统变量名 = 值;
set @@global.系统变量名 = 值;

自定义变量

说明：变量是用户自定义的，不是由系统提供的
分类
- 用户变量：作用域针对当前会话有效，等同于会话变量的作用域
- 局部变量：仅仅在 begin end 中有效
使用步骤
- 声明
- 赋值
- 使用（查看，比较，运算）

用户变量

# 声明并初始化
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

# 赋值
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;
SELECT 字段 INTO 变量名 FROM 表;

# 使用：查看用户变量的值
SELECT @用户变量名


/*案例*/
# 声明
SET @count=1;
# 赋值
SELECT COUNT(*) INTO @count
FROM employees;
# 查看
SELECT @count;

局部变量

应用在 begin end 中的第一句话

# 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
# 赋值同上
# 使用同上

Stored Procedures and Functions 存储过程和函数

存储过程的使用

含义：一组预先编译好的 SQL 语句的集合，理解成批处理语句
减少了编译次数并且减少了和数据库服务器连接的次数，提高了效率
参数列表包含三部分：参数模式参数名参数类型
参数模式
- IN：该参数可以作为输入，也就是该参数需要调用方传入值
- OUT：该参数可以作为输出，也就是该参数可以作为返回值
- INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

# 创建语法
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
	存储过程体（一组合法的 SQL 语句）
END

# 参数列表
IN stuname VARCHAR(20)

# 调用语法
CALL 存储过程名（实参列表）;


# 判断用户是否登录成功（IN）
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; # 声明并初始化
	
	SELECT COUNT(*) INTO result # 赋值
	FROM admin
	WHERE admin.username = username
	AND admin.PASSWORD = PASSWORD;
	
	SELECT IF(result>0,'Success','Fail'); # 使用
END $

CALL myp3('Tim Duncan','000') $

# 根据输入的女生名，返回对应男生的名字和魅力值（OUT）
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT usercp INT)
BEGIN
	SELECT boys.boyname, boys.usercp INTO boyname, usercp
	FROM boys
	RIGHT JOIN beauty b 
	ON b.boyfriend_id = boys.id
	WHERE b.name beautyName;
END $

CALL myp('小昭', @name, @cp)$
SELECT @name, @cp;


# 创建带INOUT模式参数的存储过程
# 传入 a b 两个值，最终 a b 都翻倍并返回
CREATE PROCEDURE myp3(INOUT a INT, INOUT b INT)
BEGIN
    SET a = a * 2
    SET b = b * 2;
END

# 调用
SET @m=10$
SET @n=20$
CALL myp3(@m,@n)$
SELECT @m,@n

# 存储过程的删除：drop procedure 存储过程名
DROP PROCEDURE p1;

# 存储过程的查看：show create procedure 存储过程名
SHOW CREATE PROCEDURE myp2;

函数

含义：一组预先编译好的 SQL 语句的集合，理解成批处理语句
与存储过程的区别
- 存储过程：可以有 0 个返回，也可以有多个返回，合适做批处理插入，批处理更新
- 函数：有且仅有 1 个返回，合适做处理数据后返回一个结果

# 语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

# 调用
SELECT 函数名(参数列表)

# 查看函数
SHOW CREATE FUNCTION myf;

# 删除函数
DROP FUNCTION myf;

Control Flow Functions 流程控制结构

分支结构

IF

1	IF(表达式1, 表达式2, 表达式3) # 表达式1成立，则返回表达式2，否则返回表达式3

CASE

特点
- 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
- 可以作为独立的语句去使用，放在 BEGIN END 中

# 实现等值判断：类似于 JAVA 中的 SWITCH
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1 或者 语句1;
WHEN 要判断的值 THEN 返回的值2 或者 语句2;
ELSE 要返回的值n;
END CASE;

# 实现区间判断：类似于 JAVA 中的 多重 IF
CASE 
WHEN 要判断的条件1 THEN 返回的值1 或者 语句1;
WHEN 要判断的条件2 THEN 返回的值2 或者 语句2;
ELSE 要判断的条件n;
END CASE;

IF 结构

1
2
3

IF 条件1 THEN 执行语句1;
ELSEIF 条件2 THEN 执行语句2;
ELSE 语句n;

循环结构

循环主体

while：先判断后执行
repeat：先执行后判断
loop：没有条件就是死循环

# while
[标签:] WHILE 循环条件 DO
	循环体;
END WHILE [标签];

# loop
[标签:] LOOP
	循环体;
END LOOP [标签]

# repeat
[标签:] REPEAT
	循环体;
UNTIL 结束循环的条件
END REPEAT [标签]

# 批量插入：根据插入次数到 admin 表中多条记录
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE 1 < insertCount DO
		INSERT INTO admin(username, 'password') VALUES (CONCAT('Rose', 1), '666');
		SET i = i + 1;
	END WHILE
END $

CALL pro_while(100)$

循环控制

leave：类似于 break，用于跳出所在循环
iterate：类似于 continue，用于结束本次循环，继续下一次

# leave
# 批量插入：根据插入次数到 admin 表中多条记录，如果次数大于 20 则停止
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a: WHILE 1 < insertCount DO
		INSERT INTO admin(username, 'password') VALUES (CONCAT('Rose', 1), '666');
		IF i >= 20 THEN LEAVE a;
		END IF;
		SET i = i + 1;
	END WHILE a;
END $

# iterate
# 批量插入：根据插入次数到 admin 表中多条记录，只插入偶数次
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a: WHILE 1 < insertCount DO
		SET i = i + 1;
		IF MOD(i,2) != 0 THEN ITERATE a;
		END IF
		INSERT INTO admin(username, 'password') VALUES (CONCAT('Rose', 1), '666');
	END WHILE a;
END $