MySQL的使用——存储过程与函数详解！！！_mysql存储过程和函数-CSDN博客

本文链接：https://blog.csdn.net/2401_82884096/article/details/136626410

MySQL中存储过程和函数是一种可重复使用的数据库对象，用于封装一系列SQL语句并在需要时进行调用。下面详细介绍MySQL存储过程和函数的使用方法。

一、存储过程

存储过程是一组预编译的SQL语句集合，可以接受参数、执行逻辑和返回结果。存储过程可以简化复杂的数据库操作，提高性能和安全性。

1. 参数

存储过程可以接受输入参数、输出参数和输入/输出参数。
参数可以是任意数据类型，如INT，VARCHAR，DATE等。

2. 语法

创建存储过程的语法如下：

CREATE PROCEDURE procedure_name(param1 datatype, param2 datatype)
   BEGIN
       -- 存储过程逻辑
   END;

3. 调用

调用存储过程的语法如下：

CALL procedure_name(value1, value2);

4. 代码示例

只带有输入参数

DELIMITER //
   CREATE PROCEDURE GetCustomerByID(IN customerID INT)
   BEGIN
       SELECT * FROM customers WHERE id = customerID;
   END //
   DELIMITER ;

在这里插入图片描述

带有输出参数并定义变量的存储过程

DELIMITER //

CREATE PROCEDURE CalculateOrderTotal(IN orderId INT, OUT total DECIMAL(10, 2))
BEGIN
    DECLARE subtotal DECIMAL(10, 2);
    DECLARE tax DECIMAL(10, 2);
    
    -- 计算订单小计
    SELECT SUM(price * quantity) INTO subtotal FROM order_items WHERE order_id = orderId;
    
    -- 计算税费（假设税率为10%）
    SET tax = subtotal * 0.1;
    
    -- 计算总价
    SET total = subtotal + tax;
    
    -- 返回总价
    SELECT total;
END //

DELIMITER ;

DECLARE用于声明变量
SET 用于设置变量的值

上面存储过程 CalculateOrderTotal 接受一个订单ID作为输入参数，计算订单的小计、税费和总价，并将总价作为输出参数返回。在存储过程中，首先计算订单小计，然后根据小计计算税费，最后计算总价并将其赋给输出参数 total ，输出参数的存储过程调用有些特别需要先定义再传入，调用方式如下：

SET @orderId = 123;
SET  @total = 0;
CALL CalculateOrderTotal(@orderId, @total);
SELECT @total AS OrderTotal;

在这里插入图片描述

带有游标的存储过程示例

DELIMITER //

CREATE PROCEDURE GetOrders()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE orderId INT;
    DECLARE orderDate DATE;
    
    DECLARE cur CURSOR FOR
        SELECT id, order_date FROM orders;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    
    SELECT 'Order ID', 'Order Date'; -- 返回表头
    
    read_loop: LOOP
        FETCH cur INTO orderId, orderDate;
        
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        
        SELECT orderId, orderDate; -- 返回每行数据
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

5. 存储过程使用注意事项

在存储过程中，应该避免使用过多的逻辑，以免影响性能。
确保存储过程的参数和返回结果都被正确处理。
注意存储过程的权限设置，确保只有授权用户可以执行存储过程。

二、函数

MySQL函数是一段可重复使用的SQL代码，接受参数并返回一个值,函数可以用于计算、转换数据等操作，可以在SQL语句中直接调用。

1. 参数定义

函数可以接受零个或多个参数，每个参数都有一个数据类型。
参数可以是IN参数（只读），OUT参数（只写），或INOUT参数（读写）。

2. 语句

函数内部可以包含SQL语句、流程控制语句、变量声明等。
函数可以返回一个值，可以是任意数据类型。

3. 关键字

创建函数时会使用 CREATE FUNCTION 语句。
函数内部可以使用 RETURN 关键字来返回值。

4. 返回值：

函数通过 RETURN 语句返回计算结果或值。
返回值可以是标量值（如整数、字符串等）或表格。

5.使用代码示例

只带输入参数

DELIMITER //

CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = a + b;
    RETURN sum;
END //

DELIMITER ;

带有OUT参数的函数代码

DELIMITER //

CREATE FUNCTION GetEmployeeName(IN employeeId INT, OUT employeeName VARCHAR(50))
RETURNS INT
BEGIN
    SELECT name INTO employeeName FROM employees WHERE id = employeeId;
    RETURN 1;
END //

DELIMITER ;

上面语句中函数 GetEmployeeName 接受员工ID作为输入参数，并将对应员工的姓名通过OUT参数 employeeName 返回。函数返回值为INT类型，之后可以调用这个函数并接收OUT参数的值。

返回值为表格的函数代码

DELIMITER //

CREATE FUNCTION GetEmployeesByDepartment(departmentId INT)
RETURNS TABLE
BEGIN
    RETURN (
        SELECT * FROM employees WHERE department_id = departmentId;
    );
END //

DELIMITER ;