1. What is a stored procedure

Stored Procedure (Stored Procedure) is in the database, a set of SQL statements to complete a specific function, it is stored in the database, once compiled permanently effective, the user specifies the name of the stored procedure and gives parameters (optional) to implement

Advantages of stored procedures

• Precompile SQL to improve execution efficiency

• Execution logic can be hidden, only names and parameters are exposed

• Compared with the program, it is more convenient to modify

Disadvantages of stored procedures

• As the number of SQL rows increases, the maintenance complexity increases linearly

• Unable to debug, high risk during iteration

Second, the use of stored procedures

Improve delivery efficiency

This is also the advantage of the stored procedure: stored in the database, when the logic needs to be modified, only need to connect to the database, modify and save, if the logic is written in the program, then it needs to be compiled, packaged, deployed, especially deployed The process will be more troublesome. If it is a single server, the user's use may be affected during the release process. If it is multiple servers, then it needs to be released one by one.

In contrast, modifying stored procedures may save more than 30 minutes of time than modifying programs

For database development

All relational databases have a complete user authority system. In the relatively early ToB software system, the logic with certain commercial value will be provided in the form of a stored procedure. In the software, only the name and parameters of the stored procedure exist. Developers and Party A's secondary development and docking personnel do not have access to relevant logic, and this stored procedure is encrypted (SQL Server, Oracle is supported), and the program can only access and execute the stored procedure, and the program accesses the database The account has only the execution permission of the stored procedure and no other permissions.

In this way, the development speed can also be accelerated through the division of labor between DBA and Developer. However, this is a thing of the past. Now ToB systems are basically on the cloud.

3. Do you want to use stored procedures?

simple business system

If you are developing a small system with few business processes, the overall project has only one site or software, and the number of users and data in this system are very small (for example: personal blog, corporate website, work order system, etc.), then put The logic is written in the storage process. If you encounter problems, you can quickly fix them. In most cases, the advantages outweigh the disadvantages.

complex business system

If you are developing a system with many business processes, whether it is ToC's e-commerce system, or ToB's ERP, CRM, HRM, no matter how much data the system carries, I do not recommend using stored procedures to implement business logic, because There are many business processes in such a system. If most of the business logic is written in the stored procedure, with the development of the system and business, the stored procedure with more than a dozen lines may develop into dozens, hundreds or even thousands of lines. , so for maintenance, who is bald~

It’s very simple. There is no ready-made debugging function for stored procedures. If the syntax is wrong during the update process, the SQL designer will help you intercept it, but the syntax is fine, and the logic is wrong. After saving, it will generate a bunch of dirty data.
Just repairing the data can break people down, not to mention causing business losses, and that's real money gone

In addition, with the development of the system, if you have a single database and single table, as the number of users grows, a single database table cannot support the business, then you need to split the database horizontally, and all stored procedures need to be modified and tested at that time, even if The modification test has passed, and there is no problem. Then, in the future, multiple libraries must ensure that the stored procedures have the same logic at the same time. What should I do?

Four. Summary

I am strongly against using stored procedures to execute business logic in commercial projects

Although stored procedures have many advantages and can improve delivery efficiency in simple business systems, in my opinion, this is drinking poison to quench thirst, because business and systems always need to develop. Once the business and data volume develop to a certain level, it is difficult to down, it's too late

If you really want to use stored procedures in your project, then pray that the people who write stored procedures are reliable, the written SQL is easy to read, and you won’t write too complicated logic in stored procedures, which is okay Pray that this business/system will not develop too well, otherwise, sooner or later, the hair will not be enough~

If it's your personal project, it doesn't matter if you use a database or not