Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use if in mysql stored procedures, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

In mysql, the if statement is used to execute a set of SQL statements based on a condition or value result of an expression with the syntax "IF expression THEN statements;END IF;"; when the expression evaluates to TRUE, the statements statement is executed.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

The MySQL IF statement allows you to execute a set of SQL statements based on a condition or value result of an expression. To form an expression in MySQL, you can combine words, variables, operators, and even functions. The expression can return either TRUE,FALSE or NULL, one of these three values.

MySQL IF statement syntax

The syntax of the IF statement is explained below:

IF expression THEN statements;END IF

If the expression (expression) evaluates to TRUE, the statements statement is executed, otherwise the control flow is passed to the next statement after END IF.

The following flowchart shows the execution of the IF statement:

MySQL IF ELSE statement

If the statement is executed when the expression evaluates to FALSE, use the IF ELSE statement, as follows:

IF expression THEN statements;ELSE else-statements;END IF

The following flowchart illustrates the execution of the IF ELSE statement:

MySQL IF ELSEIF ELSE statement

If you want to conditionally execute a statement based on multiple expressions, use the IF ELSEIF ELSE statement as follows:

IF expression THEN statements;ELSEIF elseif-expression THEN elseif-statements;...ELSE else-statements;END IF

If the expression (expression) evaluates to TRUE, the statement (statements) in the IF branch executes; if the expression evaluates to FALSE, the elseif-expression is executed if the elseif_expression evaluates to TRUE,MySQL, otherwise the else-statements statement in the ELSE branch is executed. The specific process is as follows

Example of MySQL IF statement

The following example shows how to use the IF ESLEIF ELSE statement, where the GetCustomerLevel () stored procedure accepts two parameters, the customer number and the customer level.

First, it gets the credit line from the customers table.

Then, based on the credit line, it determines the customer level: PLATINUM, GOLD, and SILVER.

The parameter p_customerlevel stores the level of the customer and is used by the caller.

USE yiibaidb;DELIMITER $$CREATE PROCEDURE GetCustomerLevel (in p_customerNumber int (11), out p_customerLevel varchar (10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = pendant customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim = 10000) THEN SET p_customerLevel =' GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF;END$$

The following flowchart demonstrates the logic of determining the customer level

Thank you for reading this article carefully. I hope the article "how to use if in mysql stored procedures" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.