011 学习笔记--视图 + 存储过程(if\case\while\repeat\loop\cursor) + 存储函数

视图：

视图：是一种虚拟的表。视图中的数据在数据库中并不实际存在，行和列的数据来自自定义视图中查询使用的表，并且是在使用视图时动态生成的。
创建视图：create or replace view viewname as select 语句 [with[cascaded|local|check option]]
- 例如：create or REPLACE view View_GetUsers as select * from tb_users;
查询视图：
- 查看创建视图语句：show create view View_GetUsers
- 查看视图数据：select * from View_GetUsers
修改
- create or replace view viewname as select ...... [with[cascaded|local|check option]]
- alter view viewname as select ...... [with[cascaded|local|check option]]
删除：drop view if exists viewname

注：[with[cascaded|local|check option]]，为可选项.................

存储过程：

特点：封装、复用，可以接受参数，也可以返回数据，减少网络交互，效率提升

语法：

创建
- create procudure 存储过程名称(参数列表)
- begin
- -----------sql
- end;
调用
- call 存储过程名称(参数列表)

-- --------------------------------存储过程--------------------------------
-- 创建
create PROCEDURE proctest()
begin
    select count(*) from tb_users;
end;
-- 调用
call proctest();
-- 查看
-- 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='proctest';
-- 查询某个存储过程的定义
show create PROCEDURE proctest;
 
-- 查看结果如下
CREATE DEFINER=`root`@`localhost` PROCEDURE `proctest`()
begin
    select count(*) from tb_users;
end

-- 删除
drop PROCEDURE if EXISTS proctest;

变量 —— 默认session　　

系统变量：是Mysql服务器提供，不是用户定义的，属于服务器层面。分为全局变量(golbal)、回话变量(session)

查看系统变量
- show [session|global] variables;——查询所有系统变量
- show [session|global] variables like '......';——可以通过like模糊匹配方式查找变量
- select @@[session|global] 系统变量名； ——查看指定变量的值

设置系统变量

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

-- -- -- -- -- -- -- -- -- -- -- -- -- 变量-- -- -- -- -- -- -- -- -- -- -- -- 
show session variables;
show session variables like 'auto%';
show global variables;
show global variables like 'auto%';
 
select @@session.autocommit;
select @@global.autocommit;
 
set SESSION autocommit =0;
set SESSION autocommit =1;　

注：如果没有制定session/global，默认是session--回话变量。mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。

用户自定义变量

定义：是用户根据需要自己定义的变量，用户变量不用提前生命，在用的时候直接用 @变量名称就可以使用。其作用域为当前连接。　
赋值：
- set @var_name=expr,.......
- set @var_name:=expr,.......
- select @var_name := exper.....
- select 字段名 into @var_name from 表名
使用：select @var_name;
示例如下所示：

-- 用户自定义变量
-- 赋值
set @myname='sunkun';
set @myage:=18;
set @mygender:='boy',@myhobby:='C#';
 
select @mynickname:='kun';
select count(*) into @mycunt from tb_users;
-- 使用
select @myname,@myage,@mygender,@myhobby,@mynickname,@mycunt;

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

局部变量

定义：是根据需要定义在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin..............end块。
声明：declare 变量名变量类型 [default...]；变量类型就是数据库字段类型：int\bigint\char\varchar\date\time等。
赋值：set 变量名 = 值、set 变量名：= 值、select 字段名 into 变量名 from 表名....
示例如下所示：

-- 创建
create PROCEDURE proctest2()
begin
    DECLARE stucount int DEFAULT 0;
    DECLARE stuname varchar(50);
     
    set stucount =18,stuname='sunwugang';
    select stuname,stucount;
     
end;
-- 调用
call proctest2();

语法：
if 条件 then ......
ELSEIF 条件 THEN ......  -- 可选
else ......  --  可选
end if

- 示例如下所示：

create PROCEDURE proc3()
BEGIN
    DECLARE age int DEFAULT 22;
    DECLARE temp VARCHAR(20);
 
    if age>=18 and age<=30 then set temp:='初级';
    ELSEIF age >=30 and age<=40 THEN set temp='中级';
    else set temp :='高级';
    end if;
 
select temp;
END;
 
call proc3(); -- return  初级

参数

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 输入输出参数示例
drop PROCEDURE if EXISTS proc4;
create PROCEDURE proc4(in age int,out temp varchar(20))
BEGIN
--  DECLARE age int DEFAULT 22;
--  DECLARE temp VARCHAR(20);
 
    if age<=25 then
        set temp:='初级';
    ELSEIF age<=40 THEN
        set temp='中级';
    else set temp :='高级';
    end if;
 
select temp;
 
END;
 
call proc4(28,@temp); 
select @temp;
 
 
drop PROCEDURE if EXISTS proc5;
create PROCEDURE proc5(in age int,in name varchar(50),out temp varchar(20))
BEGIN
--  DECLARE age int DEFAULT 22;
--  DECLARE temp VARCHAR(20);
 
    if age>=18 and age<=30 then set temp:=CONCAT(name,',初级');
    ELSEIF age >=30 and age<=40 THEN set temp=CONCAT(name,',中级');
    else set temp :=  CONCAT(name,',高级');
    end if;
 
END;
call proc5(28,'sunwugang',@temp); 
select @temp;
 
 
drop PROCEDURE if EXISTS proc6;
create PROCEDURE proc6(in name varchar(50),inout age int,out temp varchar(20))
BEGIN
--  DECLARE age int DEFAULT 22;
--  DECLARE temp VARCHAR(20);
 set age:= age+10;
 
    if age>=18 and age<=30 then set temp:=CONCAT(name,',初级');
    ELSEIF age >=30 and age<=40 THEN set temp=CONCAT(name,',中级');
    else set temp :=  CONCAT(name,',高级');
    end if;
 
