MySQL的变量、存储过程、存储函数以及循环控制结构

变量

系统变量

系统变量：

全局变量
会话变量

说明：变量由系统定义，不是用户定义，属于服务器层面

注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
全局变量作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启（重启之后会恢复到你修改前的状态，修改要永久生效，需要修改配置文件）

查看所有系统变量

# 全局
show global variables;

#会话
show [session] variables;

查看满足条件的部分系统变量

show global | [session] variables like '%char%';

查看指定的系统变量的值（带.）

select @@global | [@@session].系统变量名;

为某个系统变量赋值

方式一：

set global | [session]系统变量名=值;

方式二（带.）：

set @@global | [@@session].系统变量名=值;

例如：

# 查看所有全局变量
SHOW GLOBAL VARIABLES;
# 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
# 查看指定的系统变量的值
SELECT @@global.autocommit;
# 为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

# 查看所有会话变量
SHOW SESSION VARIABLES;
# 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
# 查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
# 为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

自定义变量

变量由用户自定义，而不是系统提供的

作用域：针对于当前会话（连接）有效，作用域同于会话变量

声明并初始化

赋值操作符：=或:=

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

更新变量的值

方式一

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

方式二

# 将某个字段的值赋给 自定义变量
SELECT 字段 INTO @变量名
FROM 表;

查看变量的值

SELECT @变量名;

局部变量

作用域：仅仅在定义它的begin end块中有效

应在begin end中的第一句话声明

声明

DECLARE
    变量名 类型;
    ...
END

# 值的类型要和前面声明的类型一致
DECLARE 
    变量名 类型 [DEFAULT 值];
    ...
END

赋值（更新变量的值）

方式一

SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;

方式二

SELECT 字段 INTO 局部变量名
FROM 表;

查看变量的值

SELECT 局部变量名;

用户变量和局部变量的区别

变量	作用域	定义和使用的位置	语法
自定义变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	BEGIN END中	只能在BEGIN END中，且为第一句话	一般不用加@符号，需要限定类型

案例

声明两个用户（自定义）变量，求和并打印
```
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
```

存储过程

什么是存储过程：

事先经过编译并存储在数据库中的一段sql语句的集合。

类似于java中的方法

使用好处

简化应用开发人员的很多工作
减少数据在数据库和应用服务器之间的传输
提高了数据处理的效率
一组预先编译好的SQL语句的集合，理解成批处理语句

创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN

    存储过程体（一组合法的SQL语句）
END

参数列表包含三部分

参数模式
参数名
参数类型

例：in stuname varchar(20)

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值

out：该参数可以作为输出，也就是该参数可以作为返回值

inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用delimiter重新设置（避免与存储过程体的分号混淆）
```
delimiter 结束标记
# 例
delimiter $
```

例：

插入到admin表中五条记录

