Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • MySQL Stored Procedure

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 727
    Comment on it

    MySQL Stored Procedure

    Stored Procedure

    1)Stored Procedure are a set of declarative SQL statements which when created are compiled and stored in database.The main reason for their creation is that they are reusable. Stored procedure work differentlty in MySQL, in this Stored procedures are compiled when they are on high demand i.e when they are used frequently. After compiling frequently used stored procedure, MySQL puts compiled version into a cache. For every single connection separate cache of stored procedure is maintained. If in a single connection stored procedure is used frequently then compiled version of stored procedure is used otherwise it works like a query.

    2)Stored procedures can be invoked by other stored procedure, triggers or application like java, python etc. A stored procedure can call itself called recursive stored procedure.

    3)The traffic between application and database server is reduced because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.

    4)The developers dont have to develop functions that are already supported in stored procedures as database interface is visible to all applications.

    5)Stored procedure are executed explicitly using by using CALL command.

    Syntax for creating procedure

    CREATE PROCEDURE PROCEDURE-NAME()
       BEGIN
    
       //A set of declarative SQL statements
    
       END 
    

    This command will be executed and stored procedure will be stored in database.

    Declaring a variable in Stored Procedure

    DECLARE variable-name datatype(size) DEFAULT default-value;
    

    Assigning a value to variable:

    SET variable-name = value;
    

    MySQL stored procedure parameters:

    Syntax for defining a parameter in the stored procedures:

    MODE parameter-name parameter-type(parameter-size)
    

    MODE ARE OF THREE TYPES:

    1)IN: IN mode is the default mode. IN parameter in stored procedure causes calling program to pass an argument to stored procedure and its value is protected i.e stored procedure work on copy of IN parameter value, it can be changed inside stored procedure but original value is retained when stored procedure ends.

    2)OUT:OUT value can be changed inside Stored procedure but is passed back to calling program. When stored procedure starts initial value of OUT parameter cannot be accessed.

    3)INOUT: Combination of IN and OUT parameter thereby combining their functionality.

    Example of Stored Procedure:

    CREATE PROCEDURE GetEmployeeByDepartment(IN departmentName VARCHAR(255))
     BEGIN
     SELECT * 
     FROM Employee
     WHERE department = departmentName;
     END
    

    Explanation:

    The departmentName is the IN parameter of the stored procedure. Inside the stored procedure, we select all employees that are in particular department specified by the departmentName parameter. When calling this stored procedure an IN parameter is passed.

    Stored Procedure is called as:

    CALL  GetEmployeeByDepartment('sales')
    

 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: