Mysql 视图触发器定时任务 (event事件) 游标的使用

一、视图

视图的定义:
虚拟表,和普通表一样使用；MySQL从5.0.1版本开始提供视图功能，一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，行和列的数据来自自定义视图的查询中使用的表，是通过表动态生成的数据,只保存了sql逻辑，不保存查询结果。
应用场景：
- 多个地方用到同样的查询结果。
- 该查询结果使用的sql语句比较复杂。

#示例：
Create view my_v1
as
Select studentname,majorname
from student s
inner join major m
on s.majorid=m.majorid
where s.majorid=1;

1.使用视图

案例: 查询姓张的学生名和专业名
方法一
select 学生名,专业名
from 学生表 s
inner join 专业表 m
on s.专业id=m.id
where s.学生名 like '张%';

# 方法二 使用视图
create view v1
as
select 学生名,专业名
from 学生表 s
inner join 专业表 m
on s.专业id=m.id;

select * from v1 where 学生名 like '张%';

2.视图的常用操作

# 1.创建视图:
语法:
create view 视图名
as
查询语句;

# 2.使用视图:
select * from 视图名 where 条件...;

# 3.视图的作用:
重用sql语句
简化复杂的sql操作,不必要知道它的查询细节.
与基表分离,保护数据,提高安全性.

# 4.视图的修改:
方式一(创建或修改):
create or replace view 视图名
as
查询语句

方式二
alter view 视图名
as
查询语句

# 5.视图的删除
drop view 视图名,视图名...;

6.查看视图
show tables;  # 可以直接在库下看到视图在表的列表中.(但其实是sql逻辑)
desc 视图名;
show create view 视图名;

# 7.视图的更新
1.)可以插入数据
insert into 视图名 values(值);

2.)增删改查都与表操作一样,可以直接操作视图.
# 注意,视图的增删改查操作都会对原表产生修改.所以一般会给视图设置只读权限.

以下是视图不可更新的情况:

3.视图与表的区别

=====创建语法======是否实际占用物理库空间====使用===============
视图 create view   没有占用,只保存sql逻辑   增删改会改动原数据表
表   create table  保存了数据              增删改查都可

使用案例

二、触发器

# 定义:
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

# ！！尽量少使用触发器，不建议使用。

　　假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

　　触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

1.创建触发器

# 语法
create trigger trigger_name
　　on {table_name | view_name}
　　{Before | After | Instead of }
　　[ insert,update,delete ]
　　FOR EACH ROW　[trigger_order]
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
trigger_order是MySQL5.7之后的一个功能，用于定义多个触发器，使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。 

# 例句:
delimiter  $create trigger 触发器名 before|after insert|delete|update on 表名 for each row begin ...(new|old); end$ 
这里的new对应insert语句,old对应delete语句,update对应new|old两者.

当一条命令执行多条操作的时候,每一条数据操作都会引起触发器反应,执行相应操作.

# 1、创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

# 例:
创建了一个名为trig1的触发器，一旦在work表中有插入动作，就会自动往time表里插入当前时间
mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

# 2、创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;

# 例1:
delimiter  $create trigger t1 before insert on 表1 for each row begin insert into 表2(tname) values('aaaaa'); end$ 
测试：
insert into 表1(性别,sname) values('女','小张'),('女','小王')
结果:表1插入2条数据的时候,表2的tname列同时插入2条值aaaaa

# 例2:
delimiter  $create trigger t1 before insert on 表1 for each row begin insert into 表2(tname) values(new.sname); end$ 
insert into 表1(性别,sname) values('女','小张'),('女','小王')
结果:表1插入2条数据的时候,表2的tname列同时插入2条值('女','小张'),('女','小王')

注意:

# 另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

例3：

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter  $mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount<0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount>100 THEN -> SET NEW.amount = 100; -> END IF; -> END$ 
mysql> delimiter ;

mysql> update account set amount=-10 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

mysql> update account set amount=200 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

2.查看触发器

# 语句查看触发器信息
1、SHOW TRIGGERS

mysql> SHOW TRIGGERS\G;
……
结果，显示所有触发器的基本信息；无法查询指定的触发器。

