不废话,这次没有太多花里胡哨的,只有干货——-
mysql中的流程控制
if()函数
先来最基本的两个函数—-
mysql> select if(true,'hello','world');
+--------------------------+
| if(true,'hello','world') |
+--------------------------+
| hello |
+--------------------------+
1 row in set (0.00 sec)
mysql> select if(false,'hello','world');
+---------------------------+
| if(false,'hello','world') |
+---------------------------+
| world |
+---------------------------+
1 row in set (0.00 sec)
mysql>
case 函数
select *,case job
when 'clerk' then '收银员'
when 'salesman' then '销售员'
when 'analyst' then '市场分析师'
when 'manager' then '小白领'
else '领导'
end
中文职位
from emp1
;
流程控制里的if
create PROCEDURE testmy(in name varchar(6),out countnum int)
begin
declare mycondition1 CONDITION for 1048 ;
declare mycondition2 condition for SQLSTATE '42000';
declare continue HANDLER for mycondition1 set @info='继续';
declare exit HANDLER for mycondition2 set @info='停止';
if name is null or name=''
then
select * from emp ;
elseif name ='king'
then
select * from emp where ename='king';
else
select * from emp where job='clerk';
end if ;
select FOUND_ROWS() into countnum;
end;;
> OK
> 时间: 0.027s
call testmy('king',@countnum);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7893 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
select @countnum;
+-----------+
| @countnum |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
注意的点是 elseif在mysql中要合在一块写;
流程控制里的case
有两种方式—
第一种——-如下代码
delimiter ;;
-- 查询在这个工资以下的员工信息
CREATE procedure mypro8(in sale int,out countnum int)
BEGIN
case
when sale <=0 then update emp1 set salgrade ='已离职' where sal <=sale ;
when sale<= 1800 then update emp1 set salgrade='涨工资500' where sal <=sale ;
when sale <=3000 then update emp1 set salgrade='涨工资300' where sal <=sale ;
else UPDATE emp1 set salgrade ='维持原工资' where sal >3000;
end case;
end
> Affected rows: 0
> 时间: 0.03s
-- 调用
call mypro8(1800,@count)
> OK
> 时间: 0.025s
mysql> select * from emp1;---原表参考case函数里的图
+-------+--------+-----------+------+------------+---------+---------+--------+-----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | SALGRADE |
+-------+--------+-----------+------+------------+---------+---------+--------+-----------+
| 7367 | NULL | NULL | NULL | NULL | NULL | NULL | 40 | NULL |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 涨工资500 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 涨工资500 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 涨工资500 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | NULL |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 涨工资500 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | NULL |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | NULL |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | NULL |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 涨工资500 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 涨工资500 |
| 7893 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | NULL |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 涨工资500 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 涨工资500 |
+-------+--------+-----------+------+------------+---------+---------+--------+-----------+
15 rows in set (0.00 sec)
第二种就类似于case函数的形式,
CREATE procedure mypro9(in works varchar(18),out countnum int)
BEGIN
case works
when 'clerk' then update emp1 set salgrade ='涨工资1000' where job=works ;
when 'salesman' then update emp1 set salgrade='涨工资500' where job=works ;
when 'ANALYST' then update emp1 set salgrade='涨工资300' where job=works ;
else UPDATE emp1 set salgrade ='维持原工资' where job=works;
end case;
end
> Affected rows: 0
> 时间: 0.027s
call mypro9('clerk',@countnum);
select * from emp1;
```sql
mysql> select * from emp1;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | SALGRADE |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| 7367 | NULL | NULL | NULL | NULL | NULL | NULL | 40 | NULL |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 涨工资1000 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | NULL |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | NULL |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | NULL |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | NULL |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | NULL |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | NULL |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | NULL |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | NULL |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 涨工资1000 |
| 7893 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | NULL |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 涨工资1000 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | NULL |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 涨工资1000 |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
15 rows in set (0.00 sec)
**这里的CASE语句不能有ELSE NULL子句，并且用ENDCASE替代END来终止。
LOOP语句
说到这我突然就想起了—-benchmark(a,b)函数—将b重复执行a次
mysql> select BENCHMARK(50000000,md5('I love you'));
+---------------------------------------+
| BENCHMARK(50000000,md5('I love you')) |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (20.68 sec)
这个时间是本地时间而不是到服务器的耗时
还有一个可以重复操作的函数—-
select repeat('Iloveyou',6);
+--------------------------------------------------+
| repeat('Iloveyou',6) |
+--------------------------------------------------+
| IloveyouIloveyouIloveyouIloveyouIloveyouIloveyou |
+--------------------------------------------------+
1 row in set (0.00 sec)
说正题—–
loop循环控制函数—–
LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程;
格式–
执行加法操作,求100之内的整数的和;
mysql> create function fun_add(num int) returns int
-> reads sql data
-> begin
-> declare i int default 0;
-> declare result int default 0;
->
-> myloop:loop
-> set i=i+1;
-> set result=i+result;
-> if i>=num
-> then
-> leave myloop;
-> end if;
-> end loop myloop;
-> return result;
-> end ;;
Query OK, 0 rows affected (0.03 sec)
mysql> select fun_add(100);
-> ;;
+--------------+
| fun_add(100) |
+--------------+
| 5050 |
+--------------+
1 row in set (0.00 sec)
版权声明：本文为weixin_54061333原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。