Stored Procedures

Stored Procedures

Unit: 16 of 24

Stored procedures are procedures and functions that represent sets of SQL commands stored on the server. Thus, clients do not have to repeat certain commands several times, but can use the commands stored on the server. 

Stored procedures require the existence of the proc table in the mysql database, which I mentioned at the beginning of the course. This table is created automatically during MySQL server installation.

Stored procedures can be very useful in the following situations: 

  • when there are several client applications written in different programming languages, which communicate with the same database; 
  • when security is imperative; for example, in banking systems stored procedures and functions are used for all operations; this allows for a secure and consistent environment, and the use of routines ensures that each operation is properly logged; In such application scenarios, users do not have direct access to the database tables, but can execute specific, stored procedures. 

Stored procedures are blocks of SQL code that can perform activity on the server. These blocks of code are the most abstract and accept parameters, so they can be used multiple times and in different situations. 

The goal of stored procedures is code automation and data manipulation speed. As for the first, it’s probably pretty self-explanatory, since stored procedures are very similar to functions in any modern programming language. 

The speed of data manipulation is less clear. The speed of data manipulation is determined by the application that manipulates the database. Instead of the application, which manipulates the database, sending to the server and sequentially executing the SQL commands, it can distribute the parameter to a stored procedure on the server, without further contact with the application. So the application, instead of the large number of the SQL command, distributed only one parameter to the server. 

In MySQL, there are stored procedures and functions . The procedures and functions are very similar, and their differences and points of contact are presented in the next chapter. 

Stored procedures vs. user-defined function

In this chapter, the main differences between stored procedures and user-defined functions ( User-defined functions or, abbreviated, UDF) will be presented . On this occasion, we say user-defined functions because, unlike the functions that we will familiarize ourselves with in this lesson, but which are created by the user, there are also functions that are automatically accessible in MySQL (such as the now() function , to obtain the current time).

The following table shows the main differences between procedures and functions: 

Stored Procedures User-defined functions
They can return null or an arbitrary number of values I can only return one value
They can have input/output parameters They can only have input parameters
It can use SELECT and query DML Only query SELECT can be used
Functions can be called from the procedure The procedure cannot be called from the function
It is possible to handle exceptions using the try-catch block Try-catch block cannot be used
They cannot be used within the SQL command They can be used within the SQL command
It supports transactions It does not support transactions

Table 16.1. Difference between stored procedures and user defined functions

We now begin to study stored procedures.

Stored Procedures 

Creating stored procedures 

The stored procedure is the object on the server like any other. Therefore, to create it, we use the DDL CREATE command, which has the following syntax: 

1
2
3
4
CREATE
   [DEFINER = { user | CURRENT_USER }]
   PROCEDURE sp_name ([proc_parameter[,...]])
   [characteristic ...] routine_body

The syntax for creating a very simple stored procedure would look like this:

1
2
3
4
5
6
DELIMITER //
CREATE PROCEDURE my_procedure()
BEGIN
SELECT 'hello from stored procedure';
END//
DELIMITER ;

However, as can be seen from the example, the creation of the stored procedure still contains some features (the DELIMITER command). In fact, this command is not part of the procedure definition, but is used to avoid a problem.

The main delimiter in MySQL is the ; sign. This means that every line of code, if we want to have more than one, must end with this sign. Since stored procedures are basically saved as text and usually have more than one line of code, somehow all lines of code must be stored with delimiters (;).

The problem occurs when, when creating the stored procedure, MySQL finds this character, because instead of treating it as routine text, it treats it as an SQL symbol.

To avoid this, we use the SQL DELIMITER command , which allows defining the default base delimiter. In the example, instead of the sign ; I put the // sign, which means that each line, which at the end contains this sign (more exactly two signs), will be treated as an SQL command.

Thus, we can easily insert the sign ;.

At the end, we end the definition of the procedure with the sign END// (// menu 😉 and return the delimiter to the old value with the command DELIMITER ;.

If we wanted to create the stored procedure from MySQL Workbench, it is enough to select the Create Stored Procedure… option from the context menu obtained by right-clicking on the Stored Procedures tab.

 

Image 16.1. Creating the stored procedure 

Thus, in the query input window we will obtain the already mentioned syntax for creating stored procedures: 

