何时使用存储过程而不是使用任何具有编程逻辑的 ORM？
大家好，我想知道什么时候我应该更喜欢编写存储过程而不是编写编程逻辑和使用 ORM 或其他东西提取数据。
Hi all I wanted to know when I should prefer writing stored procedures over writing programming logic and pulling data using a ORM or something else.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
存储过程在服务器端执行。
这意味着处理大量数据不需要通过网络连接传递这些数据。
此外，通过存储过程，您可以构建一致的复杂业务逻辑。
比如说，您每次插入交易时都需要更新帐户余额，并且需要一次插入许多交易。
您可以传递带有输入的表变量或临时表，然后发出基于集合的
SQL语句，而不是使用触发器（在许多系统中使用低效的逐条记录方法实现）来执行此操作程序内部。这样效率会高很多。Stored procedures are executed on server side.
This means that processing large amounts of data does not require passing these data over the network connection.
Also, with stored procedures, you can build consistent complicated business logic.
Say, you need to update the account balance each time you insert a transaction, and you need to insert many transactions at once.
Instead of doing this with triggers (which are implemented using inefficient record-by-record approach in many systems), you can pass a table variable or temporary table with the inputs and issue a set-based
SQLstatement inside the procedure. This will be much more efficient.与编程逻辑相比，我更喜欢 SP，主要有两个原因
Row_Delete而不是DELETE FROM Rows听起来不错。I prefer SPs over programming logic mainly for two reasons
Row_Deletefor single each of them instead ofDELETE FROM Rowsalready sounds good.除非您发现性能问题，否则永远不要。 （主要是意见）
（杰夫的博客文章！）
http://www.codinghorror.com/ blog/2004/10/who-needs-stored-procedures-anyways.html
如果您将存储过程视为优化：
http://en.wikipedia.org/wiki/Program_optimization#When_to_optimize
Never unless you identify a performance issue. (largely opinion)
(a Jeff blog post!)
http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html
If you see stored procs as optimizations:
http://en.wikipedia.org/wiki/Program_optimization#When_to_optimize
适当的时候。
您不能说“从不”或“总是”。
还有一种情况是数据库引擎的寿命比客户端代码的寿命长。我敢打赌，除了数据库引擎升级/重构之外，还有更多的 DAL 或 ORM 升级/重构正在进行。
最后，为什么我不能将代码封装在存储过程中？这不是一件好事吗？
When appropriate.
You can't say "never" or "always".
There is also the case where the database engine will outlive your client code. I bet there's more DAL or ORM upgrades/refactoring that DB engine upgrades/refactoring going on.
Finally, why can't I encapsulate code in a stored proc? Isn't that a good thing?
与以往一样，您对使用哪个的决定很大程度上取决于您的应用程序及其环境。
这里有几个思想流派，这场辩论总是引起双方的强烈情绪。
存储过程（以及 Quassnoi 提到的大数据移动）的优点是逻辑被束缚在数据库中，因此可能更安全。它也只存在于一个地方。
然而，也有人认为应用程序逻辑的位置应该在应用程序中，特别是如果您计划访问其他类型的数据库（您将不得不为此编写经常不同的 SP）。
另一个考虑因素可能是您实施应用程序所拥有的资源的技能。
As ever, much of your decision as to which to use will depend on your application and its environment.
There are a couple of schools of thought here, and this debate always arouses strong sentiments on both sides.
The advantanges of Stored Procedures (as well as the large data moving that Quassnoi has mentioned) are that the logic is tied down in the database, and therefore potentially more secure. It is also only ever in one place.
However, there will be others who believe that the place for application logic should be in the application, especially if you are planning to access other types of datebases (for which you will have to write often different SPs).
Another consideration may be the skills of the resources you have to implement your application.
当多个应用程序与同一个数据库进行通信时，存储过程变得比 ORM 更可取。此时，您希望将查询逻辑嵌入到一个位置，而不是每个应用程序一次。即使在这里，您可能更喜欢服务层（可以水平扩展）而不是数据库（只能垂直扩展）。
The point at which stored procedures become preferable to an ORM is that point at which you have multiple applications talking to the same database. At this point, you want your query logic embedded in one place, rather than once per application. And even here, you might want to prefer a service layer (which can scale horizontally) instead of the database (which only scales vertically).