mysql動的カーソル学習(mysqlストアド・プロシージャ・カーソル)

 
  
--          
  
create table webuser (username varchar(10)); 
  
insert into webuser values ('a1'),('a2'),('a3'),('b1'),('b2'),('b3'); 
  
commit; 
  
 
  
--        
  
drop procedure if exists dynamic_cursor; 
  
delimiter // 
  
CREATE PROCEDURE dynamic_cursor (IN p_name varchar(10)) 
  
BEGIN 
  
    DECLARE done INT DEFAULT 0; 
  
    DECLARE v_username varchar(10); 
  
    DECLARE cur CURSOR for( SELECT username from webuser_view); 
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
  
 
  
    DROP VIEW IF EXISTS webuser_view; 
  
 
  
    SET @sqlstr = "CREATE VIEW webuser_view as "; 
  
    SET @sqlstr = CONCAT(@sqlstr , "SELECT username FROM webuser WHERE username like '", p_name,"%'"); 
  
 
  
    PREPARE stmt FROM @sqlstr; 
  
    EXECUTE stmt; 
  
    DEALLOCATE PREPARE stmt; 
  
 
  
    OPEN cur; 
  
    f_loop:LOOP 
  
    FETCH cur INTO v_username; 
  
    IF done THEN 
  
        LEAVE f_loop;  
  
    END IF; 
  
    SELECT v_username;    
  
    END LOOP f_loop; 
  
    CLOSE cur; 
  
END; 
  
// 
  
delimiter ; 
  
 
  
--    
  
call dynamic_cursor('a');