mysql游标使用简单实例：过期VIP处理

追影

1. BEGIN
   
2. #定义 变量
   
3. DECLARE a int;
   
4. #这个用于处理游标到达最后一行的情况
   
5. DECLARE s int default 0;
   
6. #声明游标cursor_name（cursor_name是个多行结果集）
   
7. DECLARE cursor_name CURSOR FOR select uid from member where shouquan_code<>'' and unix_timestamp(now())> end_time;
   
8. #设置一个终止标记
   
9. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
   
10. #打开游标
    
11. OPEN cursor_name;               
    
12. #获取游标当前指针的记录，读取一行数据并传给变量a,b
    
13. fetch  cursor_name into a;
    
14. #开始循环，判断是否游标已经到达了最后作为循环条件
    
15. while s <> 1 do
    
16. update member set shouquan_code= '' where uid = a;
    
17. update member_field_forum set medals= '' where uid = a;
    
18. fetch  cursor_name into a;
    
19. end while;                                 
    
20. #关闭游标
    
21. CLOSE cursor_name ;
    
22. END

复制代码