問題：我想利用兩層游標回圈嵌套來做考勤資訊的統計，外層游標回圈拿到:t_student 學生表的id值，
并通過遍歷將其逐個動態賦值給內層回圈游標定義的查詢條件引數:students_id，內層游標定義目的是查詢每個學生當年當月的考勤記錄，通過while回圈對每個人當月每天的考勤記錄判定考勤例外，并最終將每個人最終考勤例外資料插入統計表;但是目前運行結果是外層回圈成功，在最終統計表中插入了所有學生的考勤記錄，但悲催的是只有第一條考勤記錄正確，其他的例外都為0（如果去掉外層游標，指定引數students_id時，最終插入統計表的資料優勢正常的）！
本人剛開始接觸mysql存盤程序，這兩天快被玩炸了.....
希望哪位大佬能幫我瞅瞅，在下拜謝啦！
由于字數限制 所以對代碼格式調整了下請見諒！
上渣白的代碼：
BEGIN
#定義變數
#3個動態引數
declare select_year int;
declare select_month int;
declare students_id ；
declare check_temp_date varchar(256);
declare check_date date;
declare week_day int;
declare check_id int;
declare check_year int;
declare check_month int;
declare check_day int;
declare check_IN time;
declare check_OUT time;
declare student_Id int;
declare v_student_Name varchar(256);
declare v_Late int default 0;
declare v_Early int default 0;
declare v_Half_absenteeism int default 0;
declare v_Absenteeism int default 0;
declare v_Work_day int default 0;
declare i int default 1;declare v_month_days int ;
#設定遍歷結束條件
declare flag int default 0;
declare id_info cursor for select id from t_student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
#拿到當前時間年月
select DATE_FORMAT(NOW(), '%Y') into select_year;
select DATE_FORMAT(NOW(), '%m') into select_month;
open id_info;
read_loop:Loop
#遍歷游標，取值
fetch id_info into students_id;
#無資料可遍歷時退出回圈
if flag =1 then
leave read_loop;
end if;
BEGIN
declare flags int default 0;
declare check_info cursor for select * from t_check as c where c.student_id=students_id and
c.year=select_year and c.month=select_month;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flags=1;
open check_info;
fetch check_info into check_id,check_year,check_month,check_day,check_In,check_Out,student_Id
#自己的事物操作
#配置考勤狀況
#摘取需要遍歷資料的年、月、日欄位拼接成日期變數 拼接函式：concat(field_1,"-",field_2,"-",field_3)
select concat(select_year,'-',select_month,'-',i) into check_temp_date ;
select date_format( check_temp_date, '%y-%m-%d') into check_date;
#查詢當月天數
select day(LAST_DAY(check_date)) into v_month_days;
#回圈
WHILE i<=v_month_days do
IF EXISTS(select * from t_check as c where c.student_id=students_id and c.year=select_year and
c.month=select_month and c.day=i) THEN
select concat(select_year,'-',select_month,'-',i) into check_temp_date ;
select date_format( check_temp_date, '%y-%m-%d') into check_date;
#根據拼接的日期變數排除作業日:回傳放入日期為星期幾，1代表星期天，7代表星期六
select DAYOFWEEK (check_date ) into week_day ;
#判斷是否為周末
if week_day in(1,7) THEN
#跳出回圈操作，賦值i開始下一次回圈
set i=i;
#判斷是否為五一節假(1,2,3放假)
ELSEIF select_month=5 and i in(1,2,3) THEN
#跳出回圈操作，賦值i開始下一次回圈
set i=i;
#判斷是否為國慶節日
ELSEIF select_month=10 and i in(1,2,3,4,5,6,7) THEN
#跳出回圈操作，賦值i開始下一次回圈
set i=i;
ELSE
#排除雙null，共15種情況
#簽入為null，簽出正常，曠工半天+1
if check_In is null and check_Out >'17:00:00' THEN
set v_Half_absenteeism=v_Half_absenteeism+1 ;
#簽入為null，簽出超過最大限制，曠工一天+1
ELSEIF check_In is null and check_Out <'16:30:00' THEN
set v_Absenteeism=v_Absenteeism+1 ;
#簽入為null，簽出未超過最大限制，曠工半天+1、早退+1
ELSEIF check_In is null and check_Out <='17:00:00' and check_Out>='16:30:00' THEN
set v_Early =v_Early+1;
set v_Half_absenteeism=v_Half_absenteeism+1 ;
#簽入為正常，簽出為null，曠工半天+1
ELSEIF check_In<'09:00:00' and check_Out is null THEN
set v_Half_absenteeism=v_Half_absenteeism+1 ;
#簽入為正常，簽出正常，無事發生
ELSEIF check_In<'09:00:00' and check_Out>'17:00:00' THEN
set i=i;
#簽入為正常，簽出未超過最大限制，早退+1
ELSEIF check_In<'09:00:00' and check_Out>='16:30:00' && check_Out<='17:00:00' THEN
set v_Early =v_Early+1;
#簽入為正常，簽出超過最大限制，曠工半天+1
ELSEIF check_In<'09:00:00' && check_Out<'16:30:00' THEN
set v_Half_absenteeism=v_Half_absenteeism+1 ;
#簽入未超過最大限制，簽出為null，遲到+1，曠工半天+1
ELSEIF check_In>='09:00:00' and check_In<='09:30:00' and check_Out is null THEN
set v_Half_absenteeism=v_Half_absenteeism+1 ;
set v_Late =v_Late+1;
#簽入未超過最大限制，簽出為正常，遲到+1
ELSEIF check_In>='09:00:00' and check_In<='09:30:00' and check_Out >'17:00:00' THEN
set v_Late =v_Late+1;
#簽入未超過最大限制，簽出未超過最大限制，遲到+1，早退+1
ELSEIF check_In>='09:00:00' and check_In<='09:30:00' and check_Out>='16:30:00' &&
check_Out<='17:00:00' THEN
set v_Late =v_Late+1;
set v_Early =v_Early+1;
#簽入未超過最大限制，簽出超過最大限制，遲到+1，曠工半天+1
ELSEIF check_In>='09:00:00' and check_In<='09:30:00' and check_Out<'16:30:00' THEN
set v_Late =v_Late+1;
set v_Half_absenteeism=v_Half_absenteeism+1 ;
#簽入超過最大限制，簽出為null,曠工一天+1
ELSEIF check_In>'09:30:00' and check_Out is null THEN
set v_Absenteeism=v_Absenteeism+1 ;
#簽入超過最大限制，簽出正常,曠工半天+1
ELSEIF check_In>'09:30:00' and check_Out>'17:0:00' THEN
set v_Half_absenteeism=v_Half_absenteeism+1 ;
#簽入超過最大限制，簽出未超過最大限制,曠工半天+1，早退+1
ELSEIF check_In>'09:30:00' and check_Out<='17:00:00' && check_Out>='16:30:00' THEN
set v_Half_absenteeism=v_Half_absenteeism+1 ;
set v_Early =v_Early+1;
#簽入超過最大限制，簽出超過最大限制,曠工一天+1，早退+1 c check_Out<'16:30:00'
ELSEIF check_In>'09:30:00' and check_Out<'16:30:00' THEN
set v_Absenteeism = v_Absenteeism+1;
END if;#結束例外判斷
END if; #結束例外判斷
ELSE
#判斷是否為周末
if week_day in(1,7) THEN
#跳出回圈操作，賦值i開始下一次回圈
set i=i;
#判斷是否為五一節假(1,2,3放假)
ELSEIF select_month=5 and i in(1,2,3) THEN
#跳出回圈操作，賦值i開始下一次回圈
set i=i;
#判斷是否為國慶節日
ELSEIF select_month=10 and i in(1,2,3,4,5,6,7) THEN
#跳出回圈操作，賦值i開始下一次回圈
set i=i;
ELSE
set v_Absenteeism = v_Absenteeism+1;
END if;
END IF;
set i=i+1;
fetch check_info into check_id,check_year,check_month,check_day,check_In,check_Out,student_Id;
END WHILE;
#事務提交
select name into v_student_Name from t_student where students_id =id;
INSERT INTO
t_status_check(v_student_name,v_year,v_month,v_student_id,v_late,v_early,v_half_absenteeism,v_absenteeism)
values(v_student_Name,select_year,select_month,students_id ,v_Late,v_Early,v_Half_absenteeism,v_Absenteeism);
commit;
set v_Late = 0;
set v_Early = 0;
set v_Half_absenteeism = 0;
set v_Absenteeism = 0;
close check_info;
END;
end loop;
END
這是我的這是最終的統計表：
所有學生都在上面 但只有第一個資料正確（id=1和 5是整月的假資料，其他沒有，但是也應該根據我的規則添加相對應的曠工數的）；
這是我的學生表：
這是我的考勤表（這是假資料的考勤表） ：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/125956.html
標籤：基礎類