Jun 25th, 2014 at 04:28 PM

Suggestion quesitons on MySQL stored routines

Q1. Write advantages and disadvantages of stored routines.

Advantages: 1. Consistency 2. Performance 3. Security 4. Architecture Disadvantages: 1. Performance 2. Capability 3. Maintainability 4. Portability

Q2. Classify MySQL stored routines.

1. Stored Procedures: Stored procedures support execution of SQL commands such as SELECT,INSERT,UPDATE, and DELETE. 2. Stored Functions: Stored function support execution only of the SELECT

Q3. Create a procedure name called show_datetime() to display current date and time.

show_datetime() function defination

delimiter //
create procedure display_datetime()
select now();
//
delimiter ;

Execute display_datetime() function

mysql>call display_datetime();

Q4. Define mysql input and return parameters: IN, OUT and INOUT

To declare the parameter’s purpose, use one of the following three keywords: IN: IN parameter is intended solely to pass information into the procedure. OUT: OUT parameter is intended solely to pass information back out of the procedure. INOUT INOUT parameter can pass information into the procedure, have its value changed, and then pass information back out of the procedure.

Q5. How can you declare and setting variables? give an example for each.

Declaring Variable: The DECLARE statement is used to declare the variable in a stored routine. Example:

DECLARE salary DECIMAL(8,2);

Setting variable: The SET statement is used to set the value of a declared stored routine variable. Example:

DECLARE salary INT; 
SET salary= 20000;

Q6. Create and use multistatement stored routines for calculate bonus on sales revenue.

Sales Table

id	employee_id	revenue
1	33	20000
2	23	30000
3	33	60000
4	23	20000

d:sales_table.sql

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

insert into sales(employee_id,revenue)values(33,20000);
insert into sales(employee_id,revenue)values(23,30000);
insert into sales(employee_id,revenue)values(33,40000);
insert into sales(employee_id,revenue)values(23,20000);
insert into sales(employee_id,revenue)values(33,10000);

d:onus_function.sql

use test;
DELIMITER //
drop function if exists calculate_bonus//
CREATE FUNCTION calculate_bonus(IN emp_id INT) 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;
COMMENT 'Set bonus 5% = 5/100 = .05 of total'
SET bonus = total * .05; 
RETURN bonus;
END;
//
DELIMITER ;

The calculate_bonus function would then be called like this:

mysql>source d:sales_table.sql
mysql>source d:onus_function.sql
mysql>select * from sales;
mysql>show function status like 'calculate_%' G
mysql>show create function test.calculate_bonus G
mysql>select calculate_bonus(33);
mysql>select calculate_bonus(23);

How can you integrate MySQL stored function calculate_bonus() with PHP? calculate_bonus.php

<?php 
if(isset($_POST["btnSubmit"])){
   $emp_id=$_POST["txtEmpId"];
   $db=new mysqli("localhost","root","","test");  
   $table=$db->query("select calculate_bonus('$emp_id')"); 
   list($bonus)=$table->fetch_row();
   echo $bonus;
}
?>

<form action="#" method="post">
Emp ID: <input type="text" name="txtEmpId" />
<input type="submit" name="btnSubmit" value="Calculate" />
</form>

Q7. Create a function to return letter grade A,B,C according to input with the following criteria:

a) 90=<A<100 80=<B<90 c) 70=<C<80 d:calculate_grade_function.sql

delimiter //
drop function if exists calculate_grade//
create function calculate_grade(x int) returns varchar(2)
begin
declare grade varchar(2);
if x>=90 and x<=100 then
set grade='A';
elseif x>=80 and x<90 then
set grade='B';
elseif x>=70 and x<80 then
set grade='C';
end if;
return grade;
end;
//
delimiter ;

How to use calculate_grade() function?

mysql>show function status like 'calculate_g%' G
mysql>select calculate_grade(78);

Q8. How can you make comment in stored routines? Q9. Create a procedure to produce N pairs of random number and inserting into analysis table according to input.

10. How can you modify a stored routine?

We can modify stored routine characteristics, via the ALTER statement. Example:

ALTER PROCEDURE calculate_bonus SQL SECURITY invoker;

11. How can you view a specific routine?

We can view a specific routine with the SHOW STATUS statement. Example:

mysql>SHOW PROCEDURE STATUS LIKE 'get_products'G

12. How can you display routine's creation syntax?

We can display routine’s creation syntax using the SHOW CREATE statement. Example:

 
mysql>SHOW CREATE PROCEDURE test.userG

13. What is HANDLER clause?

A handler is used in a procedure to determine when the iteration of a result set had completed. 14. How can you call a stored routine using PHP mysqli?

/25