变量、流程控制与游标

变量

系统变量

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

查看系统变量

# 变量：系统变量（全局系统变量、会话变量）vs 用户自定义变量
# 查看系统变量
# 查看所有全局变量
show global variables ;
# 查看所有会话变量
show session variables ;
# 或者
show variables ;
# 查看满足条件的部分系统变量
show global variables like '%标识符%';
show global variables like 'admin_%';
# 查看满足条件的部分会话变量
show session variables like '%标识符%';
show session variables like '%admin_%'

查看指定系统变量

作为mysql编码规范，MySQL中的系统变量以两个@@开头，其中@@global仅用于标记全局系统变量，@@session仅用于标记会话中的系统变量。@@首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量

# 查看指定系统变量的值
select @@global.变量名;
# 查看指定的会话变量的值
select @@session.变量名;
# 或者
select @@变量名;

# 查看指定系统变量的值
-- 查看全局最大连接数
select @@global.max_connections;
# 查看指定的会话变量的值
-- 当前会话的mysql连接的id
select @@session.pseudo_thread_id;
# 或者
select @@pseudo_thread_id;

修改系统变量的值

# 修改系统变量的值
# 方式1
set @@global.max_connections = 161;
# 方式2
set global max_connections  =171;
# 全局系统变量:针对于当前的数据库实例是有效的，一旦重启mysql服务，就失效了
select @@global.max_connections;
# 会话系统变量
# 方式1:
set @@session.character_set_client='gbk';
# 方式2:
set session character_set_client ='gbk';
# 针对于当前会话是有效的，一旦结束会话，重新建立起新的会话，就失效了

用户变量

用户变量分类

会话用户变量

会话变量不需要指定类型
定义

# 用户变量
/**
  1.用户变量：会话用户变量vs局部变量
  2.会话用户变量：使用@开头，作用域为当前会话
  3.局部变量：只能使用在存储过程和存储函数中的
 */
# 会话用户变量
# 方式1
set @m1 =1;
set @m2 :=2;
set @sum :=@m1+@m2;
select @sum;
# 方式2
select count(1) into @count from emp;
select @count;
# 查看某个未声明的变量时，将得到null值
select @big;

局部变量

# 定义变量
# declare 变量名 类型[default 值];# 如果没有default子句，初始值为null
declare myparam int default 100;
# 变量赋值
# 方式1：一般用于赋简单的值
set 变量名 = 值;
set 变量名 :=值;
# 方式2：一般用于赋表中的字段值
select 字段名或表达式 into 变量名 from 表;
# 使用变量 (查看、比较、运算等)
select 局部变量名;

# 局部变量必须满足
/*
局部变量必须 1.使用declare声明 2.声明并使用在begin..end中(使用在存储过程 ,函数中)
declare的方式声明的局部变量必须声明在begin中的首行的位置
*/
# 声明局部变量，分别赋值
delimiter $
create procedure test_var()
begin
#     声明局部变量
    declare a int default 0;
    declare b int;
#  declare a,b int default 0;
    declare emp_name varchar(25);
#     赋值
    set a=1;
    set b :=2;
select ename into emp_name from emp where empno=7499;
# 使用
    select a,b,emp_name;
end $
delimiter ;

# 调用存储过程
call test_var();

定义条件与处理程序

# 定义条件
# 格式 declare 错误名称  conition for 错误码(或错误条件)
# 例子：定义'Field_Not_Be_NULL'错误名与MySQL中违反非空约束的错误类型是'ERROR 1048（23000)'对应
# 方式1 使用MySQL_error_code
declare Field_Not_Be_NULL condition for 1048;
# 方式2：使用sqlstate_value
declare Field_Not_Be_NULL condition for SQLSTATE '2300';

定义处理程序

# 定义处理程序
# 格式：declare 处理方式 handler for 错误类型 处理语句
# 举例
# 方法1：捕获sqlstate value
declare continue handler for sqlstate '42s02' set @info='NO_SUCH_TABLE';
# 方法2:捕获mysql_error_value
declare continue handler for 1146 set @info='NO_SUCH_TABLE';
# 方法3 先定义条件，再调用
declare no_such_table continue for 1146;
declare continue handler for NO_SUCH_TABLE set @info='NO_SUCH_TABLE';

# 方法4:使用SQLWARNING
declare exit handler for sqlwarning set @info='NO_SUCH_TABLE';
# 方法5：使用not found
declare exit handler for not found set @info='NO_SUCH_TABLE';

