MySQL 8 教程

调用不带参数的存储过程

-- 调用不带参数的存储过程
CALL procedure_name();

-- 示例：调用一个获取所有员工信息的存储过程
CALL GetAllEmployees();

调用带输入参数（IN）的存储过程

-- 调用带输入参数的存储过程
CALL procedure_name(parameter1, parameter2, ...);

-- 示例：调用一个根据部门ID查询员工信息的存储过程
CALL GetEmployeesByDepartment(10);

-- 示例：使用命名参数方式调用存储过程（MySQL 5.7.5及以上版本支持）
CALL GetEmployeesByDepartment(dept_id => 10);

-- 示例：调用带多个输入参数的存储过程
CALL SearchEmployees(10, '工程师', '张三');

-- 示例：调用带默认值参数的存储过程（可以省略有默认值的参数）
CALL GetEmployeesByHireYear(); -- 使用默认值
CALL GetEmployeesByHireYear(2021); -- 传递自定义值

调用带输出参数（OUT）的存储过程

对于带输出参数的存储过程，需要使用用户变量来接收输出值：

-- 调用带输出参数的存储过程
CALL procedure_name(input_param, @output_var);

-- 查看输出参数的值
SELECT @output_var;

-- 示例：调用一个获取指定部门员工数量的存储过程
CALL GetEmployeeCountByDepartment(10, @emp_count);
SELECT @emp_count AS department_employee_count;

-- 示例：调用带多个输出参数的存储过程
CALL GetDepartmentStatistics(10, @avg_salary, @max_salary, @min_salary);
SELECT 
    @avg_salary AS average_salary,
    @max_salary AS maximum_salary,
    @min_salary AS minimum_salary;

调用带输入/输出参数（INOUT）的存储过程

对于带输入/输出参数的存储过程，需要先为参数赋值，然后再调用存储过程：

-- 为输入/输出参数赋值
SET @inout_var = initial_value;

-- 调用带输入/输出参数的存储过程
CALL procedure_name(@inout_var);

-- 查看参数的新值
SELECT @inout_var;

-- 示例：调用一个带输入/输出参数的存储过程
SET @current_value = 1000;
CALL CalculateNewValue(@current_value);
SELECT @current_value AS new_value;

-- 示例：带多个输入/输出参数的存储过程
SET @x = 10;
SET @y = 20;
CALL SwapValues(@x, @y);
SELECT @x AS new_x, @y AS new_y;

在SELECT语句中调用存储函数

-- 在SELECT语句中调用存储函数
SELECT function_name(parameter1, parameter2, ...) AS result_name;

-- 示例：调用一个计算员工年薪的存储函数
SELECT employee_id, first_name, last_name, salary,
       CalculateAnnualSalary(salary) AS annual_salary
FROM employees;

-- 示例：在WHERE子句中使用存储函数
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > CalculateDays(5); -- 5年的天数

-- 示例：在GROUP BY子句中使用存储函数
SELECT 
    GetDepartmentCategory(department_id) AS dept_category,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_category;

在INSERT语句中调用存储函数

-- 在INSERT语句中调用存储函数
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, function_name(parameter1, ...), ...);

-- 示例：在插入数据时使用存储函数计算值
INSERT INTO employee_annual_salaries (employee_id, annual_salary, calculation_date)
SELECT 
    employee_id,
    CalculateAnnualSalary(salary),
    CURDATE()
FROM employees;

-- 示例：使用存储函数生成唯一ID
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (GenerateOrderId(), 1001, CURDATE());

在UPDATE语句中调用存储函数

-- 在UPDATE语句中调用存储函数
UPDATE table_name
SET column_name = function_name(parameter1, ...)
WHERE condition;

-- 示例：使用存储函数更新员工的年薪信息
UPDATE employees
SET annual_salary = CalculateAnnualSalary(salary);

-- 示例：根据存储函数的返回值更新数据
UPDATE products
SET discount_price = price * GetDiscountFactor(category_id)
WHERE category_id IN (1, 2, 3);

-- 示例：使用存储函数生成更新日志
UPDATE employees
SET 
    salary = salary * 1.1,
    last_update = CURDATE(),
    update_log = CONCAT(update_log, '; ', GenerateUpdateLog('salary', salary, salary * 1.1))
WHERE performance_rating > 4;

在DELETE语句中调用存储函数

-- 在DELETE语句中调用存储函数
DELETE FROM table_name
WHERE condition involving function_name(parameters);

-- 示例：根据存储函数的返回值删除数据
DELETE FROM temporary_records
WHERE IsExpired(record_date) = 1;

