Procedures in MySQL

Procedures are nothing but reusable SQL statements. We can write the SQL statements once in procedure and use the same procedure multiple times with different parameters.

Procedure can take multiple parameters. There are 3 types of parameters in procedures.

  1. IN – Input parameter passed by value
  2. OUT – Output parameter – This is used to specify that this parameter is going to be returned back
  3. INOUT – Input as well as output parameter. This is passed by reference

Below procedure shows that OUT parameter is always NULL by default.

drop procedure if exists TestOutParam;

DELIMITER //

CREATE PROCEDURE TestOutParam(out P1 VARCHAR(22))

BEGIN
SELECT P1;

END //

DELIMITER;
SET @V1 = “hello”;

CALL TestOutParam(@V1);

Main body of the procedure starts with BEGIN keyword and ends with END keyword.
We can declare the variable as shown in below syntax.

DECLARE myvariable datatype(size) DEFAULT defaultValue;
DECLARE counter INT DEFAULT 0;

We can assigne the value to variable using below syntax.
SET counter = 2;

We can access session and global variables using below syntax.
SELECT @mysessionvariabl1;
SELECT @@autocommit;

Procedure syntax in MySQL

delimiter $$
Create procedure PROCNAME(in param1 int, in param2 int)
BEGIN
Declare V1 INT;
…..statements
END$$
delimiter “;”

Conditional Statements in MySQL

IF expression THEN
…..statements;
ELSE
…..statements;
END IF;

IF expression THEN
…..statements;
ELSEIF elseif-expression THEN
…..statements;
ELSE
…..statements;
END IF;

Example of conditional statement in MySQL

In below example, we have used If condition to check if the number is odd or even.

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1 (IN mydata int)

BEGIN

IF (MOD(mydata,2) = 0) THEN

select ‘mydata is even’;

Else

select ‘mydata is odd’;

END IF;

END$$

Call p1(11);

SELECT IF(mod(12,2)=0,”Even”,”odd”);
Case Statements in MySQL

case exp
When ex1 Then
            ..statements
When ex2 Then
            ..statements
Else
           ..statements
End Case

DELIMITER $$
CREATE PROCEDURE p1 ()
BEGIN
      DECLARE mycase int default 1;
          case mycase
                    when 1 then select ‘mycase is 1’;
                    when 2 then select ‘mycase is 2’;
                    when 3 then select ‘mycase is 3’;
         end case;
END$$
DELIMITER ;
Looping Statements in MySQL

There are 3 types of loops in MySQL.

  1. While
  2. Repeat
  3. Loop

While (exp) Do
        ..statements
End While;

Repeat
         ..statements
Until (exp)
End Repeat;

MyLabel:Loop
       ..statements
End Loop;

You can exit the loop using below statement.
Leave MyLabel;

You can continue the loop (skipping remaining rest of code in a block) using below statement.
Iterate MyLabel;

Example on loops in MySQL

DELIMITER $$
CREATE PROCEDURE p1 (IN mydata int)
BEGIN
DECLARE mycase int default 1;
DECLARE counter int default 5;

MyLabel1:While (counter > 0) Do
select concat(‘counter is ‘, CAST(counter as CHAR(50)));
SET counter = counter -1;
Leave MyLabel1;
End While;

SET counter = 5;
MyLabel2:Repeat
select concat(‘counter is ‘, CAST(counter as CHAR(50)));
Leave MyLabel2;
Until (exp)
End Repeat;

SET counter = 5;
MyLabel3:Loop

SET counter = counter -1;
IF (counter = 2) THEN
Iterate MyLabel3;
END IF;
select concat(‘counter is ‘, CAST(counter as CHAR(50)));
IF (counter = 0) THEN
Leave MyLabel3;
END IF;
End Loop;

END$$
DELIMITER ;

delimiter “;”

Create procedure addproc(in a int, in b int)
BEGIN
    Declare c INT;
   set c=(a+b);
   SELECT concat(‘addition is ‘, c);
END$$
delimiter “;”

To call procedure you have to use below syntax
call addproc(2,3);

You can get all defined procedures in specific database by firing below query.

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=’PROCEDURE’ AND ROUTINE_SCHEMA=’YOUR_DB_NAME’;

Adding comments in Procedure

We can add comments in 3 ways.

  1. #This is a comment using # symbol
  2. — This is a comment using — symbol
  3. Finally you can add multi-line comment using below syntax.

/*
This is a Java style comment
Multi line
*/

You may also like...