1205、mysql视图、mysql存储过程

一、mysql视图

1、视图概述

1.1 视图介绍（什么是视图）

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表。

视图是一张虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。

同真实表一样，视图包含一系列带有名称的列和行数据

数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
1
2
3
4
5
6
7
8
9
10

1.2 视图的优点（为什么要使用视图）

简单

用户无需关心视图中的数据如何查询获得的

视图中的数据已经是过滤好的符合条件的结果集

安全：用户只能看到视图中的数据

数据独立：一旦视图结构确定，可以屏蔽表结构对用户的影响

2、视图基础操作

2.1 创建视图

语法格式:

create view 库.视图名称 as SQL查询;

create view 库.视图名称(字段名列表) as SQL查询;
1
2
3

例子:

mysql> create database viewdb;  

mysql> create view viewdb.v1 as select name , uid from tarena.user ;
 
mysql> create view viewdb.v2(姓名,家目录,登录状态) as
    -> select name,homedir,shell from tarena.user;


mysql> use tarena;
mysql> create view emp_view as
  ->  select name, email, dept_name
  ->  from employees as e inner join departments as d
  ->  on e.dept_id=d.dept_id;

# 查看结构
desc tarena.emp_view;
desc viewdb.v1;

# 查询视图中的数据
mysql> select * from viewdb.v1;
mysql> select * from viewdb.v2;
mysql> select * from tarena.emp_view;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.2 查看视图

use  viewdb;
查看当前库里哪些表是视图
mysql> show table status where comment = "view" \G   
Comment: VIEW
 
查看视图表里的数据是从哪个基表获取的
mysql> show create view viewdb.v1 \G
*************************** 1. row ***************************
        View: v1
     Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `tarena`.`user`.`name` AS `name`,`tarena`.`user`.`uid` AS `uid` from `tarena`.`user`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

2.3 操作视图 select insert update delete

当对视图里的数据做 insert 、update 、 delete ，对应的基本数据也会跟着改变，反之亦然

mysql> select * from viewdb.v1 where uid is null ;
mysql> delete from viewdb.v1 where uid is null ;
mysql> select * from tarena.user where uid is null ;
1
2
3

通过视图表对数据做删除、更新、 插入操作，对应基表数据也会改变。（因为视图里的数据就是基本里数据）

mysql> delete from viewdb.v1 where uid is null ;
mysql> update viewdb.v1 set uid=100 where name="root";
mysql> insert into tarena.user(name,uid) values("nb2",8888);
1
2
3

查看基本里的数据

mysql> select * from tarena.user where uid is null ;
mysql> select count(*) from tarena.user;
mysql> select name from tarena.user where name="nb";
mysql> select * from viewdb.v1 where name="nb2"; 
1
2
3
4

2.4 删除已有视图

mysql> drop table viewdb.v1;
ERROR 1051 (42S02): Unknown table 'viewdb.v1'

mysql> drop view viewdb.v1;     #使用删除视图的命令drop view 
1
2
3
4

3、视图进阶

3.1设置查询语句中的字段别名（select 命令查询的表里有同名的字段时）

create  table  tarena.t3  select name,uid from tarena.user limit 3;

create  table  tarena.t4  select name,shell from tarena.user limit 5;
 
mysql> use  tarena;
mysql> create view v3 as select * from 
t3 inner join t4 on  t3.name = t4.name; 报错
1
2
3
4
5
6
7

定义别名

mysql> create view v3 as select t3.name as username , t4.name as 姓名  
from  t3 inner join t4 on  t3.name = t4.name;

mysql> select  * from  v3; 
1
2
3
4

3.2 覆盖的方式创建视图 （达到修改已有视图的目的）

mysql> create view  viewdb.v2 as select name,uid,gid from tarena.user;
ERROR 1050 (42S01): Table 'v2' already exists

mysql> create or replace view  viewdb.v2 as select name,uid,gid from tarena.user;
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5

3.3 with check option (支持的检查选项)

选项 local 首先满足自身的限制 ，同时要满足基本的限制

选项 cascaded (默认值 ) 满足视图自身限制即可

默认情况下 ,通过视图修改数据是不受限制

# 可以设置通过视图修改数据受限制：限制的方式如下

