Creating MySql Procedure

In todays world of programming where every one is using 3 tier architecture ( like MVC). Its highly desirable that problem should be properly distributed and coded in such a manner that help us to achieve our desired architecture.

In the same context in order to resolve some complex problems we are required to use mysql procedures. To Create a sample procedure you just open your query browser and run the following code:

 

DELIMITER $$

DROP PROCEDURE IF EXISTS square_root$$

CREATE PROCEDURE square_root(input_number INT,OUT output_number FLOAT)

BEGIN

SET output_number = SQRT(input_number);

END$$

DELIMITER ;

 

when you run this query a new procedure is created on your database (make sure you have permission to create procedure for same database otherwise it wouldn’t work)

 

Now to call the procedure you simply use follwoing in your query browser

call square_root(4,@root);

select @root;

 

A Procedure can have three parameters

  1. IN: Which is default mode and it indicates that the parameter can be passed into the procedure but any modification in this is not returned into the calling program.
  2. OUT: This indicates that this parameter can be used to assign a value that will be returned from the calling program
  3. INOUT: Which indicates a procedure can read the parameter and the calling program can see if any modification is being made by calling program . This parameter can be used as either for input or output purpose of the calling program.

Related posts:

The following two tabs change content below.

Chandra Shekhar

GCP Architect
Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video... Read More about Chandra Shekhar

Latest posts by Chandra Shekhar (see all)