examcloud-core-basic/sql/考生导入-存储过程.sql

CREATE DEFINER = CURRENT_USER PROCEDURE `import_exam_students`(IN `batch_id` bigint)
BEGIN
	declare total BIGINT(20);
	declare count BIGINT(20);
	declare id BIGINT(20);
	declare course_id_temp BIGINT(20);
	declare course_id BIGINT(20);
	declare course_code varchar(255); 
	declare identity_number varchar(255); 
	declare student_code varchar(255);
	declare org_id_temp BIGINT(20);
	declare org_id BIGINT(20);
	declare org_code varchar(255);
 	declare	root_org_id  BIGINT(20);
	
	DECLARE cursor_temp CURSOR FOR  
	select t.id,t.course_id,t.org_id,t.course_code,t.student_code,t.identity_number,t.org_code,t.root_org_id 
	from ec_e_exam_student_tmp t 
	where t.batch_id= batch_id;
	
  set count = 0;
	select count(1) into total 	from ec_e_exam_student_tmp t 	where t.batch_id= batch_id;
	
	open cursor_temp;
	
	read_loop: LOOP
		set count = count+1;
		FETCH cursor_temp INTO id,course_id,org_id,course_code,student_code,identity_number,org_code,root_org_id;
		
    SELECT		id,course_id,org_id,course_code,student_code,identity_number,org_code,root_org_id;
		
		IF course_id is null THEN
			SELECT x.id into course_id_temp from ecs_core_course x where x.code= course_code and x.enable=true and x.org_id=root_org_id;
			IF course_id_temp is not null THEN
				 update ec_e_exam_student_tmp x set x.course_id=course_id_temp 
				 where  x.course_id is null and x.course_code=course_code and x.root_org_id=root_org_id and  x.batch_id= batch_id;
			END IF;
			set course_id_temp=null;
		END IF;
		
		IF org_id is null THEN
			SELECT x.id into org_id_temp from ecs_core_org x where x.code= org_code and x.root_id=root_org_id;
			IF org_id_temp is not null THEN
				 update ec_e_exam_student_tmp x set x.org_id=org_id_temp 
				 where  x.org_id is null and x.org_code=org_code and x.root_org_id=root_org_id and  x.batch_id= batch_id;
			END IF;
			set org_id_temp=null;
		END IF;
		
		commit;
		
		IF total = count THEN
			LEAVE read_loop;
		END IF;
		
	END LOOP;
 	
    close cursor_temp;
	
END