User defined functions are incredibly useful in SQL (or any language for that matter). They give us a way to re-use code many times; across different scripts, by simply referencing the saved function.
Stored Procedure vs Function
A stored procedure can run INSERT, UPDATE and DELETE calls while a function cannot. This is because, a procedure does not have to return values as an output, whereas a function does – a function can only have SELECT statements.
Furthermore, a function can be used as part of your select statement (e.g. SELECT func(9) as cost); whereas a procedure cannot.
You may want to use a stored procedure to extract, update, delete and insert data in batch and use a function to run repeatable code to analyse that data – the result of the function can then be further utilized in your query.
Back to functions!
A simple example of a function in MySQL is as below. Lets walk through it line by line:
- First of all, we create a function. This function is called ‘hello’ and takes one input parameter ‘s’ which is of type CHAR.
- The output of this function is a CHAR (String) and it is deterministic (this suggests that there will be no replication issues and we should expect the same result when we run the query). For select queries this should usually be the case.
- We then return the result of a case statement as the final function output.
CREATE FUNCTION hello(s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN case when s = 'world' then 'yes' else 'no' end; SELECT hello('world');
Awesome! So let’s check a couple more example. In the below,
- I am adding a statement which checks if the function with that name already exists & if not, it deletes it.
- Next, we define the function called ‘ggg’ which has 2 input parameters ‘s’ which is an INT and ‘y’ which is an INT.
- The function will return an int & is again deterministic
- Finally, we return the result of s * y.
DROP FUNCTION IF EXISTS ggg; CREATE FUNCTION ggg (s int, y int) RETURNS int DETERMINISTIC RETURN s*y; SELECT ggg(1, 5);
Finally, we will run something a little bit different.
- Again, we will drop the function if it doesn’t exist
- It will be a function that takes a single parameter called ‘s’ which is an integer
- It will return an integer
- This time, we’re using the input parameter in the where statement. This means, we can call the function multiple times in the same query to produce several output fields.
DROP FUNCTION IF EXISTS ggg; CREATE FUNCTION ggg(s int) RETURNS int DETERMINISTIC RETURN (SELECT COUNT(1) from myfavoritetable where age = s); SELECT ggg(30) as thirty, ggg(10) as ten;
Code reusability should be something we bake into our processes whenever we can – it’s more efficient from a manpower & resource usage perspective and makes our queries more readable!