mysql 带参数的游标

这里是查询  把多个uuid以逗号(,)的格式去查询数据，把数据放入一张临时表
里面值得学习的是动态游标，mysql动态sql语法

DELIMITER $$

DROP PROCEDURE IF EXISTS `get_checkList`$$

CREATE  PROCEDURE  `get_checkList` (v_schId VARCHAR(100),v_mark VARCHAR(100)) 

BEGIN  

  

    DECLARE done INT DEFAULT 0;   

    DECLARE temp_id VARCHAR(32); 

    DECLARE temp_personId VARCHAR(32);   #'接收人ID'

    DECLARE temp_gradeId VARCHAR(5000);  #'(多个)年级id'

    DECLARE temp_clazzId VARCHAR(5000);  #'(多个)班级id'

    DECLARE temp_pushId VARCHAR(32);     #'考勤推送ID'

    DECLARE temp_mark VARCHAR(10);   

   
    #定义游标

    DECLARE cur CURSOR FOR( SELECT * FROM check_person_view);  

    #设置跳出游标条件

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  

    #这里采用视图，表示游标的结果 为动态数据

    DROP VIEW IF EXISTS check_person_view;  

    
    #这里是mysql的动态sql语法运用

    SET @sqlstr = "CREATE VIEW check_person_view as ";  

    SET @sqlstr = CONCAT(@sqlstr , "SELECT a.* FROM  kq_pushperson a JOIN kq_checkpush b ON a.pushId =b.id  AND b.schId ='", v_schId,"' "); 

    IF v_mark IS NOT NULL THEN

    SET @sqlstr = CONCAT(@sqlstr , "and a.mark='", v_mark,"' ");

    END IF;

    PREPARE stmt FROM @sqlstr;  

    EXECUTE stmt;  

    DEALLOCATE PREPARE stmt; 

    DELETE FROM kq_pushperson_temp;

   
    #打开游标

    OPEN cur;  

    FETCH cur INTO temp_id,temp_personId,temp_gradeId,temp_clazzId,temp_pushId,temp_mark;  

    WHILE done <> 1 DO  

    #select  temp_id,temp_personId,temp_gradeId,temp_clazzId,temp_pushId,temp_mark; 

    SET @tempSql="insert into kq_pushperson_temp(id,personId,gradeId,clazzId,pushId,empName,gradeName,clazzName,mark) select ";

    SET @tempSql= CONCAT(@tempSql,'"',temp_id,'","',temp_personId,'" ');

    IF temp_gradeId IS NULL THEN

SET @tempSql= CONCAT(@tempSql,",null,null");

SET @tempSql= CONCAT(@tempSql,',"',temp_pushId,'",(SELECT username FROM sys_user WHERE id="',temp_personId,'")');

SET @tempSql= CONCAT(@tempSql,',null,null,"',temp_mark,'" ');

    ELSE 

IF temp_clazzId IS NULL THEN

SET @tempSql= CONCAT(@tempSql,',REPLACE("',temp_gradeId,'","\'","") gradeId,null clazzId');

SET @tempSql= CONCAT(@tempSql,',"',temp_pushId,'",(SELECT username FROM sys_user WHERE id="',temp_personId,'")');

SET @tempSql= CONCAT(@tempSql,',(SELECT GROUP_CONCAT(NAME) FROM xj_grade WHERE id in (',temp_gradeId,')) gradeName');

SET @tempSql= CONCAT(@tempSql,',null clazzName');

SET @tempSql= CONCAT(@tempSql,',"',temp_mark,'" ');

ELSE 

SET @tempSql= CONCAT(@tempSql,',REPLACE("',temp_gradeId,'","\'","") gradeId,REPLACE("',temp_clazzId,'","\'","") clazzId');

SET @tempSql= CONCAT(@tempSql,',"',temp_pushId,'",(SELECT username FROM sys_user WHERE id="',temp_personId,'")');

SET @tempSql= CONCAT(@tempSql,',(SELECT GROUP_CONCAT(NAME) FROM xj_grade WHERE id in (',temp_gradeId,')) gradeName');

SET @tempSql= CONCAT(@tempSql,',(SELECT GROUP_CONCAT(NAME) FROM xj_clazz WHERE id in (',temp_clazzId,')) clazzName');

SET @tempSql= CONCAT(@tempSql,',"',temp_mark,'" ');

END IF;

    END IF;

    #select @tempSql;

    PREPARE stmt FROM @tempSql;  

    EXECUTE stmt;  

    DEALLOCATE PREPARE stmt; 

    FETCH cur INTO temp_id,temp_personId,temp_gradeId,temp_clazzId,temp_pushId,temp_mark; 

    END WHILE;  

    CLOSE cur; 

    SELECT * FROM kq_pushperson_temp;

END $$

DELIMITER ;