END;
 
set @age=22;
call proc6('sunwugang',@age,@temp); 
select @age,@temp; 

case

-- case when else end 
/*
(
case 字段 when xxx then xxx
          when xxx then xxx
                  else xxx
end
)as 重命名字段
*/
select t.*,
(case content when '001' then '一级戒备'
              when '002' then '二级戒备'
              else '三级戒备'
end)戒备戒备
from remarkinfo t
ORDER BY t.content;

drop PROCEDURE if EXISTS proc7;
create PROCEDURE proc7(in month int)
BEGIN
    DECLARE result VARCHAR(100);
     
    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 proc7(8);

循环

while：满足条件执行循环

drop PROCEDURE if EXISTS proc8;
create PROCEDURE proc8(in num int,out count int)
BEGIN
    DECLARE result int DEFAULT 0;
 
    while num >0 do
        set result := result + num;
        set num := num -1;
  end WHILE;
 
  set count:=result;
END;
  
call proc8(4,@count);
select @count;

repeat：当满足条件的时候退出循环

-- repeat 循环
drop PROCEDURE if EXISTS proc9;
create PROCEDURE proc9(in num int,out count int)
BEGIN
    DECLARE result int DEFAULT 0;
 
    repeat 
        set result := result + num;
        set num := num -1;
  until num<=0
    end repeat;
 
  set count:=result;
END;
  
call proc9(4,@count);
select @count;

loop

实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环。Loop可以配合以下两个语句使用：
LEAVE：配合循环使用，退出循环；
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环；
示例如下所示：

-- LOOP 计算从1累加到N的值(只计算偶数)，N为入参
drop PROCEDURE if EXISTS proc10;
create PROCEDURE proc10(in num int,out count int)
BEGIN
    DECLARE total int DEFAULT 0;
 
    sum:LOOP 
            if num <=0 then
                leave sum;                  -- leave 退出循环
            end if;
 
            set total := total + num;
            set num := num -1;
 
    end LOOP sum;
 
  set count:=total;
END;
  
call proc10(4,@count);
select @count;
 
 
/*
leave 退出循环
iterate 类似continue
*/
-- LOOP 计算从1累加到N的值，N为入参
drop PROCEDURE if EXISTS proc11;
create PROCEDURE proc11(in num int,out count int)
BEGIN
    DECLARE total int DEFAULT 0;
 
    sum:LOOP 
            if num <=0 then
                leave sum;                  -- 
            end if;
 
            if num %2 =1 then
                set num := num -1;
                iterate sum;                    -- iterate 类似continue
            end if;
 
            set total := total + num;
            set num := num -1;
 
    end LOOP sum;
 
  set count:=total;
END;
  
call proc11(4,@count);
select @count;

cursor游标

游标，是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。
游标的使用包括：游标的声明、OPEN、FETCH、CLOSE，其语法如下所示：
- 声明游标：declare 游标名称 cursor for 查询语句；
- 打开游标：open 游标名称；
- 获取游标记录：fetch 游标名称 into 变量1，变量2，变量N;
- 关闭游标：close 游标名称；
条件处理程序
- 条件处理程序(handler)可用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤，语法如下：
示例如下所示：实现将age =18的用户信息插入 tb_users2
select * from tb_users;

-- -------------------------------------游标-------------------------------------
 
-- 存储过程，实现age =18的 数据插入 tb_users2
drop PROCEDURE if EXISTS proc12;
create PROCEDURE proc12(in inage int)
BEGIN
    declare uname varchar(100);
    declare uage int;
    declare ustatus char(1);
    declare ugender char(1);
    declare uphone varchar(11);
 
--  1.声明游标
    DECLARE user_cursor cursor for select NAME,age,status,gender,phone from tb_users where age = inage;     
 
-- 方式一条件处理程序，当SQLSTATE为 02000时，退出游标 为状态码
--  DECLARE exit handler for SQLSTATE '02000' CLOSE user_cursor;
 
-- 方式二
    DECLARE exit handler for not found CLOSE user_cursor;
 
-- 创建表,可将建表部分至于存储过程之外
  DROP table if EXISTS tb_users2;
    create table if not exists tb_users2
    (
        id int auto_increment PRIMARY key COMMENT '主键',
        name VARCHAR(100) not null unique COMMENT '姓名',
        age INT CHECK (age >0 && a<=120),
        status char(1) DEFAULT '1' COMMENT '状态',
        gender char(1) COMMENT'性别',
        phone VARCHAR(11) COMMENT '手机号'
    )COMMENT'用户表';
 
-- 2.打开游标
    open user_cursor;
    while true do 
--  3.循环操作数据
        fetch user_cursor into uname,uage,ustatus,ugender,uphone;
        insert into tb_users2 values(null,uname,uage,ustatus,ugender,uphone);
    end while;
-- 4.关闭游标
    close user_cursor;
END;
  
call proc12(18);
SELECT * from tb_users2;

存储函数：

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的。其语法如下所示
示例如下所示：

-- -----------------------------存储函数-----------------------------
drop function  if EXISTS fun1;
create function fun1(num int)
returns int DETERMINISTIC
BEGIN
    declare total int DEFAULT 0;
-- DECLARE stucount int DEFAULT 0;
     
    while num>0 do
        set total:=total + num;
        set num:=num -1;
    end while;
     
    return total;
END;
 
-- 调用存储函数
select fun1(4);