스토어드 프로시저

-- MySQL 스토어드 프로시저 생성
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(
    IN dept_id INT,
    OUT employee_count INT
)
BEGIN
    -- 직원 목록 조회
    SELECT id, name, salary
    FROM employees
    WHERE department_id = dept_id;

    -- 직원 수 반환
    SELECT COUNT(*) INTO employee_count
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

-- 프로시저 호출
CALL GetEmployeesByDept(10, @count);
SELECT @count AS total_employees;

-- 조건문과 반복문 포함 프로시저
DELIMITER //
CREATE PROCEDURE UpdateSalaries()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, salary FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO emp_id, emp_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF emp_salary < 3000000 THEN
            UPDATE employees SET salary = salary * 1.1 WHERE id = emp_id;
        END IF;
    END LOOP;
    CLOSE cur;

    COMMIT;
END //
DELIMITER ;