前言:
当前大家对“mysql定义游标”大体比较着重,同学们都需要分析一些“mysql定义游标”的相关内容。那么小编也在网上汇集了一些对于“mysql定义游标””的相关资讯,希望我们能喜欢,小伙伴们快快来学习一下吧!Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。
欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。
这是Mysql系列第18篇。
环境:mysql5.7.25,cmd命令中进行演示。
代码中被[]包含的表示可选,|符号分开的表示可选其一。
上一篇存储过程&自定义函数,对存储过程和自定义函数做了一个简单的介绍,但是如何能够写出复杂的存储过程和函数呢?
这需要我们熟练掌握流程控制语句才可以,本文主要介绍mysql中流程控制语句的使用,上干货。
本篇内容if函数case语句if结构while循环repeat循环loop循环循环体控制语句准备数据
/*建库javacode2018*/drop database if exists javacode2018;create database javacode2018;/*切换到javacode2018库*/use javacode2018;/*创建表:t_user*/DROP TABLE IF EXISTS t_user;CREATE TABLE t_user( id int PRIMARY KEY COMMENT '编号', sex TINYINT not null DEFAULT 1 COMMENT '性别,1:男,2:女', name VARCHAR(16) not NULL DEFAULT '' COMMENT '姓名')COMMENT '用户表';/*插入数据*/INSERT INTO t_user VALUES(1,1,'路人甲Java'),(2,1,'张学友'),(3,2,'王祖贤'),(4,1,'郭富城'),(5,2,'李嘉欣');SELECT * FROM t_user;DROP TABLE IF EXISTS test1;CREATE TABLE test1 (a int not null);DROP TABLE IF EXISTS test2;CREATE TABLE test2 (a int not null,b int NOT NULL );if函数语法
if(条件表达式,值1,值2);
if函数有3个参数。
当参数1为true的时候,返回值1,否则返回值2。
示例
需求:查询t_user表数据,返回:编号、性别(男、女)、姓名。
分析一下:数据库中性别用数字表示的,我们需要将其转换为(男、女),可以使用if函数。
mysql> SELECT id 编号,if(sex=1,'男','女') 性别,name 姓名 FROM t_user;+--------+--------+---------------+| 编号 | 性别 | 姓名 |+--------+--------+---------------+| 1 | 男 | 路人甲Java || 2 | 男 | 张学友 || 3 | 女 | 王祖贤 || 4 | 男 | 郭富城 || 5 | 女 | 李嘉欣 |+--------+--------+---------------+5 rows in set (0.00 sec)CASE结构
2种用法。
第1种用法
类似于java中的switch语句。
case 表达式when 值1 then 结果1或者语句1(如果是语句需要加分号)when 值2 then 结果2或者语句2...else 结果n或者语句nend [case] (如果是放在begin end之间需要加case,如果在select后则不需要)示例1:select中使用
查询t_user表数据,返回:编号、性别(男、女)、姓名。
/*写法1:类似于java中的if else*/SELECT id 编号,(CASE sex WHEN 1 THEN '男' ELSE '女' END) 性别,name 姓名 FROM t_user;/*写法2:类似于java中的if else if*/SELECT id 编号,(CASE sex WHEN 1 then '男' WHEN 2 then '女' END) 性别,name 姓名 FROM t_user;示例2:begin end中使用
写一个存储过程,接受3个参数:id,性别(男、女),姓名,然后插入到t_user表
创建存储过程:
/*删除存储过程proc1*/DROP PROCEDURE IF EXISTS proc1;/*s删除id=6的记录*/DELETE FROM t_user WHERE id=6;/*声明结束符为$*/DELIMITER $/*创建存储过程proc1*/CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16)) BEGIN /*声明变量v_sex用于存放性别*/ DECLARE v_sex TINYINT UNSIGNED; /*根据sex_str的值来设置性别*/ CASE sex_str when '男' THEN SET v_sex = 1; WHEN '女' THEN SET v_sex = 2; END CASE ; /*插入数据*/ INSERT INTO t_user VALUES (id,v_sex,name); END $/*结束符置为;*/DELIMITER ;
调用存储过程:
CALL proc1(6,'男','郭富城');
查看效果:
mysql> select * from t_user;+----+-----+---------------+| id | sex | name |+----+-----+---------------+| 1 | 1 | 路人甲Java || 2 | 1 | 张学友 || 3 | 2 | 王祖贤 || 4 | 1 | 郭富城 || 5 | 2 | 李嘉欣 || 6 | 1 | 郭富城 |+----+-----+---------------+6 rows in set (0.00 sec)示例3:函数中使用
需求:写一个函数,根据t_user表sex的值,返回男女
创建函数:
/*删除存储过程proc1*/DROP FUNCTION IF EXISTS fun1;/*声明结束符为$*/DELIMITER $/*创建存储过程proc1*/CREATE FUNCTION fun1(sex TINYINT UNSIGNED) RETURNS varchar(8) BEGIN /*声明变量v_sex用于存放性别*/ DECLARE v_sex VARCHAR(8); CASE sex WHEN 1 THEN SET v_sex:='男'; ELSE SET v_sex:='女'; END CASE; RETURN v_sex; END $/*结束符置为;*/DELIMITER ;
看一下效果:
mysql> select sex, fun1(sex) 性别,name FROM t_user;+-----+--------+---------------+| sex | 性别 | name |+-----+--------+---------------+| 1 | 男 | 路人甲Java || 1 | 男 | 张学友 || 2 | 女 | 王祖贤 || 1 | 男 | 郭富城 || 2 | 女 | 李嘉欣 || 1 | 男 | 郭富城 |+-----+--------+---------------+6 rows in set (0.00 sec)
第2种用法
类似于java中多重if语句。
casewhen 条件1 then 结果1或者语句1(如果是语句需要加分号)when 条件2 then 结果2或者语句2...else 结果n或者语句nend [case] (如果是放在begin end之间需要加case,如果是在select后面case可以省略)
这种写法和1中的类似,大家用上面这种语法实现第1中用法中的3个示例,贴在留言中。
if结构
if结构类似于java中的 if..else if...else的语法,如下:
if 条件语句1 then 语句1;elseif 条件语句2 then 语句2;...else 语句n;end if;
只能使用在begin end之间。
示例
写一个存储过程,实现用户数据的插入和新增,如果id存在,则修改,不存在则新增,并返回结果
/*删除id=7的记录*/DELETE FROM t_user WHERE id=7;/*删除存储过程*/DROP PROCEDURE IF EXISTS proc2;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name varchar(16),OUT result TINYINT) BEGIN DECLARE v_count TINYINT DEFAULT 0;/*用来保存user记录的数量*/ /*根据v_id查询数据放入v_count中*/ select count(id) into v_count from t_user where id = v_id; /*v_count>0表示数据存在,则修改,否则新增*/ if v_count>0 THEN BEGIN DECLARE lsex TINYINT; select if(lsex='男',1,2) into lsex; update t_user set sex = lsex,name = v_name where id = v_id; /*获取update影响行数*/ select ROW_COUNT() INTO result; END; else BEGIN DECLARE lsex TINYINT; select if(lsex='男',1,2) into lsex; insert into t_user VALUES (v_id,lsex,v_name); select 0 into result; END; END IF; END $/*结束符置为;*/DELIMITER ;
看效果:
mysql> SELECT * FROM t_user;+----+-----+---------------+| id | sex | name |+----+-----+---------------+| 1 | 1 | 路人甲Java || 2 | 1 | 张学友 || 3 | 2 | 王祖贤 || 4 | 1 | 郭富城 || 5 | 2 | 李嘉欣 || 6 | 1 | 郭富城 |+----+-----+---------------+6 rows in set (0.00 sec)mysql> CALL proc2(7,'男','黎明',@result);Query OK, 1 row affected (0.00 sec)mysql> SELECT @result;+---------+| @result |+---------+| 0 |+---------+1 row in set (0.00 sec)mysql> SELECT * FROM t_user;+----+-----+---------------+| id | sex | name |+----+-----+---------------+| 1 | 1 | 路人甲Java || 2 | 1 | 张学友 || 3 | 2 | 王祖贤 || 4 | 1 | 郭富城 || 5 | 2 | 李嘉欣 || 6 | 1 | 郭富城 || 7 | 2 | 黎明 |+----+-----+---------------+7 rows in set (0.00 sec)mysql> CALL proc2(7,'男','梁朝伟',@result);Query OK, 1 row affected (0.00 sec)mysql> SELECT @result;+---------+| @result |+---------+| 1 |+---------+1 row in set (0.00 sec)mysql> SELECT * FROM t_user;+----+-----+---------------+| id | sex | name |+----+-----+---------------+| 1 | 1 | 路人甲Java || 2 | 1 | 张学友 || 3 | 2 | 王祖贤 || 4 | 1 | 郭富城 || 5 | 2 | 李嘉欣 || 6 | 1 | 郭富城 || 7 | 2 | 梁朝伟 |+----+-----+---------------+7 rows in set (0.00 sec)循环mysql中循环有3种写法while:类似于java中的while循环repeat:类似于java中的do while循环loop:类似于java中的while(true)死循环,需要在内部进行控制。循环控制
对循环内部的流程进行控制,如:
结束本次循环
类似于java中的continue
iterate 循环标签;退出循环
类似于java中的break
leave 循环标签;
下面我们分别介绍3种循环的使用。
while循环
类似于java中的while循环。
语法
[标签:]while 循环条件 do循环体end while [标签];
标签:是给while取个名字,标签和iterate、leave结合用于在循环内部对循环进行控制:如:跳出循环、结束本次循环。
注意:这个循环先判断条件,条件成立之后,才会执行循环体,每次执行都会先进行判断。
示例1:无循环控制语句
根据传入的参数v_count向test1表插入指定数量的数据。
/*删除test1表记录*/DELETE FROM test1;/*删除存储过程*/DROP PROCEDURE IF EXISTS proc3;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc3(v_count int) BEGIN DECLARE i int DEFAULT 1; a:WHILE i<=v_count DO INSERT into test1 values (i); SET i=i+1; END WHILE; END $/*结束符置为;*/DELIMITER ;
见效果:
mysql> CALL proc3(5);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+---+| a |+---+| 1 || 2 || 3 || 4 || 5 |+---+5 rows in set (0.00 sec)示例2:添加leave控制语句
根据传入的参数v_count向test1表插入指定数量的数据,当插入超过10条,结束。
/*删除存储过程*/DROP PROCEDURE IF EXISTS proc4;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc4(v_count int) BEGIN DECLARE i int DEFAULT 1; a:WHILE i<=v_count DO INSERT into test1 values (i); /*判断i=10,离开循环a*/ IF i=10 THEN LEAVE a; END IF; SET i=i+1; END WHILE; END $/*结束符置为;*/DELIMITER ;
见效果:
mysql> DELETE FROM test1;Query OK, 20 rows affected (0.00 sec)mysql> CALL proc4(20);Query OK, 1 row affected (0.02 sec)mysql> SELECT * from test1;+----+| a |+----+| 1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 || 9 || 10 |+----+10 rows in set (0.00 sec)示例3:添加iterate控制语句
根据传入的参数v_count向test1表插入指定数量的数据,只插入偶数数据。
/*删除test1表记录*/DELETE FROM test1;/*删除存储过程*/DROP PROCEDURE IF EXISTS proc5;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc5(v_count int) BEGIN DECLARE i int DEFAULT 0; a:WHILE i<=v_count DO SET i=i+1; /*如果i不为偶数,跳过本次循环*/ IF i%2!=0 THEN ITERATE a; END IF; /*插入数据*/ INSERT into test1 values (i); END WHILE; END $/*结束符置为;*/DELIMITER ;
见效果:
mysql> DELETE FROM test1;Query OK, 5 rows affected (0.00 sec)mysql> CALL proc5(10);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+----+| a |+----+| 2 || 4 || 6 || 8 || 10 |+----+5 rows in set (0.00 sec)示例4:嵌套循环
test2表有2个字段(a,b),写一个存储过程(2个参数:v_a_count,v_b_count),使用双重循环插入数据,数据条件:a的范围[1,v_a_count]、b的范围[1,v_b_count]所有偶数的组合。
/*删除存储过程*/DROP PROCEDURE IF EXISTS proc8;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc8(v_a_count int,v_b_count int) BEGIN DECLARE v_a int DEFAULT 0; DECLARE v_b int DEFAULT 0; a:WHILE v_a<=v_a_count DO SET v_a=v_a+1; SET v_b=0; b:WHILE v_b<=v_b_count DO SET v_b=v_b+1; IF v_a%2!=0 THEN ITERATE a; END IF; IF v_b%2!=0 THEN ITERATE b; END IF; INSERT INTO test2 VALUES (v_a,v_b); END WHILE b; END WHILE a; END $/*结束符置为;*/DELIMITER ;
代码中故意将ITERATE a;放在内层循环中,主要让大家看一下效果。
见效果:
mysql> DELETE FROM test2;Query OK, 6 rows affected (0.00 sec)mysql> CALL proc8(4,6);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test2;+---+---+| a | b |+---+---+| 2 | 2 || 2 | 4 || 2 | 6 || 4 | 2 || 4 | 4 || 4 | 6 |+---+---+6 rows in set (0.00 sec)
repeat循环语法
[标签:]repeat循环体;until 结束循环的条件 end repeat [标签];
repeat循环类似于java中的do...while循环,不管如何,循环都会先执行一次,然后再判断结束循环的条件,不满足结束条件,循环体继续执行。这块和while不同,while是先判断条件是否成立再执行循环体。
示例1:无循环控制语句
根据传入的参数v_count向test1表插入指定数量的数据。
/*删除存储过程*/DROP PROCEDURE IF EXISTS proc6;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc6(v_count int) BEGIN DECLARE i int DEFAULT 1; a:REPEAT INSERT into test1 values (i); SET i=i+1; UNTIL i>v_count END REPEAT; END $/*结束符置为;*/DELIMITER ;
见效果:
mysql> DELETE FROM test1;Query OK, 1 row affected (0.00 sec)mysql> CALL proc6(5);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+---+| a |+---+| 1 || 2 || 3 || 4 || 5 |+---+5 rows in set (0.00 sec)
repeat中iterate和leave用法和while中类似,这块的示例算是给大家留的作业,写好的发在留言区,谢谢。
loop循环语法
[标签:]loop循环体;end loop [标签];
loop相当于一个死循环,需要在循环体中使用iterate或者leave来控制循环的执行。
示例1:无循环控制语句
根据传入的参数v_count向test1表插入指定数量的数据。
/*删除存储过程*/DROP PROCEDURE IF EXISTS proc7;/*声明结束符为$*/DELIMITER $/*创建存储过程*/CREATE PROCEDURE proc7(v_count int) BEGIN DECLARE i int DEFAULT 0; a:LOOP SET i=i+1; /*当i>v_count的时候退出循环*/ IF i>v_count THEN LEAVE a; END IF; INSERT into test1 values (i); END LOOP a; END $/*结束符置为;*/DELIMITER ;
见效果:
mysql> DELETE FROM test1;Query OK, 5 rows affected (0.00 sec)mysql> CALL proc7(5);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+---+| a |+---+| 1 || 2 || 3 || 4 || 5 |+---+5 rows in set (0.00 sec)
loop中iterate和leave用法和while中类似,这块的示例算是给大家留的作业,写好的发在留言区,谢谢。
总结本文主要介绍了mysql中控制流语句的使用,请大家下去了多练习,熟练掌握if函数常用在select中case语句有2种写法,主要用在select、begin end中,select中end后面可以省略case,begin end中使用不能省略caseif语句用在begin end中3种循环体的使用,while类似于java中的while循环,repeat类似于java中的do while循环,loop类似于java中的死循环,都用于begin end中循环中体中的控制依靠leave和iterate,leave类似于java中的break可以退出循环,iterate类似于java中的continue可以结束本次循环
Mysql系列目录
MySQL系列-第18篇:流程控制语句(高手进阶)
标签: #mysql定义游标