Stored Procedure in MySQL 

• ·         A procedure (also called a stored procedure) is a collection of pre-compiled SQL statements, stored inside the database.
• ·         It is a subroutine or a subprogram used for computing.
• ·         It contains a name, parameter lists, and SQL statements.
• ·         It is also used in triggers, other procedures, and applications such as Java, Python, PHP, etc.
• ·         It was first introduced in MySQL- 5.
• ·         A procedure is called a recursive stored procedure when it calls itself. 

 Stored Procedure Features

• Increases the performance of the applications.
• Once stored procedures are created, they are compiled and stored in the database.
• Reduces the traffic between the application and database server. Because the application call stored procedure's name and sends parameters only in place of multiple SQL statements.
• Reusable and transparent to any applications.
• Always secure. The database administrator can grant permissions to applications that access stored procedures in the database without giving any permissions on the database tables.
• Can contain control flow statements (IF, CASE, and LOOP). 

Advantages

The following are the advantages of stored procedures.

·         Reduce network traffic

between applications and MySQL Server. Because it is sending only the name and parameters, not sending multiple lengthy SQL statements of stored procedures.

·         Centralize business logic in the database

It uses business logic that is reusable by multiple applications. It helps to reduce the efforts of duplicating the same logic in many applications and make the database more consistent.

·         Make database more secure

The database administrator can grant appropriate privileges to applications that only access specific stores.

 

Disadvantages

·         resource usages

If use many stored procedures, then the memory usage of every connection will increase.

Too, overusing a large number of logical operations will increase CPU usage.

·         Troubleshooting

It’s difficult to debug stored procedures.

MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server.

·         Maintenances

Developing and maintaining stored procedures requires a specialized skill which does not have all developers.

 

Create a procedure

Syntax:

DELIMITER {custom delimiter} CREATE PROCEDURE {procedure_Name}([optional parameters]) BEGIN     // procedure body...     // procedure body... END {custom delimiter}

 Or

DELIMITER //

CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )

BEGIN

SQL statements

END //

DELIMITER ;

 The procedure syntax has the following parameters:

Parameter Name	Descriptions
procedure_name	Name of the stored procedure.
parameter	The number of parameters. It can be one or more.
Declaration_section	Declarations of all variables.
Executable_section	Represents the code for the function execution.

 

·         MySQL client program to define a stored procedure that contains semicolon characters.

·         It will not treat the whole stored procedure as a single statement, but many statements.

·         Must redefine the delimiter temporarily so that can pass the whole stored procedure to the server as a single statement.

delimiter_character :-

·         A single character or multiple characters e.g., // or $$, avoid backslash (\) because it’s the escape character in MySQL.

·         To change the delimiter to the default one, which is a semicolon (;).

Example:-

DELIMITER ;

 Stored procedures syntax: -

DELIMITER $$

 CREATE PROCEDURE sp_name()

BEGIN

  -- statements

END $$

 DELIMITER ;

 

Procedure parameter modes: three- IN / OUT/ INOUT

IN parameter

·         It is the default mode.

·         Use for input, as an attribute.

·         It is a calling program and passes an argument to the stored procedure.

·         Its value is always protected.

OUT parameters

·         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.

·         Not access the initial value.

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.

 

Calling a stored procedure: -

·         CALL statement used to call a stored procedure and returns the values to its caller through its parameters (IN, OUT, or INOUT).

Syntax:-

CALL procedure_name ( parameter(s))  

 

Example

1.    USE database_name;

2.    student_info table data:

Procedure without Parameter

Example:-

To display all records of this table whose marks are greater than 70 and count all the table rows.

procedure named :-  get_merit_students:

1.    DELIMITER &&  

2. CREATE PROCEDURE get_merit_student ()  

3.    BEGIN  

4.     SELECT * FROM student_info WHERE marks > 70;  

5.        SELECT COUNT(stud_code) AS Total_Student FROM student_info;    

6. END &&  

7.    DELIMITER ;  

Call to procedure:-

CALL get_merit_student();  

 

outputs:

Procedures with IN Parameter

Procedure code:

1.    DELIMITER &&  

2. CREATE PROCEDURE get_student (IN var1 INT)  

3.    BEGIN  

4.     SELECT * FROM student_info LIMIT var1;  

5.        SELECT COUNT(stud_code) AS Total_Student FROM student_info;    

6. END &&  

7.    DELIMITER ;  

 

After successful execution, call the procedure:

CALL get_student(4);  

 

Output:

Procedures with OUT Parameter

procedure code:

1.    DELIMITER &&  

2. CREATE PROCEDURE display_max_mark (OUT highestmark INT)  

3.    BEGIN  

4.     SELECT MAX(marks) INTO highestmark FROM student_info;   

5.    END &&  

6. DELIMITER ;  

 Pass value to a session variable @M in the CALL statement as follows:

CALL display_max_mark(@M);  

SELECT @M;  

 output:

Procedures with INOUT Parameter

procedure code:

1.    DELIMITER &&  

2. CREATE PROCEDURE display_marks (INOUT var1 INT)  

3.    BEGIN  

4.     SELECT marks INTO var1 FROM student_info WHERE stud_id = var1;   

5.    END &&  

6. DELIMITER ;  

 After successful execution, call the procedure:

SET @M = '3';  

CALL display_marks(@M);  

SELECT @M;  

output:

Show or list all the stored procedures:-

Syntax:-

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]  

SHOW PROCEDURE STATUS WHERE db = 'mystudentdb';  

If want to list of specific word stored procedures, use the LIKE clause.

 Output :

Delete/drop stored procedures in MySQL

When the procedure is dropped, it is removed from the database server also.

syntax:

DROP PROCEDURE [ IF EXISTS ] procedure_name;    

Example:-

DROP PROCEDURE display_marks;  

 output:

=================================================================