1.选项 cascaded (默认值 ) 满足视图自身限制即可
mysql> create view tarena.v21  as 
    -> select name , uid from  tarena.user where uid > 10 
    -> with check option;

mysql> update v21 set uid=1 where name="root"; 条件不成立不会执行修改
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select  * from  v21  where name="root";
Empty set (0.01 sec)


mysql> update v21 set uid=7 where name="ftp";		# 不满足 >10 的限制
ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v21'
mysql> update v21 set uid=21 where name="ftp";		# 满足 >10 的限制
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0




2.选项 local   首先满足自身的限制 ，同时要满足基本的限制
mysql> create view v31 as select name,uid from tarena.user where uid<=100;
Query OK, 0 rows affected (0.03 sec)

mysql> create view v45 as select name,uid from v31 where uid>=10 
    -> with local check option;
Query OK, 0 rows affected (0.05 sec)


mysql> update v45 set uid=8 where name="ftp";
ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v45'


# 虽然超出基表v31 限制 但还改成了 因为基表v31没加限制 with check option 
mysql> update v45 set uid=800 where name="ftp";
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#修改视图v31 加修改限制
mysql>  create or REPLACE view  v31 as select name,uid from tarena.user
    -> where uid <= 100 with check option ;
Query OK, 0 rows affected (0.05 sec)

# 没有满足基表v31限制
mysql> update v45 set uid=600 where name="sshd";			
ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v45'	

# 没有满足自身限制
mysql> update v45 set uid=6 where name="sshd";				
ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v45'
# 既满足自身限制又满足基表限制
mysql> update v45 set uid=60 where name="sshd";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

二、mysql存储过程

1、mysql 存储过程基础

什么是存储过程

–mysql服务中的脚本；

–由一系列sql命令组成；

–通过存储过程可以对数据做批量处理和重复操作；

–可以防止对表的直接访问；

–避免重复的sql操作。

说白了就是mysql服务的脚本，登录服务器后 要重复执行的命令写成存储过程;

存储过程就是mysql服务的脚本。

1.1 创建存储过程的命令格式（需牢记）

语法格式：

mysql> delimiter //
mysql> create procedure 名称（参数列表）
	-> begin
	-> 		一组合法的sql命令
	-> end
	-> //			
mysql> delimiter ;

# delimiter  指定命令结束符号
# mysql默认已';'为分隔符，没有声明分隔符，
1
2
3
4
5
6
7
8
9
10

演示delimiter 命令的作用:

命令行的结束符号 默认是 ;

mysql> delimiter //         # 把命令行的结束符号 改为//
mysql> desc tarena.user //    # 执行命令是得使用//结束命令

mysql> delimiter ;   	   # 再改回默认的 ；
mysql> desc tarena.user ;
1
2
3
4
5

例子：

# 存储过程的使用
# 创建存储过程  pria()

mysql> use  tarena;
mysql> delimiter //
mysql> create procedure pria()
    -> begin
    -> select  count(*) from   tarena.salary  ;
    -> select  count(*) from   tarena.employees  ;
    -> end
    -> //
mysql> delimiter  ;
1
2
3
4
5
6
7
8
9
10
11
12

1.2 执行存储过程

# 格式：
call 库名.名称（）
1
2

-----------------------------------例子-----------------------------------------
 mysql> call pria();  或  call  tarena.pria();    
+----------+
| count(*) |
+----------+
|     8055 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|      135 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> call pria;   # 存储创建时括号（） 里没有参数 ，执行可以省略()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1.3查看存储过程

1.格式一
# 查当前所在库已有的存储过程
show procedure status \G		

2.格式二
mysql库里proc表中存放所有的存储过程
# 列出服务器上所有的存储过程
select db, name ,  type  from mysql.proc where   type="PROCEDURE";  

# 查看是否有名字叫pria的存储过程
mysql> select  db, name ,  type  from mysql.proc where   type="PROCEDURE"  and   name="存储过程名"; 
1
2
3
4
5
6
7
8
9
10
11

例子

1.
mysql> use tarena; 
mysql> show procedure status \G
                      Db: tarena
                Name: pria
                  Type: PROCEDURE
2. 
mysql> select  db, name ,  type  from mysql.proc 
where   type="PROCEDURE"  and   name="pria";   查看是否有名字叫pria的存储过程
+--------+------+-----------+
| db     | name | type      |
+--------+------+-----------+
| tarena | pria | PROCEDURE |
+--------+------+-----------+
1 row in set (0.00 sec)         

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1.4 删除存储过程

