The differences between database stored procedures and functions, their pros and cons, and the words of each family

Excerpt from the View:

1. Stored procedure (Stored Procedure): A set of programmable functions that are created and saved in a database for the purpose of completing a specific set of SQL statements, and the user can invoke execution by specifying the name of the stored procedure and given the parameters (when needed).
2. A stored procedure is typically executed as a separate part (EXECUTE statement execution), and the function can be invoked as part of a query statement (select Call), since the function can return a Table object, so it can be located in the query statement after the FROM keyword. Stored procedures are not available in SQL statements, and functions can be used. Functions can be embedded in SQL and can be called in a select, and stored procedures do not work.
3. Database to concentrate on data access is enough, the business is to the program to achieve! Don't put everything in the business layer into the database. The internet basically does not use stored procedures
4. Because of the complexity of database development and optimization, DBAs simply do not believe that people who write business logic can write good SQL (while not trusting ORM), and business logic developers do not want to study how deep the water is behind the schema, so the stored procedure solves the problem of division of responsibilities very well.
5. Rapid development of stored procedures, easy to upgrade, do not replace the business version, run down the data upgrade script on the line. Participated in a large project business, the business logic is mostly Oracle PL/SQL stored procedure implementations. Later performance encountered bottlenecks, hot query app code rewrite again.
6. Once a stored procedure has been debugged, it can run stably, which is relatively stable and deterministic for a period of time, and the stored procedure greatly reduces the interaction between the business system and the database, which reduces the coupling between the business system and the database, for example, even if the business system and application system are not in the same city, The impact on performance is also controllable (100 SQL statements interact once, even if the delay is increased from the same city 1ms to an offsite 50ms, also just increase 49ms, if the interaction 100 times, then increase 4900ms). In the Internet industry, where stored procedures are rarely used, one important reason is the widespread use of MySQL, while the MySQL stored procedure is weak (compared to a commercial database) and has a certain relationship with the changes in the Internet industry.

Citation Source:

Mysql stored procedure and function differences http://www.cnblogs.com/youxin/p/3568379.html

MySQL Stored procedure _ Create-call-parameter http://www.cnblogs.com/geaozhang/p/6797357.html

Are stored procedures used more in actual projects? https://www.zhihu.com/question/54408187

The differences between database stored procedures and functions, their pros and cons, and the words of each family