Week 3 — Stored Procedures & Control Flow Programming¶

DELIMITER $$

CREATE PROCEDURE procedure_name (
    IN  param1 datatype,
    OUT param2 datatype,
    INOUT param3 datatype
)
BEGIN
    -- procedure body
    SQL statements;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE get_student_info (
    IN  p_student_id INT,
    OUT p_full_name  VARCHAR(101),
    OUT p_gpa        DECIMAL(3,2),
    OUT p_dept_name  VARCHAR(100)
)
BEGIN
    SELECT CONCAT(s.first_name, ' ', s.last_name),
           s.gpa,
           d.dept_name
    INTO   p_full_name, p_gpa, p_dept_name
    FROM   students    s
    INNER JOIN departments d ON d.dept_id = s.dept_id
    WHERE  s.student_id = p_student_id;
END$$

DELIMITER ;

-- Calling the procedure
CALL get_student_info(1042, @name, @gpa, @dept);
SELECT @name AS student, @gpa AS gpa, @dept AS department;

-- INOUT example: normalize a GPA value (cap at 4.0)
DELIMITER $$

CREATE PROCEDURE normalize_gpa (INOUT p_gpa DECIMAL(3,2))
BEGIN
    IF p_gpa > 4.0 THEN SET p_gpa = 4.0; END IF;
    IF p_gpa < 0.0 THEN SET p_gpa = 0.0; END IF;
END$$

DELIMITER ;

SET @my_gpa = 4.3;
CALL normalize_gpa(@my_gpa);
SELECT @my_gpa;   -- Returns 4.00

DELIMITER $$

CREATE PROCEDURE assign_honor_status (IN p_student_id INT)
BEGIN
    DECLARE v_gpa DECIMAL(3,2);

    SELECT gpa INTO v_gpa FROM students WHERE student_id = p_student_id;

    IF v_gpa >= 3.9 THEN
        UPDATE students SET honor_status = 'Summa Cum Laude'  WHERE student_id = p_student_id;
    ELSEIF v_gpa >= 3.7 THEN
        UPDATE students SET honor_status = 'Magna Cum Laude'  WHERE student_id = p_student_id;
    ELSEIF v_gpa >= 3.5 THEN
        UPDATE students SET honor_status = 'Cum Laude'        WHERE student_id = p_student_id;
    ELSE
        UPDATE students SET honor_status = NULL               WHERE student_id = p_student_id;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE get_letter_grade (
    IN  p_points DECIMAL(3,1),
    OUT p_letter CHAR(2)
)
BEGIN
    SET p_letter = CASE
        WHEN p_points >= 3.85 THEN 'A'
        WHEN p_points >= 3.50 THEN 'A-'
        WHEN p_points >= 3.15 THEN 'B+'
        WHEN p_points >= 2.85 THEN 'B'
        WHEN p_points >= 2.50 THEN 'B-'
        WHEN p_points >= 2.15 THEN 'C+'
        WHEN p_points >= 1.85 THEN 'C'
        WHEN p_points >= 1.50 THEN 'C-'
        WHEN p_points >= 1.15 THEN 'D+'
        WHEN p_points >= 0.85 THEN 'D'
        ELSE 'F'
    END;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE calculate_all_gpas ()
