MySQL 8 教程

创建存储过程的语法

CREATE PROCEDURE procedure_name ([parameter[,...]])
BEGIN
    -- SQL语句
END;

其中，parameter的语法格式如下：

[IN|OUT|INOUT] parameter_name type [DEFAULT value]

• IN：输入参数，表示该参数的值必须在调用存储过程时指定
• OUT：输出参数，表示该参数的值可以在存储过程中被修改，并可返回
• INOUT：既可以作为输入参数，也可以作为输出参数
• parameter_name：参数名
• type：参数的数据类型
• DEFAULT value：参数的默认值（可选）

创建简单存储过程示例

-- 创建一个简单的存储过程，用于查询所有员工信息
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

-- 创建一个接受参数的存储过程，用于根据部门ID查询员工信息
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT * FROM employees WHERE department_id = dept_id;
END //
DELIMITER ;

-- 创建一个带有输出参数的存储过程，用于获取指定部门的员工数量
DELIMITER //
CREATE PROCEDURE GetEmployeeCountByDepartment(
    IN dept_id INT,
    OUT employee_count INT
)
BEGIN
    SELECT COUNT(*) INTO employee_count FROM employees WHERE department_id = dept_id;
END //
DELIMITER ;

-- 创建一个带有默认值参数的存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeesByHireYear(
    IN hire_year INT DEFAULT 2020
)
BEGIN
    SELECT * FROM employees WHERE YEAR(hire_date) = hire_year;
END //
DELIMITER ;

注意：在MySQL命令行中，需要使用DELIMITER语句来更改语句结束符，因为存储过程体中包含分号，会被MySQL解释为语句结束。

创建存储函数的语法

CREATE FUNCTION function_name ([parameter[,...]])
RETURNS return_type
[NOT] DETERMINISTIC
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
[SQL SECURITY {DEFINER | INVOKER}]
BEGIN
    -- SQL语句
    RETURN return_value;
END;

• function_name：函数名
• parameter：参数列表（可选），存储函数的参数只能是IN类型
• return_type：返回值的数据类型
• DETERMINISTIC：表示函数是否是确定性的，即相同的输入是否总是产生相同的输出
• CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：表示函数对SQL数据的操作类型
• SQL SECURITY {DEFINER | INVOKER}：指定函数的安全上下文
• RETURN return_value：指定函数的返回值

创建存储函数示例

-- 创建一个简单的存储函数，用于计算员工的年薪
DELIMITER //
CREATE FUNCTION CalculateAnnualSalary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN monthly_salary * 12;
END //
DELIMITER ;

