MySQL进阶

• 用户定义变量

 赋值

#赋值
set @myname = 'itcast';
set @muage :=10;
set @mygender :='男',@myhobby := 'java';

set @mycolor := 'red';
select count(*) into @mycount from tb_user;

使用

#使用
select @myname,@myage,@mygender,@myhobby;

注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

• 局部变量

 声明

赋值

#声明
#赋值
create procedure p2();
begin
     declare stu_count int default 0;
     (set stu_count := 100;)赋值
     select count(*) into stu_count from student;赋值
     select stu_count;
end;

call p2();

 4、if判断

create procedure p3()
begin
     declare score int default 58;
     declare result varchar(10);
     if score >= 85 then
        set result := '优秀';
     elseif score >= 60 then
        set result := '及格';
     else
        set result := '不及格';
     end if;
     select result;
end;

call p3();

5、参数

create procedure p4(in score int, out result varchar(10))
begin
     if score >= 85 then
        set result := '优秀';
     elseif score >= 60 then
        set result := '及格';
     else
        set result := '不及格';
     end if;
end;

call p4(68,@result);
select @result;

6、case

 7、循环

• while

A定义局部变量，记录累加之后的值
B每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p7(in n int)
begin
     declare total int default 0;
     while n>0 do
          set total := total + n;
          set n := n-1;
     end while;
     select total;
end;

call p7(100);

• repeat

create procedure p8(in n int)
begin
     declare total int default 0;
     repeat
        set total := total +n;
        set n := n-1;
     until n<=0
     end repeat;
     select total;
end;

call p8(10);
call p8(100);

• loop

A定义局部变量，记录累加之后的值
B每循环一次，就会对n进行减1，如果n减到0，则退出循环-->leave xx

1、create procedure p9(in n int)
begin
     declare total int default 0;
     sum:loop
         if n<=0 then
            leave sum;
         end if;
         set total := total + n;
         set n := n-1;
     end loop sum;
     select total;
end;

call p9(10);

A定义局部变量，记录累加之后的值
B每循环一次，就会对n进行减1，如果n减到0，则退出循环-->leave xx
C如果当次累加的数量是奇数，则直接进入下一次循环。--->iterate xx
2、create procedure p10(in n int)
begin
     declare total int default 0;
     sum:loop
         if n<=0 then
            leave sum;
         end if;
         if n%2 =1 then
            set n:=n-1;
            iterate sum;
         end if;

         set total := total + n;
         set n := n-1;
     end loop sum;

     select total;
end;

call p10(10);

8、游标

9、条件处理程序

 案例

逻辑：
A 声明游标，存储查询结果集
B 准备：创建表结构
C 开启游标
D 获取游标中的记录
E 插入数据到新表中
F 关闭游标

create procedure p11(in uage int)
begin
     declare uname varchar(100);
     declare upro varchar(100);
     定义游标存储符合条件的结果集
     declare u_cursor cursor for select name,profession from tb_user where age <= uage;
     声明条件处理程序
     delare exit handler for SQLSTATE '02000' close u_cursor;状态码为02000时，执行退出游标操作

     drop table if exists tb_user_pro;清除已经存在的表
     create table if exists tb_user_pro(
          id int primary key auto_increment,
          name varchar(100),
          profession varchar(100)
     );

     open u_cursor;开启游标
     while true do 循环获取游标中的数据
          fetch u_cursor into uname,upro;
          insert into tb_user_pro values (null,uname,upro);
     end while;
     close u_cursor;关闭游标
end;

call p11(30);

 10、存储函数

案例

#计算从1累加到n的值，n为传入的参数值

create function fun1(n int)
returns int deterministic
begin
     declare total int default 0;通过total记录累加结果
     
     while n>0 do
         set total := total +n;
         set n := n-1；
     end while;
     
     return total;
end;

select fun1(100);

注：能够使用存储函数的地方也可以使用存储过程替代。