Store Procedure

Store Procedure

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers , other stored procedures or applications such as Java, C#, PHP, etc.

A stored procedure that calls itself is known as a recursive stored procedure. Most database management system supports recursive stored procedures.

MySQL stored procedures advantages

  • Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it to a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise the stored procedure works like a query.
  • Stored procedures helps reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.
  • Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures.
  • Stored procedures are secure. Database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permission on the underlying database tables.

Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before using the store procedures.

MySQL stored procedures disadvantages

  • If you use a lot of stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside store procedures, the CPU usage will also increase because database server is not well-designed for logical operations.
  • A constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic.
  • It is difficult to debug stored procedures. Only few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
  • It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.

How to Create Store Procedure

MySQL stored procedure by using CREATE PROCEDURE statement.

  • Open Localhost and Select PhpMyAdmin for Mysql Database
  • Select Database
  • Click on SQL Tab Editor

Syntax

DELIMITER $$
CREATE PROCEDURE procedure Name
BEGIN
    statement;

END

Example

DELIMITER $$
CREATE PROCEDURE sp()
BEGIN
    select * from emp;
END

Calling or Fetching stored procedures

In order to call a stored procedure, you use the following SQL command:

CALL STORED_PROCEDURE_NAME()

Example:Fetching store Procedure Data Using PHP

$sql=mysqli_query($mysqli,"CALL sp()");

while($row=mysqli_fetch_array($sql))
{
echo $row['lastName'].$row['firstName'].'
'; }

Drop Store Procedure

Drop statement used for delete the store procedure

Syntax

DROP PROCEDURE IF EXISTS Procedure Name

Example

DROP PROCEDURE IF EXISTS sp

Show The List Of Store Procedure

To list all stored procedures of the databases that you have the privilege to access, you use the SHOW PROCEDURE STATUS statement as follows:

SHOW PROCEDURE STATUS;
If you want to show just stored procedure in a particular database, you can use the WHERE clause in the SHOW PROCEDURE STATUS statement:

SHOW PROCEDURE STATUS WHERE db = 'shiksha360';
If you want to show stored procedures that have a particular pattern e.g., its name contains product, you can use the LIKE operator as the following command:

SHOW PROCEDURE STATUS WHERE name LIKE '%sp%'

Displaying stored procedure’s source code

To display source code of a particular stored procedure, you use the SHOW CREATE PROCEDURE statement as follows:
Syntax

SHOW CREATE PROCEDURE stored_procedure_name;

Example

SHOW CREATE PROCEDURE sp

Admin has written 171 articles