1
2
3
4
DELIMITER $$
CREATE PROCEDURE new_procedure ()
BEGIN
END

In the syntax of stored procedures, which is given at the beginning of this lesson, we can also see some optional parameters, which can be mentioned when defining stored procedures.  

The first keyword is DEFINER, which is used to define the user who creates the stored procedure. 

This is important because, by default, the procedure can only be changed or deleted by the user who created it. That’s why we can, during creation, explicitly mention the creator of the procedure with the DEFINER keyword.

1
2
3
4
5
6
DELIMITER //
CREATE DEFINER = John PROCEDURE my_procedure ()
BEGIN
SELECT * FROM customer;
END//
DELIMITER ;

The procedure can be performed with several structural parameters (CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA), which describe the nature of the procedure, but have no syntactic influence on our manipulation, but help MySQL to create statistics and optimization:

1
2
3
4
5
CREATE PROCEDURE my_procedure ()
MODIFIES SQL DATA
BEGIN
//procedure body
END;

The DETERMINISTIC (and NON-DETERMINISTIC) parameter has a similar purpose. Also, this parameter influences the activity of the optimization engine, but it does not have an exaggerated influence on our syntactic obligations when writing the procedure. If the procedure is marked as deterministic, it means that its output never changes, like in the first procedure I wrote, but which emits the message hello from stored procedure. If the procedure is not deterministic (which is the default), MySQL does not expect the result of the procedure to always be the same (for example, if the procedure contains the now() function, which presents the exact time and, of course, never the same result).

Activating the procedure

Activation of the procedure is done with the CALL command:

1
CALL my_procedure;

or:

1
CALL my_procedure ();

If we execute one of these two commands, we will get as a result the execution of the block in the procedure my_procedure.

Modification of the procedure 

From the creation example we see that the syntax of the procedure starts with the BEGIN keyword, but ends with the END keyword with a delimiter. This rule is valid only when the procedure is created, but not for its changes (ALTER).

Changing the characteristics of the procedure can be achieved using the ALTER keyword:

1
ALTER PROCEDURE proc_name [characteristic ...]

If we want to modify the body of the procedure, the ALTER command is not usable, but the only way to delete the procedure is to rebuild it. Regarding the ALTER command, only the structure of the procedure can be modified with it, not its body. It can be said about the ALTER command, in procedures, that it is not used excessively. 

When we create the STORED procedure, it works only at the level of the base in which it is created, and this only for users who have the right to use it ( grant execute on procedure my_procedure to John ). The same rules apply to these rights as for rights in views. The user does not need to have rights to the data to have rights to the stored procedure and to see the data through it. We must be careful in cases of modifying the body of the procedure, when we use the DROP and CREATE commands. With the DROP command, all user rights to the procedure are lost, even if the new procedure has the same name as the old one. 


Deleting the procedure

To delete the stored procedure, the following syntax must be used:

1
DROP PROCEDURE [IF EXISTS] sp_name

With this we see that to delete the procedure the keywords DROP PROCEDURE are used, after which the name of the procedure to be deleted is mentioned. As an optional parameter, the IF EXISTS keyword can also be mentioned, which will prevent the error from occurring in situations where the procedure to be deleted does not exist. 

To delete stored procedures, we need the Alter routine privilege.

Parameterization of the procedure 

The stored procedure has the possibility of accepting and assigning parameters, which is why three types of parameters can be used: IN , OUT and INOUT .

1
2
proc_parameter:
   [ IN | OUT | INOUT ] param_name type

Although the names are a bit picturesque, we will explain them briefly: 

  • IN assumes the input parameter, which after acceptance is visible only in the body of the procedure; 
  • OUT is created within the procedure, but may be visible outside of it;
  • INOUT is created outside the procedure and is visible all the time, both inside and outside the procedure. 

IN is the default movement direction of the parameter. This means that you don’t have to explicitly mention this property of it. We intentionally say direction of movement, not type, because parameters also have types (which are not the same as direction of movement). Moreover, it is impossible to assign the parameter to the procedure, and its expected type is not defined within its definition. 

Here is an example of a parameterized procedure: 

1
2
CREATE PROCEDURE my_procedure (param1 int, param2 int)
SELECT param1+param2;

The procedure adds two numbers. In order for this stored procedure to be called, the CALL keyword is used and, on assignment, you must, of course, pass the required parameters. Therefore, the call will look like this:

