流程控制

CREATE DATABASE IF NOT EXISTS school;
USE school;

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

DELIMITER //

CREATE FUNCTION GetGrade(score DECIMAL(5,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    IF score IS NULL THEN
        RETURN '未评分';
    ELSEIF score >= 90 THEN
        RETURN '优秀';
    ELSEIF score >= 60 THEN
        RETURN '及格';
    ELSE
        RETURN '不及格';
    END IF;
END //

DELIMITER ;

-- 使用函数查询每个员工的绩效等级
SELECT emp_name, score, GetGrade(score) AS grade FROM employees;

DELIMITER //

CREATE FUNCTION GetLevel(score DECIMAL(5,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    CASE
        WHEN score IS NULL THEN RETURN 'N';
        WHEN score >= 90 THEN RETURN 'A';
        WHEN score >= 60 THEN RETURN 'B';
        ELSE RETURN 'C';
    END CASE;
END //

DELIMITER ;

-- 使用
SELECT emp_name, score, GetLevel(score) AS level FROM employees;

DELIMITER //

CREATE FUNCTION GetDeptCode(dept VARCHAR(20))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    CASE dept
        WHEN '技术部' THEN RETURN 'TECH';
        WHEN '市场部' THEN RETURN 'MKT';
        ELSE RETURN 'OTHER';
    END CASE;
END //

DELIMITER ;

-- 使用
SELECT emp_name, dept, GetDeptCode(dept) AS dept_code FROM employees;

DELIMITER //

CREATE PROCEDURE PrintNumbers()
BEGIN
    DECLARE i INT DEFAULT 1;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_numbers (num INT);
    TRUNCATE TABLE temp_numbers;

    WHILE i <= 5 DO
        INSERT INTO temp_numbers VALUES (i);
        SET i = i + 1;
    END WHILE;

    SELECT * FROM temp_numbers;
    DROP TEMPORARY TABLE IF EXISTS temp_numbers;
END //

DELIMITER ;

-- 调用
CALL PrintNumbers();

DELIMITER //

CREATE PROCEDURE CalcSumWithRepeat()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;

    REPEAT
        SET total = total + i;
        SET i = i + 1;
    UNTIL i > 5 END REPEAT;

    SELECT total AS sum_1_to_5;
END //

DELIMITER ;

-- 调用
CALL CalcSumWithRepeat();

DELIMITER //

CREATE PROCEDURE CompareWhileRepeat()
BEGIN
    DECLARE i INT DEFAULT 10;
    DECLARE while_result INT DEFAULT 0;
    DECLARE repeat_result INT DEFAULT 0;

    -- WHILE：条件不满足，一次都不执行
    WHILE i < 5 DO
        SET while_result = while_result + i;
        SET i = i + 1;
    END WHILE;

    SET i = 10;

    -- REPEAT：先执行一次，再判断条件
    REPEAT
        SET repeat_result = repeat_result + i;
        SET i = i + 1;
    UNTIL i < 5 END REPEAT;

    SELECT while_result AS while_result, repeat_result AS repeat_result;
END //

DELIMITER ;

CALL CompareWhileRepeat();

DELIMITER //

CREATE PROCEDURE PrintWithLoop()
BEGIN
    DECLARE i INT DEFAULT 1;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_loop_result (num INT);
    TRUNCATE TABLE temp_loop_result;

    my_loop: LOOP
        IF i > 5 THEN
            LEAVE my_loop;  -- 跳出循环，相当于 break
        END IF;

        IF i = 3 THEN
            SET i = i + 1;
            ITERATE my_loop;  -- 跳过当前迭代，相当于 continue
        END IF;

        INSERT INTO temp_loop_result VALUES (i);
        SET i = i + 1;
    END LOOP my_loop;

    SELECT * FROM temp_loop_result;
    DROP TEMPORARY TABLE IF EXISTS temp_loop_result;
END //

DELIMITER ;

-- 调用
CALL PrintWithLoop();

DROP TABLE IF EXISTS temp_employees;
CREATE TABLE temp_employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    score DECIMAL(5,2)
);

DELIMITER //

CREATE PROCEDURE BatchInsertEmployees()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 2 DO
        INSERT INTO temp_employees (emp_name, score)
        VALUES (IF(i=1, '小崔', '黄俪'), 70 + i * 10);
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

-- 调用前确认表为空
SELECT COUNT(*) AS before_count FROM temp_employees;

-- 执行批量插入
CALL BatchInsertEmployees();

-- 调用后查看数据
SELECT * FROM temp_employees;