-- 示例：使用存储函数验证删除条件
DELETE FROM user_sessions
WHERE 
    session_id = @session_id AND 
    ValidateSession(@session_id, @user_id) = 1;

在存储过程中调用其他存储过程

-- 在存储过程中调用其他存储过程
DELIMITER //
CREATE PROCEDURE ProcessDepartmentData(IN dept_id INT)
BEGIN
    -- 声明变量
    DECLARE emp_count INT;
    
    -- 调用其他存储过程
    CALL GetEmployeesByDepartment(dept_id);
    
    -- 调用带输出参数的存储过程
    CALL GetEmployeeCountByDepartment(dept_id, @count);
    SET emp_count = @count;
    
    -- 根据获取的结果执行其他操作
    IF emp_count > 0 THEN
        CALL CalculateDepartmentStatistics(dept_id);
    END IF;
END //
DELIMITER ;

在存储过程中调用存储函数

-- 在存储过程中调用存储函数
DELIMITER //
CREATE PROCEDURE GenerateEmployeeReport(IN dept_id INT)
BEGIN
    -- 声明变量
    DECLARE avg_salary DECIMAL(10,2);
    DECLARE report_date VARCHAR(20);
    
    -- 调用存储函数获取平均工资
    SET avg_salary = GetAverageSalaryByDepartment(dept_id);
    
    -- 调用存储函数格式化日期
    SET report_date = FormatDate(CURDATE());
    
    -- 创建报告
    INSERT INTO employee_reports (department_id, average_salary, report_date)
    VALUES (dept_id, avg_salary, report_date);
END //
DELIMITER ;

在存储函数中调用其他存储函数

-- 在存储函数中调用其他存储函数
DELIMITER //
CREATE FUNCTION CalculateEmployeeBonus(emp_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    -- 声明变量
    DECLARE salary DECIMAL(10,2);
    DECLARE performance_rating INT;
    DECLARE bonus_percentage DECIMAL(5,2);
    DECLARE bonus_amount DECIMAL(10,2);
    
    -- 获取员工信息
    SELECT salary, performance_rating
    INTO salary, performance_rating
    FROM employees
    WHERE employee_id = emp_id;
    
    -- 调用存储函数获取奖金比例
    SET bonus_percentage = GetBonusPercentage(performance_rating);
    
    -- 计算奖金金额
    SET bonus_amount = CalculateAmount(salary, bonus_percentage);
    
    RETURN bonus_amount;
END //
DELIMITER ;

在存储函数中调用存储过程

注意：在存储函数中不能直接调用有输出参数或执行数据修改操作的存储过程，因为存储函数不能修改数据（除了在函数体内部声明的变量）。但是，可以调用不修改数据的存储过程。

-- 在存储函数中调用存储过程（有限制）
DELIMITER //
CREATE FUNCTION GetDepartmentInfo(dept_id INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    -- 声明变量
    DECLARE dept_info VARCHAR(255) DEFAULT '';
    
    -- 调用一个不修改数据的存储过程
    CALL LogOperation('GetDepartmentInfo', dept_id);
    
    -- 获取部门信息
    SELECT CONCAT('部门ID: ', department_id, ', 部门名称: ', department_name)
    INTO dept_info
    FROM departments
    WHERE department_id = dept_id;
    
    RETURN dept_info;
END //
DELIMITER ;

-- 简单的日志记录存储过程（不修改调用环境的数据）
DELIMITER //
CREATE PROCEDURE LogOperation(IN operation_name VARCHAR(50), IN operation_param INT)
BEGIN
    -- 注意：在实际应用中，这里应该插入日志记录到日志表
    -- 但在存储函数中调用的存储过程不能修改数据，所以这里只是一个示例
    SELECT CONCAT('Operation: ', operation_name, ', Param: ', operation_param) AS log_message;
END //
DELIMITER ;

在PHP中调用MySQL存储过程和函数

<?php
// 连接到MySQL数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 调用不带参数的存储过程
$sql = "CALL GetAllEmployees()";
$result = $conn->query($sql);

// 处理结果
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "员工ID: " . $row["employee_id"]. " - 姓名: " . $row["first_name"]. " " . $row["last_name"]. "
";
    }
} else {
    echo "0 结果";
}

// 调用带输入参数的存储过程
$dept_id = 10;
$sql = "CALL GetEmployeesByDepartment(?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $dept_id);
$stmt->execute();
$result = $stmt->get_result();

// 处理结果
// ...

// 调用带输出参数的存储过程
$dept_id = 10;
$sql = "CALL GetEmployeeCountByDepartment(?, @emp_count)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $dept_id);
$stmt->execute();

