MySQL User Defined Function Examples

I'm currently in the process of converting one of my web applications from static tables all generated on the server side to a YUI based implementation which uses a lot of JavaScript calls to get the data on the client side to display. This has some distinct advantages but also some disadvantages.

For instance, where I used to use this PHP function to convert a file size (in bytes) to a string that would display nicely:

/**


                     * Format Bytes to a human readable format


                     * @param int $BYTES Size in bytes


                     * @param string $SIZE Format to use (k,m,g,t,best)


                     * @param int $DECIMALS Format string to use in


                     * @return string Formated string


                     * @todo Support k-t size


                     * @deprecated Use the MySQL UDF to do this in database

*/


           function byteFormat($BYTES, $SIZE="BEST", $DECIMALS=2){


                                    if(substr(strtolower($SIZE),0,1) == "b"){


                                        if($BYTES > pow(1024,4)){


                                            $SIZE = "t";


                                        } elseif($BYTES > pow(1024,3)){


                                            $SIZE = "g";


                                        } elseif($BYTES > pow(1024,2)){


                                            $SIZE = "m";


                                        } else {


                                            $SIZE = "k";


                                    switch(substr(strtolower($SIZE),0,1)){


                                        case "k":


                                            $FORMATED_DATA = number_format(($BYTES / pow(1024,1)),$DECIMALS) . "kb";


                                            break;


                                        case "m":


                                            $FORMATED_DATA = number_format(($BYTES / pow(1024,2)),$DECIMALS) . "mb";


                                            break;


                                        case "g":


                                            $FORMATED_DATA = number_format(($BYTES / pow(1024,3)),$DECIMALS) . "gb";


                                            break;


                                        case "t":


                                            $FORMATED_DATA = number_format(($BYTES / pow(1024,4)),$DECIMALS) . "tb";


                                            break;


                                    return $FORMATED_DATA;

I need to now use a SQL function to do the processing. (I could still use PHP, but that would require processing the data more than I want to). I've been doing a lot of work in PL/SQL lately so I decided to write a MySQL UDF to accomplish this instead of a huge case statement. While there are a lot of examples for PL/SQL functions, I had a hard time finding a good example of what a MySQL UDF looked like so I thought I would post this in case anyone else is going through what I did.


DROP FUNCTION IF EXISTS byteFormat;


CREATE FUNCTION byteFormat (bytes INTEGER


                                       ,desiredSize VARCHAR(30)


                                       ,decimalPlaces INTEGER)


RETURNS VARCHAR(255)


BEGIN


    DECLARE formatedString VARCHAR(250) DEFAULT '';


    IF LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'b' THEN


                                   SET desiredSize = CASE


                                                         WHEN (bytes > POWER(1024,4)) THEN 't'


                                                         WHEN (bytes > POWER(1024,3)) THEN 'g'


                                                         WHEN (bytes > POWER(1024,2)) THEN 'm'


                                                         ELSE 'k'


                                                     END;


    END IF;


           SET formatedString = CASE


                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'k' THEN CONCAT((FORMAT(bytes / POWER(1024,1),2)) , 'kb')


                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'm' THEN CONCAT((FORMAT(bytes / POWER(1024,2),2)) , 'mb')


                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'g' THEN CONCAT((FORMAT(bytes / POWER(1024,3),2)) , 'gb')


                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 't' THEN CONCAT((FORMAT(bytes / POWER(1024,4),2)) , 'tb')


           END;


    RETURN formatedString;


END;

BTW, in the MySQL documentation it shows the DECLARE statement but doesn't emphasize enough that the DEFAULT is mandatory. It took me a while to figure that out.

uggs outlet @ Dec 29, 2011

Nike Heels @ Dec 29, 2011

the u.s. food and drug administration says a california man who has fathered 14 children through sperm donation is breaking the law, and its trying to stop him from making Jordan High Heels further donations, kpix reported.the agency is classifying trent arsenault, of fremont, calif., as a “manufacturer of human cells,” since he has fathered 14 children.the fda is taking issue with arsenault because he does not donate through regulated sperm banks or clinics; instead, he does it through his own website, trentdonor.org.couples looking to conceive with donated sperm can sift through his medical records, which include his childhood immunizations and sexually transmitted disease tests online.“it is helping people in need,” said arsenault, Nike Dunk Heels 36, who has Nike Dunk High Heels been making donations since 2004. “i don’t make any Jordan Heels money, i don’t charge people anything. and it’s just helping childless couples have children.”

however, the fda believes sperm donation should be regulated by the government. the legal dispute has gone on for longer than a year.according to a letter from the fda, his “firm or establishment located in fremont recovers and distributes semen, and therefore is a manufacturer of human cells.”arsenault, who works Nike Heels as a computer security expert in silicon valley, said he doesn’t understand why the fda is getting involved - and he fears the case will interfere with his job.?\"but i think my employer would be supportive,\" he told foxnews.com.he said he “enters into mutually desired partnership with childless couples,” and he doesn’t feel it’s necessary to submit to the same standards as sperm Nike Dunk Low Heels banks.\"the semen is provided fresh (unfrozen) and is transferred immediately to the Nike High Heels recipient,\" said arsenault, who is single. \"health of the mother and child is my utmost concern.\"arsenault said he has retained the services of non-profit law firm cause of action, which \"fights for Nike Heels For Women taxpayers against government over-regulation,\" and will represent him at the fda hearing.?

Womens North Face Jackets @ Dec 28, 2011

Justice and Homeland safety North Face Jackets On Sale Correspondent Bob Orr: \\\\\\\"I think possibly to the earliest time cyber safety catches terrorism -- possibly even passes terrorism -- as our best domestic safety concern. And I think we\\\\\\\'re about because of the earliest precise cyber-attack over a product of crucial infrastructure, so we may enjoy for that.\\\\\\\"Senior business Correspondent Anthony Mason: \\\\\\\"I think the overall economy struggles along. I think Womens North Face Jackets we get merely a tiny enlarger using the joblessness level but not much, maybe, as I said, eight as well as a half percent. careers is nonetheless the top lingering problems using the economy.\\\\\\\"

National safety Correspondent David Martin: \\\\\\\"If Iran doesn\\\\\\\'t blink Israel will strike... Go shortly after its nuclear facilities, if Iran doesn\\\\\\\'t blink... [The U.S. will] have no option but to help Israel. And based on what the proof is at that time they may even desire to think about element in that raid, it is determined by what type of intelligence they have about that choice that Iran is required to create about no make any difference whether to genuinely create a bomb and go previous placing each of the pieces in place, that is The North Face Jackets what it\\\\\\\'s executing now.\\\\\\\"Congressional Correspondent Nancy Cordes: \\\\\\\"I predict Republicans will obtain seats using the Senate, possibly even consider deal with using the Senate. They will lose some seats using Mens North Face Jackets the home but they\\\\\\\'ll preserve their majority. And I think every day time of reckoning is coming in between home Speaker John Boehner and his amount two dude Eric Cantor to the reason which they experienced been on opposites sides of almost each and every and every key battle this year. The much less charitable enjoy by Boehner\\\\\\\'s allies will most likely be the actuality that Cantor keeps slicing him away in the knees and which has place Republicans in some instead sticky positions this year.\\\\\\\"

Chief light home Correspondent Norah O\\\\\\\'Donnell: \\\\\\\"I\\\\\\\'m hearing that there experienced been lots of genuinely secretive talks on the way on at the rear of the scenes inside the Gang of 6 on deficit reduction. They hold on to encounter regularly. I\\\\\\\'m informed they are instead near to some offer that\\\\\\\'s 4 to 5 trillion dollars. And which they are working to acquire new hint away on that. And I think that\\\\\\\'s on the way for The North Face Sale getting element inside the debate once more subsequent yr is genuinely a considerable deficit decrease tactic that also consists of key taxes reform.\\\\\\\"Political Director John Dickerson: \\\\\\\"I believe that we\\\\\\\'re truly are on the Womens North Face Denali Fleece Jackets way to decide concerning the very Committee that failed to create its $1.2 trillion in cuts, those people automated cuts are on the way to occur January 1, 2013 .

Christian Red Sole @ Dec 23, 2011

If you treasure that NBA video games will even corresponding to a Christian Louboutin Flats shoes number of the players feet donning sneakers, you need to understand of which right now that NBA League there are several online players are going to decide some sort of label for that Surroundings Potential 24-7 Adult males because their boots 鈥?certainly, that will even now transparent bottoms emphasize this shoes and boots get individuality, in addition to incomparable padding performance will be the casino shoe tend to make most Christian Louboutin Peep Toe individuals Christian Louboutin Boots are keen with the cause.

From your layout perspective, Nike Air flow Utmost 180 Basketball is actually presented by way of uncomplicated design Christian Louboutin Sandals design, yet a few a little uncommon details design and style is actually from another perspective proof: This is not an effective shoes or boots. For a start, Nike Air conditioning Utmost 180 Golf ball functions near higher to help design, which will increases the air conditioning size for your footwear, specifically crucial; second, the Nike Air flow Christian Louboutin Shoes Utmost 180 Basketball within tongue Section additional stretchy style, in order to your similar increase around shoes and boots get their unique preset as well Christian Louboutin Boots as encapsulated; last but not least, Air flow Utmost 24-7 Ladies on the ankle, using added safeguard layout, and also in that way to solve the actual ankle, and provide probably the most trustworthy safeguard.

By employing transparent ebony nylon uppers level connected with assisting substance as well New Christian Louboutin as distributed from a well lit azure vamp Flywire, this particular set of minimalist Air conditioning Utmost NINETY Flywire gentle because Tinker Harper Feder within 1990 production shoes darkness, needless to say, this nevertheless holds the sports footwear everlasting vintage design. 鈥?You'll find also been aimed at, 鈥?said Jesse, 鈥?to the primary Air conditioning Potential Christian Louboutin Pumps 92 % OF Women鈥檚 vintage design continued to be very high respect, so it can be your comfortable in addition to much loved products. 鈥?

lsjcma @ Dec 22, 2011

ralph lauren outlet online @ Dec 21, 2011

les @ Dec 19, 2011

north face outlet store @ Dec 16, 2011

Ugg Boots Australia @ Dec 10, 2011

The Cheap UGG Boots Sale could be a legend in its own right. The stylish, yet comfortable shoes or boots are made of authentic cheap ugg boots. Even though cannot think theyre the best looking UGG Boots Sale around, none of us, who slips their feet inside the Classic Tall UGG, can argue relating to amazing fit and luxury. Youve seen them worn on tv as a result of stars, all of this was, everywhere you look around your city, youre guaranteed to watch the Cardy Boots UGG sported by kids in various age groups, together with the expectations adults. A pair of Mini Boots UGG are formulated for someone to wear barefoot, if you happen to please. Bailey Button Boots UGG or Ultra Tall UGG are made out of part sheepskin and part rubber.

Unquestionably designed for durability, dependability but for the coldest of temperatures. These Cheap Uggs Boots on Sale takes folks via roughest of terrains and make the feet warm and dry. The Ugg Classic Short are a good winter and Uggs Boots Cheap. They could be sewn with a great sheepskin lining. Considering Uggs Bailey Button Boots inside such demand, sometimes websites and stores depletes them quickly. If internet shopping make sure the Sale Uggs Boots footwear information and facts are current you arent purchasing factor that isnt in store anymore. The beauty of the Cheap Uggs Boots kind shoes are not came up with Uggs Boots sale, they also have UGG clogs, shoes and slippers.

A lot happens to be a vogue aware area. In these days customers are not simply and google from gown and Ugg Boots Australia it can easily defend their physical body and ft from exterior accidents and mud but can make them search fashionable and attractive. Black ugg for cheap is definitely the most commonly encountered forms of shoe choose in the current market. Both males and females alike treasure black ugg outlet. Due to the fact purely the trend in todays style aware earth and now have are a must-have inside wardrobe of every single gentleman and female. A beautiful personal is sure stock few UGG Bailey Button Boots in his/her closet. You might find plenty of models in UGG Classic Short Boots attainable in black from each persons. The famous UGG Classic Tall Boots that exist in black are UGG Classic Mini Boots, UGG Cardy Boots and lots of further.

AdamStout^.com