[Communication] Why use stored procedures in sql, and what are the advantages?

In my opinion, stored procedures are simply a bunch of SQL mergers. A little logic control is added in the middle.

However, stored procedures are more useful when dealing with more complex businesses.
For example, a complex data manipulation. If you handle it at the front desk. Multiple database connections may be involved. But if you use stored procedures. Only once. There is an advantage in terms of response time.
In other words, stored procedures can bring us the benefits of improved operational efficiency.
In addition, programs are prone to bug instability, and stored procedures, as long as there are no database problems, there will be basically no problems. In other words, in terms of security, the system that uses stored procedures is more stable.

Projects with small amounts of data or have nothing to do with money can function normally without stored procedures. MySQL's stored procedures have yet to be tested in practice. If it is a formal project, it is recommended that you use SQL Server or Oracle stored procedures. When dealing with data to data, the process is much faster than a program. The interviewer asked if there was any storage, in fact, he wanted to know if the programmer who came to the interview had done a project with a large amount of data. If it is trained, or a small project or a small company, there will definitely be less contact with storage.

Therefore, if you want to enter a large company, you can't do it without rich experience in storage processes.

So when can you use storage? For small projects that are not very large in data volume and not very complex in business processing, are they not necessary?
Wrong. Stored procedures are not only suitable for large projects, but also for small and medium-sized projects, using stored procedures is also very necessary. Its power and advantages are mainly reflected in:
  1. Stored procedures are only compiled at the time of creation, and there is no need to recompile them every time the stored procedure is executed in the future, while general SQL statements are compiled every time they are executed, so using stored procedures can improve the execution speed of the database.
  2. When performing complex operations on the database (such as Update, Insert, Query, and Delete multiple tables), this complex operation can be encapsulated in stored procedures and used in conjunction with the transaction processing provided by the database. These operations, if done programmatically, become SQL statements that may require multiple connections to the database. Instead of storage, you only need to connect to the database once.
  3. Stored procedures can be reused, which reduces the workload of database developers.
  4. High security, only this user can be set to have the right to use the specified stored process.