WHEN (sale_value >200 AND customer_status='SILVER') THEN WHEN (sale_value >200 AND customer_status='GOLD') THEN WHEN (sale_value >200 AND customer_status='PLATINUM') THEN So, we should probably adapt the previousĬlause that applies a zero discount to a customer who meets none We could create an exception handler to cause this error toĦ), but it is probably better practice to code an ELSE clause to ensure that all possibleĬonditions are handled. This means that in Example 4-15 if the customer_status was not one of ' PLATINUM', ' GOLD', ' SILVER', or ' BRONZE' then the following runtimeĮxception would occur: ERROR 1339 (20000): Case not found for CASE statement However, it is critical to realize that a CASE statement will raise an exception Statements and you can specify an ELSE clause that executes if none of the It might be better to use a nested IF structure that makes it clear thatĮveryone gets free shipping for orders over $200, and thatĭiscounts are then applied based on the customer loyalty status The readability of our logic-as well as imposing aĢ2). In this case, the constant repetition of the sale_value condition and the free_shipping call actually undermines IF (sale_value >200 and customer_status='PLATINUM') THENĬALL free_shipping(sale_id) /* Free shipping*/ĬALL apply_discount(sale_id,20) /* 20% discount */ĮLSEIF (sale_value >200 and customer_status='GOLD') THENĬALL apply_discount(sale_id,15) /* 15% discount */ĮLSEIF (sale_value >200 and customer_status='SILVER') THENĬALL apply_discount(sale_id,10) /* 10% discount */ĮLSEIF (sale_value >200 and customer_status='BRONZE') THENĬALL apply_discount(sale_id,5) /* 5% discount*/ The logic in a single IF-ELSEIF block might look like that Shipping, along with a variable discount based on the customer’s So let’s say ourīusiness rules state that for orders over $200 we give free Individually test for all the other conditions. Than $200, then it might make sense to perform the sale_value test once, and then There are a lot of additional evaluations that we need to conduct Generally we want to avoid nesting IF statements where possible, but if IF (sale_value >200 and customer_status='PREFERED') THENīoth of the alternatives shown in Example 4-12 are perfectly Statements, provide usage examples, and, finally, compare the pros and The following subsections describe the syntax of both Type of statement is more readable or efficient than the other. However, there are circumstances in which one Usually,Ĭhoosing between IF and CASE is a matter of personal preference or There is always a way to rewrite an IF statement as a CASE statement or vice versa. IF and CASE perform very similar functions, and The MySQL stored program language supports two conditional (such as the day of the week or the time of the day). The stored program, to data in the database, or to other variable data The value of such an expression, which could refer to parameters to ConditionalĬontrol statements allow you to take different actions depending on Variables, operators, and functions that returns a value. Said earlier, an expression can be any combination of MySQL literals, Conditional control-or “flow of control”-statementsĪllow you to execute code based on the value of some expression.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |