How to manage stored routines?

In the second part of the series we will see basic commands used to manage the Stored Routines

As these are also Database objects so the basic DDL commands hold true here also

• Create
• Alter
• Drop

CREATE

The syntax to create a Stored Procedure is
CREATE PROCEDURE <procedure name> (<input parameter name> <datatype>......)
BEGIN
<Procedure Body >
END;
For example lets create a stored procedure to increment salaries of employees by percentage

DELIMITER $$
CREATE PROCEDURE salary_increment(increment_percent decimal(5,2))
BEGIN
UPDATE employee_master set salary = salary*(1+increment_percent/100);
end$$
Delimiter ;

The above procedure when called by the command

call salary_increment(6.5)

DELIMITER $$
CREATE FUNCTION get_age(employee_id int)
RETURNS decimal
BEGIN
declare age decimal;
select TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) into age from employee_master where id= employee_id;
return age;
end$$
Delimiter ;

To test this function execute the following

select get_age(2);

The above command will return the age of the employee with id=2

DROP

To drop a stored routine the command is :
For a Stored Procedure

DROP PROCEDURE IF EXISTS `employee`.`salary_increment`;

For a Function

DROP FUNCTION IF EXISTS `employee`.`get_age';

ALTER

Alter Procedure command as per MySQL 5.6 Reference Manual can only be used to alter the characteristics of the procedure and not the body or the parameters being passed

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

Alter Function command as per MySQL 5.6 Reference Manual can only be used to alter the characteristics of the function and not the body or the parameters being passed

ALTER FUNCTION func_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

How to alter the body or parameters stored routines?

The only solution is to first drop and then create again.

DELIMITER $$
DROP PROCEDURE IF EXISTS `employee`.`salary_increment`$$
CREATE  PROCEDURE `salary_increment`(increment_percent decimal(5,2))
Begin
update employee_master set salary = salary*(1+increment_percent/100);
end$$
DELIMITER ;

DELIMITER $$
DROP FUNCTION IF EXISTS `employee`.`get_age`$$
CREATE FUNCTION `get_age`(employee_id int) RETURNS decimal(10,0)
BEGIN
declare age decimal;
select TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) into age from employee_master where id= employee_id;
return age;
end$$
DELIMITER ;

In the next part we will discuss variables and their declarations in parameters and body, of procedure and function.

NOTE : To try the above examples please refer to sample tables given here

Previous
Need of Stored Routines