手把手教你mysql(十五)游标变量流程控制
前言:
这是我准备的测试用数据
Mysql>DROP TABLE IF EXISTS `t_user`;
Mysql>CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);
Mysql>DROP TABLE IF EXISTS `t_user`
Mysql>CREATE TABLE `t_user2`(
id2 INT PRIMARY KEY AUTO_INCREMENT,
username2 VARCHAR(20),
password2 VARCHAR(20)
);
Mysql>INSERT INTO `t_user2` VALUES (NULL,'aaa','1234');
一: 变量的使用
1.1 定义变量
DECLARE var_name [,...] type [ DEFAULT value ;
1.2 为变量赋值
语法1: SET var_name = expr [,var_name=expr] ... 通过Set来给变量赋值
例: mysql>DELIMITER $$
CREATE PROCEDURE pro_user()
BEGIN
DECLARE a,b VARCHAR(30);#定义变量a,b
SET a='Jon',b='123456';#给变量赋值
INSERT INTO t_user VALUES(NULL,a,b);
END;
$$ DELIMITER ;
语法2: SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition 通过SELECT语句来给变量赋值
Mysql>DELIMITER $$
CREATE PROCEDURE pro_user2()
BEGIN
DECLARE a,b VARCHAR(30);#定义变量a,b
SELECT username2,password2 INTO a,b FROM t_user2 WHERE id2=1;#通过Select语句为变量赋值
INSERT INTO t_user VALUES(NULL,a,b);
END;
$$ DELIMITER ;
二: 游标的使用
查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。
2.1 声明游标
DECLARE cursor_name CURSOR FOR select_statement ;
2.2 打开游标
OPEN cursor_name;
2.3 使用游标
FETCH cursor_name INTO var_name [,var_name ... ];
2.4 关闭游标
CLOSE cursor_name;
例:通过使用游标将t_user2的数据全部插入到t_user表中(目前只有一条数据,等讲完循环,就能实现多条插入),
Mysql>UPDATE t_user2 SET username2='bbb',password2='123456';#更新记录,为了显示效果
Mysql>DELIMITER $$
CREATE PROCEDURE pro_user3()
BEGIN
DECLARE a,b VARCHAR(30);
DECLARE cur_t_user2 CURSOR FOR SELECT username2,password2 FROM t_user2;#在查询出的结果集中定义游标
OPEN cur_t_user2;#打开游标
FETCH cur_t_user2 INTO a,b;#使用游标,取出数据存入变量
INSERT INTO t_user VALUES(NULL,a,b);
CLOSE cur_t_user2;#别忘了关闭游标
END;
$$ DELIMITER ;
三: 流程控制的使用
存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和WHILE 语句来进行流程控制。
3.1 IF 语句
IF search_condition THEN statement_list
[ ELSEIF search_condition THEN statement_list ]...
[ ELSE statement_list ]
END IF
例:创建名为pro_user4的存储过程,根据输入的id来判断记录是否存在然后做对应的操作.
mysql>DELIMITER $$
CREATE PROCEDURE pro_user4(IN userId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=userId; #@num是用户变量
IF @num>0 THEN #判断记录是否存在
UPDATE t_user SET username='oop' WHERE id=userId;
ELSE
INSERT INTO t_user VALUES(NULL,'oop','123456');
END IF;#别忘了End if;
END;
$$ DELIMITER ;
什么是用户变量?
你可以把它想象为全局变量
用户变量和局部变量的区别是什么?
用户变量可以作用于当前整个连接 Declare是定义局部变量 其作用域仅限于语句块,当语句块结束,变量就消失了。
大家可以看到,我这边输入的是4,因为记录不存在,@num=0,所以插入一条新数据。
3.2 CASE 语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list ]
END CASE
例:创建一个存储过程,使用case来判断用户输入的值做不同的操作
Mysql>DELIMITER $$
CREATE PROCEDURE pro_user5(IN userId INT)
BEGIN
CASE userId
WHEN 1 THEN UPDATE t_user SET username='xxx' WHERE id=userId;
WHEN 2 THEN UPDATE t_user SET username='xxx' WHERE id=userId;
ELSE INSERT INTO t_user VALUES(NULL,'xxx','123456');
END CASE;#别忘了End Case
END;
$$ DELIMITER ;
3.3 LOOP,LEAVE 语句
LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP 语句本身没有停止循环的语句，必须是遇到LEAVE 语句等才能停止循环。
LOOP 语句的语法的基本形式如下：
[begin_label：]LOOP
Statement_list
END LOOP [ end_label ];
LEAVE 语句主要用于跳出循环控制。语法形式如下：
LEAVE label
例:循环插入t_user表total条数据
Mysql>DELIMITER $$
CREATE PROCEDURE `pro_user6`(IN total INT)
BEGIN
aa:LOOP #aa为标签
IF total=0 THEN
LEAVE aa;
ELSE
INSERT INTO t_user VALUES(NULL,'ccc','111111');
END IF;
SET total=total-1;
END LOOP aa;
END;
$$ DELIMITER ;
3.4 ITERATE 语句
ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次
循环。
基本语法：ITERATE label ;
Mysql>DELIMITER $$
CREATE PROCEDURE `pro_user7`(IN total INT)
BEGIN
aa:LOOP #aa为标签
SET total=total-1;
IF total = 0 THEN
LEAVE aa;
ELSEIF total>2 THEN
ITERATE aa; #当total大于2 跳出本次循环然后进入下一次
END IF;
INSERT INTO t_user VALUES(NULL,'eee','111111');
END LOOP aa;
END;
$$ DELIMITER ;
这边你们的id应该是11 12,我不小心多插了条记录,Sorry;
3.5 REPEAT 语句
REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本
语法形式如下：
[ begin_label : ] REPEAT
Statement_list
UNTIL search_condition
END REPEAT [ end_label ];
Mysql>DELIMITER $$
CREATE PROCEDURE `pro_user8`(IN total INT)
BEGIN
REPEAT
SET total=total-1;
INSERT INTO t_user VALUES(NULL,'kkkkk','111111');
UNTIL total=2 #这边没有;
END REPEAT;
END;
$$ DELIMITER ;
3.6 WHILE 语句
[ begin_label : ] WHILE search_condition DO
Statement_list
END WHILE [ end_label ];
Mysql>DELIMITER $$
CREATE PROCEDURE `pro_user9`(IN total INT)
BEGIN
WHILE total>4 DO
INSERT INTO t_user VALUES(NULL,'pppp','111111');
SET total=total-1;
END WHILE;
END;
$$ DELIMITER ;
四: 总结
今天为大家带来了游标,变量,流程控制的知识,量比较大,大家好好消化一下,下节为大家带来数据备份与还原的知识,谢谢大家!