1
CALL my_procedure(1, 2);

These digits are accepted by the procedure through parameters param1 and param2, and then processed by code. We notice that the parameters do not have a standard notation (with the @ sign), but are assigned only by name. However, parameter notation combinations are only possible within the procedure itself. If we changed the procedure block to look like this: 

1
2
SET @x=5;
SELECT param1+param2+@x;

the procedure would show the number 8 as a result, because the input parameters and those within the procedure are treated identically. 

Variables that are inside the procedure are not visible outside of it. So, the variable @x will be visible only within the BEGIN and END block.

If we want to use parameters within the procedure and outside it, we need the output (OUT) or input and output (INOUT) parameters.

Output parameters require that the parameter be defined before the procedure is called: 

1
2
CREATE PROCEDURE my_procedure (OUT param varchar(20))
SET param='hello from procedure';

Now we passed the parameter along with the OUT keyword (which means the parameter is output). Then, in the code we treat this parameter as standard. 

When calling the procedure, we now don’t have to distribute the parameter that has a real value, but any parameter (even Null) that doesn’t have to be defined before: 

1
2
CALL my_procedure (@p);
SELECT @p;

After the procedure is executed, the value of the variable will be changed according to the procedure itself. 

If we created the procedure my_procedure as follows: 

1
2
CREATE PROCEDURE my_procedure (OUT param int)
SET param=param+1;

and we would call it in the following way:

1
2
3
SET @a=1;
CALL mp(@a);
SELECT @a;

the result of the query would be Null, because the OUT parameter is not treated as a pointer, but simply as a parameter that will be returned to the system. For the parameter to be treated as a pointer, we should use the INOUT directive:

1
2
CREATE PROCEDURE my_procedure (INOUT param int)
SET param=param+1;

 

Along with this change, the parameter will be treated as a pointer, and changes to the parameter (variable) during the procedure will be current for the entire code where this procedure is called. 

You can get all the structural data about the procedure with the command: 

1
SHOW CREATE PROCEDURE my_procedure

and about all procedures:

1
SHOW PROCEDURE STATUS

User Defined Functions (UDFs)

During the lesson we familiarized ourselves with a type of stored procedures. Now we will look at user defined functions. We say user-defined functions, because all functions can be divided into two types: 

  • user defined functions; 
  • built-in functions. 

The built-in functions are all those functions that the standard form of MySQL server assumes.

We will familiarize ourselves with the built-in functions in the next lesson. 

When it comes to user-defined functions, on the MySQL server they can be done in two ways: through SQL script (DDL) or through the source programming language of the MySQL server (C ) .

Creating user-defined functions (UDFs) through SQL is a simple method. In fact, the syntax is very similar for creating stored procedures. 

1
CREATE FUNCTION myFunction() RETURNS varchar(20) RETURN 'hello from UDF';

Let’s look at the parts of this function one by one: 

1
CREATE FUNCTION myFunction()

This line is identical to creating the stored procedure. 

1
RETURNS varchar(20)

Unlike a procedure, a function can (and must) return only one value. The type of this value must be defined when defining the function itself. In this case, it is varchar(20).

1
RETURN 'hello from UDF';

The function has only one line of code. This line returns the result of the function and therefore interrupts its execution. When it would have more than one line, we should approach the technique we also used with the procedures – temporary replacement of the delimiter.

1
2
3
4
5
6
7
DELIMITER //
CREATE FUNCTION myFunction()
RETURNS varchar(20)
BEGIN
RETURN 'Hello';
END//
DELIMITER;

The creation of the user-defined function can also be executed from MySQL Workbench. It is enough to select Create Function… from the context menu, which is obtained by right-clicking on the Functions section.

Image 16.2. Create user defined function 

Unlike the procedure, the function can be inserted in the query itself, which gives us more possibilities to intervene on the data at the time of creating the output. For example, we call the function by entering it in the query: 

1
SELECT myFunction()

Now, let’s imagine a situation related to the test database that we have been using since the beginning of the course, the  Sakila database . We know that in the database, among other things, information about movies is saved. If we wanted to confirm that a certain movie is longer than 2 hours, we could use the function. This function would look like this: 

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER $$
CREATE DEFINER='root'@'localhost' FUNCTION movie_over_2_hours(film_title varchar(50)) RETURNS tinyint(1)
   DETERMINISTIC
