Logic judgment and conditional control _mysql of Mysql stored procedure

For specific details, please see the following small set of knowledge points brought to you.

Similar to the writing program, the stored procedure also has a corresponding conditional judgment, the function is similar to if, switch. In MySQL it corresponds to if and case

1. If judgment

The format of the If judgment is this:

IF expression THEN commands 
  [ELSEIF expression THEN commands] 
  [ELSE commands] 
  

Here expression is our judgment expression; else IF and else are optional, and command is the one that executes when the condition is true (True 1,false 0). For example, we design a stored procedure to return the price of a commodity, where the price is judged by the incoming parameters, whether it is a tax or a price with no tax. First look at the table data:

And here's our stored procedure:

The stored procedure has two input parameters, the first istaxed the tax price, the second is the name of the product, two variables are defined in the stored procedure, Finalprice is used to save the price, and the taxrate represents the tax rate. Here the code is relatively simple, that is, if you want to increase the tax, the original price on the tax rate. Here is the test result:

The condition of TRUE indicates the price with tax. Okay, this is an example of using if, look at the case below;

2, the use of case

The switch in the same programming ... case ... Similarly, using case programming is as much an easy way to read and maintain as it is to be judged, so let's take a look at the syntax of cases:

Case Case_expression when when_expression THEN commands when when_expression THEN commands 
  ... 
  ELSE commands 

A, here you can see the case is like the switch in our programming, the following case_expression is like the expression following the switch;

b, then when it is similar to programming inside the case,when_expression similar to the value followed by the case, commands corresponds to the corresponding cases under the execution of the order;

C, the last ELSE is similar to default, which is the command to execute if none is in the case above.

Here we assume that the tax rates for different types of goods are not the same, for example, this assumes that the rate of dessert is 0.05, the milk product is 0.1, the furniture class is 0.2:, the following is the stored procedure:

A variable protype that holds the product type is added to hold the item type. Then use the case to judge to set the tax rate, the following is the part of the test:

can see the sofa price is 1250*1.2=1500, and the price of the cake is 10*1.05 = 10.5