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

MySQL provides an alternative conditional statement CASE. MySQL CASE statements make the code more readable and efficient.

There are two forms of CASE statements: simple search CASE statements. Let's talk about the usage of CASE statements in MySQL stored procedures.

1Perfect case grammatical structure

CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands... ELSE commands END CASE;2,CASE application instance 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 = p_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$$

If the when_expression in the WHEN clause matches the value of case_expression, the command in the ELSE clause is executed. The ELSE clause is optional. If the ELSE clause is omitted and no match is found, MySQL throws an error.

The article is from http://www.dc3688.com/.

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.