20 - MySQL Stored Procedures

MySQL stored procedure is a set of sql statements that are encapsulate into the single function. Stored procedures are stored on the MySQL server itself. It is generally built for repetitive tasks. They are invoked through any programming language or MySQL triggers or another MySQL procedure.

Initially MySQL did not provided stored procedure, this feature was added from MySQL version 5.0. So check the version if you got error in creating stored procedure. It also provides recursive stored procedure (meaning a stored procedure can call itself within sql statements.)

Basic Syntax of a stored procedure:

DELIMITER //
CREATE PROCEDURE procedureName ()
   BEGIN
   SELECT * FROM TableName;
   END //
DELIMITER;

DELIMITER command change the standard delimiter semicolon (;) to some other because semicolon delimiter is used within the sql statements also, so we have to select another delimiter to avoid conflict. Here we use // as a delimiter. We should again make the semicolon as default delimiter at the end. BEGIN and END contains the main body or functionality part of the stored procedure.

Basic syntax to call or execute the stored procedure:

CALL procedureName ();

Variables in Stored Procedure

Like any programming we can use variables for calculations within the BEGIN END block of stored procedure. These variables are local to a stored procedure mean within the BEGIN END block, if you want to make it session variable and available to a user during its session then use @ in the variable name beginning. Declaring a variable need DECLARE keyword, its name, its data types, range and default value if any. Syntax of declaring a variable within stored procedure is:

     DECLARE variableNamedataType(size) DEFAULT defaultValue;

For example:

     DECLARE COUNRTY VARCHAR(100) Default ‘United States’;

To assign a value to a variable, we have to use a SET keyword, for example to set a value to variable COUNTRY the simple syntax is:

     SET COUNTRY = ‘India’;

Or you can also assign the value using INTO keyword with select statement, for example as:

     SELECT DISTINCT(COUNTRY) from tableName where Capital=’Delhi’ INTO COUNTRY;

Here the country fetching from the table and assign to variable name COUNTRY having the scope of stored procedure only.

Stored Procedure Parameters

MySQL procedures provide even more flexible way of its usage by allowing a user to pass the parameters and also give back the output through parameters. There are three kind of parameters in MySQL stored procedures:

  • IN
  • OUT
  • INOUT

IN – The default parameter type is IN, it denotes the input value to the stored procedure. We can pass any value when we make a call to the procedure. Its value manipulates during the execution and retains afterthat.

OUT–This is used to give the result back to the user after execution. It has no value in the beginning but stored procedure gives the value through this variable at the end.

INOUT– This parameter works for both input and output parameter. At initial stage it takes input from the user through this variable and after execution returns the modified value to the same variable.

Example of stored procedure showing usage of parameters:

We have a table having the data of population, year for some countries as:

S.no

Country

Population

Year

1

India

1.2 Billion

2000

2

USA

2.3 Billion

2008

3

India

3 Billion

2009

4

Europe

1 Billion

2004

Now the stored procedure to get the population, year data from this table by passing the country name and year as:

DELIMITER //
CREATE PROCEDURE GetPopulationByCountry(IN countryName VARCHAR(255), OUT population BIGINT default 0, INOUT  yearNumber INT)
BEGIN
SELECT population, population_year INTO population, yearNumber
FROM populationTable
WHERE country = countryName;
END //
DELIMITER;

Now if we want to get for USA for the year 2008, then we call the procedure as:

CALL GetPopulationByCountry(‘USA’,@populationCount,@year);

Note: we can coding practices like if conditions, if-else conditions, while loop, for loop, do-while loop, CASE statements etc. All work in the same fashion as they defined for any programming language.

Stored procedure status

MySQL provide way to check the existing stored procedures in a database via a simple command:

Show procedure status;
Or
Show procedure status where db = ‘test’;

Example image:

It gives us the details of database name, type, definer, modified date, creation date etc.

You can also search the procedure if you know the name as:

Show procedure status WHERE name like ‘%Retrieve%’

To display the procedure coding use the command:

Show create procedure ProcedureName;

To drop / deleting a procedure:

DROP procedure if exists ProcedureName;

Error handling in stored procedure

Stored procedure has very nice way to handle an error during the execution. It needs to declare the handler with its action like variable declaration.

Handler action means whether to continue the execution or exit the execution process when specified error occurs defined in the declaration of error handler.

For example, suppose we may get an error of duplicate key in the insertion query within a stored procedure so we can handle it through error handling on error number 1062 and decide whether to continue or exit the execution process. So the declaration of handler as:

      DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error- duplicate key Found’;

To exit on duplicate error:

      DECLARE EXIT HANDLER FOR 1062 SELECT 'Error- duplicate key Found‘;

Declare handler according to sql exceptions:

      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasError = 1;

Cursors in Stored Procedure

Cursor gives a way to create a run time loop based on the result of the query. It allows iterating on the values return from the query within cursor.

Cursor also needs to declare a variable having type CURSOR including the query from where to select data to iterate. The cursor declaration and processing is also in the BEGIN-END block.

The basic example of a CURSOR:

We want to proceed the select query for displaying all cities but one at a time, means we want to select one city at a time but all city from a countryTable. So here we created a cursor which fetch all cities and then creates and executed all select statements at runtime for all cities.

DELIMITER //

CREATEPROCEDURE` Procedure_CURSOR_example` ()
BEGIN
    DECLAREcityName, done INT;
    DECLAREcur1 CURSORFORSELECTcity FROMcountryTable;
    DECLARECONTINUEHANDLER FORNOTFOUND SETdone = 1;
    OPENcur1;
    SETdone = 0;
    SETc = 0;
    WHILE done = 0 DO
        FETCHcur1 INTOcityName;
        IF done = 0 or done = ‘’ THEN
            Select “no more rows”;
EXIT;
           Select cityName;         // generate at runtime from cityName parameter
    ENDIF; 
    ENDWHILE;
    CLOSEcur1;
END//

Advantages of using Stored Procedure

It provides security as we can restrict application not to retrieve, update or delete data directly but only via the stored procedures.

Increase performance as it is compiled once by the server and stores the execution path accordingly by its own for every execution.

Reduces the traffic between the application and the database server as it interacts at a single layer no need to send large logics and query each time to database server.

Disadvantages of using Stored Procedure

Increase load on database server as some programming logic needs to run at database level instead of application server level.

Need additional learning to developer as he knows both application and database languages.

Logic is dividing into two places so little difficult to debug.

Like us on Facebook