// 获取输出参数的值
$result = $conn->query("SELECT @emp_count AS emp_count");
$row = $result->fetch_assoc();
echo "部门员工数量: " . $row["emp_count"];

// 调用存储函数
$emp_id = 1001;
$sql = "SELECT CalculateAnnualSalary((SELECT salary FROM employees WHERE employee_id = ?)) AS annual_salary";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $emp_id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
echo "员工年薪: " . $row["annual_salary"];

// 关闭连接
$stmt->close();
$conn->close();
?>

在Python中调用MySQL存储过程和函数

# 导入MySQL连接器
import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="myDB"
)

# 创建游标对象
cursor = db.cursor()

# 调用不带参数的存储过程
cursor.callproc("GetAllEmployees")

# 处理结果
for result in cursor.stored_results():
    for row in result.fetchall():
        print(f"员工ID: {row[0]}, 姓名: {row[1]} {row[2]}")

# 调用带输入参数的存储过程
dept_id = 10
cursor.callproc("GetEmployeesByDepartment", [dept_id])

# 处理结果
for result in cursor.stored_results():
    for row in result.fetchall():
        print(f"员工ID: {row[0]}, 姓名: {row[1]} {row[2]}")

# 调用带输出参数的存储过程
dept_id = 10
output_params = cursor.callproc("GetEmployeeCountByDepartment", [dept_id, 0])
emp_count = output_params[1]  # 输出参数的值在返回的元组中
print(f"部门员工数量: {emp_count}")

# 或者使用以下方式获取输出参数
cursor.execute("SET @dept_id = %s", (dept_id,))
cursor.execute("CALL GetEmployeeCountByDepartment(@dept_id, @emp_count)")
cursor.execute("SELECT @emp_count AS emp_count")
result = cursor.fetchone()
print(f"部门员工数量: {result[0]}")

# 调用存储函数
emp_id = 1001
cursor.execute("SELECT salary FROM employees WHERE employee_id = %s", (emp_id,))
salary = cursor.fetchone()[0]
cursor.execute("SELECT CalculateAnnualSalary(%s) AS annual_salary", (salary,))
result = cursor.fetchone()
print(f"员工年薪: {result[0]}")

# 关闭游标和连接
cursor.close()
db.close()

在Java中调用MySQL存储过程和函数

import java.sql.*;

public class CallMySQLStoredProcedures {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/myDB";
        String user = "username";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 调用不带参数的存储过程
            CallableStatement cs1 = conn.prepareCall("{CALL GetAllEmployees()}");
            ResultSet rs1 = cs1.executeQuery();
            
            // 处理结果
            while (rs1.next()) {
                int empId = rs1.getInt("employee_id");
                String firstName = rs1.getString("first_name");
                String lastName = rs1.getString("last_name");
                System.out.println("员工ID: " + empId + ", 姓名: " + firstName + " " + lastName);
            }
            
            // 调用带输入参数的存储过程
            int deptId = 10;
            CallableStatement cs2 = conn.prepareCall("{CALL GetEmployeesByDepartment(?)}");
            cs2.setInt(1, deptId);
            ResultSet rs2 = cs2.executeQuery();
            
            // 处理结果
            // ...
            
            // 调用带输出参数的存储过程
            CallableStatement cs3 = conn.prepareCall("{CALL GetEmployeeCountByDepartment(?, ?)}");
            cs3.setInt(1, deptId);
            cs3.registerOutParameter(2, Types.INTEGER);
            cs3.execute();
            
            // 获取输出参数的值
            int empCount = cs3.getInt(2);
            System.out.println("部门员工数量: " + empCount);
            
            // 调用存储函数
            int empId = 1001;
            CallableStatement cs4 = conn.prepareCall("{? = CALL CalculateAnnualSalary(?)}");
            cs4.registerOutParameter(1, Types.DECIMAL);
            
            // 先获取员工工资
            PreparedStatement ps = conn.prepareStatement("SELECT salary FROM employees WHERE employee_id = ?");
            ps.setInt(1, empId);
            ResultSet rsSalary = ps.executeQuery();
            if (rsSalary.next()) {
                BigDecimal salary = rsSalary.getBigDecimal("salary");
                cs4.setBigDecimal(2, salary);
                cs4.execute();
                
                BigDecimal annualSalary = cs4.getBigDecimal(1);
                System.out.println("员工年薪: " + annualSalary);
            }
            
            // 关闭资源
            rs1.close();
            cs1.close();
            rs2.close();
            cs2.close();
            cs3.close();
            rsSalary.close();
            ps.close();
            cs4.close();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}