What is mysql string stored procedure

1. Define string stored procedures

In MySQL, you can use the DELIMITER command to specify a delimiter different from ";" to use multiple SQLs in the stored procedure statement. For example, a stored procedure named my_proc is defined in the following example, which uses the delimiter "//":

DELIMITER //
CREATE PROCEDURE my_proc()
BEGIN
    -- 定义字符串变量
    DECLARE my_str VARCHAR(50) DEFAULT 'Hello, world!';

    -- 输出字符串变量
    SELECT my_str;

    -- 修改字符串变量的值
    SET my_str = CONCAT(my_str, ' MySQL!');

    -- 再次输出字符串变量
    SELECT my_str;
END //
DELIMITER ;

In the above example, first use the DELIMITER command to set the delimiter to "/ /", and then defines a string variable named my_str with an initial value of "Hello, world!". Next, the value of the my_str variable was output in the stored procedure, and then the SET command was used to modify its value. When the value of the my_str variable was output, it was found that it had changed to "Hello, world! MySQL!".

2. Pass parameters to the string stored procedure

Usually, in actual applications, you need to pass parameters to the stored procedure in order to perform different operations based on these parameters. A stored procedure named my_proc is defined in the following example, which accepts two parameters (x and y), calculates their sum and returns the result:

DELIMITER //
CREATE PROCEDURE my_proc(IN x INT, IN y INT, OUT result INT)
BEGIN
    SET result = x + y;
END //
DELIMITER ;

In the above example, x is defined using the IN keyword and y are input parameters, and use the OUT keyword to define result as the output parameter (that is, the stored procedure will return the value of result). The body of the stored procedure simply contains a computed SQL statement that adds the values ​​of x and y and stores the result in the result variable.

Next, you can use the CALL command to call the my_proc stored procedure and pass two parameters, for example:

SET @x = 10;
SET @y = 20;
CALL my_proc(@x, @y, @result);
SELECT @result;

In the above example, the two variables @x and @y are first defined, And assign values ​​​​to 10 and 20; then use the CALL command to call the my_proc stored procedure, and pass @x and @y as input parameters; finally, the output result @result of the stored procedure is output to the console.

3. Use IF statements and loop structures

More complex logic can be achieved by applying IF statements and loop structures in stored procedures. For example, the following example defines a stored procedure named my_proc, which accepts a parameter n, calculates the nth number of the Fibonacci sequence and returns the result:

DELIMITER //
CREATE PROCEDURE my_proc(IN n INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE a INT DEFAULT 1;
    DECLARE b INT DEFAULT 1;

    IF n <= 0 THEN
        SET result = 0;
    ELSEIF n = 1 OR n = 2 THEN
        SET result = 1;
    ELSE
        WHILE i < n DO
            SET result = a + b;
            SET a = b;
            SET b = result;
            SET i = i + 1;
        END WHILE;
    END IF;
END //
DELIMITER ;

In the above example, use IF statement for judgment: if n is less than or equal to 0, return 0 directly; if n is equal to 1 or 2, return 1; otherwise, use the WHILE loop to calculate the nth number of the Fibonacci sequence.

You can use the CALL command to call the my_proc stored procedure and pass a parameter, for example:

SET @n = 10;
CALL my_proc(@n, @result);
SELECT @result;

In the above example, assign the parameter @n to 10, call the my_proc stored procedure, and The output @result is output to the console. The final output should be 55 because the tenth number in the Fibonacci sequence is 55.

The above is the detailed content of What is mysql string stored procedure. For more information, please follow other related articles on the PHP Chinese website!