• 当前位置：首页 > SQL Life
SQL Life

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

时间：2018/12/9 15:22:57  作者：solgle  来源：solgle.com  查看：628  评论：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 版权所有，欢迎分享！！！

上一篇：Oceanbase摘要笔记
下一篇：MySql的存储过程及函数
相关文章
    相关评论
     img1 img2 img3 img4 img5 img6 img7 img8 img9 img10
    评论者：      验证码：  点击获取验证码
    本类更新
    本类推荐
    本类排行
    国家数据 | 中证指数 | Oracle官方文档 | 沪深行情 | 流量统计 | 深交所 | 上交所 | 中国证券登记 | 同业拆放利率 | 巨潮资讯 | 指数估值 | 看财报 |
    联系我们 - 关于我们 - 网站同盟
       Copyright © 2013-2028 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1