BEGIN
DECLARE film_duration INT;
SELECT length INTO film_duration
FROM film
WHERE title = film_title;
if film_duration > 120 then return TRUE; end if;
if film_duration < 120 then return FALSE; end if;
END

Now we will explain a little the code given in the example above. I created the user defined function named  movie_over_2_hours  and on this occasion I defined DEFINER. Of course, this is not necessary. This function has an input parameter, namely the name of the movie whose duration we want to check. This parameter has the name  film_title  and is of type varchar . As an output parameter, the function has the type tinyint .

At the beginning of the function body, the first time a variable is declared in which we will store the length of the movie whose name is distributed to the function. This parameter has the name  film_duration and the type is INT . To obtain the length of the film, we use the standard SELECT query, only we put its result in the film_duration variable . We then use conditional syntax to confirm whether the duration is greater than or less than 120 minutes. 

From these examples, we conclude that stored functions and procedures are very similar in nature. The main differences are in the number of output parameters (the function must return one, and the procedure several) and the way of calling (the function can be called from the query).

Apart from the stored ones, there is another type of user-defined functions, mentioned at the beginning: these are the functions written with the source language ( C ). These functions are much faster than stored functions, but their creation is much more complicated, so they are not created unless they are used a lot. Since the basis for creating these functions is a very good knowledge of the C language, the description of this process would be out of the context of the course. 

In the previous example, which demonstrates the use of functions, we used the syntax that we have not explained so far. This is definitely the conditional syntax, so in the rest of the lesson we will deal with flow control. 

 

Flow control

At the end of this lesson we will deal with another very important functionality that stored procedures bring, namely the possibility of flow control. In fact, the use of language constructors, which will be presented next, is possible only within stored procedures, that is, within procedures and functions. 

If

In the previous example we used the following line: 

1
if film_duration > 120 then return TRUE; end if;

This is the syntax for a basic flow control command and the programming symbol of the general If command . The If control operator informs the system that something will be done conditionally in the next flow, during the program. In the example we said: if the duration of the movie is greater than 120 minutes, then the whole function will return the value TRUE.

Here is how we check the value of a variable, and based on this we performed an operation:

1
2
3
if @myVariable>0 then
set @myVariable=@myVariable-1;
end if;

 

We distinguish several sequences in this condition: the first is the condition itself (if @myVariable>0 then). The condition starts with the if keyword , followed by the conditional expression (in this case, myVariable must be greater than 0) and the then keyword .

After the then keyword , block code begins . In this case, this is a line that decrements the value of the variable  myVariable by one , but it can be more lines .
Block is completed with the endif command .

The if conditional block can also have alternative blocks, with the help of the else clause . In this case, instead of ending the block with the  endif command , we activate the alternative condition:

1
2
3
4
5
6
7
if @myVariable>0 then
set @myVariable=@myVariable-1;
else if @myVariable<0 then
set @myVariable=@myVariable+1;
else
set @myVariable=0;
end if;

At the end, in any case, we must close the endif block with the command ;.

Remember that it is one of the modes for flow control and the switch structure, which will be explained in the next lesson, as well as the if function, also mentioned before. 

While

Another important way to control program flow is loops. The most frequently used loops in programming are while and for (and their derivatives).

The while loop starts with the while keyword. Then follows the conditional expression and the do keyword.

Everything after the do keyword will be executed on each iteration of the loop. When the condition of the loop will no longer be fulfilled, it is completed with end while;

1
2
3
4
5
set @x=10;
while @x > 0 do
  set @x=@x-1;
  select @x;
end while;

The while loop is characteristic in that it is usually used when we do not know exactly how many times its code will need to be executed. Given that the condition for stopping this loop is usually regulated in the loop itself, it is very important not to bring the loop into a state of never ending (dead loop). In the example, this would happen if we did not decrement the value of x by one at each iteration.

Loop

Loop is not much different from while loop. At the beginning, the loop must be initialized by assigning the name (my_loop: loop). Then the code block starts and lasts until the end loop command (end loop my_loop;). The loop executes until it is interrupted with the command leave (leave my_loop;).

1
2
3
4
5
6
7
8
set @x=10;
  my_loop: loop
         set @x=@x-1;
         select @x;
         if @x<=0 then
            leave my_loop;
         end if;
   end loop my_loop;


