oracle 业务逻辑：数据库或应用层

Business Logic: Database or Application Layer

提问by Matthew Watson

The age old question. Where should you put your business logic, in the database as stored procedures ( or packages ), or in the application/middle tier? And more importantly, Why?

古老的问题。您应该将业务逻辑作为存储过程（或包）放置在数据库中还是应用程序/中间层中？更重要的是，为什么？

Assume database independence is not a goal.

假设数据库独立性不是目标。

采纳答案by Damien_The_Unbeliever

Put enough of the business logic in the database to ensure that the data is consistent and correct.

在数据库中放入足够多的业务逻辑，以保证数据的一致性和正确性。

But don't fear having to duplicate some of this logic at another level to enhance the user experience.

但是不要害怕必须在另一个级别复制某些逻辑以增强用户体验。

回答by Ash

Maintainability of your code is always a big concern when determining where business logic should go.

在确定业务逻辑应该去哪里时，代码的可维护性始终是一个大问题。

Integrated debugging tools and more powerful IDEs generally make maintaining middle tier code easier than the same code in a stored procedure. Unless there is a real reason otherwise, you should start with business logic in your middle tier/application and not in stored procedures.

集成调试工具和更强大的 IDE 通常比存储过程中的相同代码更容易维护中间层代码。除非有真正的原因，否则您应该从中间层/应用程序中的业务逻辑开始，而不是在存储过程中。

However when you come to reporting and data mining/searching, stored procedures can often a better choice. This is thanks to the power of the databases aggregation/filtering capabilities and the fact you are keeping processing very close the the source of the data. But this may not be what most consider classic business logic anyway.

但是，当您进行报告和数据挖掘/搜索时，存储过程通常是更好的选择。这要归功于数据库聚合/过滤功能的强大功能，以及您在非常接近数据源的情况下进行处理的事实。但这可能不是大多数人认为的经典业务逻辑。

回答by Mendelt

For very simple cases you can put your business logic in stored procedures. Usually even the simple cases tend to get complicated over time. Here are the reasons I don't put business logic in the database:

对于非常简单的情况，您可以将业务逻辑放在存储过程中。通常，即使是简单的情况也会随着时间的推移而变得复杂。以下是我不将业务逻辑放入数据库的原因：

Putting the business logic in the database tightly couples it to the technical implementation of the database. Changing a table will cause you to change a lot of the stored procedures again causing a lot of extra bugs and extra testing.

将业务逻辑放在数据库中将其与数据库的技术实现紧密耦合。更改表将导致您再次更改许多存储过程，从而导致许多额外的错误和额外的测试。

Usually the UI depends on business logic for things like validation. Putting these things in the database will cause tight coupling between the database and the UI or in different cases duplicates the validation logic between those two.

通常 UI 依赖于业务逻辑来进行诸如验证之类的事情。将这些东西放在数据库中会导致数据库和 UI 之间的紧密耦合，或者在不同情况下会在这两者之间重复验证逻辑。

It will get hard to have multiple applications work on the same database. Changes for one aplication will cause others to break. This can quickly turn into a maintenance nightmare. So it doesn't really scale.

让多个应用程序在同一个数据库上工作会变得很困难。一个应用程序的更改将导致其他应用程序中断。这很快就会变成维护噩梦。所以它并没有真正扩展。

More practically SQL isn't a good language to implement business logic in an understandable way. SQL is great for set based operations but it misses constructs for "programming in the large" it's hard to maintain big amounts of stored procedures. Modern OO languages are better suited and more flexible for this.

更实际地说，SQL 并不是一种以可理解的方式实现业务逻辑的好语言。SQL 非常适合基于集合的操作，但它错过了“大型编程”的构造，很难维护大量的存储过程。现代 OO 语言更适合且更灵活。

This doesn't mean you can't use stored procs and views. I think it sometimes is a good idea to put an extra layer of stored procedures and views between the tables and application(s) to decouple the two. That way you can change the layout of the database without changing external interface allowing you to refactor the database independently.

这并不意味着您不能使用存储过程和视图。我认为有时在表和应用程序之间放置额外的存储过程和视图层以将两者解耦是个好主意。这样您就可以在不更改外部接口的情况下更改数据库的布局，从而允许您独立地重构数据库。

