<aside> 💡

프로젝트에 필요한 레퍼런스를 모아보세요. 링크, 이미지, 글 등 모든 소스를 여기에서 한번에 관리해보세요! 🙂

</aside>

<aside> 📁 sequence와 Max + 1 중 어떤 것이 더 효율적일까

MAX 값 vs SEQUENCE 사용의 효율성 비교

</aside>

<aside> 📁 [레퍼런스] - 참고하기

</aside>

<aside> 📁 [레퍼런스] - 참고하기

</aside>

CREATE OR REPLACE PROCEDURE update_attendance_score(
    p_student_seq NUMBER,
    p_course_seq NUMBER
)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    total_days NUMBER := 0;
    attended_days NUMBER := 0;
    tardy_count NUMBER := 0;
    early_leave_count NUMBER := 0;
    absent_count NUMBER := 0;
    adjusted_absent_count NUMBER := 0;
    attendance_rate NUMBER := 0;
    subject_end_date DATE;
    score_exists NUMBER;
    final_score NUMBER := 0;
BEGIN
    -- 학생이 속한 과정(course_seq)에 매핑된 과목(subject_seq) 찾기
    FOR subject_rec IN (
        SELECT subject_seq
        FROM tblCourseSubjectManage
        WHERE course_seq = p_course_seq
    ) LOOP
        -- 전체 출석 가능 일수 조회
        SELECT COUNT(*) INTO total_days 
        FROM tblAttendance
        WHERE student_seq = p_student_seq
        AND course_seq = p_course_seq;

        -- 정상 출석 일수 조회
        SELECT COUNT(*) INTO attended_days 
        FROM tblAttendance
        WHERE student_seq = p_student_seq
        AND course_seq = p_course_seq
        AND attendance_status = '정상';

        -- 지각 횟수 조회
        SELECT COUNT(*) INTO tardy_count
        FROM tblAttendance
        WHERE student_seq = p_student_seq
        AND course_seq = p_course_seq
        AND attendance_status = '지각';

        -- 조퇴 횟수 조회
        SELECT COUNT(*) INTO early_leave_count
        FROM tblAttendance
        WHERE student_seq = p_student_seq
        AND course_seq = p_course_seq
        AND attendance_status = '조퇴';

        -- 결석 횟수 조회
        SELECT COUNT(*) INTO absent_count
        FROM tblAttendance
        WHERE student_seq = p_student_seq
        AND course_seq = p_course_seq
        AND attendance_status = '결석';

        -- 지각 3번을 결석 1번으로 변환
        adjusted_absent_count := absent_count + FLOOR(tardy_count / 3);

        -- 출석률 계산
        IF total_days > 0 THEN
            attendance_rate := (attended_days * 100) / total_days;
        ELSE
            attendance_rate := 0;
        END IF;

        -- 출석 점수 계산 (출석률 + 출석 상태별 감점 반영)
        final_score := 20; -- 기본 점수 설정

        -- 출석률 기반 감점
        IF attendance_rate < 80 THEN
            final_score := 15;
        END IF;
        
        IF attendance_rate < 70 THEN
            final_score := 10;
        END IF;

        -- 출석 상태별 감점
        final_score := final_score - (tardy_count * 0.5) - (early_leave_count * 0.5);

        -- 결석 3회 이상이면 점수 0점 처리
        IF adjusted_absent_count >= 3 THEN
            final_score := 0;
        END IF;

        -- 최종 점수가 음수가 되지 않도록 조정
        IF final_score < 0 THEN
            final_score := 0;
        END IF;

        -- `tblScore`에 해당 학생의 데이터가 있는지 확인
        SELECT COUNT(*) INTO score_exists
        FROM tblScore
        WHERE student_seq = p_student_seq
        AND subject_seq = subject_rec.subject_seq;

        -- 기존 데이터가 있으면 UPDATE, 없으면 INSERT
        IF score_exists > 0 THEN
            UPDATE tblScore 
            SET score_attend = final_score
            WHERE student_seq = p_student_seq
            AND subject_seq = subject_rec.subject_seq;
        ELSE
            INSERT INTO tblScore (score_seq, student_seq, subject_seq, score_attend)
            VALUES (SCORE_SEQ.NEXTVAL, p_student_seq, subject_rec.subject_seq, final_score);
        END IF;
    END LOOP;
    
    COMMIT;
END;
/