MySQL调用存储过程|极客笔记

MySQL调用存储过程

MySQL存储过程是一组预编译的SQL语句，它们可被多次调用。存储过程类似于函数，但存储过程可以包含多个SQL语句并可以接受参数。在一些需要重复执行相同SQL语句的场景下，存储过程可以提高数据库性能和减少重复代码。本文将详细介绍如何在MySQL中创建和调用存储过程。

创建存储过程

在MySQL中，可以使用CREATE PROCEDURE语句来创建存储过程。下面是一个示例存储过程，它接受一个参数，并返回该参数的平方值：

DELIMITER //

CREATE PROCEDURE calculate_square (IN num INT, OUT result INT)
BEGIN
    SET result = num * num;
END //

DELIMITER ;

在上面的示例中，我们创建了一个名为calculate_square的存储过程，它接受一个整数参数num，并将计算结果存储在result参数中。参数的方向可以是IN（输入）、OUT（输出）或INOUT（既输入又输出）。

调用存储过程

调用存储过程可以使用CALL语句，如下所示：

CALL calculate_square(5, @square);
SELECT @square;

在上面的示例中，我们调用了calculate_square存储过程，并传入了参数5。存储过程将计算结果存储在名为@square的变量中，并通过SELECT语句将其打印出来。

参数类型

存储过程可以接受不同类型的参数，包括整数、浮点数、字符串等。以下是一个示例，展示了如何定义不同类型的参数：

CREATE PROCEDURE example_procedure (IN int_param INT, IN float_param FLOAT, IN string_param VARCHAR(255))
BEGIN
    -- 逻辑代码
END;

上面的示例中，我们定义了一个存储过程example_procedure，它接受一个整数参数int_param、一个浮点数参数float_param和一个字符串参数string_param。

控制流语句

存储过程支持各种控制流语句，如IF、ELSEIF、ELSE、WHILE、LOOP等，使其具有更灵活的逻辑处理能力。以下是一个示例，展示了如何在存储过程中使用IF语句：

CREATE PROCEDURE example_procedure (IN num INT)
BEGIN
    DECLARE result VARCHAR(255);

    IF num > 0 THEN
        SET result = 'Positive';
    ELSEIF num < 0 THEN
        SET result = 'Negative';
    ELSE
        SET result = 'Zero';
    END IF;

    SELECT result;
END;

在上面的示例中，我们定义了一个存储过程example_procedure，它接受一个整数参数num，并根据该参数的值设置一个字符串变量result，最后将result打印出来。

错误处理

在存储过程中，可以通过DECLARE CONTINUE HANDLER FOR SQLSTATE 'XXXXX'语句来捕获特定错误并进行处理。以下是一个示例，展示了如何在存储过程中捕获并处理错误：

CREATE PROCEDURE example_procedure (IN num INT)
BEGIN
    DECLARE continue_handler INT DEFAULT 1;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET continue_handler = 0;

    IF num < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Number must be non-negative';
    END IF;

    IF continue_handler = 0 THEN
        SELECT 'An error occurred';
    ELSE
        SELECT 'No error';
    END IF;
END;

在上面的示例中，我们定义了一个存储过程example_procedure，它接受一个整数参数num，如果num小于0，则会抛出一个自定义错误，否则会打印出'No error'。通过DECLARE CONTINUE HANDLER FOR SQLEXCEPTION语句，我们定义了一个处理器，用于捕获SQL异常并设置continue_handler变量。

示例

下面我们将展示一个完整的存储过程示例，它接受一个整数数组作为参数，并返回数组中所有元素的平均值：

DELIMITER //

CREATE PROCEDURE calculate_avg (IN numbers TEXT, OUT result FLOAT)
BEGIN
    DECLARE sum FLOAT DEFAULT 0;
    DECLARE count INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE current_num FLOAT;

    WHILE i <= LENGTH(numbers) DO
        IF SUBSTRING(numbers, i, 1) <> ',' THEN
            SET current_num = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers, ',', i), ',', -1) AS FLOAT);
            SET sum = sum + current_num;
            SET count = count + 1;
        END IF;
        SET i = i + 1;
    END WHILE;

    SET result = sum / count;
END //

DELIMITER ;

在上面的示例中，我们创建了一个名为calculate_avg的存储过程，它接受一个用逗号分隔的整数数组作为参数，并将所有元素的平均值存储在result参数中。

接下来，我们将调用这个存储过程，并传入参数'1,2,3,4,5'，然后打印出平均值：

CALL calculate_avg('1,2,3,4,5', @avg);
SELECT @avg;

运行上述代码后，将得到如下输出：

+--------+
| @avg   |
+--------+
| 3.0    |
+--------+

以上就是关于MySQL存储过程的详细介绍和示例。存储过程是用于在数据库中执行一系列SQL语句并返回结果的强大工具，可以提高数据库性能和代码复用性。

MySQL调用存储过程