Continuing with our series on Stored Procedures and Functions (see part 1 , part 2 , or part 3 ), this month we focus on Stored Functions. Most of what we have covered in those earlier tutorials is relevant here, so I suggest you read those first if you haven't already.
If procedural programming is new to you, you may be wondering what the difference is between a Stored Procedure and a Stored Function. Not too much really. A function always returns a result, and can be called inside an SQL statement just like ordinary SQL functions. A function parameter is the equivalent of the IN procedure parameter, as functions use the RETURN keyword to determine what is passed back. Stored functions also have slightly more limitations in what SQL statements they can run than stored procedures.
Stored functions in early versions of MySQL 5.0 ( 5.0.10) could not reference tables except in a very limited capacity. That limited their usefulness to a large degree. Newer versions can now do so, but still cannot make use of statements that return a result set. So, no SELECT queries returning result sets from a table. However, you can get around this by using SELECT INTO. For the next example, we create a table allowing us to store 4 marks, and a name. Then we will define a new WEIGHTED_AVERAGE function to make use of the dynamic data from the table.
By SELECTING the contents of the mark1 to mark4 rows INTO the variables we have just declared, there is no need to return a result set, and we can happily use the results inside of the function.
All the usual behaviors and conditions apply inside the function. Here is what happens if one of the records is missing a field.
JAVA - How To Make A Calculator In Java Using NetBeans [ with source code ] Part:3 - Dauer: 21:40
Generate Codes for your favorite sites ! - Dauer: 2:17