2、在information_schema.triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggers\G
……
结果，显示所有触发器的详细信息；同时，该方法可以查询制定触发器的详细信息。

# Tips：所有触发器信息都存储在information_schema数据库下的triggers表中.
可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。
mysql> select * from information_schema.triggers 
    -> where trigger_name='upd_check'\G;

3.修改触发器

alter trigger trigger_name--类似于修改表结构

4.删除触发器

# 删除语法:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

三、事件简介

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

1.事件的优缺点

优点

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

缺点

定时触发，不可以调用。

2.创建事件

一条create event语句创建一个事件。
每个事件由两个主要部分组成，第一部分是事件调度（eventschedule，表示事件何时启动以及按什么频率启动;

第二部分是事件动作（event action），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者
benin...end语句块，这两种情况允许我们执行多条SQL。

一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次。

3.创建语法

CREATE
  [DEFINER = { user | CURRENT_USER }] 
  EVENT 
  [IF NOT EXISTS] 
  event_name 
  ON SCHEDULE schedule 
  [ON COMPLETION [NOT] PRESERVE] 
  [ENABLE | DISABLE | DISABLE ON SLAVE] 
  [COMMENT 'comment'] 
  DO event_body; 

schedule: 
  AT timestamp [+ INTERVAL interval] ... 
 | EVERY interval 
  [STARTS timestamp [+ INTERVAL interval] ...] 
  [ENDS timestamp [+ INTERVAL interval] ...] 

interval: 
  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
       WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
       DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}


