MySQL Life

mysql存储过程及流程控制案例

时间:2018-12-9 15:22:57  作者:solgle  来源:solgle.com  查看:398  评论:0
内容摘要:mysql存储过程及流程控制案例
mysql存储过程及流程控制案例
create procedure my_loop (in num1 bigint)
sql security invoker 
 begin 
  set @x=0;  
  ins:loop
    set @x=@x+1;
      if @x>=num1 then
             leave ins;   --退出loop循环
      end if;
    select @x;
  end loop;
 end; 
 
mysql> call my_loop(3);
+------+
| @x   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
+------+
| @x   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
 
create procedure my_loop2 (in num1 bigint)
sql security invoker 
begin 
  set @x=0;  
  ins:loop
    set @x=@x+1;
      if @x>=num1 then
             leave ins;    /* 退出整个loop循环 */
      elseif mod(@x,2)=0 then
     iterate ins;  /* 退出本次循环,直接进入下一轮循环 */
      end if;
    select @x;
  end loop;
end; 
 
mysql> call my_loop2(4);
 
+------+
| @x   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
 
create procedure get_city()
sql security invoker
begin
  declare id bigint;
  declare city_name varchar(20);
  declare country_name varchar(30);
  declare cur_cl cursor for select * from myai;
  declare exit handler for not found close cur_cl;  /* 处理错误:ERROR 1329 (02000): No data - zero rows fetched, selected, */ 
 
  open cur_cl;
  repeat
    fetch cur_cl into id,city_name,country_name;
    select id,city_name,country_name;
  until 0 end repeat;
 
  close cur_cl;    
end;
 
mysql> call get_city;
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    1 | ???       | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    2 | ??        | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    3 | ??        | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    4 | ??        | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    5 | 成都      | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
 
create procedure get_city2()
sql security invoker
begin
  declare id bigint;
  declare city_name varchar(20);
  declare country_name varchar(30);
  declare x bigint default 0;
  declare cur_cl cursor for select * from myai;
  declare exit handler for not found close cur_cl;
 
  open cur_cl;
  repeat
    fetch cur_cl into id,city_name,country_name;
    select id,city_name,country_name;
    
    case when id=1 then 
   set x=-100;
         else 
           set x=x+id; 
    end case; 
    select x; 
  until 0 end repeat;
  
  close cur_cl;    
  
end;
 
mysql> call get_city2;
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    1 | ???       | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+
| x    |
+------+
| -100 |
+------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    2 | ??        | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+
| x    |
+------+
|  -98 |
+------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    3 | ??        | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+
| x    |
+------+
|  -95 |
+------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    4 | ??        | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+
| x    |
+------+
|  -91 |
+------+
1 row in set (0.00 sec)
 
+------+-----------+--------------+
| id   | city_name | country_name |
+------+-----------+--------------+
|    5 | 成都      | china        |
+------+-----------+--------------+
1 row in set (0.00 sec)
 
+------+
| x    |
+------+
|  -86 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
--事件调度器(每一分钟清空一下表)
mysql> create event trunc_temp_ai on schedule every 1 minute do truncate table temp_ai;
Query OK, 0 rows affected (0.02 sec)

----下面命令将打开后台调度
mysql> set global event_scheduler=1;
Query OK, 0 rows affected (0.00 sec)

----查看后台调度进程状态
mysql> show variables like '%scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

----查看后台进程
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                       | Info             |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+
|  6 | root            | localhost | test | Query   |    0 | NULL                        | show processlist |
|  7 | event_scheduler | localhost | NULL | Daemon  |   60 | Waiting for next activation | NULL             |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+
2 rows in set (0.00 sec)
 
mysql> 
-----禁用及删除该调度evnet
mysql> 
mysql>alter event trunc_temp_ai disables;

mysql>drop event trunc_temp_ai;

----关闭scheduler的event功能
mysql> set global event_scheduler=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show processlist ;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  6 | root | localhost | test | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
 
mysql> 
 
 
标签:mysql流程控制案例 

solgle.com 版权所有,欢迎分享!!!

上一篇:没有了
下一篇:MySql的存储过程及函数
相关文章
    相关评论
       Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1