BEGIN
    -- 1. Declare variables to hold each row's data
    DECLARE v_student_id INT;
    DECLARE v_calculated_gpa DECIMAL(3,2);
    DECLARE v_done TINYINT DEFAULT 0;

    -- 2. Declare the cursor
    DECLARE cur_students CURSOR FOR
        SELECT DISTINCT student_id FROM enrollments WHERE grade IS NOT NULL;

    -- 3. Declare NOT FOUND handler (MUST come after cursor declaration)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 4. Open cursor
    OPEN cur_students;

    -- 5. Fetch loop
    fetch_loop: LOOP
        FETCH cur_students INTO v_student_id;

        IF v_done = 1 THEN
            LEAVE fetch_loop;
        END IF;

        -- Calculate GPA for this student
        SELECT ROUND(AVG(points), 2)
        INTO   v_calculated_gpa
        FROM   grade_points
        WHERE  student_id = v_student_id
          AND  points IS NOT NULL;

        -- Update the students table
        UPDATE students
        SET    gpa = v_calculated_gpa
        WHERE  student_id = v_student_id;

    END LOOP fetch_loop;

    -- 6. Close cursor
    CLOSE cur_students;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE safe_enroll (
    IN  p_student_id INT,
    IN  p_course_id  INT,
    IN  p_semester   CHAR(6),
    OUT p_result     VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'ERROR: Enrollment failed — transaction rolled back.';
    END;

    DECLARE EXIT HANDLER FOR 1062    -- MySQL error: Duplicate entry
    BEGIN
        SET p_result = 'ERROR: Student already enrolled in this course this semester.';
    END;

    START TRANSACTION;

    INSERT INTO enrollments (student_id, course_id, semester)
    VALUES (p_student_id, p_course_id, p_semester);

    COMMIT;
    SET p_result = 'SUCCESS: Enrollment recorded.';
END$$

DELIMITER ;

-- CONTINUE handler: log error and skip to next row
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        @err_code = MYSQL_ERRNO,
        @err_msg  = MESSAGE_TEXT;
    INSERT INTO error_log (proc_name, error_code, error_msg, occurred_at)
    VALUES ('my_procedure', @err_code, @err_msg, NOW());
    SET v_error_count = v_error_count + 1;
END;

DELIMITER $$

CREATE PROCEDURE validated_enrollment (
    IN p_student_id INT,
    IN p_course_id  INT,
    IN p_semester   CHAR(6)
)
BEGIN
    DECLARE v_credits INT;

    -- Business rule: max 18 credits per semester
    SELECT COALESCE(SUM(c.credits), 0)
    INTO   v_credits
    FROM   enrollments e
    INNER JOIN courses c ON c.course_id = e.course_id
    WHERE  e.student_id = p_student_id
      AND  e.semester   = p_semester;

    IF v_credits >= 18 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Credit limit exceeded: maximum 18 credits per semester',
                MYSQL_ERRNO  = 1644;
    END IF;

    INSERT INTO enrollments (student_id, course_id, semester)
    VALUES (p_student_id, p_course_id, p_semester);
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE transfer_enrollment (
    IN  p_student_id   INT,
    IN  p_from_section INT,
    IN  p_to_section   INT,
    IN  p_semester     CHAR(6),
    OUT p_status       VARCHAR(100)
)
BEGIN
    DECLARE v_to_capacity INT;
    DECLARE v_to_enrolled INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'FAILED: Unexpected error. Transaction rolled back.';
    END;

    START TRANSACTION;

    -- Check capacity of target section
    SELECT capacity INTO v_to_capacity
    FROM   course_sections WHERE section_id = p_to_section;

    SELECT COUNT(*) INTO v_to_enrolled
    FROM   enrollments
    WHERE  course_id = p_to_section AND semester = p_semester;

    IF v_to_enrolled >= v_to_capacity THEN
        ROLLBACK;
        SET p_status = 'FAILED: Target section is full.';
    ELSE
        -- Remove from old section
        DELETE FROM enrollments
        WHERE  student_id = p_student_id
          AND  course_id  = p_from_section
          AND  semester   = p_semester;

        -- Add to new section
        INSERT INTO enrollments (student_id, course_id, semester)
        VALUES (p_student_id, p_to_section, p_semester);

        COMMIT;
        SET p_status = 'SUCCESS: Transfer complete.';
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE batch_grade_import (IN p_semester CHAR(6))
BEGIN
    DECLARE v_done     TINYINT DEFAULT 0;
    DECLARE v_s_id     INT;
    DECLARE v_c_id     INT;
    DECLARE v_grade    CHAR(2);
    DECLARE v_failures INT DEFAULT 0;

    DECLARE cur CURSOR FOR
        SELECT student_id, course_id, grade FROM grade_import_staging
        WHERE  semester = p_semester;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_failures = v_failures + 1;

    OPEN cur;
    gloop: LOOP
        FETCH cur INTO v_s_id, v_c_id, v_grade;
        IF v_done THEN LEAVE gloop; END IF;

        SAVEPOINT sp_row;

        UPDATE enrollments
        SET    grade = v_grade
        WHERE  student_id = v_s_id
          AND  course_id  = v_c_id
          AND  semester   = p_semester;

        IF ROW_COUNT() = 0 THEN
            ROLLBACK TO SAVEPOINT sp_row;
            INSERT INTO import_errors VALUES (p_semester, v_s_id, v_c_id, 'No matching enrollment');
        END IF;

        RELEASE SAVEPOINT sp_row;
    END LOOP gloop;
    CLOSE cur;

    SELECT CONCAT('Import complete. Failures: ', v_failures) AS result;
END$$

DELIMITER ;

-- DEFINER (default): procedure runs with the privileges of the creator
CREATE DEFINER = 'app_admin'@'localhost'
PROCEDURE secure_grade_update (IN p_student_id INT, IN p_grade CHAR(2))
SQL SECURITY DEFINER
BEGIN
    UPDATE enrollments SET grade = p_grade WHERE student_id = p_student_id;
END;

-- INVOKER: procedure runs with the privileges of the caller
CREATE PROCEDURE my_report ()
SQL SECURITY INVOKER
BEGIN
    SELECT * FROM enrollments;  -- caller must have SELECT on enrollments
END;

CREATE TABLE proc_debug_log (
    log_id     INT           PRIMARY KEY AUTO_INCREMENT,
    proc_name  VARCHAR(100),
    step       VARCHAR(200),
    var_dump   TEXT,
    logged_at  DATETIME(6)   DEFAULT NOW(6)
);

-- Inside your procedure, instrument key points:
INSERT INTO proc_debug_log (proc_name, step, var_dump)
VALUES ('calculate_all_gpas', 'Before GPA update',
        CONCAT('student_id=', v_student_id, ' gpa=', v_calculated_gpa));

-- Quick debug without a table
SET @debug_step = 'Starting enrollment check';
-- ... procedure logic ...
SET @debug_step = 'After credit limit validation';
-- After CALL:
SELECT @debug_step;

DELIMITER $$

CREATE PROCEDURE recalculate_semester_gpas (
    IN  p_semester  CHAR(6),
    OUT p_updated   INT,
    OUT p_errors    INT
)
BEGIN
    DECLARE v_student_id   INT;
    DECLARE v_new_gpa      DECIMAL(3,2);
    DECLARE v_done         TINYINT DEFAULT 0;
    DECLARE v_update_count INT DEFAULT 0;
    DECLARE v_err_count    INT DEFAULT 0;

    DECLARE cur CURSOR FOR
        SELECT DISTINCT student_id FROM enrollments WHERE semester = p_semester;

    DECLARE CONTINUE HANDLER FOR NOT FOUND   SET v_done = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_err_count = v_err_count + 1;

    -- Log procedure start
    INSERT INTO proc_debug_log (proc_name, step, var_dump)
    VALUES ('recalculate_semester_gpas', 'START', CONCAT('semester=', p_semester));

    START TRANSACTION;

    OPEN cur;
    gpa_loop: LOOP
        FETCH cur INTO v_student_id;
        IF v_done THEN LEAVE gpa_loop; END IF;

        -- Compute cumulative GPA across all completed semesters
        SELECT ROUND(AVG(gp.points), 2)
        INTO   v_new_gpa
        FROM   grade_points gp
        WHERE  gp.student_id = v_student_id
          AND  gp.points     IS NOT NULL;

        UPDATE students SET gpa = v_new_gpa WHERE student_id = v_student_id;
        SET v_update_count = v_update_count + 1;
    END LOOP gpa_loop;
    CLOSE cur;

    COMMIT;

    SET p_updated = v_update_count;
    SET p_errors  = v_err_count;

    INSERT INTO proc_debug_log (proc_name, step, var_dump)
    VALUES ('recalculate_semester_gpas', 'END',
            CONCAT('updated=', v_update_count, ' errors=', v_err_count));
END$$

DELIMITER ;

-- Execute and check results
CALL recalculate_semester_gpas('F2025', @updated, @errors);
SELECT @updated AS rows_updated, @errors AS error_count;