# 方法6 使用sqlexception
declare exit handler for sqlexception set @info='ERROR';

流程控制

分支结构之if

# 分支结构之if
delimiter $
create procedure test_if()
begin
#     声明局部变量
    declare stu_name varchar(15);
    if stu_name is null
        then select 'stu_name is null';
    end if;
end $
delimiter ;
# 调用
call test_if();

# 分支结构之if
delimiter $
create procedure test_if2()
begin
# 二选一
#     声明局部变量
    declare email varchar(15);
    if email is null
        then select 'email is null';
    else
        select 'email is not null';
    end if;
end $
delimiter ;
# 调用
call test_if2();

# 分支结构之if
delimiter $
create procedure test_if3()
begin
#     多选1
#     声明局部变量
    declare age int default 20;
    if age > 40
    then
        select '中老年';
    elseif age > 18
    then
        select '青壮年';
    elseif age > 8
    then
        select '青少年';
    else
        select '婴幼儿';
    end if;

end $
delimiter ;
# 调用
call test_if3();

# 声明存储过程update_salary_by_id,定义in参数emp_id,输入员工编号
# 判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元，否则就不变
delimiter $
create procedure update_salary_by_id(in emp_id int)
begin
    #声明局部变量
    declare emp_sal double;
    declare hire_date date;
    declare hire_year double;
    # 赋值
    select sal into emp_sal from emp where empno = emp_id;
    select hiredate into hire_date from emp where empno=emp_id;
    select datediff(current_date(),hire_date)/365 into hire_year;
    if emp_sal <8000 and  hire_year>5 then
        update emp set sal = sal+5000 where empno=emp_id;
    end if;
end $
delimiter ;

# 调用存储过程
call update_salary_by_id(7369);
# 查询数据
select * from emp where empno=7369;

分支结构之CASE

Case语句的语法结构1

# 情况1：类似switch
case 表达式
when 值1 then 结果1或语句1(如果是语句，需要加分号)
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n语句n(如果是语句，需要加分号)
end [case](如果是放在begin end中需要加上case,如果放在select后面不需要)

Case语句的语法结构2

# 情况2：类似多重if
case
when 值1 then 结果1或语句1(如果是语句，需要加分号)
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n语句n(如果是语句，需要加分号)
end [case](如果是放在begin end中需要加上case,如果放在select后面不需要)

例子1

# 分支结构之case
delimiter $
create procedure test_case()
begin
    # 演示1：case ... when ... then...
    declare var int default 2;
    case var
        when 1 then select 'var=1';
        when 2 then select 'var=2';
        when 3 then select 'var=3';
        else select 'other value';
        end case;
end $
delimiter ;
# 存储过程调用
call test_case();

例子2

# 分支结构之case
delimiter $
create procedure test_case2()
begin
    # 演示2：case ... when ... then...
    declare var int default 10;
    case
        when var >= 100 then select '三位数';
        when var >= 10 then select '两位数';
        else select '个位数';
        end case;
end $
delimiter ;
# 存储过程调用
call test_case2();

# 例子3
# 声明存储过程update_salary_by_empno,定义in参数emp_id,输入员工编号
# 判断该员工薪资如果低于9000元，就更新薪资为9000元;薪资大于等于9000元且低于10000的
# 但是奖金比例为null的，就更新奖金比例为0.01,其他的涨薪100元
delimiter $
create procedure update_salary_by_empno(in emp_id int)
begin
    # 局部变量的声明
    declare emp_sal double;#记录员工的工资
    declare bonus double;#记录员工的奖金率
    # 局部变量的赋值
    select sal into emp_sal from emp where empno=emp_id;
    select comm into bonus from emp where empno=emp_id;
    case
        when emp_sal < 9000 then update emp set sal = 9000 where empno = emp_id;
        when emp_sal < 10000 and bonus is null then update emp set comm = 0.01 where empno = emp_id;
        else
            update emp set sal=sal + 100 where empno = emp_id;
    end case;
end $
delimiter ;
# 调用存储过程
call update_salary_by_empno(7369);

循环结构之Loop

#循环结构-Loop
# 使用Loop语句进行循环操作，id值小于10时将重复执行循环过程
delimiter $
create procedure test_loop()
begin
    declare id int default 0;
    add_loop:loop
        set id = id + 1;
        if id >= 10
            then leave add_loop;
        end if;
    end loop add_loop;
    # 查看 id
    select id;
