Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Stored Procedures and Functions in MySQL - Part 2

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 478
    Comment on it

    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)
    Increments the salaries by 6.5%

    To create a Function the syntax is :
    CREATE FUNCTION <function_name>(<input parameter name> <datatype>......)
    RETURNS <datatype of the variable to be returned> BEGIN
    <Function Body >
    Return <variable>
    END;
    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

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: