mysql 存储过程 利用游标循环添加实例

CREATE DEFINER = 'www'@'192`.`168`.`%`.`%'
PROCEDURE newsng.pro_test(IN _params varchar(255))
BEGIN
  DECLARE _string varchar(50); 
  DECLARE _string1 varchar(50);  
  DECLARE SplitChar varchar(50);
  DECLARE p_name_index INT;
  DECLARE p_id_index INT;
   DECLARE p_class_id INT;
  DECLARE stop int default 0;
  DECLARE p_name varchar(50);
  DECLARE p_clasid int;
  DECLARE p_tech_names varchar(50);
  DECLARE p_tech_name varchar(50);
  DECLARE p_tech_ids varchar(50);
  DECLARE p_tech_id int;
  DECLARE p_num int;##班级是否存在教师
  DECLARE i int default 0;

  DECLARE cur cursor for(
    select  clas_name, clas_id FROM classes where is_del = 0 );

  	DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK; #回滚事务
  END;
  ##DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
  /*这把 游标 异常后 捕捉并设置 循环使用 变量 stop 为 null 跳出循环。*/
  SET SplitChar = ',';
  start transaction;
  SET @j = 0;
  SELECT COUNT(1) INTO @j FROM classes;
  SELECT @j; 
  OPEN cur;
    ##myLoop: LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到  
      FETCH cur INTO p_name, p_clasid;
      WHILE ( stop is not null) DO
      ##查询条件下的数据是否存在，避免游标循环中断
      SELECT COUNT(1) INTO p_num FROM teacher_class tc INNER JOIN edugate_base.teacher t ON t.tech_id = tc.tech_id AND t.is_del = 0 WHERE tc.is_del = 0 AND tc.clas_id = p_clasid; 
      IF p_num = 0 THEN
        SELECT p_num;
      ELSE
        SELECT GROUP_CONCAT(t.tech_name) tech_name, GROUP_CONCAT(t.tech_id) tech_id, tc.clas_id INTO p_tech_names, p_tech_ids, p_class_id  FROM teacher_class tc INNER JOIN edugate_base.teacher t ON t.tech_id = tc.tech_id AND t.is_del = 0 WHERE tc.is_del = 0 AND tc.clas_id = p_clasid GROUP BY tc.clas_id; 
        IF p_tech_names = NULL THEN
          SELECT p_class_id;
        ELSE
          SET p_name_index = 1+(length(p_tech_names) - length(replace(p_tech_names, SplitChar,'')));##教师名称数组长度
          set @i = 0;
          while @i < p_name_index
            DO
            set @i = @i + 1;
            SET p_tech_name = reverse(substring_index(reverse(substring_index(p_tech_names,SplitChar,@i)),SplitChar,1));## 如 11,12,13 先截取第一个逗号（,）之前的字符串11  反序11 截取第一个逗号之前的11 再反序11，第二次截取第二个逗号之前的字符串11,12 反序21,11 截取逗号之前的21, 再反序12
            SET p_tech_id = reverse(substring_index(reverse(substring_index(p_tech_ids,SplitChar,@i)),SplitChar,1));
            INSERT INTO tmp_strs(name,tech_id,tech_name) VALUES(p_name, p_tech_id, p_tech_name); 
          end while;
        END IF;
      END IF;
      #IF p_tech_ids = NULL THEN
      #  SELECT p_class_id;
      #ELSE
      #  SET p_id_index = 1+(length(p_tech_ids) - length(replace(p_tech_ids, SplitChar,'')));##教师id数组长度
      #END IF;
        
      FETCH cur INTO p_name, p_clasid;
      END WHILE;
      commit;
    ##END LOOP myLoop; -- 结束自定义循环体  
  CLOSE cur;
END