Stored Procedure MySQL Example

MySQl Stored Procedure Example

Table:

mysql> desc employees;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empid     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql>

mysql> select * from employees order by id;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|  100 | Ramu      |  10000 |
|  101 | Santhosh  |  15000 |
|  102 | Pratheesh |  16000 |
|  103 | Vasudevan |  20000 |
|  104 | Sajeevan  |  25000 |
+------+-----------+--------+
5 rows in set (0.02 sec)

mysql>

Stored Procedure getsalary() - gets name and salary for the given id.

delimiter //
create procedure getsalary(IN id INT)
begin
select name, salary
from employees
where empid=id;
end
//
delimiter ;

Defining Stored Procedure in MySQL:

mysql> delimiter //
mysql> create procedure getsalary(IN id INT)
    -> begin
    -> select name, salary
    -> from employees
    -> where empid=id;
    -> end
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;

Calling Stored Procedure

mysql> call getsalary(102);
+-----------+--------+
| name      | salary |
+-----------+--------+
| Pratheesh |  16000 |
+-----------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Notes:

DELIMITER statement is used to change the standard delimiter(;) to another, in this case the delimiter is changed to //.


Declaring Variables

DECLARE variablename datatype DEFAULT initialvalue;

delimiter //
create procedure gettotalsalary()
begin
DECLARE totalsalary int DEFAULT 0; 
select sum(salary) into totalsalary
from employees;
select totalsalary;
end
//
delimiter ;

mysql> call gettotalsalary;
+-------------+
| totalsalary |
+-------------+
|       86000 |
+-------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> 

Parameters
The parameter has one of three modes IN, OUT and INOUT.

IN -parameter to input.
OUT-parameter to output.
INOUT -shared parameter.

Conditional Control

IF expression THEN commands
ELSEIF expression THEN commands
ELSE commands
END IF; 

CASE
WHEN expression THEN commands
…
WHEN expression THEN commands
ELSE commands
END CASE;

WHILE loop

WHILE expression DO
Statements
END WHILE

REPEAT loop - equivalent to do while loop in c.

REPEAT
statements;
UNTIL expression
END REPEAT

LEAVE - equivalent to BREAK in c.
   
ITERATE - equivalent to CONTINUE in c.