mysql stored procedure judgment

MySQL stored procedure judgment: use IF, CASE and conditional statements to make logical judgments

MySQL stored procedure is one of the important tools for business logic implementation in MySQL. It can batch process and convert data , calculation and analysis, etc. In MySQL stored procedures, we often need to make logical judgments on the data to decide how to perform subsequent operations. This article will introduce how to use IF, CASE and conditional statements to make logical judgments in MySQL stored procedures.

1. IF statement

The IF statement is the most commonly used conditional statement in MySQL stored procedures, which is used to perform different operations based on the true or false conditions. Its syntax format is as follows:

IF (condition) THEN
    statements;
ELSEIF (condition) THEN
    statements;
ELSE
    statements;
END IF;

Among them, condition represents the judgment statement, and statements represents the statement block that needs to be executed. In an IF statement, the IF block is executed when the condition is true, and the ELSEIF or ELSE block is executed when the condition is false. There can be multiple ELSEIF statement blocks, but they will only be executed when the IF statement block is false.

The following is an example to determine whether a student's score is passing through an IF statement:

CREATE PROCEDURE check_score(IN score INT)
BEGIN
    IF (score >= 60) THEN
        SELECT 'Pass';
    ELSEIF (score >= 50) THEN
        SELECT 'Makeup Exam';
    ELSE
        SELECT 'Fail';
    END IF;
END;

In the above example, the corresponding results are output based on the student's score. If the student's score fails, "Fail" is output; if the score is between 50 and 60 points, "Makeup Exam" is output; if the score is above 60 points, "Pass" is output.

2. CASE statement

The CASE statement is another commonly used conditional statement in MySQL stored procedures. It is also used to perform different operations according to different conditions. The difference is that the CASE statement supports more Complex conditional judgment. Its syntax format is as follows:

CASE expr
    WHEN value1 THEN
        statements1;
    WHEN value2 THEN
        statements2;
    ...
    ELSE
        statements;
END CASE;

Among them, expr represents an expression or column, used to match the value in the WHEN condition; value1, value2... represents the matching value; statements1, statements2... represents matching The operation to be performed on the value; the ELSE statement block represents the operation to be performed when there is no match. In a CASE statement, there can be multiple WHEN conditions.

The following is an example to determine the student's grade through the CASE statement:

CREATE PROCEDURE check_level(IN score INT)
BEGIN
    CASE 
        WHEN (score >= 90) THEN SELECT 'Excellent';
        WHEN (score >= 80) THEN SELECT 'Good';
        WHEN (score >= 70) THEN SELECT 'Fair';
        WHEN (score >= 60) THEN SELECT 'Pass';
        ELSE SELECT 'Fail';
    END CASE;
END;

In the above example, the corresponding grade is output based on the student's performance. If the student's score is above 90 points, "Excellent" is output; between 80 and 90 points, "Good" is output; between 70 and 80 points, "Fair" is output; between 60 and 70 points, Then output "Pass"; if the result is failed, output "Fail".

3. Conditional Statement

Conditional statement is another commonly used logical judgment tool in MySQL stored procedures. Its syntax format is as follows:

(condition1) AND/OR (condition2) 

Among them, condition1 and condition2 Represents the two judgment conditions of the conditional statement respectively. You can use AND or OR to connect different conditions. AND means that two conditions must be met, and OR means that at least one condition must be met.

The following is an example to determine whether a student is late through a conditional statement:

CREATE PROCEDURE check_lateness(IN late INT)
BEGIN
    IF (late = 0) OR (late <= 5) THEN
        SELECT 'On Time';
    ELSE
        SELECT 'Late';
    END IF;
END;

In the above example, if the student is not late (i.e. late=0), or is late for no more than 5 minutes , then output “On Time”; otherwise, output “Late”.

Summary

IF, CASE and conditional statements are commonly used logical judgment tools in MySQL stored procedures. They can perform different operations based on different conditions. In actual development work, we can choose different judgment statements based on specific business needs to improve the efficiency and readability of the code.

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