CREATE DEFINER=`root`@`localhost` PROCEDURE `creat_student_group`(IN `group_name` varchar(128),IN `student_id_card` varchar(900))
BEGIN
    #Routine body goes here...
DECLARE group_id_now  INT DEFAULT 0;
DECLARE i INT DEFAULT 0;


 DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为2

START TRANSACTION; -- 开始事务

INSERT INTO group_info (group_name) VALUES(group_name);

SET group_id_now = @@IDENTITY ;

SET @arraylength=1+(LENGTH(student_id_card) - LENGTH(REPLACE(student_id_card,‘,‘,‘‘)));

WHILE i<@arraylength 
DO
    SET i=i+1;
    SET @result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(student_id_card,‘,‘,i)),‘,‘,1));
    INSERT INTO group_list(student_info_id,group_info_id) VALUES(@result,group_id_now);
END WHILE;

IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
ROLLBACK; 
ELSE 
 COMMIT; 
END IF;

select result_code;

END