# 名词解释：
event_name ：创建的event名字（唯一确定的）。
ON SCHEDULE：计划任务。
schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY。
[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉。
[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节。
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改
DO event_body: 需要执行的sql语句（可以是复合语句）。CREATE EVENT在存储过程中使用时合法的。

4.开启关闭事件调度器

1）事件调度器event_scheduler

event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件是否要调用，要创建事件，必须打开调度器。

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name   | Value | 
+-----------------+-------+ 
| event_scheduler | OFF   | 
+-----------------+-------+

2）开启事件调度器

通过命令行
可通过如下任何一个命令行

SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1;

或通过配置文件my.cnf
event_scheduler = 1 #或者ON 


# 查看调度器线程
mysql> show processlist; 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 
| Id | User      | Host   | db  | Command | Time | State         | Info       | 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 
| 2 | root      | localhost | NULL | Query  |  0 | NULL          | show processlist | 
| 3 | event_scheduler | localhost | NULL | Daemon |  6 | Waiting on empty queue | NULL| 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

3）关闭事件调度器

通过命令行
可通过如下任何一个命令行

SET GLOBAL event_scheduler = OFF;  
SET @@global.event_scheduler = OFF;  
SET GLOBAL event_scheduler = 0;  
SET @@global.event_scheduler = 0; 

通过配置文件my.cnf
在[mysqld]下增加
event_scheduler = 0 #或者OFF，DISABLED 

# 查看调度器线程
mysql> show processlist; 
+----+------+-----------+------+---------+------+-------+------------------+ 
| Id | User | Host      | db   | Command | Time | State | Info             | 
+----+------+-----------+------+---------+------+-------+------------------+ 
| 2  | root | localhost | NULL | Query   |  0   | NULL  | show processlist | 
+----+------+-----------+------+---------+------+-------+------------------+

4）创建一个表记录每次事件调度的名字和事件戳 (举例)

5.创建测试表

mysql> drop table if exists events_list; 
mysql> create table events_list(event_name varchar(20) not null, event_started timestamp not null);

1）创建事件1（立即启动事件）

create event event_now 
on schedule 
at now() 
do insert into events_list values('event_now', now());

2）查看事件执行结果

mysql> select * from events_list; 
+------------+---------------------+ 
| event_name | event_started       | 
+------------+---------------------+ 
| event_now  | 2018-07-01 04:06:40 | 
+------------+---------------------+

3）创建事件2（每分钟启动事件）

create event test.event_minute 
on schedule 
every 1 minute  
do insert into events_list values('event_now', now());

4）查看事件执行结果

mysql> select * from events_list; 
+------------+---------------------+ 
| event_name | event_started       | 
+------------+---------------------+ 
| event_now  | 2018-07-01 04:26:53 | 
| event_now  | 2018-07-01 04:27:53 | 
| event_now  | 2018-07-01 04:28:53 | 
+------------+---------------------+

5）创建事件3（每秒钟启动事件）

CREATE event event_now 
ON SCHEDULE 
EVERY 1 SECOND
DO INSERT INTO event_test VALUES(1);

6）创建事件4（每秒钟调用存储过程）

CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus` 
ON SCHEDULE EVERY 1 SECOND 
STARTS '2017-11-21 00:12:44' 
ON COMPLETION PRESERVE 
ENABLE 
DO call updateStatus()

注意：

默认创建事件存储在当前库中，也可显示指定事件创建在哪个库中
通过show events只能查看当前库中创建的事件
事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件可以查看到。
如果两个事件需要在同一时刻调用，mysql会确定调用他们的顺序，如果要指定顺序，需要确保一个事件至少在另一个事件1秒后执行

对于递归调度的事件，结束日期不能在开始日期之前。
select可以包含在一个事件中，然而他的结果消失了，就好像没执行过。

6.查看事件的语法

#查看当前所在库的事件
mysql> show events;

#查看所有事件
mysql> select * from mysql.event;

#查看创建好的event详细信息，在进入当前db后
show create event xxx\G

7.event的信息查询和含义

查看某个event的状态信息，可查看mysql.event或者information_schema.events，或者简单地切到当前DB后执行show events; 三者的内容基本一致，information_schema无法做了下数据复制，更改了下列名称和starts时间以便更好的阅读。这里已information_schema.events里的信息为例解释

EVENT_CATALOG：一般都是def，不管

EVENT_SCHEMA：event所在的schema

EVENT_NAME：event的名称

DEFINER：event的定义者，和定义这个event时，默认selectcurrent_user()的结果一致，如果该user有super权限，可以指定为其他用户

TIME_ZONE：event使用的时区，默认是system，建议别做修改

EVENT_BODY：一般都是SQL，不用管

EVENT_DEFINITION：该event的内容，可以是具体的insert等SQL，也可以是一个调用存储过程的操作

EVENT_TYPE：这个参数比较重要，定义的时候指定，有两个值：RECURRING和ONE TIME，RECURRING表示只要符合条件就会重复执行，而ONE TIME只会调用一次

EXECUTE_AT： 针对one-time类型的event有效，如果是RECURRING类型的event一般为NULL，表示该event的预计执行时间

INTERVAL_VALUE：针对RECURRING类型的event有效，表示执行间隔长度

INTERVAL_FIELD：针对RECURRING类型的event有效，表示执行间隔的单位，一般是SECOND，DAY等值，可参考创建语法

SQL_MODE：当前event采用的SQL_MODE

STARTS：针对RECURRING类型的event有效，表示一个event从哪个时间点点开始执行，和one-time的EXECUTE_AT功能类似。为NULL表示一符合条件就开始执行

ENDS：针对RECURRING类型的event有效，表示一个event到了哪个时间点后不再执行，如果为NULL就是永不停止

STATUS：一般有三个值，ENABLED, DISABLED和 SLAVESIDE_DISABLED，其中ENABLED表示激活这个event，该event只要符合其他条件就会执行；DISABLED状态改event将不会执行，SLAVESIDE_DISABLED表示在从库上不执行该event。需要特别注意在从库上不要执行任何形式的event，因为如果主库执行一次，复制到从库后，从库再执行一次的话，那就数据不一致了，一般来说直接禁用掉从库上的总开关event_scheduler就行。

ON_COMPLETION：只有两种值，PRESERVE和NOT PRESERVE，PRESERVE

CREATED：event的创建时间

LAST_ALTERED：event最新一次被修改的时间

LAST_EXECUTED：event最近一次执行的时间，如果为NULL表示从未执行过

EVENT_COMMENT：event的注释信息

ORIGINATOR：当前event创建时的server-id，用于主从上的处理，比如SLAVESIDE_DISABLED

CHARACTER_SET_CLIENT：event创建时的客户端字符集，即character_set_client

COLLATION_CONNECTION：event创建时的连接字符校验规则，即collation_connection

DATABASE_COLLATION：event创建时的数据库字符集校验规则

8.EVENT的权限管理

1 设置event_scheduler系统变量，需要super_priv权限

2 创建，修改和删除event需要该user用户EVENT权限，该权限是schema级别的

3 对应于event的具体内容，需要对应的权限。比如event里有对某张表的insert操作，那么该user需要对该表的insert操作，不然LAST_EXECUTED一直会是NULL

10.EVENT的状态查询

通过以下命令查看DB启动以来的event的相关信息统计:
mysql> showglobal status like '%event%';
+--------------------------+-------+
|Variable_name             | Value |
+--------------------------+-------+
|Com_alter_event           | 0     |
|Com_create_event          | 2     |
|Com_drop_event            | 2     |
|Com_show_binlog_events    | 0     |
|Com_show_create_event     | 191   |
|Com_show_events           | 40    |
|Com_show_relaylog_events  | 0     |
+--------------------------+-------+
7 rows in set(0.00 sec)

四、事件使用建议

mysql事件之修改事件（ALTER EVENT）、禁用事件（DISABLE）、启用事件（ENABLE）、事件重命名及数据库事件迁移操作。

1 如果主库已经执行过，从库上务必要保证event不会执行(除非故意在slave上创建的event)

2 创建，删除等操作严禁直接操作mysql.event表，而是通过create等正规语法实现，不然会导致元数据混乱，各种莫名其妙的问题随之产生，比如event不执行或者重复执行。这时一般只有重启DB才能解决 了。

3 创建的event涉及到海量数据变更的话，要做好充分测试，确保不影响现网服务

4 如果需要备份带有event的DB，mysqldump时需要加上--event参数

1.修改事件（ALTER EVENT）

MySQL允许我们更改现有事件的各种属性。如果我们要更改现有事件，可以使用ALTER EVENT语句，如下所示：

ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
 event_body

ALTER EVENT语句仅适用于存在的事件，如果我们尝试修改不存在的事件，MySQL将会发出一条错误消息，因此在更改事件之前，应先使用SHOW EVENTS语句检查事件的存在：

mysql> SHOW EVENTS FROM testdb;
+--------+------------+----------------+-----------+----------+
| Db     | Name       | Definer        | Time zone | Type     |...
+--------+------------+----------------+-----------+----------+
| testdb | test_event | root@localhost | SYSTEM    | ONE TIME |...
+--------+------------+----------------+-----------+----------+


创建一个每分钟将一条新记录插入到messages表中的示例事件来演示如何使用ALTER EVENT语句的各种功能：
USE testdb;
CREATE EVENT test_event_04
ON SCHEDULE EVERY 1 MINUTE
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test ALTER EVENT statement',NOW());

#把事件修改为为每2分钟运行一次：
"方法一"
ALTER EVENT test_event_04
ON SCHEDULE EVERY 2 MINUTE;

"方法二"
还可以通过指定新的逻辑来更改事件的主体代码：
先清空表中的数据 truncate messages;
ALTER EVENT test_event_04
DO
  INSERT INTO messages(message,created_at)
  VALUES('Message from event',NOW());

修改完成后，可以等待2分钟，再次查看messages表：
mysql> SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message            | created_at          |
+----+--------------------+---------------------+
| 1  | Message from event | 2017-08-03 04:46:47 |
| 2  | Message from event | 2017-08-03 04:48:47 |
+----+--------------------+---------------------+
2 rows in set

2.禁用事件

可以在ALTER EVENT语句之后使用DISABLE关键字来禁用某个事件：

ALTER EVENT test_event_04
DISABLE;

使用SHOW EVENTS语句来查看事件的状态：
mysql> SHOW EVENTS FROM testdb;
+----+---------+--...+----------+----------+...---------+
| Db | Name    | D...| Type     |Execute at|...|Status  |
+----+---------+--...+----------+----------+...---------+
|db1 | event   | r...| ONE TIME |2017-08-03|...|DISABLED|
|db1 | event_04| r...| RECURRING| NULL     |...|DISABLED|
+----+---------+--...+----------+----------+...---------+
可以看到event_04的STATE状态为disabled

可以在ALTER EVENT语句之后使用ENABLE关键字来启用事件：

ALTER EVENT test_event_04
ENABLE;

mysql> SHOW EVENTS FROM testdb;
+----+---------+--...+----------+----------+...---------+
| Db | Name    | D...| Type     |Execute at|...|Status  |
+----+---------+--...+----------+----------+...---------+
|db1 | event   | r...| ONE TIME |2017-08-03|...|DISABLED|
|db1 | event_04| r...| RECURRING| NULL     |...|ENABLED |
+----+---------+--...+----------+----------+...---------+
可以看到event_04的STATE状态为enabled

3.重命名事件和复制

尝试使用ALTER EVENT重命名现有事件：

ALTER EVENT test_event_04
RENAME TO test_event_05;

mysql> SHOW EVENTS FROM testdb;
+----+---------+--...+----------+----------+...---------+
| Db | Name    | D...| Type     |Execute at|...|Status  |
+----+---------+--...+----------+----------+...---------+
|db1 | event   | r...| ONE TIME |2017-08-03|...|DISABLED|
|db1 | event_05| r...| RECURRING| NULL     |...|ENABLED |
+----+---------+--...+----------+----------+...---------+

ALTER EVENT db1.test_event_05
RENAME TO newdb.test_event_05;

mysql> SHOW EVENTS FROM newdb;
+------+---------+--...+----------+----------+...---------+
| Db   | Name    | D...| Type     |Execute at|...|Status  |
+------+---------+--...+----------+----------+...---------+
|newdb |event_05 | r...| RECURRING| NULL     |...|ENABLED |
+------+---------+--...+----------+----------+...---------+

五、游标的用法及作用

场景：
当前有三张表A、B、C其中A和B是一对多关系，B和C是一对多关系，现在需要将B中A表的主键存到C中；
常规思路就是将B中查询出来然后通过一个update语句来更新C表就可以了，但是B表中有2000多条数据，
难道要执行2000多次？显然是不现实的；最终找到写一个存储过程然后通过循环来更新C表，
然而存储过程中的写法用的就是游标的形式。

1.游标的简介

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
游标充当指针的作用。
尽管游标能遍历结果中的所有行，但他一次只指向一行。
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

2.游标的用法

1.声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
2.打开定义的游标:open 游标名称;
3.获得下一行数据:FETCH  游标名称 into testrangeid,versionid;
4.需要执行的语句(增删改查):这里视具体情况而定
5.释放游标:CLOSE 游标名称;

# 注:mysql存储过程每一句后面必须用;结尾，使用的临时字段需要在定义游标之前进行声明。

3.游标的使用实例

例子1：

-  
BEGIN  

--定义变量  
declare testrangeid BIGINT;  
declare versionid BIGINT;   
declare done int;  
--创建游标，并存储数据  
declare cur_test CURSOR for   
   select id as testrangeid,version_id as versionid from tp_testrange;  
--游标中的内容执行完后将done设置为1  
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
--打开游标  
open cur_test;  
--执行循环  
  posLoop:LOOP  
--判断是否结束循环  
        IF done=1 THEN    
      LEAVE posLoop;  
    END IF;   
--取游标中的值  
    FETCH  cur_test into testrangeid,versionid;  
--执行更新操作  
    update tp_data_execute set version_id=versionid where testrange_id = testrangeid;  
  END LOOP posLoop;  
--释放游标  
CLOSE cur_test;  

END  
-

例子2：

我们现在要用存储过程做一个功能，统计iphone的总库存是多少，并把总数输出到控制台。

--在windows系统中写存储过程时，如果需要使用declare声明变量，需要添加这个关键字，否则会报错。  
delimiter //  
drop procedure if exists StatisticStore;  
CREATE PROCEDURE StatisticStore()  
BEGIN  
    --创建接收游标数据的变量  
    declare c int;  
    declare n varchar(20);  
    --创建总数变量  
    declare total int default 0;  
    --创建结束标志变量  
    declare done int default false;  
    --创建游标  
    declare cur cursor for select name,count from store where name = 'iphone';  
    --指定游标循环结束时的返回值  
    declare continue HANDLER for not found set done = true;  
    --设置初始值  
    set total = 0;  
    --打开游标  
    open cur;  
    --开始循环游标里的数据  
    read_loop:loop  
    --根据游标当前指向的一条数据  
    fetch cur into n,c;  
    --判断游标的循环是否结束  
    if done then  
        leave read_loop;    --跳出游标循环  
    end if;  
    --获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  
    set total = total + c;  
    --结束游标循环  
    end loop;  
    --关闭游标  
    close cur;  

    --输出结果  
    select total;  
END;  
--调用存储过程  
call StatisticStore();

fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环；

read_loop:loop  
fetch cur into n,c;  
set total = total+c;  
end loop;

在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

declare continue HANDLER for not found set done = true;

所以在循环时加上了下面这句代码：

--判断游标的循环是否结束  
if done then  
    leave read_loop;    --跳出游标循环  
end if;

如果done的值是true，就结束循环。继续执行下面的代码

4.游标使用方式

游标有三种使用方式：
- 第一种就是上面的实现，使用loop循环；
- 第二种方式如下，使用while循环：
- 第三种方式是使用repeat执行：

drop procedure if exists StatisticStore1;  
CREATE PROCEDURE StatisticStore1()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  
    set total = 0;  
    open cur;  
    fetch cur into n,c;  
    while(not done) do  
        set total = total + c;  
        fetch cur into n,c;  
    end while;  

    close cur;  
    select total;  
END;  

call StatisticStore1();

repeat执行：

drop procedure if exists StatisticStore2;  
CREATE PROCEDURE StatisticStore2()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  
    set total = 0;  
    open cur;  
    repeat  
    fetch cur into n,c;  
    if not done then  
        set total = total + c;  
    end if;  
    until done end repeat;  
    close cur;  
    select total;  
END;  

call StatisticStore2();

5.游标嵌套

在mysql中，每个begin end 块都是一个独立的scope区域，由于MySql中同一个error的事件只能定义一次，如果多定义的话在编译时会提示Duplicate handler declared in the same block。

drop procedure if exists StatisticStore3;  
CREATE PROCEDURE StatisticStore3()  
BEGIN  
    declare _n varchar(20);  
    declare done int default false;  
    declare cur cursor for select name from store group by name;  
    declare continue HANDLER for not found set done = true;  
    open cur;  
    read_loop:loop  
    fetch cur into _n;  
    if done then  
        leave read_loop;  
    end if;  
    begin  
        declare c int;  
        declare n varchar(20);  
        declare total int default 0;  
        declare done int default false;  
        declare cur cursor for select name,count from store where name = 'iphone';  
        declare continue HANDLER for not found set done = true;  
        set total = 0;  
        open cur;  
        iphone_loop:loop  
        fetch cur into n,c;  
        if done then  
            leave iphone_loop;  
        end if;  
        set total = total + c;  
        end loop;  
        close cur;  
        select _n,n,total;  
    end;  
    begin  
            declare c int;  
            declare n varchar(20);  
            declare total int default 0;  
            declare done int default false;  
            declare cur cursor for select name,count from store where name = 'android';  
            declare continue HANDLER for not found set done = true;  
            set total = 0;  
            open cur;  
            android_loop:loop  
            fetch cur into n,c;  
            if done then  
                leave android_loop;  
            end if;  
            set total = total + c;  
            end loop;  
            close cur;  
        select _n,n,total;  
    end;  
    begin  

    end;  
    end loop;  
    close cur;  
END;  

call StatisticStore3();

上面就是实现一个嵌套循环。

6.动态SQL

Mysql 支持动态SQL的功能

set @sqlStr='select * from table where condition1 = ?';  
prepare s1 for @sqlStr;  
--如果有多个参数用逗号分隔  
execute s1 using @condition1;  
--手工释放，或者是 connection 关闭时， server 自动回收  
deallocate prepare s1;