SQL: Stored Routines
In previous posts, I have described where certain options could be taken to help increase the efficiency of making queries in SQL. In this post, I will go over one the most helpful tools to increase efficiency: Stored Routines.
What are Stored Routines?
Stored Routines are prepared pieces of SQL code that are saved to a specific database server. By storing this code, it can be called by the user over and over again without having to continuously rewrite the entire code, which is why they are so useful in writing more efficient code. If, as an employee, you are required to go over the results of a certain query often, it is faster to write a stored procedure and call it when needed instead of writing out the entire code each time.
There are two types of stored routines: procedures and functions. Procedures are, as described in stored routines, saved pieces of SQL code that can be called over and over again. Functions are user defined functions that are also saved to a specific database server. They are different from the built-in functions that are already programmed into SQL like the aggregate function.
These two might sound the same, however there are slight differences within the syntax for each that can make one type of routine better to use than another. Procedures allow for a wide range of coding and are easier to write, while functions are more rigid, but the returned variables are easier to manipulate than the output from stored procedures.
Procedure Syntax
DELIMITER $$CREATE PROCEDURE procedure_name (possible parameters)
BEGIN
SQL Code
END$$DELIMITER ;
Let’s go through each step in the syntax.
The first step you see is a DELIMITER statement. The DELIMITER statement changes which symbol is used to end a SQL statement. However, when writing a procedure, you might be writing multiple lines of SQL statements. By changing the symbol, it allows the procedure to complete each statement within. Without it, there would be an error trying to create the procedure before using the END statement. Most commonly used symbols are $$ and // .
The next step is the CREATE PROCEDURE statement. This starts the process of creating the procedure. There is a procedure_name , which is self-explanatory. Then there are a set of parentheses. These are required even if there are no parameters. We will go over the procedure parameters later.
The main body of the syntax will involve the SQL Code . To start writing the SQL code that you wish to save, the BEGIN statement is used. Then you write the SQL code you want to save to the procedure and use the END statement to declare that you have finished writing the code. The $$ ends the SQL statement for creating the procedure and the final DELIMITER statement changes the symbol back from $$ to ; . So, when the procedure is then called, it can complete each piece of SQL coding.
Procedure Parameters
As mentioned above, procedures can have one or more parameters. There are two types of parameters: IN and OUT . IN is a parameter that enters a value into the procedure. The OUT parameter is a value that is passed out from the procedure to the individual calling on it. This is necessary if you need the results to be saved in an outside variable. If the type of parameter is not specified, the default is an IN parameter. In any case, it is best to fully write out the types of parameter.
Once the type of variable is specified, you name a variable for the value being entered or passed out. Then the final step for declaring a parameter is to specify the data type the procedure should expect the value to be.
Procedure Example
Let’s go through an example of a created procedure. The database is for an online store seen in my previous posts. Perhaps we want to see how many clients the store has in a particular State. To do this we would want to be able to enter a specific value for a State and see the total number of clients in that State returned.
DELIMITER $$CREATE PROCEDURE number_clients_state (IN state_v VARCHAR(20))
BEGIN
SELECT COUNT(*)
FROM clients
WHERE state = state_v;
END$$DELIMITER ;
As mentioned in the syntax section, we change the delimiter to $$ . We create a procedure with a parameter for the entered State value which is a VARCHAR data type. The next step is to create the query by selecting the total number of clients and using the WHERE clause to filter the clients to those living only in the specified state. Once the procedure is created, the delimiter is changed back to the normal ; .
To call on this procedure, we use the CALL statement. So if we wanted to know the total number of clients from New York, who have used the online store, we can write the following.
CALL number_clients_state('New York');This would return the number of clients in New York. As we can see, this would be much faster than having to write out the entire query every time we needed the information.
User Defined Functions Syntax
The syntax for functions is slightly different to procedures.
DELIMITER $$CREATE FUNCTION function_name (parameter_name data_type) RETURN data_type
(NOT) DETERMINISTIC
BEGIN
DECLARE variable_name data_type
SQL CODE
RETURN variable_name
END$$DELIMITER ;
We use the delimiter due to the same reasoning as procedures. Then the CREATE statement is used with what you want to create, in this case a FUNCTION . Following these statements is the name of the function. It is important that you don’t name your function with the name of a built-in function or else it will return an error. Within the parentheses there is a parameter name and the data type of the parameter.
The entered parameters is where the function starts to be slightly different to procedures. There is only one type of entered parameter for functions and that is an IN parameter. The next difference is that a function expects a return value. After the input parameter, there is a RETURN statement along with the expected data type the value should be returned as.
The next step is to decide if you want the function to be DETERMINISTIC or not. A deterministic function will always return the same value for a given parameter. This is not something that MySQL checks, so it relies on the programmer to not have put in coding that may result in non-deterministic values. A incorrectly assigned routine may decrease performance. The default value is NOT DETERMINISTIC .
Within the body of SQL code, the main differences between a function and stored procedure are at the beginning and end. At the beginning one should declare a variable that you want to assign the result. Then at the end of the function you return that variable.
User Defined Function Example
In this case, we will give a simple example of converting metrics. Let’s say we needed to convert an area value from square feet to square meters.
DELIMITER $$CREATE FUNCTION convert_sqft_sqmet (area DECIMAL(10,2)) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE square_meters DECIMAL(10, 2);
SELECT area * 0.0929 INTO square_meters;
RETURN square_meters;
END$$DELIMITER ;
Stored Routines are an efficient way of being able to use the same code or SQL queries over and over again, saving the time it would require to write everything out each time. I hope this post has helped your understanding of using stored routines. Thanks for reading.
For more documentation on stored routines:
