Shulou(Shulou.com)06/01 Report--

This paper illustrates the usage of if statements in mysql stored procedures. Share with you for your reference, the details are as follows:

The IF statement in mysql allows us to execute a set of SQL statements based on a condition or value result of an expression, so we want to form an expression in MySQL that can be combined with text, variables, operators, and even functions. The expression can return either TRUE,FALSE or NULL, one of these three values. Take a look at the grammatical structure:

IF expression THEN statements;END IF

If the above expression (expression) evaluates to TRUE, the statements statement is executed, otherwise the control flow is passed to the next statement after END IF. Let's take a look at the execution of the IF statement:

Let's take a look at the syntax structure of the IF ELSE statement:

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

When you're done, let's take a look at the execution of the IF ELSE statement:

If we want to conditionally execute the statement based on multiple expressions, we use the IF ELSEIF ELSE statement, which has the following syntax structure:

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 and elseif_expression evaluates to TRUE,mysql, the elseif-expression is executed, otherwise the else-statements statement in the ELSE branch is executed. Let's take a look at the specific implementation process:

We next use the IF ESLEIF ELSE statement and the GetCustomerLevel () stored procedure to accept the customer number and customer-level parameters. First of all, the GetCustomerLevel () stored procedure has to get the credit line from the customers table, and then, according to 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. Let's look at the specific sql:

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 flow chart of the logic for determining the customer level is as follows:

All right, that's all for this sharing.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL stored procedure skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary" and "MySQL Database Lock related skills Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.