Stored Procedure in MySQL
Using MySQL had one setback – lack of stored procedures. But, we had celebrated the farewell of this hindrance after welcoming MySQL 5.0.
Stored procedure is a collection of valid SQL statements residing in the data server. It may or may not return value based on our need.
Stored procedure should be our first choice when we have a choice to play with them – because:
- Our application would be fast due to decreased use of network
- Logics can be used more than once
- Maintenance is simple – just edit at one place… and even more
I will try to use simple example to demonstrate how to use stored procedure in MySQL. I will use MySQL CLI for the illustration.
Syntax: CREATE PROCEDURE procname(parameters)
BEGIN
logic goes here
END
Example: simple procedure to collect rows of a table:
Lets create a table first:
USE dbTest;
CREATE TABLE member (Id int not null auto_increment primary key, first_name nvarchar(12), last_name nvarchar(12) );
Say we want to collect all rows from the above table using stored procedure:
DELIMITER **
CREATE PROCEDURE selectAll()
BEGIN
SELECT * FROM member;
END
DELIMITER ;
The above snippet would create a stored procedure named selectAll() on the dbTest database.
DELIMITER ** would change the default delimiter of the CLI which is ‘;’ to **. I have to change the delimiter from ‘;’ to ‘**’ or any other character except ”. Otherwise, when the execution gets ‘;’ in the middle of execution, it will halt – and finally we would return the default delimiter at the end.
Using the stored procedure would be:
CALL selectAll(); or CALL selectAll; –> which will list all the rows of the table.
What about when we have parameters?
Get maximum occurring character
Flatten nested javascript array
Implement Queue Using two Stacks – JavaScript algorithm
Update Node and npm on mac OSX
Tunneling to AWS instance using pem file