mysql stored procedure creation

MySQL stored procedure is a precompiled block of code that can be defined and saved in a MySQL database for future execution. They can accept input parameters and return results, and many complex operations can be written using stored procedures. Using stored procedures can improve database performance and reduce code duplication.

In this article, we will introduce how to use MySQL to create stored procedures, including the syntax of stored procedures, how to define input parameters and return results, and how to call stored procedures. Before starting, make sure you are connected to the MySQL database.

Create a basic stored procedure

MySQL's stored procedure uses DELIMITER to set the custom delimiter because the default delimiter is ";", which is the same as the end symbol of the stored procedure statement. We can create a basic stored procedure using the following syntax:

DELIMITER $$
CREATE PROCEDURE procedure_name()
BEGIN
    -- 存储过程代码
END $$
DELIMITER ;

We define the name of the stored procedure and use the BEGIN and END keywords to define the code block. In newer MySQL versions, it is also possible to define stored procedures using syntax other than BEGIN and END, as shown below:

CREATE PROCEDURE procedure_name()
    COMMENT '存储过程描述'
    LANGUAGE SQL
    [NOT] DETERMINISTIC
    [CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
    SQL SECURITY INVOKER
    COMMENT '存储过程描述'
BEGIN
    -- 存储过程代码
END;

In this syntax, you can optionally add additional attributes, such as SQL SECURITY, to Specify the security of stored procedures.

Now, let's create a simple stored procedure that accepts a parameter and returns the square of that parameter. Let's name it "square".

DELIMITER $$
CREATE PROCEDURE square(IN num INT)
BEGIN
    SELECT num * num;
END $$
DELIMITER ;

We use the IN keyword to define an input parameter. In the stored procedure, we simply multiply that parameter by itself and return the result using a SELECT statement.

Calling a stored procedure

To call a stored procedure, we can use the CALL statement, followed by the name and parameters of the stored procedure. Let's use the following syntax to call the "square" stored procedure we just created.

CALL square(5);

This will return 25. To better understand how a stored procedure works, we can compare it to a normal query.

SELECT 5 * 5;

This will also return 25. As can be seen, the same results are obtained using stored procedures and ordinary queries, but stored procedures have more advantages, as mentioned earlier, including fast queries and reusable code blocks.

Control flow statements

The stored procedure contains various control flow statements, including IF statements, CASE statements and loop statements. Here are some examples.

IF Statement

The IF statement determines when to execute a block of code based on conditions. Here is an example of a simple IF statement:

DELIMITER $$
CREATE PROCEDURE even_or_odd(IN num INT)
BEGIN
    IF num % 2 = 0 THEN
        SELECT 'even';
    ELSE
        SELECT 'odd';
    END IF;
END $$
DELIMITER ;

In this example, we have created a stored procedure that accepts a number and returns a string based on whether the number is even or odd. Returns "even" if the number is divisible by 2, otherwise returns "odd".

CASE statement

The CASE statement is similar to the IF statement, but can execute different blocks of code based on more conditions. Here is a simple CASE statement example:

DELIMITER $$
CREATE PROCEDURE grade(IN score INT)
BEGIN
    CASE 
        WHEN score >= 90 THEN SELECT 'A';
        WHEN score >= 80 THEN SELECT 'B';
        WHEN score >= 70 THEN SELECT 'C';
        WHEN score >= 60 THEN SELECT 'D';
        ELSE SELECT 'F';
    END CASE;
END $$
DELIMITER ;

In this example, we create a stored procedure that accepts a score and returns a letter grade based on that score. If the score is greater than or equal to 90, it will be assigned A grade, 80 points and 70 points will be assigned B grade and C grade respectively, 60 points and below will be assigned D grade, otherwise it will be assigned F grade.

WHILE Loop

WHILE loop is a type of loop that repeatedly executes a block of code. It is based on a condition that will continue to execute the code as long as the condition is met. Here is a simple WHILE loop example:

DELIMITER $$
CREATE PROCEDURE count_down(IN num INT)
BEGIN
    WHILE num > 0 DO
        SELECT num;
        SET num = num - 1;
    END WHILE;
END $$
DELIMITER ;

In this example, we have created a stored procedure that accepts a number and counts down from that number and outputs each number to the result set . To count down, we use a WHILE loop and as long as the number is greater than 0, we output it and decrement the value of num.

Conclusion

MySQL stored procedures are a powerful tool that can significantly improve the performance of your database and simplify your code. This article explains how to create a stored procedure using MySQL and how to define input parameters and return results, and provides examples of various control flow statements. Although stored procedures are powerful, it is important to pay attention to security to ensure that access is properly controlled and data is protected.

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