Should I use stored procedures



Baidu Encyclopedia’s definition of stored procedures

Stored procedure is a set of SQL statements to complete specific functions in a large database system. It is stored in the database and is permanently valid after one compilation. Users execute it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). Stored procedure is an important object in database. When the amount of data is very large, the efficiency can be doubled by using stored procedures;


There has always been a lot of debate about whether to use stored procedures in development;

There is such a provision in Alibaba java coding specification




Internet people’s views

I personally agree with this in the Internet industry;


First, it’s really difficult to debug. If you write a lot of business in stored procedures, debugging is easy to read

It’s very painful. Imagine that in hundreds of lines of SQL, you have to understand its meaning one by one;

Sometimes you need to verify right and wrong line by line; In addition, if you want to add functions, you can only continue to heap SQL on it, resulting in a vicious circle;


As for migration, some mechanisms are slightly different in different databases, and SQL still needs to be changed;


Therefore, in the Internet industry, the use of stored procedures is generally not advocated;


Well, some people will ask that the execution efficiency of stored procedures is high. It is true, but for a little bit of performance

Sacrificing development efficiency and maintainability is not worth the loss; Moreover, the focus of efficiency performance is not so little loss at all; It’s just like some people think that native SQL is highly efficient and it’s the same to reject ORM; Failing to notice the nature of things; give up eating for fear of choking;

Things have two sides

of course,Things have two sides, in some industries, such as traditional management systems, the demand changes greatly,

For objective reasons such as complex business and software productization, a good way to store processes is not lost, such as the implementation of ERP; Most of the time, the implementers who know a little about SQL do it, rather than the developers do the code.