mysql> drop  procedure  库.存储过程名 ;
1

# 例子
mysql> drop  procedure   tarena.pria;
Query OK, 0 rows affected (0.00 sec)

mysql> call tarena.pria;
ERROR 1305 (42000): PROCEDURE tarena.pria does not exist
mysql> select name  from mysql.proc where name="pria";
Empty set (0.00 sec)
1
2
3
4
5
6
7
8

2、存储过程进阶

2.1 变量的使用

变量的分类 ：

1.系统变量： mysql服务定义包括：

​ （1）全局变量（任意用户连接服务查看到值都一样的）

​ （2）会话变量：连接服务器的用户登录期间使用的变量

​

**2.自定义变量：**连接数据库服务的用户定义包括：

​ （3）**用户变量：**用户登录数据库服务器，自己定义的变量

​ （4）**局部变量 ：**在begin 和 end 定义的变量,仅存储过程执行中有效

(1) 全局变量

--影响服务器整体操作，作用于所有会话；
--当服务启动时，他将所有全局变量初始化为默认值；
--更改全局变量，必须具备super权限；
--其作用域为server的整个生命周期，服务重启消失。

1.查看所有全局变量
mysql> show global variables\G
2.设置全局变量
	   set global     全局变量=xxx ;
mysql> set global     validate_password_length=6 ;
3.查看单个变量
mysql> show global variables like  "全局变量名";
mysql> show global variables like  "validate_password_length";
或着：
mysql> select @@全局变量名;
mysql> select @@validate_password_length;
4.仅查看匹配条件的
mysql> show global variables like  "%关键字%";
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

例子：

[root@host50 ~]# mysql -uroot -p123456
# 查看所有的全局变量
mysql> show global variables \G      
Variable_name: version_compile_os
         Value: Linux

# 仅查看一个全局变量	
mysql> show global variables  like  "version_compile_os" ;

	
# 修改全局变量的值  
set   global   变量名="值"； 
mysql> set global  wait_timeout = 20000;
mysql> show global variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 20000 |
+---------------+-------+
1 row in set (0.00 sec)

# 输出某个全局变量的值 
mysql> select  @@version_compile_os;    
+----------------------+
| @@version_compile_os |
+----------------------+
| Linux                |
+----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

(2) 会话变量

--服务器为每个连接的客户端维护一系列会话变量；
--其作用域仅限于当前连接，即每个连接中的会话变量是独立的。

1. 查看会话变量
mysql> show session variables;						# 查看连接的所有变量
mysql> show session variables like "会话变量名";		# 查看单个
mysql> show session variables like "%关键字%";			# 查看匹配条件的
mysql> select @@会话变量名;

2.设置会话变量
mysql> set session 会话变量=xxx ;

1
2
3
4
5
6
7
8
9
10
11
12

例子：

mysql> show session variables  like  "%cache%" ;   仅查看与内存相关的变量
mysql> set session sort_buffer_size=50000;  修改 

mysql>  show session variables  like "sort_buffer_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 50000 |
+------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

(3) 用户变量

--用户变量不用提前声明，在用的时候直接用  “@变量名”  使用；
--仅对当前登陆用户有效

格式：
1.自定义变量并直接赋值
mysql> set @自定义变量 = 值 ;
mysql> set @自定义变量1 = 值1,@自定义变量2 = 值2;
2.查看
mysql> select @自定义变量1,@自定义变量2;

3.使用sql命令查询结果赋值
mysql> sql查询语句   into @自定义变量 from 库.表;

mysql> select max(uid) into @x from tarena.user;
mysql> select count(*) into @num from tarena.user;
mysql> select @x,@num;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

例子：

#定义并赋值
mysql> set @age=19,@name="zhu";
Query OK, 0 rows affected (0.00 sec)

mysql> select @age,@name;
+------+-------+
| @age | @name |
+------+-------+
|   19 | zhu   |
+------+-------+
1 row in set (0.00 sec)

