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

Today, I will talk to you about how to use case statements in mysql stored procedures, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents. I hope you can get something according to this article.

In addition to the if statement, mysql provides an alternative conditional statement CASE. Mysql's CASE statements make the code more readable and efficient. Needless to say, let's first look at the syntax of a simple case statement:

CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands... ELSE commandsEND CASE

We can use a simple CASE statement to check that the value of the expression matches a set of unique values, and in the above sql, case_expression can be any valid expression. We compare the value of case_expression with the when_expression in each WHEN clause, such as when_expression_1,when_expression_2, and so on. If the values of case_expression and when_expression_n are equal, execute the command (commands) in the corresponding WHEN branch. If the when_expression in the WHEN clause matches the value of case_expression, the command in the ELSE clause is executed. Where the ELSE clause is optional, and if we omit the ELSE clause and cannot find a match, mysql will throw an error. Let's look at an example of using a simple CASE statement:

DELIMITER $$CREATE PROCEDURE GetCustomerShipping (in p_customerNumber int (11), out p_shiping varchar (50)) BEGIN DECLARE customerCountry varchar (50); SELECT country INTO customerCountry FROM customers WHERE customerNumber = pendant customerNumber; CASE customerCountry WHEN 'USA' THEN SET p_shiping =' 2-day Shipping'; WHEN 'Canada' THEN SET p_shiping =' 3-day Shipping'; ELSE SET p_shiping = '5-day Shipping'; END CASE;END$$

The above sql works as follows:

The GetCustomerShipping stored procedure takes the customer number as the IN parameter and returns the shipping time based on the customer's country.

In the stored procedure, first of all, we get the customer's country according to the customer number entered. Then use a simple CASE statement to compare the customer's country to determine the shipping date. If the customer is located in the United States (USA), the shipping time is 2 days. If the customer is in Canada, the shipping time is 3 days. Customers from other countries need 5 days of shipping time.

Let's take a look at the flow chart of the logic that determines the time of shipment:

The following is a test script for the above stored procedure:

SET @ customerNo = 112 position select country into @ countryFROM customersWHERE customernumber = @ customerNo;CALL GetCustomerShipping (@ customerNo,@shipping); SELECT @ customerNo AS Customer, @ country AS Country, @ shipping AS Shipping

Execute the above code to get the following results:

+-+

| | Customer | Country | Shipping | |

+-+

| | 112 | USA | 2-day Shipping |

+-+

1 row in set

A simple CASE statement only allows us to match the value of an expression to a different set of values. To perform more complex matches, such as ranges, we can use searchable CASE statements. A searchable CASE statement is equivalent to an IF statement, but its construction is more readable, so take a look at its syntax structure:

CASE WHEN condition_1 THEN commands WHEN condition_2 THEN commands... ELSE commandsEND CASE

The above sql first evaluates each condition in the evaluation WHEN clause until a condition with a value of TRUE is found, and then executes the corresponding command (commands) in the THEN clause. If none of the conditions is TRUE, the command (commands) in the ELSE clause is executed. If you do not specify the ELSE clause, and none of the conditions is TRUE,mysql, an error message will be issued. Mysql does not allow empty commands in the THEN or ELSE clause. If we don't want to deal with the logic in the ELSE clause while preventing mysql from throwing errors, we can place an empty BEGIN END block in the ELSE clause. Let's take a look at the case where we use the searchable CASE statement to find the customer level: SILVER,GOLD or PLATINUM based on the customer's credit line:

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; CASE WHEN creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; WHEN (creditlim = 10000) THEN SET p_customerLevel =' GOLD'; WHEN creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END CASE;END$$

In the above query logic, if the credit line is:

If it is greater than 50K, the customer is a PLATINUM customer.

If it is less than 50K and greater than 10K, then the customer is a GOLD customer.

Less than 10K, then the customer is the SILVER customer.

We can test the stored procedure by executing the following test script:

CALL GetCustomerLevel (112 minutes level); SELECT @ level AS 'CustomerLevel'

Execute the above query statement and get the following results:

+-+

| | Customer Level |

+-+

| | PLATINUM |

+-+

1 row in set

After reading the above, do you have any further understanding of how to use case statements in mysql stored procedures? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.