Mysql存储-变量、函数、游标、判断、循环

存储过程(procedure)

1、介绍：
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用
2、特点
封装、复用
可以接收参数，也可以返回数据
减少网络交互，效率提升
3、基本语法
创建存储

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
   -- SQL语句
END;

调用存储

call 存储过程名称

查看存储

-- 查看存储
-- 查看切换到指定库中指定存储创建具体信息
show create procedure  p1;
-- 查看库中所有存储
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='test';

删除存储

DROP PROCEDURE [IF EXISTS] 存储过程名称;

案例

-- 创建存储
create procedure p4()
begin
    declare name_sto int;-- 定义变量
    select count(*) into name_sto from emp; -- 查询数据并且赋值给定义变量 into 变量名：数据来源复赋值变量
end;
-- 调用存储
call p4();
-- 查看存储
show create procedure  p4;
-- 删除存储
drop procedure p4;

变量

1、系统变量

介绍：
系统变量时MySQL服务器提供，不是用户定义的，属于服务器层面。
可分为：全局变量（GLOBAL）、会话变量（SESSION）。
全局变量在所有会话中有效，会话变量仅在当前会话中有效

查看系统变量：

-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;

-- 可以通过like模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';

-- 查看指定变量的值
SELECT @@[SESSION. | GLOBAL.] 系统变量名;

设置系统变量：

SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION. | GLOBAL.]系统变量名 = 值;

案例：

-- 变量：系统变量
-- 查看系统变量
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;

-- 设置系统变量
set session autocommit = 1;
set global autocommit = 1;
set @@global.autocommit = 1;

总结：mysql系统自带的变量可以通过系统变量的需求进行查看和设置

2、用户自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接（会话）。
语法：
赋值

SET @var_name = expr[, @var_name = expr] ...;
SET @var_name := expr[, @var_name := expr] ...;

SELECT @var_name := expr[, @var_name := expr] ...;
SELECT 字段名 INTO @var_name FROM 表名;

调用：
select @var_name

案例：

set @myname = 'itcast'; -- 自定义变量
set @int=14;
select @myname;-- 查看自定义变量
select @`int`;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

语法：

声明局部变量

DECLARE 变量名 变量类型 [DEFAULT ...];

局部变量赋值：

SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名 ...;

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCAHR、DATE、TIME等

案例

create procedure p5()-- 创建存储
begin
    declare id int default 0;-- 声明局部变量
    set id=100;-- 变量赋值
    select count(*) into id from emp;-- 变量赋值
    select id;-- 查看变量或者调用局部变量返回给存储
end;

call p5;-- 调用存储过程

if

语法：

IF 条件1 THEN
  ...
ELSEIF 条件2 THEN    -- 可选
  ...
ELSE                -- 可选
  ...
END IF;

案例

create procedure  p6()
begin
    -- 声明局部变量
    declare score int default 60;-- 定义局部变量赋予默认值
    declare result varchar(100);-- 定义局部变量
    if score>80 then-- if编写
        set  result='优秀';
        elseif score>70 then
        set result='良好';
        else
        set  result='不合格';
    end if;
    select  result;-- 查看变量值返回存储
end;
call p6;-- 调用存储

参数(IN/OUT/INOUT)

参数：

IN 该类参数作为输入，也就是需要调用时传入值 默认
OUT 该类参数作为输出，也就是该参数可以作为返回值
INOUT 既可以作为输入参数，也可以作为输出参数

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
  -- SQL语句
END;

case：

语法1：

CASE case_value
     WHEN when_value1 THEN statement_list1
     [WHEN when_value2 THEN statement_list2]...
     [ELSE statement_list]
END CASE;

语法2：

CASE
     WHEN search_condition1 WHEN statement_list1
     [WHEN search_condition2 WHEN statement_list2]...
     [ELSE statement_list]
END CASE;

案例：

-- 创建存储过程
create procedure p6(in month int)
begin
  declare result varchar(10);
  
  case
    when month >= 1 and month <= 3 then
      set result := '第一季度';
    when month >= 4 and month <= 6 then
      set result := '第二季度';
    when month >= 7 and month <= 9 then
      set result := '第三季度';
    when month >= 10 and month <= 12 then
      set result := '第四季度';
    else 
      set result := '非法传输';
  end case;
  
  select concat('您输入的月份为：',month,"，所属的季度为：",result);
end;

-- 调用
call p6(8);

循环
1、while
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
   SQL逻辑...
END WHILE;

2、repeat
repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT 
   SQL逻辑...
UNTIL 条件
END REPEAT;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

语法：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristics ...]
BEGIN
	-- SQL语句
	RETURN ...;
END;

characteristics的说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL：不包含SQL语句
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

案例：

-- 存储函数
-- 从1到n的累加
create function fun1(n int) 
returns int deterministic --返回类型
begin
	declare total int default 0;
	
	while n > 0 do
		set total = total + n;
		set n = n - 1;
	end while;
	
	return total;
end;

-- 调用存储函数，并显示结果
select fun1(100);