-- 创建一个存储函数，用于获取指定部门的平均工资
DELIMITER //
CREATE FUNCTION GetAverageSalaryByDepartment(dept_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE avg_salary DECIMAL(10,2);
    SELECT AVG(salary) INTO avg_salary FROM employees WHERE department_id = dept_id;
    RETURN avg_salary;
END //
DELIMITER ;

-- 创建一个存储函数，用于格式化日期
DELIMITER //
CREATE FUNCTION FormatDate(date_value DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN DATE_FORMAT(date_value, '%Y年%m月%d日');
END //
DELIMITER ;

-- 创建一个存储函数，用于根据员工ID获取员工的完整姓名
DELIMITER //
CREATE FUNCTION GetEmployeeFullName(emp_id INT)
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
    DECLARE full_name VARCHAR(100);
    SELECT CONCAT(first_name, ' ', last_name) INTO full_name FROM employees WHERE employee_id = emp_id;
    RETURN full_name;
END //
DELIMITER ;

定义和使用局部变量

-- 示例：在存储过程中使用局部变量
DELIMITER //
CREATE PROCEDURE CalculateEmployeeStatistics(
    IN dept_id INT,
    OUT avg_salary DECIMAL(10,2),
    OUT max_salary DECIMAL(10,2),
    OUT min_salary DECIMAL(10,2)
)
BEGIN
    -- 声明局部变量
    DECLARE emp_count INT DEFAULT 0;
    
    -- 计算员工数量
    SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dept_id;
    
    -- 如果没有员工，设置默认值
    IF emp_count > 0 THEN
        SELECT AVG(salary), MAX(salary), MIN(salary)
        INTO avg_salary, max_salary, min_salary
        FROM employees
        WHERE department_id = dept_id;
    ELSE
        SET avg_salary = 0;
        SET max_salary = 0;
        SET min_salary = 0;
    END IF;
END //
DELIMITER ;

定义和使用用户变量

-- 在存储过程外部定义和使用用户变量
SET @dept_id = 10;
SELECT @dept_id;

-- 在存储过程中使用用户变量
DELIMITER //
CREATE PROCEDURE ProcessEmployeesByDepartment()
BEGIN
    -- 使用用户变量
    SELECT * FROM employees WHERE department_id = @dept_id;
    
    -- 修改用户变量的值
    SET @employee_count = (SELECT COUNT(*) FROM employees WHERE department_id = @dept_id);
END //
DELIMITER ;

-- 调用存储过程并查看用户变量的值
CALL ProcessEmployeesByDepartment();
SELECT @employee_count;

查看和修改系统变量

-- 查看全局系统变量
SELECT @@GLOBAL.max_connections;

-- 查看会话系统变量
SELECT @@SESSION.sql_mode;

-- 修改会话系统变量
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

-- 在存储过程中使用系统变量
DELIMITER //
CREATE PROCEDURE GetServerInfo()
BEGIN
    DECLARE server_version VARCHAR(50);
    SELECT VERSION() INTO server_version;
    SELECT 
        server_version AS version,
        @@GLOBAL.max_connections AS max_connections,
        @@GLOBAL.innodb_buffer_pool_size AS innodb_buffer_pool_size;
END //
DELIMITER ;

定义条件和处理程序的语法

-- 定义条件
DECLARE condition_name CONDITION FOR condition_value;

-- 定义处理程序
DECLARE handler_type HANDLER FOR condition_value[, condition_value]... statement;

-- condition_value可以是以下值之一：
-- 1. MySQL错误代码（如1062表示重复键错误）
-- 2. SQLSTATE值（如'23000'表示完整性约束违反）
-- 3. 条件名称（使用DECLARE定义的条件）
-- 4. SQLWARNING（SQL警告）
-- 5. NOT FOUND（未找到数据）
-- 6. SQLEXCEPTION（SQL异常）

-- handler_type可以是以下值之一：
-- 1. CONTINUE（继续执行程序）
-- 2. EXIT（退出当前的BEGIN...END块）
-- 3. UNDO（撤销当前事务，但MySQL不支持）

定义条件和处理程序示例

-- 示例：使用条件和处理程序处理重复键错误
DELIMITER //
CREATE PROCEDURE InsertEmployeeWithErrorHandling(
    IN emp_id INT,
    IN first_name VARCHAR(50),
    IN last_name VARCHAR(50),
    IN email VARCHAR(100),
    OUT result_message VARCHAR(255)
)
BEGIN
    -- 定义条件
    DECLARE duplicate_key CONDITION FOR 1062;
    
    -- 定义处理程序
    DECLARE CONTINUE HANDLER FOR duplicate_key
    BEGIN
        SET result_message = '错误：员工ID或邮箱已存在！';
    END;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET result_message = '错误：插入员工数据时发生未知错误！';
    END;
    
    -- 尝试插入数据
    INSERT INTO employees (employee_id, first_name, last_name, email)
    VALUES (emp_id, first_name, last_name, email);
    
    -- 如果没有错误发生，设置成功消息
    IF result_message IS NULL THEN
        SET result_message = '成功：员工数据已插入！';
    END IF;
END //
DELIMITER ;

-- 示例：使用处理程序处理未找到数据的情况
DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(
    IN emp_id INT,
    OUT emp_name VARCHAR(100),
    OUT emp_salary DECIMAL(10,2),
    OUT emp_dept VARCHAR(50)
)
BEGIN
    -- 定义变量
    DECLARE not_found INT DEFAULT 0;
    
    -- 定义处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET not_found = 1;
    END;
    
    -- 尝试查询员工信息
    SELECT 
        CONCAT(first_name, ' ', last_name),
        salary,
        department_name
    INTO emp_name, emp_salary, emp_dept
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = emp_id;
    
    -- 如果未找到员工，设置默认值
    IF not_found = 1 THEN
        SET emp_name = '未找到该员工';
        SET emp_salary = 0;
        SET emp_dept = '未知部门';
    END IF;
END //
DELIMITER ;

声明和使用光标的步骤

-- 1. 声明光标
DECLARE cursor_name CURSOR FOR select_statement;

-- 2. 打开光标
OPEN cursor_name;

-- 3. 获取光标中的数据
FETCH cursor_name INTO variable_list;

-- 4. 关闭光标
CLOSE cursor_name;

-- 通常，还需要定义一个处理程序来处理光标遍历结束的情况
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

使用光标示例

-- 示例：使用光标遍历员工表，计算每个部门的平均工资
DELIMITER //
CREATE PROCEDURE CalculateAverageSalaryByDepartment()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE dept_id INT;
    DECLARE dept_name VARCHAR(50);
    DECLARE avg_sal DECIMAL(10,2);
    
    -- 声明光标
    DECLARE dept_cursor CURSOR FOR SELECT department_id, department_name FROM departments;
    
    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 创建临时表存储结果
    DROP TABLE IF EXISTS department_salary_stats;
    CREATE TABLE department_salary_stats (
        department_id INT,
        department_name VARCHAR(50),
        average_salary DECIMAL(10,2)
    );
    
    -- 打开光标
    OPEN dept_cursor;
    
    -- 循环遍历光标
    read_loop:
    LOOP
        -- 获取下一行数据
        FETCH dept_cursor INTO dept_id, dept_name;
        
        -- 如果没有更多数据，退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 计算平均工资
        SELECT AVG(salary) INTO avg_sal
        FROM employees
        WHERE department_id = dept_id;
        
        -- 将结果插入临时表
        INSERT INTO department_salary_stats (department_id, department_name, average_salary)
        VALUES (dept_id, dept_name, COALESCE(avg_sal, 0));
    END LOOP read_loop;
    
    -- 关闭光标
    CLOSE dept_cursor;
    
    -- 显示结果
    SELECT * FROM department_salary_stats;
END //
DELIMITER ;

-- 示例：使用光标更新员工工资
DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalariesByPerformance(
    IN rating_threshold INT,
    IN salary_increase DECIMAL(5,2)
)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE current_salary DECIMAL(10,2);
    DECLARE new_salary DECIMAL(10,2);
    
    -- 声明光标，获取符合条件的员工
    DECLARE emp_cursor CURSOR FOR 
        SELECT e.employee_id, e.salary 
        FROM employees e
        JOIN performance_reviews p ON e.employee_id = p.employee_id
        WHERE p.rating >= rating_threshold;
    
    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开光标
    OPEN emp_cursor;
    
    -- 循环遍历光标
    update_loop:
    LOOP
        -- 获取下一行数据
        FETCH emp_cursor INTO emp_id, current_salary;
        
        -- 如果没有更多数据，退出循环
        IF done THEN
            LEAVE update_loop;
        END IF;
        
        -- 计算新工资
        SET new_salary = current_salary * (1 + salary_increase / 100);
        
        -- 更新员工工资
        UPDATE employees
        SET salary = new_salary
        WHERE employee_id = emp_id;
    END LOOP update_loop;
    
    -- 关闭光标
    CLOSE emp_cursor;
END //
DELIMITER ;

IF语句示例

-- 示例：使用IF语句根据员工的工作年限计算奖金
DELIMITER //
CREATE PROCEDURE CalculateBonusByExperience(
    IN emp_id INT,
    OUT bonus DECIMAL(10,2)
)
BEGIN
    -- 声明变量
    DECLARE hire_date DATE;
    DECLARE experience_years INT;
    DECLARE current_salary DECIMAL(10,2);
    
    -- 获取员工信息
    SELECT hire_date, salary INTO hire_date, current_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    -- 计算工作年限
    SET experience_years = TIMESTAMPDIFF(YEAR, hire_date, CURDATE());
    
    -- 根据工作年限计算奖金
    IF experience_years < 1 THEN
        SET bonus = current_salary * 0.05;
    ELSEIF experience_years < 3 THEN
        SET bonus = current_salary * 0.1;
    ELSEIF experience_years < 5 THEN
        SET bonus = current_salary * 0.15;
    ELSE
        SET bonus = current_salary * 0.2;
    END IF;
END //
DELIMITER ;

CASE语句示例

-- 示例：使用CASE语句根据员工的职位等级计算工资调整幅度
DELIMITER //
CREATE PROCEDURE CalculateSalaryAdjustment(
    IN emp_id INT,
    OUT adjustment_percentage DECIMAL(5,2)
)
BEGIN
    -- 声明变量
    DECLARE job_level VARCHAR(20);
    
    -- 获取员工的职位等级
    SELECT job_level INTO job_level
    FROM employees
    WHERE employee_id = emp_id;
    
    -- 使用CASE语句确定调整幅度
    CASE job_level
        WHEN '初级' THEN
            SET adjustment_percentage = 5.0;
        WHEN '中级' THEN
            SET adjustment_percentage = 4.0;
        WHEN '高级' THEN
            SET adjustment_percentage = 3.0;
        WHEN '专家' THEN
            SET adjustment_percentage = 2.0;
        WHEN '总监' THEN
            SET adjustment_percentage = 1.0;
        ELSE
            SET adjustment_percentage = 0.0;
    END CASE;
END //
DELIMITER ;

-- 示例：使用CASE语句根据销售额计算佣金
DELIMITER //
CREATE FUNCTION CalculateSalesCommission(sales_amount DECIMAL(12,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    DECLARE commission DECIMAL(12,2);
    
    CASE
        WHEN sales_amount < 10000 THEN
            SET commission = sales_amount * 0.05;
        WHEN sales_amount < 50000 THEN
            SET commission = sales_amount * 0.08;
        WHEN sales_amount < 100000 THEN
            SET commission = sales_amount * 0.12;
        ELSE
            SET commission = sales_amount * 0.15;
    END CASE;
    
    RETURN commission;
END //
DELIMITER ;

LOOP、LEAVE和ITERATE语句示例

-- 示例：使用LOOP语句计算斐波那契数列
DELIMITER //
CREATE PROCEDURE GenerateFibonacciSequence(
    IN n INT,
    OUT result TEXT
)
BEGIN
    -- 声明变量
    DECLARE a INT DEFAULT 0;
    DECLARE b INT DEFAULT 1;
    DECLARE c INT;
    DECLARE i INT DEFAULT 0;
    
    -- 初始化结果
    SET result = CAST(a AS CHAR);
    
    -- 使用LOOP语句生成斐波那契数列
    fibonacci_loop:
    LOOP
        -- 如果达到指定的项数，退出循环
        IF i >= n - 1 THEN
            LEAVE fibonacci_loop;
        END IF;
        
        -- 计算下一个斐波那契数
        SET c = a + b;
        SET a = b;
        SET b = c;
        
        -- 将结果添加到字符串中
        SET result = CONCAT(result, ', ', CAST(a AS CHAR));
        
        -- 增加计数器
        SET i = i + 1;
    END LOOP fibonacci_loop;
END //
DELIMITER ;

-- 示例：使用LOOP、LEAVE和ITERATE语句处理员工数据
DELIMITER //
CREATE PROCEDURE ProcessHighSalaryEmployees(
    IN min_salary DECIMAL(10,2)
)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10,2);
    DECLARE emp_performance INT;
    
    -- 声明光标
    DECLARE emp_cursor CURSOR FOR 
        SELECT employee_id, salary, performance_rating 
        FROM employees
        WHERE salary >= min_salary;
    
    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开光标
    OPEN emp_cursor;
    
    -- 循环处理员工数据
    process_loop:
    LOOP
        -- 获取员工数据
        FETCH emp_cursor INTO emp_id, emp_salary, emp_performance;
        
        -- 如果没有更多数据，退出循环
        IF done THEN
            LEAVE process_loop;
        END IF;
        
        -- 如果绩效低于3，跳过当前员工
        IF emp_performance < 3 THEN
            ITERATE process_loop;
        END IF;
        
        -- 处理高绩效员工（例如，发放奖金）
        UPDATE employees
        SET bonus = salary * 0.1
        WHERE employee_id = emp_id;
    END LOOP process_loop;
    
    -- 关闭光标
    CLOSE emp_cursor;
END //
DELIMITER ;

WHILE语句示例

-- 示例：使用WHILE语句计算阶乘
DELIMITER //
CREATE FUNCTION CalculateFactorial(n INT)
RETURNS BIGINT
DETERMINISTIC
BEGIN
    -- 声明变量
    DECLARE result BIGINT DEFAULT 1;
    DECLARE i INT DEFAULT 1;
    
    -- 使用WHILE语句计算阶乘
    WHILE i <= n DO
        SET result = result * i;
        SET i = i + 1;
    END WHILE;
    
    RETURN result;
END //
DELIMITER ;

-- 示例：使用WHILE语句批量插入测试数据
DELIMITER //
CREATE PROCEDURE InsertTestData(
    IN table_name VARCHAR(50),
    IN record_count INT
)
BEGIN
    -- 声明变量
    DECLARE i INT DEFAULT 1;
    DECLARE insert_sql VARCHAR(1000);
    
    -- 使用WHILE语句循环插入数据
    WHILE i <= record_count DO
        -- 构建插入语句（假设表有id和name两个字段）
        SET insert_sql = CONCAT(
            'INSERT INTO ', 
            table_name, 
            ' (id, name) VALUES (', 
            i, ', ''Test User ', i, ''')'
        );
        
        -- 执行插入语句
        SET @sql = insert_sql;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 增加计数器
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

REPEAT语句示例

-- 示例：使用REPEAT语句计算幂
DELIMITER //
CREATE FUNCTION CalculatePower(
    base INT,
    exponent INT
)
RETURNS BIGINT
DETERMINISTIC
BEGIN
    -- 声明变量
    DECLARE result BIGINT DEFAULT 1;
    DECLARE i INT DEFAULT 1;
    
    -- 处理特殊情况
    IF exponent = 0 THEN
        RETURN 1;
    ELSEIF exponent < 0 THEN
        RETURN NULL; -- MySQL不支持返回浮点数，所以负数幂返回NULL
    END IF;
    
    -- 使用REPEAT语句计算幂
    REPEAT
        SET result = result * base;
        SET i = i + 1;
    UNTIL i > exponent
    END REPEAT;
    
    RETURN result;
END //
DELIMITER ;

-- 示例：使用REPEAT语句重试操作
DELIMITER //
CREATE PROCEDURE RetryOperation(
    IN max_retries INT,
    OUT success INT
)
BEGIN
    -- 声明变量
    DECLARE retries INT DEFAULT 0;
    DECLARE operation_success INT DEFAULT 0;
    
    -- 使用REPEAT语句重试操作
    REPEAT
        -- 尝试执行操作（这里使用随机数模拟操作成功或失败）
        SET operation_success = FLOOR(RAND() * 2);
        
        -- 如果操作成功，退出循环
        IF operation_success = 1 THEN
            LEAVE;
        END IF;
        
        -- 增加重试次数
        SET retries = retries + 1;
    UNTIL retries >= max_retries
    END REPEAT;
    
    -- 设置成功标志
    SET success = operation_success;
END //
DELIMITER ;