1. Introduction
This is due to the fact that in many cases we want to save the query so that we can use it again and again.
It could be because the query is long and difficult to understand for you, so it is difficult to remember, or because it has too many nearly identical table names but the join order also causes a headache ….
So the idea of the Stored procedure originates from this!
Basically it looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DELIMITER $$ CREATE PROCEDURE GetUsers() BEGIN SELECT userName, sex, address FROM users ORDER BY userName; END$$ DELIMITER ; |
DELIMITER to change the separator between statements in Mysql, this article I will speak more clearly!
2. How the Stored procedure operates
In order to use it again, the Mysql server will need to save it with an alias specified by the creator. All you need to do with it will only be through this alias.
When first calling the Stored procedure:
Mysql will look in the directory of that database.
-> if any, compile its sql code.
-> put it in Mysql cache area
-> finally execute it.
If during the same DB connection session that calls back later, the Stored procedure will be retrieved from the cache without having to compile.
Stored procedure allows us to pass specific parameters to customize our sql content. Like the function declaration: getUser (id) for example.
Another thing is we can call the nested Stored procedure, ie inside the sql code of this Stored procedure can call another Stored procedure, modularize it.
3. Analyze advantages and disadvantages whether you should
3.1. Pros
- The required traffic through a connection is minimal.
We still know that the application connects to the database via the connection, and of course, for Mysql to execute, you must give it the entire sql source, everything goes through that connection that few people care about. how good it is to endure.
When using the Stored procedure, everything is encapsulated by passing alias and parameters, if any.
This will make the connection feel crazy like the way you chat realtime using the socket (of course, the socket does not reduce user data but the data of the transfer method =)))
- Take advantage of reduced effort if you are the service provider.
If you are a service provider then of course there will be operations that customers run over and over again, other than some of the distinguishing features such as id or area, why not use it? There is a beautiful day that your company reached the milestone of 1 million customers, how about it, unfortunately because of the junior design of the junior before that, every time you go to the main screen, he automatically runs a few 3 queries .. .
So we can create Stored procedure, and everything becomes much easier, even if there are new customers, there is no need to rewrite the code, the overload of the connection also helps.
- Create a more secure database.
The administrator can specify the right to see which applications can use data resources through Stored procedure.
This is verified by Mysql via the user account. A database will probably have multiple accounts on it, but depending on the purpose of the parties we may just want to use it for our own account. Meanwhile, the person who understands it will apply to create awesome and those who do not understand it will not be used (yaoming)
3.2. Nhược
- If Stored procedure is overused, the amount of memory to cache it will increase significantly. Thus, even if it is not yet die, the speed is also stagnant depending on the level, maybe slower than not using.
- Overuse of arguments for the Stored procedure also makes everything useless.
In addition to the code inside your sql can be very complex, even now and in the future, it is worse to maintain.
Mysql is not well designed for handling too much logic. This will push that part of the process to the CPU stage. At this point, your system will be able to hang always, not to mention every function related to that database.
- When the sql code inside the Stored procedure encounters a problem, debugging will not be easy.
To put into use the Stored procedure, the sql is often long or complicated, and like the usage, in the application we just call its alias is done. So if there are many parties using different params, why, you will not be able to log final sql in server code. So use your head to generate sql =))
- Maintain can be quite complicated.
Stored procedures can greatly affect the system, so once it has a certain role, its modification needs to be scrutinized. Moderate development and expansion of the system while maintaining the old features.
Over
After reading this article, I hope everyone will have an overview of the Stored procedure.