Technology news and views
RSS icon Email icon Home icon
  • Understanding MySQL Control Flow Functions

    Posted on May 21st, 2009 Sandeep 1 comment

    Sure, the documentation for control flow functions is available right here. But it’s not the easiest thing in the world to understand. The documentation, I mean – since the examples given there are very simple cases.

    Here’s a portion of it, explaining the CASE statement:

    CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

    CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

    The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSEĀ  part.

    mysql> SELECT CASE 1 WHEN 1 THEN 'one'
        ->     WHEN 2 THEN 'two' ELSE 'more' END;
            -> 'one'
    mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
            -> 'true'
    mysql> SELECT CASE BINARY 'B'
        ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
            -> NULL

    Great, but how do you use it in an actual scenario where you want to, let’s say, update a column based on its current contents?

    Let’s decipher the first version a little more. It “returns the result where value=compare_value.” It’s the equivalent of a switch/case statement in C or Java, for example. You test the “value” of a particular variable (or database column in this case) against different allowed values, and based on a match, return an appropriate “result.” The C/Java equivalent would be:

    int result = -1;
    switch(myInteger)
    {
        case 33:
            result = 0x002F;
            break;
        case 87:
            result = 0x008A;
            break;
        default:
            result = 0x00FF;
    }
    
    return result;

    Let’s say you want to update the status of an order during routine order processing. You want to set the expected date of delivery based on the shipping type chosen by a user of your online store. For simplicity, let the set of shipping types be: 1 = Overnight shipping, 2 = 2nd day delivery, 3 = Regular mail.

    Here’s how you can achieve that with the MySQL CASE statement:

    UPDATE order AS o, shopping_cart AS c
    SET o.expected_delivery = CASE c.shipping_type
                                WHEN 1 THEN ADDDATE( NOW(), INTERVAL 1 DAY )
                                WHEN 2 THEN ADDDATE( NOW(), INTERVAL 3 DAY )
                                WHEN 3 THEN ADDDATE( NOW(), INTERVAL 8 DAY )
                                ELSE NULL
                              END
    WHERE o.order_id = c.order_id;

    Thus, depending on the shipping type, an expected delivery date can be communicated to the customer. I’ll examine the other control flow functions later, if anyone needs it.

     

    1 responses to “Understanding MySQL Control Flow Functions” RSS icon


    Leave a reply