You already know how to deploy various in-built MySQL functions, such as sum(), avg() and concat(), to make querying easier. Now, it is possible to find the sum of two numbers in MySQL without using the sum() function. You can use the arithmetic addition operator (+) for this purpose. Similarly, you can use the arithmetic addition and division operators to find the average of two numbers.
The sum() and avg() functions are preferred because they are easier to use and also increase the readability of the code. Another important factor is reusability. You do not need to see the entire definition behind the sum() function every time you use it; all you need is the name sum() to invoke the function whenever you want to determine the sum of two numbers.
However, there are operations that you may want to repeat multiple times in a piece of code because they do not have an in-built function. This is where you must use user-defined functions (UDFs) or stored functions. In the upcoming video, Shreyas will explain the syntax of UDFs.
As you learnt in this video, the syntax for writing a UDF is as follows:
Remember the following points:
- The CREATE FUNCTION is also a DDL statement.
- The function body must contain one RETURN statement.
So, as you learnt in this video, a delimiter is a marker for the end of each command that you send to the MySQL command-line client. Whenever you are inside a UDF, you need to define another delimiter and reset it to the default ‘;‘ (semicolon) after the function ends.
There is another stored routine in SQL – a stored procedure. How does it differ from a UDF? You will find out in the next segment.