May 31st, 2014 at 06:55 PM

Create a simple MySQL stored routine to calculate bonus on sales revenue

Question: Create a MySQL stored routine to calculate bonus for the employees (sales persons) on his/her sales. Code: my_db.sql

drop table if exists sales;
create table sales(
 id int(10) primary key auto_increment,
 employee_id varchar(10) not null,
 revenue decimal(10,2) not null,
 trans_on datetime
);

#some sample records
insert into sales(employee_id,revenue,trans_on)values('100',100,now());
insert into sales(employee_id,revenue,trans_on)values('100',700,now());
insert into sales(employee_id,revenue,trans_on)values('101',300,now());
insert into sales(employee_id,revenue,trans_on)values('101',500,now());
insert into sales(employee_id,revenue,trans_on)values('102',300,now());
insert into sales(employee_id,revenue,trans_on)values('102',200,now());
insert into sales(employee_id,revenue,trans_on)values('102',900,now());

drop function if exists calculate_bonus;
delimiter //
create function calculate_bonus(emp_id char)returns decimal(10,2)
begin
   declare total decimal(10,2);
   declare bonus decimal(10,2);
   select sum(revenue) into total from sales where employee_id=emp_id;
   set bonus=total*0.05;
   return bonus;
end;
//
delimiter ;

Import my_db.sql and calculate bonus 1. Save the above code to any drive e.g. d:my_db.sql 2. login to mysql server with the command prompt

C:>mysql -u root -p

3. To import my_db.sql file to mysql server write the following command:

mysql>source d:my_db.sql

4. To call calculate_bonus() routine and calculate bonus for the employees 100 and 102 write the following command:

mysql>select calculate_bonus('100');
mysql>select calculate_bonus('102');

Call MySQL stored function by PHP

<?php

$db=new mysqli("localhost","root","","test");

$emp_id=100;

$table=$db->query("select calculate_bonus('$emp_id')");

list($bonus)=$table->fetch_row();

echo $bonus;

?>