MySQL - Introduction To Stored Procedures And Functions Part 1

MySQL introduced Stored Routines as of 5.0 making it possible to execute SQL code stored on the server. In this article I will explain what Stored Routines are and how they can be used with examples. A routine is essentially a set of SQL statements that can be stored on the database server. Once stored, client applications can execute the routines by calling the routine name and don't need to issue individual statements. For example, if you wanted to authenticate a user in your application, you might write an SQL statement similar to that in listing 1.1.

Listing 1.1

  1. SELECT * FROM users WHERE username=user AND password=pass;

If this statement is issued from your application, it will be sent to the server for every authentication request. If the statement was stored in a routine on the server, you would only need to execute the routine and supply the username and password and in doing so, you will offload the work to the server. The advantage of this is of course that you can maintain a library of reusable SQL code on the server, which can be used by other applications written in other programming languages. The other advantage is that the SQL statements will not be visible to anyone who inspects your application code. Only those who have access to the database server and have been granted privileges can see the SQL statements stored in a routine. This provides a level of security and consistency and also improves performance because less data is being sent from a client application to the server. If the SQL statement above was stored in a routine on the server, anyone looking at your application code would not know that you have a 'users' table. The drawback is that the server must do more of the work so it is important to determine what tasks you should offload to the server. Stored routines are also hard to debug and can get complicated to maintain over time.

There are two types of stored routines (Procedures and Functions), which can be used to set SQL statements. Knowing when to use them and how to use them is important because it is likely that other parts of your code will call these routines.

Part 1 - Procedures

Procedures and functions share similar characteristics in that they can both accept parameters and return data. Beyond this they have their own characteristics. A procedure can return zero or more values as well a ResultSet where as a function must always return a single value. Procedures can not be used within an SQL statement, it must be executed where as a function can be used in an SQL statement. Most often this is a determining  factor for developers who need to know which type of routine to use. If you want to create a routine that you can use in your SQL statements you must create a function where as if you want to perform a task you should consider using a procedure. The MySQL team sometimes make changes to stored routines so it is worth keeping updated with new releases of MySQL.

To get a better understanding of each of the stored routines it's worth looking at a few examples. First let's start by taking the example SQL statement mentioned above and turn it into a stored procedure.
  1. DROP PROCEDURE IF EXISTS AuthenticateUser;
  2.  
  3. delimiter //
  4.  
  5. CREATE PROCEDURE AuthenticateUser(IN userName CHAR(30), IN pass CHAR(30))
  6.     BEGIN
  7.         SELECT * FROM users WHERE username=userName AND password=pass;
  8.     END;
  9.  
  10. //
  11.  
  12. delimiter ;
  13.  
  14. CALL AuthenticateUser('john', 'letmein');

At first glance, the code might seem intimidating especially if you are new to stored routines. Let's start by examining the first statement.

  1. DROP PROCEDURE IF EXISTS AuthenticateUser;

Once a routine is created, it will be stored in the mysql.proc table. You can't reissue the same CREATE... statement, doing so will result in an error with MySQL informing you that the procedure/function already exists. It's likely when creating a routine that you will need to alter the routine several times. For this reason a DROP statement is required, which essentially deletes the routine allowing the CREATE statement to be reissued. This also applies to functions.

Note: You can query the mysql.proc table to see a list of procedures and functions. This can be done by changing to the mysql database and querying the proc table as shown below.

use mysql;
SELECT * from proc

After the DROP statement is a delimiter // command, which changes the default ';' delimiter to '//'. This is necessary because the body of a routine may contain several statements (A compound statement) which are terminated by the ';' character. The MySQL server would not know that these statements terminated by a ';' character is part of a store routine. For this reason the default delimiter is temporarily changed, so that the entire routine can be passed to the server.

A delimiter doesn't need to be //. It can be any character such as $ or @ but you should avoid using the \ (Black slash) character. Once the delimiter as been changed, you are free to use the standard delimiter within the body of a routine. Once the routine is complete the same delimiter must be used to mark the end of the routine and the default delimiter should be restored.

After using the delimiter command, the CREATE PROCEDURE... statement is used to create the routine. When creating a routing, you specify a routine name and if the routine accepts any parameters. In the example, above the routine name is AuthenticateUser and has been created to accept two parameters (IN user char(30) and IN pass char(30)). IN is a parameter mode, which indicates that a parameter is being passed to the procedure. If the value of a parameter that uses the IN mode changes in the procedure, that change will not be seen by the caller. The parameter is only going IN to the procedure. Omitting the IN mode, will still result in an IN mode because it is the default mode. Since procedures can return more than one value and OUT mode exists as does an INOUT mode.

These modes will be explained further in the article. Parameters must be accompanied with a data type. In the example, both the user and pass parameters have a char data type and the size has been set to 30. These parameters can be used within the routine body as can be seen in the example. The body of a routine must be enclosed with the BEGIN and END statements if you have a compound statement. If your routine consists of a single SQL statement as in the example, you don't need to wrap it around the BEGIN and END statements. In the body of the routine is where the declarative SQL statements are written. Notice in the example, that the SQL statement is returning a RecodSet, which can not be achieved with a function.

