我总是再次遇到关于将业务逻辑放置在何处的讨论：在应用程序代码的业务层内，还是在存储过程的数据库中。我个人倾向于第一种方法，但我想先听听你的一些意见，而不是用我个人的观点影响你。我知道不存在一刀切的解决方案，它通常取决于许多因素，但我们可以对此进行讨论。
顺便说一句，我们处于 Web 应用程序（具有 Oracle DB）的上下文中，我们当前的方法是让
- UI 层接受 UI 输入并执行第一个客户端验证
- 业务层，其中包含许多服务类，其中包含业务逻辑，包括用户输入验证（服务器端）
- 数据访问层，它从数据库调用存储过程来执行持久性/读取操作
然而，许多人倾向于将业务层内容（特别是与验证有关）移至数据库存储过程。
你对此有何看法？我想讨论一下。
I always again encounter discussions about where to place the business logic: inside a business layer in the application code or down in the DB in terms of stored procedures. Personally I'd tend to the 1st approach, but I'd like to hear some opinions from your part first, without influencing you with my personal views. I know there doesn't exist a one-size-fits-all solution and it often depends on many factors, but we can discuss about that.
Btw, we are in the context of web applications (having an Oracle DB) and our current approach is to have
- UI layer which accepts UI input and does a first, client-side validation
- Business layer with a number of service-classes which contains the business logic including validation for user input (server-side)
- Data Access Layer which calls stored procedures from the DB for doing persistency/read operations
Many people however tend to move the business layer stuff (especially regarding the validation) down to the DB in terms of stored procedures.
What do you think about it? I'd like to discuss.
发布评论
评论（4）
无论是否有人使用您的应用程序、其他应用程序或 SQL 工具，让数据保持理智本身就很有价值。
得到一个永远不应该为 NULL 的值？ - 好，设计数据库来执行该规则。两个表之间是否存在应始终存在的关系？ - 很好，添加一个外键约束。
是否有在您的问题域中应该唯一的值？ - 很好，添加一个唯一约束。是否有一个始终应在 6-10 个字符之间的字符串？ - 好，添加一个检查约束。
这些都是基本的，易于添加到数据库中，并且让您有一定程度的信心，当您的应用程序尝试从数据库中加载有人手工破坏的内容时，它不会崩溃。在某种程度上它们可以被认为是业务逻辑。 （毕竟，您是从有关您的问题域的具体事实中得出所有这些的）。
因此，在某种程度上，我会将这种业务逻辑放入数据库中。是的，在您的应用程序中，您需要应用类似的检查，以提供更愉快的用户体验。但我宁愿让我的应用程序在尝试将无效内容放入数据库时失败（作为最后的手段），也不愿在 6 个月后发现这个事实。
Having the data keep itself sane whether or not someone's using your application, another application, or a SQL tool can be valuable in itself.
Got a value that should never be NULL? - good, design the database to enforce that rule. Got a relationship between two tables that should always exist? - good, put a foreign key constraint in.
Got values that should be unique in your problem domain? - good, put a unique constraint in. Got a string that should always be between 6-10 characters? - good, add a check constraint.
These are all basic, easy to add to the database, and give you a level of confidence that your application isn't going to fall over when it tries to load something from the database that someone has mangled by hand. And to some extent they can be considered business logic. (After all, you're deriving all of these from concrete facts about your problem domain).
So to that extent, I would put that sort of business logic in the database. Yes, up in your application, you'd want to apply similar checks, to give a more pleasant user experience. But I'd rather have my application fall over (as a last resort) when it tries to put something invalid into the database than to discover this fact 6 months later.
我只见过一种情况，存储过程中的逻辑是有意义的；基本上它与性能相关：需要移动和处理大量数据。可取之处在于逻辑并不复杂——但 SP 仍然是一场噩梦。人们认为在应用程序代码中执行此操作太慢。
所以猜测 - 这可能是 50 多个项目中的一个？
您定义的因素将是：
I've only seen one case where logic in the Stored Procs made sense; basically it was performance related: lots of data to move and crunch. The saving grace was that the logic wasn't hugely complex - but the SP was still a nightmare. It was deemed too slow to do it in the app code.
So at a guess - that's maybe a 1 in 50+ project senario?
You're defining factors are going to be:
数据库中的逻辑是维护的噩梦。在真正需要的情况下，应该很好地记录它，并将其以文本格式与其他源代码放在一起。
Logic in the database is a maintenance nightmare. In the occasions where it's really needed one ought to document it really well and place it in text format together with the other source code.
通常由于各种原因它很糟糕。如果您从事面向对象的工作，那么存储过程就不是逻辑的好地方 - 因为您的对象不再存在于那里。一个对象可能位于多个表中。
第二。 SQL 是一种非常糟糕的语言，无法用它来编写复杂的逻辑。它还没有完成——这就是 SQL Server 允许用 .NET 编写 SP 的原因之一。尝试在 SQL 中计算哈希，您就会明白我的意思 - 各种字符串操作是另一个领域。脏得要命。
SP 通常都是用愚蠢的论点来完成的。就像人们为自己辩护而提出的论点一样愚蠢，根本就不是事实。 Frans Bourma 列出了最常用的谬论，并很好地解释了为什么这些论点大多是愚蠢的胡言乱语，网址为 http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx - 是的，这是这种程度的白痴（就像人们甚至不阅读文档或思考他们实际上所说的内容，以及所有的后果）。
我个人拥有的带有存储过程的系统有限，我拥有的系统是： 有限的复杂性，但高性能。基本上没有继承，因为对象模型很简单，SP 中的事务逻辑并不太复杂，而且我需要/想要极小的锁定速度，因此某些操作被移至存储过程中。最重要的是，这个特定的应用程序也有一个非常不寻常的对象模型（对象是从各种源动态流式传输的，从不更新，总是被替换，并且所有更改都必须通过服务而不是在对象上完成 - 有时因为更改是在另一个组织的另一个国家的另一台计算机上“要求”
一个很好的例子是高性能的会计系统（因为它正在跟踪来自全自动交易系统的交易）。 。
现在，存储过程的坏处也有很多工具方面的问题，没有适当的测试框架，没有模拟框架，源代码控制有点尴尬（但可行） 集成调试？好吧，我非常感谢 Microsoft 和 Visual Studio - 这确实有效（存储过程中的断点 - 非常好）
。完全无聊的争论 - 实际上是“员工应该被解雇”的思维水平的体现。也许他们就在那里，但我没有看到他们。
It normally sucks for various reasons. If you work object oriented, then stored procedures are not eaxactls a good place for logic - because your objects don't exist there anymore. An object may be in multiple tables.
Second. SQL is a freaking bad langauge to code complex logic in. It just is no tdone for that - one reason SQL Server allows SP's to be written in .NET. Try calculating a hash in SQL and you will understand what I mean - all kinds of string manupulations are another area. Dirty as hell.
SP's in general are pretty often done with idiotic arguments. Idiotic like the arguments the people bring to defend them are simply not true at all. Frans Bourma has a list of the most often used fallacies anda good explanation why the arguments are mostly stupid ramblings at http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx - and yes, it is this level of idiocy (like people not even reading the documentation or thinking about what they actually say, in all the consequences).
I personally have limited systmes with stored procedures, and the ones I have are: Limited complexity, but high performance. Basically no inheritance because the object model is simple, the transactional logic in the SP's is not overly complex AND I need/want extreme small locking speed, so certain operations are moved into stored procedures. On top, this particular application has a very unusual object model, too (objects aare dynamically streamed from various sources, never updated, always replaced, and all changes HAVE to go through services and not be done on the object - sometimes because a change is "asked for" on another computer in another country in anothe organization.
A good example is an accounting system which is high performance (because it is tracking trades from fully automated trading systems). The logic in every SP is not really complex, but I want to have as little SQL going back and forth as possible.
Now, bad sides of Stored procedures are also a lot tool wise. There is no proper testing framework, no mocking frameowrk, source control itnegration is kind of a little awkward (but doable with the right toolset). Integrated debugging? Well, my great thanks to Microsoft and Visual Studio - that actually works (breakpoint in stored procedure - really nice).
I have yet to see one approach using a lot of stored procedures that was not defended with totally borked arguments - on the level of actually being a demonstration of "employee should be fired" level of thinking. Maybe they are out there, but I have not seen them.