Repeat

Repeat is the loop that executes until a certain condition is met. Its characteristic is that, even if the condition is never met, the body of the loop will be executed at least once. From other programming languages, we know this loop as a while loop.

Repeat starts with the repeat keyword, followed by the body of the loop. The loop is completed with the until command, followed by the condition and command (until @x = 0 end repeat):

1
2
3
4
repeat
set @x = @x - 1;
select @x;
until @x = 0 end repeat;


Iterate and leave

These two commands allow moving to the next iteration (the next circle of the loop) or leaving the loop unconditionally, in the loop loop. As parameters, it accepts loop names:

iterate my_loop; – Go to the next iteration

leave my_loop; – Leave the loop

In other programming languages, it can be compared to continue, and  leave  to   break , also in flow control.

For each

This loop iterates through the table structure, allowing a specific action each time separately. It is only valid for triggers, so we will not process it excessively.

1
for each row set @x = @x + 1;

What is the maximum number of output values ​​that the user-defined function can have?

Exercises

Exercise 1

Problem:

Create the procedure for inserting the user, named insertuser, which accepts the user’s name as a parameter. The user will be entered with status user.

Solution:

1
2
3
4
create PROCEDURE insertuser(newname varchar(50))
BEGIN
   insert into users (name,status) values (newname,1);
END;


Exercise 2

Problem:

The stored procedure that creates the user according to the parameterized name must be done. If a user with this name already exists in the table, it will not be created. The user enters with user status.

Solution:

1
2
3
4
5
6
7
8
create PROCEDURE insertuser(newname varchar(50))
BEGIN
   declare usersCount int;
   select count(id) from users where name=newname into usersCount;
   if usersCount<1 then
   insert into users (name,status) values (newname,1);
   end if;
END;


Exercise 3

Problem:

The procedure that will introduce the user to the database must be done. The procedure accepts user name and password as parameters. If the user with this name does not exist, it will enter a new one. If the user exists, their password will be replaced with a new one. The user enters with user status.

Solution:

1
2
3
4
5
6
7
8
9
10
create PROCEDURE insertuser(newname varchar(50),newpass varchar(50))
BEGIN
   declare usersCount int;
   select count(id) from users where name=newname into usersCount;
   if usersCount<1 then
   insert into users (name,password,status) values (newname,password,1);
   else
   update users set password=newpass where name = newname;
   end if;
END;


Exercise 4

Problem:

The checkNum function must be made, which will accept an int type value and return the varchar type value. When the number comes into play, its value is checked. If it is zero, the function returns the text the number is zero, if the value is lower than 5 but higher than 0, the function returns the text lower than 5, if the value is higher than 5, the function returns higher than 5 and if it is 5 , the returned value is the number equals 5.

Solution:

1
2
3
4
5
6
7
8
9
10
create function checkNum(num int)
returns varchar(50)
begin
set @outputText = 'unknown';
if num = 0 then set @outputText = 'The number is zero'; end if;
if num < 5 and num != 0 then set @outputText = 'Lower than 5'; end if;
if num > 5 then set @outputText = 'Higher than 5'; end if;
if num = 5 then set @outputText = 'The number equals 5'; end if;
return @outputText;
end//

 

Note:

If the binary log is enabled, a conflict can be reached and the function will not be able to be created, because the system will not be able to identify if the function influences the data it logs with, then we need to add the deterministic option to the function declaration or enable option log_bin_trust_function_creators:

 

1
SET GLOBAL log_bin_trust_function_creators = 1;

or:

1
2
3
4
create function checkNum(num int)
returns varchar(50) deterministic
begin
…


Exercise 5

Problem:

The setStatus function must be done, which will change the user’s statuses in the users table. This function must accept two parameters, username and statusname. Based on the statusname parameter, the ID is taken from the statuses table, then this ID is put for the user’s searched name in the users table.

Solution:

1
2
3
4
5
6
7
8
9
10
create function setStatus(username varchar(50),statusname varchar(50))
returns int
begin
set @statusId = 0;
select id from statuses where name = statusname into @statusId;
if @statusId != 0 then
update users set status = @statusId where name = username;
end if;
return @statusId;
end//

Leave a Reply

Your email address will not be published. Required fields are marked *