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

    • 0
    • 3
    • 3
    • 1
    • 0
    • 0
    • 0
    • 0
    • 391
    Comment on it

    Parameters in Stored Routines

    While executing any stored routine sometimes we need to pass on certain values in stored procedures these values are handled as Parameters. Their declaration consists of three parts :

    • Type of parameter
    • Parameter name
    • Data-type of the parameter

    Type of Parameter

    There are three types of parameters :

    • IN - This is by default so in case type is not declared its taken as in. As the name is indicative IN type parameters only accept values
    • OUT - These type of variables can be used if the values need to be passed back to calling block/statement
    • INOUT - This can pass the vale to the stored routine and take back an updated value back to calling block/statement

    Parameter Name

    Parameter name follows the same naming conventions as followed while creating database objects.

    Data-type of the parameter

    All the data-types that are allowed to create tables can be used to define the parameters in stored routines

    Lets take an example to understand the parameters better.

    We want to know by how much amount the salary will be incremented for a particular employee.

    DELIMITER $$
    
    CREATE  PROCEDURE `salary_increment_expected`(in increment_percent decimal(5,2), in employee_id int(11),out increment decimal(10,2))
    Begin
    select salary*increment_percent/100 into increment from employee_master where id=employee_id;
    end$$
    
    DELIMITER ;
    

    The above code creates a stored procedures with three parameters two with type in and one with type as out While calling this stored procedure the first two will have values passed and third will have the parameter passed which will get the value OUT from the stored procedure.

    To test execute :

    call salary_increment_expected(5,1,@increment);
    
    select @increment;
    

    Output will be something similar as shown in attached image.


    result

    Lets see an example of INOUT. We will create a stored procedure to check what is the expected new salary after an increment.

    DELIMITER $$
    CREATE PROCEDURE `salary_expected`(in increment_percent decimal(5,2), inout salary decimal(10,2))
    Begin
    select salary*(1+increment_percent/100) into salary;
    end$$
    DELIMITER ;
    

    The above code creates a stored procedures with two parameters one with type in and one with type as inout . While calling this stored procedure both parameters will have values passed but the second one will get the new value OUT as well.

    To test the same execute the following

    select 5000 into @salary;
    call salary_expected(5,@salary);
    select @salary;
    

    The result will be something as shown in the below image


    result2


    Previous
    Manage 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: