目录

一：MySQL的视图

1.视图的介绍

2.创建视图

3.修改视图

4.更新视图

5.重命名视图

6.删除视图

二：MySQL的存储过程

1.入门案例

2.变量定义

2.1局部变量

2.2用户变量

2.3系统变量

3.参数传递

3.1参数传递--in

3.2参数传递--out

3.3参数传递--inout

4.流程控制

4.1分支语句--if

4.2分支语句--case

4.3循环语句--介绍

4.4循环语句--while循环

4.5循环语句--repeat循环

4.6循环语句--loop循环

4.7游标cursor的使用

4.8异常处理--handler句柄

---

一：MySQL的视图

1.视图的介绍

概念：视图（view）是一个虚拟表，非真实存在其本质是根据SQL语句获取动态的数据集

作用：简化代码      数据安全

特点：视图本身不存在具体的数据，只存储数据的逻辑，这些数据存放在原来的表中

          一旦表中的数据发生改变，显示在视图中的数据也会发生改变

2.创建视图

操作：

create or replace view view_emp
as
select ename,job from emp;
-- 查看表和视图
show full tables;

解释：创建或代替视图view_emp,view_emp是视图的名字，视图的内容是表emp里的ename和job两列内容。

emp表如下，表的创建和数据的准备就省略了，直接展示结果

3.修改视图

MySQL可以通过create or replace view语句或alter view 语句来修改视图

格式：alter view 视图名  as select 语句

操作：查询语句根据需要自己改就行了

alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a,emp b 
where a.deptno = b.deptno;

4.更新视图

        某些视图是可以更新的。也就是说，可以在update,delete或insert等语句中使用他们，以更新基表的内容，在视图中的行和基表中的行之间必须有一对一的关系。如果视图中包含下述结构中的任意一种，那么他就是不可更新的：

* 聚合函数 sum(),min(),max(),count()等

* distinct 去重

* group by 分组

* having

* union 或 union all

* 位于选择列表中的子查询

* join

* from 子句中的不可更新视图

* where 中的子查询，引用from 子查询中的表

* 仅引用文字值（在该情况下，没有要更新的基本表）

        注意：视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，在更新数据时，如果没有全面考虑在视图中更新视图的限制，就可能会造成数据更新失败！

5.重命名视图

-- rename table 视图名 to 新视图名
rename table view1_emp to my_view1;

6.删除视图

-- drop view 视图名
drop view if exists view_student;

二：MySQL的存储过程

1.入门案例

格式：

delimiter 自定义结束符号

create  procedure  储存名 （[in,out,inout]参数名  数据类型）

begin  sql语句

end  自定义的结束符号

delimiter ;

创建存储过程：

delimiter $$
create procedure proc01()
begin
   select empno,ename from emp;
end $$
delimiter ;

调用存储过程：

call proc01();

2.变量定义

2.1局部变量

用户自定义，在begin/end中有效

语法：声明变量  declare  var_name type [default 'aaa'];

举例：declare nickname varchar (32);

操作：

delimiter $$
create procedure proc02()
begin 
    declare var_name01 varchar(20) default 'aaa' -- 声明/定义变量
    set var_name01 = 'zhangsan'  -- 给变量赋值
    select var_name01;  -- 输出变量的值
end $$
delimiter ;
-- 调用存储过程
call proc02;

注意:给变量赋值除了set,还有另一种方式selct  into,也可以为变量赋值

select ename into my_name from emp where empno = 1001;

解释：就是将ename这一列员工号为1001的员工名赋给了my_name

2.2用户变量

格式：用户自定义，当前会话（连接）有效。类比java的成员变量。

语法：@var_name

不需要提前声明，直接使用即可。

操作：

delimiter $$
create procedure proc03()
begin 
    set @var_name01 = 'beijing';
    select @var_name01;
end $$
delimiter ;
call proc03;
select @var_name01;  -- 在begin/and外也可以使用用户变量

2.3系统变量

全局变量---由系统提供，在整个数据库有效

语法：@@global.var_name

操作：

-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size=40000;
set @@global.sort_buffer_size=40000;

会话变量---由系统提供，在当前会话（连接）有效

语法：@@session.var_name

操作：

-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size=50000;
set @@session.sort_buffer_size=50000;

3.参数传递

3.1参数传递--in

        in表示传入的参数，可以传入数值或变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

举例说明:

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
    select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');

3.2参数传递--out

        out表示从存储过程内部传值给调用者

举例说明：

-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc04(in empno int,out out_name varchar(50))
begin
    select ename into out_name from emp where emp.empnno = empno;
end $$
delimiter ;
call proc04(1001,@o_ename);
select @o_name;

对代码做简要解释：首先传入参数1001，则emp.empno=empno=1001,根据它ename会得到一个值（名字），然后这个值会赋给out_name,最后out_name的值会传出去，传给变量@o_name

