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
- 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.
- OUT: This indicates that this parameter can be used to assign a value that will be returned from the calling program
- 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.
Chandra Shekhar
Latest posts by Chandra Shekhar (see all)
- Best practices for micro service design - January 23, 2022
- Spring Boot - January 23, 2022
- Java - January 23, 2022
Recent Comments