回答by Jon Limjap

It's really up to you, as long as you're consistent.

只要您始终如一，这完全取决于您。

One good reason to put it in your database layer: if you are fairly sure that your clients will never ever change their database back-end.

把它放在你的数据库层的一个很好的理由：如果你相当确定你的客户永远不会改变他们的数据库后端。

One good reason to put it in the application layer: if you are targeting multiple persistence technologies for your application.

将它放在应用程序层的一个很好的理由是：如果您的应用程序针对多种持久性技术。

You should also take into account core competencies. Are your developers mainly application layer developers, or are they primarily DBA-types?

您还应该考虑核心竞争力。您的开发人员主要是应用层开发人员，还是主要是 DBA 类型的开发人员？

回答by IK.

While there are certainly benefits to have the business logic on the application layer, I'd like to point out that the languages/frameworks seem to change more frequently then the databases.

虽然在应用程序层拥有业务逻辑肯定有好处，但我想指出语言/框架似乎比数据库更频繁地变化。

Some of the systems that I support, went through the following UIs in the last 10-15 years: Oracle Forms/Visual Basic/Perl CGI/ ASP/Java Servlet. The one thing that didn't change - the relational database and stored procedures.

我支持的一些系统在过去 10-15 年中经历了以下 UI：Oracle Forms/Visual Basic/Perl CGI/ASP/Java Servlet。没有改变的一件事 - 关系数据库和存储过程。

回答by Foo42

While there is no one right answer - it depends on the project in question, I would recommend the approach advocated in "Domain Driven Design" by Eric Evans. In this approach the business logic is isolated in its own layer - the domain layer - which sits on top of the infrastructure layer(s) - which could include your database code, and below the application layer, which sends the requests into the domain layer for fulfilment and listens for confirmation of their completion, effectively driving the application.

虽然没有一个正确的答案——这取决于所讨论的项目，但我会推荐Eric Evans在“领域驱动设计”中提倡的方法。在这种方法中，业务逻辑在它自己的层中被隔离 - 域层 - 位于基础设施层的顶部 - 可能包括您的数据库代码，以及在应用层下方，它将请求发送到域层完成并听取他们的完成确认，有效地推动应用程序。

This way, the business logic is captured in a model which can be discussed with those who understand the business aside from technical issues, and it should make it easier to isolate changes in the business rules themselves, the technical implementation issues, and the flow of the application which interacts with the business (domain) model.

通过这种方式，业务逻辑被捕获在一个模型中，除了技术问题之外，该模型可以与了解业务的人进行讨论，并且应该更容易隔离业务规则本身的变化、技术实现问题和流程与业务（领域）模型交互的应用程序。

I recommend reading the above book if you get the chance as it is quite good at explaining how this pure ideal can actually be approximated in the real world of real code and projects.

如果你有机会，我建议你阅读上面的书，因为它很好地解释了如何在真实代码和项目的现实世界中实际近似这种纯粹的理想。

回答by HLGEM

Anything that affects data integrity must be put at the database level. Other things besides the user interface often put data into, update or delete data from the database including imports, mass updates to change a pricing scheme, hot fixes, etc. If you need to ensure the rules are always followed, put the logic in defaults and triggers.

任何影响数据完整性的东西都必须放在数据库级别。除了用户界面外，其他的东西通常会在数据库中放入数据、更新或删除数据，包括导入、批量更新以更改定价方案、热修复等。如果您需要确保始终遵循规则，请将逻辑置于默认值和触发器。

This is not to say that it isn't a good idea to also have it in the user interface (why bother sending information that the database won't accept), but to ignore these things in the database is to court disaster.

这并不是说在用户界面中也有它不是一个好主意（为什么要发送数据库不会接受的信息），但在数据库中忽略这些东西是招致灾难。

回答by David Aldridge

Database independence, which the questioner rules out as a consideration in this case, is the strongest argument for taking logic out of the database. The strongest argument for database independence is for the ability to sell software to companies with their own preference for a database backend.

在这种情况下，提问者排除了作为考虑因素的数据库独立性，这是从数据库中去除逻辑的最有力论据。数据库独立性的最有力论据是能够将软件出售给那些自己偏爱数据库后端的公司。