# 使用查询结果赋值
mysql> select count(name) into @numbers from tarena.user where shell = "/bin/bash";
mysql> select count(*) into @lines from tarena.user
mysql> select @lines as 总行数,@numbers as 登录系统用户数;
+-----------+-----------------------+
| 总行数     | 登录系统用户数          |
+-----------+-----------------------+
|        23 |                     1 |
+-----------+-----------------------+
1 row in set (0.00 sec)

mysql> select max(uid) , min(uid) into  @b,@s  from tarena.user; 
Query OK, 1 row affected (0.00 sec)

mysql> select @b,@s;
+-------+------+
| @b    | @s   |
+-------+------+
| 65534 |    1 |
+-------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

(4)局部变量的使用

--只能在begin/end语句块中
--declare命令，用来 定义局部变量
declare  变量   类型；
declare  变量   类型   default  值；


1.格式：
mysql> delimiter //
mysql> create   procedure   局部变量()
    -> begin
    -> declare  变量1   类型;
    -> declare  变量2   类型   default  值；
    -> set 变量1="值2",变量2="值2";
    -> select 变量1，变量2;
    -> end
    -> //
mysql> delimiter ;  

2.调用存储过程
mysql> call 局部变量名;		

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

例子：

mysql> delimiter //
mysql> create procedure tarena.pa()
    -> begin
    -> declare name char(10);
    -> declare age int default 19;
    -> declare school char(10);
    -> set name="zhu",school="tarena";
    -> select name;
    -> select school,age;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;    
mysql> call tarena.pa;		# 调用存储过程
+------+
| name |
+------+
| zhu  |
+------+
1 row in set (0.00 sec)

+--------+------+
| school | age  |
+--------+------+
| tarena |   19 |
+--------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 使用任意一种方式调用局部变量都会报错,因为局部变量只在存储过程执行中有效
mysql> select name,age;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
mysql> select @name,@age;
+-------+------+
| @name | @age |
+-------+------+
| NULL  | NULL |
+-------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

2.2 存储过程参数

参数的使用

格式：

create  procedure    名(参数,参数,......)
参数定义的语法格式      参数类型    变量名    数据类型 
1
2

参数的种类：

(1) in

in类型的参数负责把数据传给存储过程

格式：

例如    create     proucedure  p2(  in   x   int )
        begin
			 ......
	    end
1
2
3
4

例子：

mysql> delimiter //
mysql> create procedure tarena.p3(in  dept_no int)
    -> begin
    -> select dept_id , count(*) as 总人数  from 
	-> tarena.employees where dept_id=dept_no group by  dept_id;
    -> end
    -> //
mysql> delimiter ;	
mysql> call  p3() ;    # 不给参数会报错
mysql> call  p3(1) ;   # 查看部门编号1  的员工人数
mysql> call  p3(3) ;   # 查看部门编号3  的员工人数

1
2
3
4
5
6
7
8
9
10
11
12

(2) out

负责接收存储过程的处理结果。

存储过程执行结束后， 可以调用 out类型的参数， 获取存储过程的处理结果。

格式：

create     proucedure  tarena.p31(  out   x   int )
           begin
			 ......
	       end
				
		call   tarena.p31(@名)；
		select  @名；

1
2
3
4
5
6
7
8

**例子：**编写存储过程tarena.p4 功能获取员工表里指定用户的邮箱

mysql> delimiter //
mysql> create procedure  tarena.p4( in emp_name varchar(10) , OUT mail varchar(25))
begin
       select email into mail  from employees  where name=emp_name;
end  //
mysql> delimiter ;	


# 插入做测试的员工
insert into employees(name,email)   
values("john","john@163.com"),("jerry","jerry@tedu.cn");

mysql> call tarena.p4("jerry",@m);		  # 执行存储过程
Query OK, 1 row affected (0.00 sec)

mysql> select @m; 				 # 查看变量 看 员工的邮箱 
+---------------+
| @m            |
+---------------+
| jerry@tedu.cn |
+---------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

存储过程归属的库， 使用的不是中文字符集时 ，创建的存储过程 无法识别中文。

mysql> call tarena.p4("王小红",@m);
ERROR 1366 (HY000): Incorrect string value: '\xE7\x8E\x8B\xE5\xB0\x8F...' for column 'emp_name' at row 1
mysql> 

1
2
3
4

修改库使用的字符集:

mysql> alter database tarena default CHARACTER SET utf8;  修改库使用的字符集
   