end $
delimiter ;

# 存储过程调用
call test_loop(); -- 输出 id=10

循环结构之while

while语句创建一个带条件判断的循环过程，while在执行语句时，先对指定的表达式进行判断，如果为真，就执行循环体的语句，否则退出循环。while语句的基本格式如下

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

while_label为while语句的标注名称；如果循环条件结果为真，while语句内的语句或语句群被执行，且主循环条件为假，退出循环。

# 循环结构之while
/*
[while_label:]while 循环条件 do
循环体
end whil[while_label];
*/
delimiter //
create procedure test_while()
begin
    # 初始化条件
    declare num int default 1;
    while num<=10 do
        # 循环体(略)
        # 迭代条件
        set num = num+1;
    end while;
    select num;
end //
delimiter ;
# 调用存储过程
call test_while();
/*
凡是循环结构，一定具备4个要素：
1.初始化条件
2.循环条件
3.循环体
4.迭代条件
*/

循环体结构之repeat

# 循环结构之repeat
/*
[repeat_label:] repeat
循环体的语句
until 结束循环的条件表达式
end repeat [repeat_label]
*/
delimiter //
create procedure test_repeat()
begin
    #声明变量
    declare num int default 1;
    repeat
        set num=num+1;
    until num>=10
    end repeat ;
    # 查看num
    select num;
end //
delimiter ;

# 调用
call test_repeat();

跳转语句之Leave语句

# Leave的使用
/*
  创建存储过程leave_begin，声明int类型的In参数num。给begin...end加标记
  并在begin... end中使用if语句判断num参数的值
  如果num<=0,则使用leave语句退出begin...end;
  如果num=1,则查询emp表中的平均薪资
  如果num=2,则查询emp表中的最低薪资
  如果num>2,则查询emp表中的最高薪资
  if语句结束后查询emp表的总人数
 */
delimiter //
create procedure leave_begin(in num int)
begin_label:begin
    if num<=0
        then leave begin_label;
    elseif num=1
        then select avg(sal) avgSal from emp;
    elseif num=2
        then select min(sal) minSal from emp;
    else
        select max(sal) maxSal from emp;
    end if;
    # 查询总人数
    select count(1) zs from emp;
end //
delimiter ;

# 调用存储过程
call leave_begin(1);
call leave_begin(2);
call leave_begin(3);

跳转语句之Iterate语句

# 跳转语句之Iterate语句
# 定义局部变量num,初始值为0。循环结构中执行num+1操作
# 如果num<10,则继续执行循环
# 如果num>15,则退出循环结构
delimiter //
create procedure test_iterate()
begin
    declare num int default 0;
    my_loop:loop
        set num = num + 1;
        if num < 10
        then
            iterate my_loop;
        elseif num > 15
        then
            leave my_loop;
        end if;
        select num;
    end loop my_loop;
end //
delimiter ;
# 调用存储过程
call test_iterate();

游标

使用游标步骤

# 游标
/*
    游标使用的步骤：
    1.声明游标
    2.打开游标
    3.使用游标(从游标中获取数据)
    4.关闭游标
*/
# 创建存储过程get_count_by_limit_total_salary(),声明in参数limit_total_salary double类型，
# 声明out参数total_count in 类型，函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limt_total_salary参数的值
# 返回累加的人数给total_count
delimiter //
create procedure get_count_by_limit_total_salary(in limit_total_salary double,out total_count int)
begin
    # 定义局部变量
    declare sum_sal double default 0.0;# 记录累加的工资总和
    declare emp_sal double;# 记录每一个员工的工资
    declare emp_count int default 0;#记录累加的人数
    # 声明游标
    declare emp_cursor cursor for select sal from emp order by sal desc;
    # 打开游标
    open emp_cursor;
    loop_label:loop
        # 使用游标
        fetch emp_cursor into emp_sal;
        set sum_sal=sum_sal+emp_sal;
        set emp_count=emp_count+1;
        if sum_sal >= limit_total_salary
            then leave loop_label;
        end if;
    end loop ;
    # 关闭游标
    close emp_cursor;
    set total_count = emp_count;
end //
delimiter ;

# 调用存储过程
call get_count_by_limit_total_salary(200000,@total_count);
select @total_count;