To execute a stored procedure the CALL statement is used. You must specify the name of the procedure to execute and any parameters it accepts as shown in the example. The result of invoking the procedure above is the same as executing the SQL statement on its own.

By now you should have a basic understanding of what store routines are and how to create a stored procedure. It's time to look at a few real world examples for a better understanding.

The stored procedure below can be used to show column meta data for a given table in a database. This can be useful when you want to know what columns a table has and the data types for the columns.
  1. DROP PROCEDURE IF EXISTS sp_columns;
  2.  
  3. delimiter //
  4.  
  5. CREATE PROCEDURE sp_columns(databaseName VARCHAR(50), tableName VARCHAR(50))
  6.     BEGIN
  7.         SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = databaseName
  8.         AND TABLE_NAME = tableName;
  9.     END;
  10.  
  11. //
  12.  
  13. delimiter ;
  14.  
  15. CALL sp_columns('databaseName', 'tableName');

In the next example, the stored procedure above has been modified to determine if a column exists in a table. Notice that the procedure uses a conditional control statement to determine if the column exists.
  1. DROP PROCEDURE IF EXISTS sp_column_exists;
  2.  
  3. delimiter //
  4.  
  5. CREATE PROCEDURE sp_column_exists(databaseName VARCHAR(50), tableName VARCHAR(50), columnName VARCHAR(50))
  6.     BEGIN
  7.         IF EXISTS (SELECT * FROM information_schema.COLUMNS
  8.             WHERE TABLE_SCHEMA =databaseName
  9.             AND TABLE_NAME=tableName
  10.             AND COLUMN_NAME = columnName)
  11.         THEN
  12.             SELECT CONCAT(columnName, ' exists in table ', tableName) AS message;
  13.         ELSE
  14.             SELECT CONCAT(columnName, ' column does not exist in table ', tableName) AS message;
  15.         END IF;
  16.     END;
  17.  
  18. //
  19.  
  20. delimiter ;
  21.  
  22. CALL sp_column_exists('databaseName', 'tableName', 'columnName');

So far, the examples have all used parameters with an IN mode, however there are two other types of modes (OUT and INOUT). A parameter that is defined as OUT can have it's value changed in the routine and the change can be seen by the caller. Initially the parameter value is NULL and only variables can be passed for that argument. This is better explained with an example.
  1. DROP PROCEDURE IF EXISTS sp_test_out;
  2.  
  3. delimiter //
  4.  
  5. CREATE PROCEDURE sp_test_out(OUT testVar VARCHAR(20))
  6.     BEGIN
  7.         SELECT testVar;
  8.     END;
  9.  
  10. //
  11.  
  12. delimiter ;
  13.  
  14. SET @var = 'A string';
  15. CALL sp_test_out(@var);
  16. SELECT @var;

In the example above a variable @var has been set and is supplied to the sp_test_out procedure. The procedure simply returns the variable value. However since and OUT mode is used, the variable testVar is initialized with null and therefor null is returned and not 'A string'. We can modify the code above so that a value is returned by assigning a value to the testVar variable as shown in the example below.
  1. DROP PROCEDURE IF EXISTS sp_test_out;
  2.  
  3. delimiter //
  4.  
  5. CREATE PROCEDURE sp_test_out(OUT testVar VARCHAR(20))
  6.     BEGIN
  7.         SET testVar = 'Altered string';
  8.     END;
  9.  
  10. //
  11.  
  12. delimiter ;
  13.  
  14. SET @var = 'A string';
  15. CALL sp_test_out(@var);
  16. SELECT @var;

The procedure above simply alters the value of the variable testVar by using the SET statement. SELECT @var will now display 'Altered string' after calling the procedure. In some situations, you might want to supply a value to the procedure, alter it within the routine and return the new value. To achieve this you will need to use the INOUT parameter mode. When a parameter is defined as INOUT, it's value is no longer NULL within the routine body since something is coming IN. I will conclude this part of the article with a final example that demonstrates the use of the INOUT parameter.
  1. DROP PROCEDURE IF EXISTS sp_format_date;
  2.  
  3. delimiter //
  4.  
  5. CREATE PROCEDURE sp_format_date(INOUT testVar VARCHAR(20))
  6.     BEGIN
  7.         SELECT DATE_FORMAT(testVar, '%M, %D %Y') INTO testVar;
  8.     END;
  9.  
  10. //
  11.  
  12. delimiter ;
  13.  
  14. SET @article_date = '2013-01-01';
  15. CALL sp_format_date(@article_date);
  16. SELECT @article_date;

In the example above,  the sp_format_date procedure accepts a date variable and returns a formatted date. The value of testVar in the SELECT statement is not null since the parameter is INOUT. In a real world situation, you would not use a stored procedure in this manner, rather you would use a stored function or User Defined Function (UDF), which is discussed in Introduction To Stored Procedures And Functions Part 2

No comments:

Post a Comment