Stored Procedure

How to use MySQL Stored Procedures

A Stored Procedure is a precompiled object stored in the database. It is a method to encapsulate repetitive tasks . It allows for variable declaration, flow control and other useful programming techniques.

Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statement and returns values. You can create a stored procedure with CREATE PROCEDURE statement. When the CREATE PROCEDURE statement executed, the server compiles the procedure and saves it as a database object. The process of compiling a stored procedure involved in following steps.

  • The procedure is compiled and its components are broken into various pieces. This process is known as parsing.
  • The existence of the referred objects, such as tables and views are cached. This process is called resolving.
  • The procedure is compiled and blueprint for how the query will run is created. This blueprint is specified as execution plan. The execution plan is saved in the procedure cache.
  • When the procedure is executed for the first time ,the execution plan will be read and fully optimized and the run. The next time the procedure is executed in the same session,it will be read directly from the cache. This increases performance as there is no repeated compilation.

Creating Stored Procedures

As previously discussed we can create procedure with CREATE PROCEDURE statement. Below is the syntax :

¬†To demonstrate a basic example of stored procedure, let’s start by creating a database that we can use for testing purposes.

Next, create a table in the RECRUITMENT database named t_newspaper. To do this, run the following SQL statement:

The following SQL statement adds some sample data to the t_newspaper table:

To create this stored Procedure, run the following MySQL statements:

To invoke the stored procedure, use the following MySQL statement:

Altering a Stored Procedure

A stored procedure can be modified by ALTER PROCEDURE statement

Dropping a Stored Procedure

You can drop a stored procedure from  database by using DROP PROCEDURE statement.

Parameterized stored procedure

Parameters are used to pass values to the stored procedure during run time.These values can be passed by using standard variables.The parameter that passes the values is defined as input parameter. Each parameter has a name,data type and a default value.

To demonstrate we will create a stored procedure through which we will insert , Update and select data to our t_newspaper table.

To invoke the insert statement, use the following MySQL statement:

To invoke the Update statement, use the following MySQL statement:

To invoke the select statement, use the following MySQL statement:

Rasmi Ranjan

A tech guy who loves to learn code. I'm open source enthusiast and a delicious coffee maker

Leave a Reply

Your email address will not be published. Required fields are marked *