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