mysql动态游标学习(mysql存储过程游标)

代码如下:

-- 建立测试表和数据

1. create table webuser (username varchar(10));
   
2. insert into webuser values ('a1'),('a2'),('a3'),('b1'),('b2'),('b3');
   
3. commit;

复制代码

-- 建立存储过程

1. drop procedure if exists dynamic_cursor;
   
2. delimiter //
   
3. CREATE PROCEDURE dynamic_cursor (IN p_name varchar(10))
   
4. BEGIN
   
5.     DECLARE done INT DEFAULT 0;
   
6.     DECLARE v_username varchar(10);
   
7.     DECLARE cur CURSOR for( SELECT username from webuser_view);
   
8.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   
9. 
   
10.     DROP VIEW IF EXISTS webuser_view;
    
11. 
    
12.     SET @sqlstr = "CREATE VIEW webuser_view as ";
    
13.     SET @sqlstr = CONCAT(@sqlstr , "SELECT username FROM webuser WHERE username like '", p_name,"%'");
    
14. 
    
15.     PREPARE stmt FROM @sqlstr;
    
16.     EXECUTE stmt;
    
17.     DEALLOCATE PREPARE stmt;
    
18. 
    
19.     OPEN cur;
    
20.     f_loop:LOOP
    
21.     FETCH cur INTO v_username;
    
22.     IF done THEN
    
23.         LEAVE f_loop;  
    
24.     END IF;
    
25.     SELECT v_username;   
    
26.     END LOOP f_loop;
    
27.     CLOSE cur;
    
28. END;
    
29. //
    
30. delimiter ;

复制代码

-- 测试

1. call dynamic_cursor('a');

复制代码