MySql : Stored Procedure

 MySQL Stored Procedure

A procedure (often called a stored procedure) is a collection of pre-compiled SQL statements stored inside the database. It is a subroutine or a subprogram in the regular computing language. A procedure always contains a name, parameter lists, and SQL statements.

Below is the syntax

DELIMITER &&  

CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter datatype]) ]    

BEGIN    

    Declaration_section    

    Executable_section    

END &&  

DELIMITER ;

MySQL procedure parameter has one of three modes:

IN parameter

It is the default mode. It takes a parameter as input, such as an attribute. When we define it, the calling program has to pass an argument to the stored procedure. This parameter's value is always protected.

OUT parameters

It is used to pass a parameter as output. Its value can be changed inside the stored procedure, and the changed (new) value is passed back to the calling program. It is noted that a procedure cannot access the OUT parameter's initial value when it starts.

INOUT parameters

It is a combination of IN and OUT parameters. It means the calling program can pass the argument, and the procedure can modify the INOUT parameter, and then passes the new value back to the calling program.

Example 1

DELIMITER &&  

CREATE PROCEDURE get_merit_student ()  

BEGIN  

    SELECT * FROM student_info WHERE marks > 70;  

    SELECT COUNT(stud_code) AS Total_Student FROM student_info;    

END &&  

DELIMITER ;  

Example 2

DELIMITER &&  

CREATE PROCEDURE get_student (IN var1 INT)  

BEGIN  

    SELECT * FROM student_info LIMIT var1;  

    SELECT COUNT(stud_code) AS Total_Student FROM student_info;    

END &&  

DELIMITER ;  

Example 3

DELIMITER &&  

CREATE PROCEDURE display_max_mark (OUT highestmark INT)  

BEGIN  

    SELECT MAX(marks) INTO highestmark FROM student_info;  

END &&  

DELIMITER ;  

1. 
   
2. 
   
3. 
   
4. mysql> CALL display_max_mark(@M);  
5. mysql> SELECT @M;  

Some More Example