Therefore, I'd consider the major argument for taking stored procedures out of the database to be a commercial one only, not a technical one. There may be technical reasons but there are also technical reasons for keeping it in there -- performance, integrity, and the ability to allow multiple applications to use the same API for example.

因此，我认为将存储过程从数据库中取出的主要论点仅是商业性的，而不是技术性的。可能有技术原因，但也有技术原因将其保留在那里——例如，性能、完整性以及允许多个应用程序使用相同 API 的能力。

Whether or not to use SP's is also strongly influenced by the database that you are going to use. If you take database independence out of consideration then you're going to have very different experiences using T-SQL or using PL/SQL.

是否使用 SP 也受您将要使用的数据库的强烈影响。如果您不考虑数据库独立性，那么您在使用 T-SQL 或使用 PL/SQL 时会有非常不同的体验。

If you are using Oracle to develop an application then PL/SQL is an obvious choice as a language. It's is very tightly coupled with the data, continually improved in every relase, and any decent development tool is going to integratePL/SQL development with CVS or Subversion or somesuch.

如果您使用 Oracle 来开发应用程序，那么 PL/SQL 作为一种语言是显而易见的选择。它与数据紧密结合，在每个版本中不断改进，任何不错的开发工具都会将 PL/SQL 开发与 CVS 或 Subversion 或类似的东西集成在一起。

Oracle's web-based Application Express development environment is even built 100% with PL/SQL.

Oracle 基于 Web 的 Application Express 开发环境甚至是 100% 使用 PL/SQL 构建的。

回答by Nick Pierpoint

If you need database independence, you'll probably want to put all your business logic in the application layer since the standards available in the application tier are far more prevalent than those available to the database tier.

如果您需要数据库独立性，您可能希望将所有业务逻辑放在应用程序层，因为应用程序层中可用的标准远比数据库层可用的标准更为普遍。

However, if database independence isn't the #1 factor and the skill-set of your team includes strong database skills, then putting the business logic in the database may prove to be the best solution. You can have your application folks doing application-specific things and your database folks making sure all the queries fly.

但是，如果数据库独立性不是第一要素，并且您团队的技能组合包括强大的数据库技能，那么将业务逻辑放入数据库中可能被证明是最佳解决方案。您可以让您的应用程序人员做特定于应用程序的事情，而您的数据库人员则确保所有查询都能正常运行。

Of course, there's a big difference between being able to throw a SQL statement together and having "strong database skills" - if your team is closer to the former than the latter then put the logic in the application using one of the Hibernates of this world (or change your team!).

当然，能够将 SQL 语句放在一起与拥有“强大的数据库技能”之间存在很大差异 - 如果您的团队更接近前者而不是后者，那么使用这个世界上的 Hibernate 之一将逻辑放入应用程序中（或改变你的团队！）。

In my experience, in an Enterprise environment you'll have a single target database and skills in this area - in this case put everything you can in the database. If you're in the business of selling software, the database license costs will make database independence the biggest factor and you'll be implementing everything you can in the application tier.

根据我的经验，在企业环境中，您将拥有一个单一的目标数据库和该领域的技能 - 在这种情况下，将您所能做的一切都放在数据库中。如果您从事软件销售业务，数据库许可成本将使数据库独立性成为最大的因素，您将在应用程序层中实现您所能实现的一切。

Hope that helps.

希望有帮助。

回答by tuinstoel

It is nowadays possible to submit to subversion your stored proc code and to debug this code with good tool support.

现在可以提交颠覆您存储的 proc 代码并在良好的工具支持下调试此代码。

If you use stored procs that combine sql statements you can reduce the amount of data traffic between the application and the database and reduce the number of database calls and gain big performance gains.

如果您使用结合 sql 语句的存储过程，您可以减少应用程序和数据库之间的数据流量，减少数据库调用的数量并获得巨大的性能提升。

Once we started building in C# we made the decision not to use stored procs but now we are moving more and more code to stored procs. Especially batch processing.

一旦我们开始用 C# 构建，我们就决定不使用存储过程，但现在我们将越来越多的代码移到存储过程中。尤其是批处理。

However don't use triggers, use stored procs or better packages. Triggers do decrease maintainability.

但是不要使用触发器，使用存储过程或更好的包。触发器确实会降低可维护性。