MySQL - Store Procedure

A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database

A stored procedure is a prepared MySQL code that you can save, so the code can be reused over and over again.

So if you have an MySQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

A procedure has a name, a parameter list, and SQL statement(s).

Stored procedures are fast.

Stored procedures are portable.

CREATE PROCEDURE procedure_name
[ (parameter datatype [, parameter datatype]) ]  
BEGIN  
    Declaration_section  
    Executable_section  
END;      

procedure_name: name of the procedure..

Parameter: number of parameters. It can be one or more than one.

declaration_section: all variables are declared.

executable_section: code for the function is written here.

Let's put these statements into real use.

We've a table named Students in our database that contains the following records:

DELIMITER $$  
CREATE PROCEDURE get_students()  
BEGIN  
SELECT * FROM Students where ROLL_NO > 2;  
END$$   

The above Store Procedure runs when we call it. Because store procedure functions are stored in the database.

                    CALL get_students();
            

Now, after executing the above MySQL call statement,

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

DELIMITER $$  
CREATE PROCEDURE get_students( @ROLL_NO int)  
BEGIN  
SELECT * FROM Students where ROLL_NO > @ROLL_NO;  
END$$   

The above Store Procedure runs when we call it. Because store procedure functions are stored in the database.

When we call the store procedure we have to pass a parameter.

 CALL get_students(2);
         

Now, after executing the above MySQL call statement,