mysql> drop  procedure  tarena.p4;  删除已经的存储过程重新创建 ， 因为字符集 对已经存储的存储过程无效

mysql> delimiter //
mysql> create procedure  tarena.p4( in emp_name varchar(10) , OUT mail varchar(25)) 
begin select email into mail  from employees  where name=emp_name;
end
//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call tarena.p4("王小红",@m);
Query OK, 1 row affected (0.00 sec)

mysql> select  @m;
+----------------------+
| @m                   |
+----------------------+
| wangxiaohong@tedu.cn |
+----------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

(3) inout

既有in参数的功能又有out参数的功能

mysql>  delimiter //
mysql>  create procedure tarena.myadd(INOUT i int)
    -> begin
    -> set i=i+100;
    -> end //
mysql> delimiter ;

mysql> set  @x = 8 , @y = 9 ;
Query OK, 0 rows affected (0.00 sec)

mysql> call tarena.myadd(@x);
Query OK, 0 rows affected (0.00 sec)

mysql> call tarena.myadd(@y);
Query OK, 0 rows affected (0.00 sec)

mysql> select  @x , @y;
+------+------+
| @x   | @y   |
+------+------+
|  108 |  109 |
+------+------+
1 row in set (0.00 sec)

mysql> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3、流程控制

流程控制结构

具体如下：

顺序结构：自上向下执行；

分支结构：从多条路径中选择一条路径执行；

循环结构：条件成立时，反复执行一段代码。

3.1 顺序结构----判断语句–if语句

格式：

# 格式1  一个判断条件
IF 条件 THEN
  语句;
END IF;


# 格式2  条件不成立的时候执行什么操作
IF 条件 THEN
  语句1;
ELSE
  语句2;
END IF;	


# 格式3  有多个判断条件
IF 条件1 THEN
  语句1;
ELSEIF 条件2 THEN
  语句2;
ELSE
  语句3;
END IF;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

例子：

mysql> delimiter //
mysql> create procedure tarena.deptype_pro(IN no int, OUT dept_type varchar(5))
    -> begin
    -> declare type varchar(5);
    -> select dept_name into type from departments where dept_id=no;
    ->      if type='运维部' then
    ->          set dept_type='技术部';
    ->        elseif type='开发部' then
    ->          set dept_type='技术部';
    ->        elseif type='测试部' then
    ->          set dept_type='技术部';
    ->        else
    ->          set dept_type='非技术部';
    ->        end if;
    ->      end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call deptype_pro(2,@t);
Query OK, 1 row affected (0.00 sec)

mysql> select  @t;
+--------------+
| @t           |
+--------------+
| 非技术部     |
+--------------+
1 row in set (0.00 sec)

mysql> call deptype_pro(4,@t);
Query OK, 1 row affected (0.00 sec)

mysql> select  @t;
+-----------+
| @t        |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

mysql> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

3.2 分支结构----case语句

格式：

CASE 变量|表达式|字段
WHEN 判断的值1 THEN 返回值1;
WHEN 判断的值2 THEN 返回值2;
... ...
ELSE 返回值n;
END CASE;

1
2
3
4
5
6
7

例子：

delimiter  //
create procedure tarena.deptype_pro2(IN no int, OUT dept_type varchar(5))
begin
declare type varchar(5);
select dept_name into type from departments  where dept_id=no;
case type
when '运维部' then set dept_type='技术部';
when '开发部' then set dept_type='技术部';
when '测试部' then set dept_type='技术部';
else set dept_type='非技术部';
end case; 
end //      
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13

调用存储过程tarena.deptype_pro2

call  tarena.deptype_pro2(1,@t);  @使用自定义变量接收out参数的值
select  @t;  查看查看自定义变量@t的值 

mysql> call deptype_pro2(4,@t);
Query OK, 1 row affected (0.00 sec)

mysql> select  @t;
+-----------+
| @t        |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)
mysql> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.3 循环结构----while、loop、repeat

（1）while循环

格式：

while 判断条件 do      
     代码
end while;
1
2
3

例子：

# 条件判断成立就执行do下边的命令  反之执行end while 结束循环
delimiter //
create procedure tarena.while_pro(IN i int)
begin
declare j int default 1;
while j<i do      
    insert into tarena.departments(dept_name) values('hr');  
	set j=j + 1; 
end while;
end //
delimiter ;

mysql> select * from  departments;
mysql> call  tarena.while_pro(3);
mysql> select * from  departments;

# 当首次判断条件就没成立，while是不会执行的。
mysql> call  tarena.while_pro(0);
mysql> call  tarena.while_pro(1);
mysql> select * from  departments;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

（2）loop 循环结构

没有判断条件， 重复执行同一段代码 ，只要不人为结束就一直执行， 所以被称为死循环。

格式：

loop
	代码
end loop；
1
2
3

例子：

delimiter  //
create procedure tarena.loop2()
begin
declare i int default 1;
loop
	select  sleep(1) , i;
end loop；
end  //
delimiter ;


call  tarena.loop1();
1
2
3
4
5
6
7
8
9
10
11
12

终止循环

在mysql登录状态下 查看正在执行的命令
mysql>  show  processlist;

在mysql登录状态下终止命令的执行
mysql>  kill   id号;

1
2
3
4
5
6

（3）repeat循环

至少循环一次

因为先执行循环体 ，再判断条件（当判断条件成立时继续执行循环体（判断条件不成立为为真），反之结束循环）

格式：

repeat
   循环体
   until 判断条件
end repeat;
1
2
3
4

例子：

delimiter //
create procedure tarena.repeat_pro(IN i int)
begin
declare j int default 1;
repeat
   set j=j+1;  
   insert into tarena.departments(dept_name) values('sales');
   until j>i  #判断条件不成立执行循环体，反之循环结束
end repeat;
end //
delimiter ;

mysql> call  tarena.repeat_pro(4);
mysql> select  * from tarena.departments;


# 验证repeat 是先执行循环体 再判断条件的
mysql> call  tarena.repeat_pro(0);  判断条件成立了 也执行了添加部门的insert into  命令
mysql> select  * from tarena.departments;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（4）循环控制语句

leave (结束循环)

iterate 终止当前循环并开始下次循环

leave例子：

delimiter //
create procedure tarena.p0()
begin
	loop
	     select sleep(1);
	     select "one";
	end loop;
end
//
delimiter ;


mysql> call tarena.p0 ;  一直在输出


drop procedure tarena.p0 ;

delimiter //
create procedure tarena.p0()
begin
	p:loop
             leave p;
	     select sleep(1);
	     select "one";
	end loop p;
end
//
delimiter ;

mysql> call tarena.p0 ;  没有输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

iterate例子:

delimiter //
create procedure tarena.while_pro3(IN i int)
begin
	declare j int default 0;
	a:while j<i do
		set j=j+1;
		if mod(j,2)=0 then iterate a;  #变量j 存储的是偶数时，开始下一次循环
		end if;
		insert into tarena.departments(dept_name) values(concat('hr', j));
    end while a;
end //
delimiter ;  
 
mysql> call  tarena.while_pro3(10);
mysql> select  * from  tarena.departments;	

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

ect * from tarena.departments;

验证repeat 是先执行循环体 再判断条件的

mysql> call tarena.repeat_pro(0); 判断条件成立了 也执行了添加部门的insert into 命令
mysql> select * from tarena.departments;

#### （4）循环控制语句

**leave (结束循环)** 

**iterate 终止当前循环并开始下次循环**



**leave例子：**

```sql
delimiter //
create procedure tarena.p0()
begin
	loop
	     select sleep(1);
	     select "one";
	end loop;
end
//
delimiter ;


mysql> call tarena.p0 ;  一直在输出


drop procedure tarena.p0 ;

delimiter //
create procedure tarena.p0()
begin
	p:loop
             leave p;
	     select sleep(1);
	     select "one";
	end loop p;
end
//
delimiter ;

mysql> call tarena.p0 ;  没有输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

iterate例子:

delimiter //
create procedure tarena.while_pro3(IN i int)
begin
	declare j int default 0;
	a:while j<i do
		set j=j+1;
		if mod(j,2)=0 then iterate a;  #变量j 存储的是偶数时，开始下一次循环
		end if;
		insert into tarena.departments(dept_name) values(concat('hr', j));
    end while a;
end //
delimiter ;  
 
mysql> call  tarena.while_pro3(10);
mysql> select  * from  tarena.departments;	

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16