3.3参数传递--inout

        inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

举例说明：

-- 传入一个数字，传出这个数字10倍的值
delimiter $$
create procedure proc05(inout num int)
begin 
    set num=num*10;
end $$
delimiter ;
set @inout_num = 2;
call proc05(@inout_num);
select @inout_num;  -- 20

4.流程控制

4.1分支语句--if

        if语句包含多个条件判断，根据结果为true/false执行语句，与编程语言中的if,elseif,else语法类似，其语法格式如下：

if search_condition_1 then statement_list_1
    [elseif search_condition_2 then statement_list_2]...
    [else statement_list_n]
end if

举例说明：

-- 输入学生的成绩，来判断成绩的级别
delimiter $$
create procedure proc_if(in score int)
begin 
  if score < 60
    then 
        select '不及格'；
  elseif score >= 60 and score < 80
    then
        select '及格'；
  elseif score >= 80 and score < 90
    then
        select '良好'；
  elseif score >= 90 and score <= 100
    then
        select '优秀'；
  else 
    select '成绩错误'；
  end if;
end $$
delimiter ;
call pro_if(88);  -- 良好

4.2分支语句--case

        case是另一个条件判断的语句，类似于编程语言中的switch语法

-- 语法一
case case_value
    when when_value then statement_list
    [when when_value then statement_list]...
    [else statement_list]
end case
-- 语法二
case
    when search_condition then statement_list
    [when search_condition then statement_list]...
    [else statement_list]
end case

用法和上面的if语句基本相同，这里就不再举例说明了。

4.3循环语句--介绍

概述：循环是一段在程序中只出现一次，但可能会连续运行多次的代码，循环中的代码会运行特定次数，或许是运行到特定条件成立时结束循环。

分类：*while    *repeat   *loop

循环控制：leave类似于break,跳出，结束当前的循环

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

4.4循环语句--while循环

格式：标签可写可不写，根据自己需要

[标签：] while 循环条件 do
    循环体；
end while [标签]；

具体代码展示：

-- 创建测试表
create table user(
uid int primary_key,
username varchar(50),
password varchar(50)
);
-- 向表中添加指定条数的数据
delimiter $$
create procedure proc_while_leave(in insertCount int)
begin
    declare i int default 1;
    label:while i < = insertCount do
        insert into user(uid,username,password) values(i,concat('user-',i),'123456');
        if i = 5 then
            leave label;   -- 跳出循环
        end if;
        set i = i + 1;
    end while label;
end $$
delimiter ;
call proc_while_leave(10);

插入数据后的表格如下：

4.5循环语句--repeat循环

格式：

[标签：] repeat
 循环体；
until 条件表达式
end repeat [标签];

4.6循环语句--loop循环

格式：

[标签：] loop
   循环体；
   if 条件表达式 then
        leave [标签]；
   end if;
end loop;

说明一下：repeat循环和loop循环和while循环他们都差不多，就不在用例子说明了

4.7游标cursor的使用

        游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用对结果集进行循环处理。光标的使用包括光标的声明，open，fetch和close.

举例说明：

-- 需求：输入一个部门名，查询该部门员工的编号，名字和薪资，将查询结果集添加游标
delimiter $$
create procedure proc_cursor(in in_dname varchar(50))
begin 
    -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a,emp b
        where a.deptno = b.deotno and a.dname = in_name;
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno,var_ename,var_sal;
        select var_empno,var_ename,var_sal;
    end loop label;
    -- 关闭游标
    close my_cursor; 
end $$
delimiter ;
call proc_cursor('销售部');

注意上面的loop是死循环，会有一个异常，我们会在后面进行解决

4.8异常处理--handler句柄

        MySQL存储过程也提供了对异常处理的功能：通过定义句柄handler来完成异常声明的实现。

直接上代码：

-- 需求：输入一个部门名，查询该部门员工的编号，名字和薪资，将查询结果集添加游标
delimiter $$
create procedure proc_cursor(in in_dname varchar(50))
begin 
    -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    -- 定义标记值
    declare flag int default 1;
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a,emp b
        where a.deptno = b.deotno and a.dname = in_name;
    -- 定义句柄：定义异常的处理方式
    /* 
        1.异常处理后程序该怎么执行
          continue:继续执行剩余代码
          exit:直接终止程序
          undo:不支持
        2.条件触发
          条件码：1329
          条件名：SQLWARNING
                 NOT FOUND
                 SQLEXCEPTION
        3.异常触发之后执行什么代码
          设置flag的值---》0
    */
    declare continue handler for 1329 set flag = 0;
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno,var_ename,var_sal;
    -- 判断flag,如果flag为1则执行，否则不执行
        if flag = 1 then
            select var_empno,var_ename,var_sal;
        else leave label;
        end if;
    end loop label;
    -- 关闭游标
    close my_cursor; 
end $$
delimiter ;
call proc_cursor('销售部');