USE girls;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`) 
    VALUES
    ('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $

调用语法

CALL 存储过程名(实参列表);

CALL myp1();
# 存储过程定义完结束标记$，那么此处的结束标记不用`;`，使用$
CALL myp1()$

创建带in模式参数的存储过程

创建存储过程实现根据女神名，查询对应的男神信息

DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name=beautyName;
END $

CALL myp2('赵敏');

创建存储过程实现，判断用户是否登录成功

DELIMITER $
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,'成功','失败');    #使用
END $

CALL myp3('john','8888');
CALL myp3('john','1234');

创建out模式参数的存储过程

根据输入的女神名，返回对应的男神名

DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyname INTO boyname
    FROM boys bo
    RIGHT JOIN
    beauty b ON b.boyfriend_id = bo.id
    WHERE b.name=beautyName ;
END $

# 调用 使用自定义变量接收
CALL myp4('赵敏',@name);
SELECT @name;

根据输入的女神名，返回对应的男神名和魅力值

DELIMITER $
CREATE PROCEDURE myp5(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 myp5('小昭',@name,@cp);
SELECT @name,@cp;

创建带inout模式参数的存储过程

传入a和b两个值，最终a和b都翻倍并返回

DELIMITER $
CREATE OR REPLACE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN

    SET a:=a*2;
    SET b:=b*2;

END $

SET @m=10;
SET @n=20;
CALL myp5(@m,@n);
SELECT @m,@n;

存储过程的删除

语法：

# 存储过程一次只能删除一个
DROP PROCEDURE 存储过程名;

查看存储过程的信息

SHOW CREATE PROCEDURE myp5;

存储函数

存储过程与存储函数的区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

存储函数：有且仅有1 个返回，适合做处理数据后返回一个结果

创建语法

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

参数列表包含两部分：参数名，参数类型
函数体：
- 肯定会有return语句，如果没有会报错如果return语句没有放在函数体的最后也不报错，但不建议
- 函数体中仅有一句话，则可以省略begin end
使用delimiter语句设置结束标记

调用语法

SELECT 函数名(参数列表);

无参有返回

返回公司的员工个数

USE myemployees;
DELIMITER //
CREATE FUNCTION myf1() RETURNS INT
BEGIN

    DECLARE c INT DEFAULT 0;    #定义局部变量
    SELECT COUNT(*) INTO c     #赋值
    FROM employees;
    RETURN c;

END //

SELECT myf1();

有参有返回

根据员工名，返回它的工资

DELIMITER //
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal=0;    #定义用户变量 
    SELECT salary INTO @sal    #赋值
    FROM employees
    WHERE last_name = empName;

    RETURN @sal;

END //

SELECT myf2('Kochhar');

SELECT @sal;

根据部门名，返回该部门的平均工资

DELIMITER //
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE ;
    SELECT AVG(salary) INTO sal
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name=deptName;
    RETURN sal;
END //

SELECT myf3('IT');

查看函数

SHOW CREATE FUNCTION myf3;

删除函数

DROP FUNCTION myf3;

创建函数，实现传入两个float，返回二者之和

DELIMITER //
CREATE OR REPLACE FUNCTION myf3(num1 FLOAT,num2 FLOAT) RETURNS FLOAT(3,1)
BEGIN
    DECLARE SUM FLOAT(3,1) DEFAULT 0;
    SET SUM=num1+num2;
    RETURN SUM;
END //

SELECT myf3(15.5,15.3);    --> 30.8

流程控制结构

分支结构

if函数

语法：if(条件,值1，值2)

功能：实现双分支

应用在begin end中或外面

case结构

类似java中的switch

功能：实现多分支

应用在begin end中或外面

语法一：

case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2；
...
else 语句n;
end [case];

语法二：

case 
when 条件1 then 语句1;
when 条件2 then 语句2；
...
else 语句n;
end [case];

创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

DELIMITER //
CREATE OR REPLACE FUNCTION test_case(score DOUBLE) RETURNS CHAR
BEGIN
-- DECLARE result CHAR;
CASE  
    WHEN score>90 THEN
        RETURN 'A';
    WHEN score>80 THEN 
        RETURN 'B';
    WHEN score>60 THEN
        RETURN 'C';
    ELSE
        RETURN 'D';
END CASE;

END //

SELECT test_case(95);

SELECT test_case(85);

SELECT test_case(75);

SELECT test_case(55);

if结构

功能：实现多分支

只能放在begin end中

语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;

创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

DELIMITER //
CREATE OR REPLACE FUNCTION test_if(score DOUBLE) RETURNS CHAR 
BEGIN 
    DECLARE result CHAR;
    IF score>90 THEN
        SET result:='A';
    ELSEIF score>80 THEN
        SET result:='B';
    ELSEIF score>60 THEN
        SET result:='C';
    ELSE
        SET result:='D';
    END IF;
    RETURN result;
END //

SELECT test_if(95);

SELECT test_if(85);

SELECT test_if(75);

SELECT test_if(55);

创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500

DELIMITER //
CREATE OR REPLACE PROCEDURE test_if2(IN salary DOUBLE)
BEGIN

    IF salary<2000 THEN
            DELETE FROM employees WHERE employees.salary=salary;
    ELSEIF 2000<salary<5000 THEN 
            UPDATE employees SET employees.salary=employees.salary+1000 WHERE employees.salary=salary; 
    ELSE
            UPDATE employees SET employees.salary=employees.salary+500 WHERE employees.salary=salary; 
    END IF;
END //

CALL test_if2(3300.00);

循环结构

分类：while、loop、repeat

循环控制：

iterate类似于 continue，继续，结束本次循环，继续下一次

leave 类似于 break，跳出，结束当前所在的循环

只能放在begin end中

标签：用上循环控制就必须要加

while

[标签:] while 循环条件 do
    循环体;
end while [标签];

批量插入，根据次数插入到admin表中多条记录

USE girls;

DELIMITER //
CREATE OR REPLACE PROCEDURE test_while(IN total INT)
BEGIN

DECLARE i INT DEFAULT 0;

WHILE i<total DO
    INSERT INTO admin VALUES(NULL,CONCAT('员工',i),'8888');
    SET i:=i+1;
END WHILE;

END //

CALL test_while(10);

批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止（需要加标签）

DELIMITER //
CREATE OR REPLACE PROCEDURE test_while(IN total INT)
BEGIN

DECLARE i INT DEFAULT 1;

a:WHILE i<=total DO
    IF i>20 THEN LEAVE a;
    END IF;
    INSERT INTO admin VALUES(NULL,CONCAT('员工',i),'8888');
    SET i:=i+1;
END WHILE a;

END //

TRUNCATE TABLE admin;
CALL test_while(100);

批量插入，根据次数插入到admin表中多条记录，只插入偶数次

DELIMITER //
CREATE OR REPLACE PROCEDURE test_while(IN total INT)
BEGIN

DECLARE i INT DEFAULT 0;

a:WHILE i<=total DO
    SET i:=i+1;
    IF MOD(i,2)!=0 THEN ITERATE a;
    END IF;
    INSERT INTO admin VALUES(NULL,CONCAT('员工',i),'8888');

END WHILE a;

END //

TRUNCATE TABLE admin;
CALL test_while(20);

loop

[标签:] loop

    循环体;

end loop [标签];

repeat

[标签:] repeat
    循环体;
until 结束循环的条件
end repeat [标签];

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 230,825评论 6赞 546
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 99,814评论 3赞 429
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 178,980评论 0赞 384
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 64,064评论 1赞 319
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 72,779评论 6赞 414
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 56,109评论 1赞 330
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 44,099评论 3赞 450
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 43,287评论 0赞 291
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 49,799评论 1赞 338
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 41,515评论 3赞 361
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 43,750评论 1赞 375
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 39,221评论 5赞 365
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 44,933评论 3赞 351
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 35,327评论 0赞 28
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 36,667评论 1赞 296
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 52,492评论 3赞 400
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 48,703评论 2赞 380

MySQL的变量、存储过程、存储函数以及循环控制结构

变量

系统变量

自定义变量

局部变量

案例

存储过程

创建语法

调用语法

创建带in模式参数的 存储过程

创建out模式参数的存储过程

创建带inout模式参数的存储过程

存储过程的删除

查看存储过程的信息

存储函数

存储过程与存储函数的区别

创建语法

调用语法

无参有返回

有参有返回

查看函数

删除函数

流程控制结构

分支结构

if函数

case结构

if结构

循环结构

while

loop

repeat

推荐阅读更多精彩内容

创建带in模式参数的存储过程