Against stored procedures: business logic in programming space
I place a high value on the power of expression, and I don't find the SQL space to be all that expressive. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.
But it depends. If you have multiple applications interacting with one storage mechanism and you want to make sure it maintains its integrity and workflow, then you should offload all of the logic into the database server. Or, be prepared to manage concurrent development in multiple applications.
I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored proc at the same time.
My other main complaint is that SQL is just not very good at representing complex logic. You have no concept of scope, code tends to be copy-pasted because there is a less ability to reuse code (as opposed to an OO language).
You have to give developers access to the database to develop there. In many organizations I have worked at the data people are in a different world than the devs, with different permissions, etc. Keeping the devs out of the database in these cases would be harder.
访问不关心逻辑是否存在于存储过程中、J2EE 中间层中、剪辑专家系统中或任何地方。 无论您将业务逻辑存储在哪里，“痛苦守恒定律”都将保证有人会说这是错误的想法，因为组件/存储库 X 需要替换为技术/方法 Y。
I'm of the school of thought that says that as long as business logic:
lives in one place
where it is properly documented
proper access is provided through services that can be loosely coupled
through a published abstracted interface
I don't care if the logic lives in a stored procedure, in a J2EE middle tier, in a clips expert system, or wherever. No matter where you store our business logic the "law of conservation of misery" is going to guarantee that someone will say it was the wrong idea because component/repository X needs to be swapped out for technology/method Y.
retCode = validateSomeDate(date);
if (retCode == 1) then
evaluateIfCustomerGetsEmail(...)//probably more stored proc invocations here...
sendEmailMsg(....)
else if (retCode == 2) then
performOtherBizLogicStuf(...) //again, may need data, may not need data
triggerExternalsystemToDoSomething(...) //may not be accessible via PL/SQL
fi
Some thoughts: Please note this is a Java centric response, but its the bulk of my recent (last 10years) experience
(1) Concurrent development by a (a large) team of developers. If you're application is sufficiently complex that each developer can't set up their own private version of the DB (with attended links/ref data/etc...)it is very difficult to have an entire TEAM of developers all working on the same set of PL-SQL (for example) packages at the same time stored in a shared DEVL DB? Then your stuck (my experience) with working in a DB with invalid procedures / mismatch of code to tables as people make changes...
As a Java architect, I think its much easier to have each developer have a private JBoss instance on their desktop and work easily on their own set of functionality, and integrating at their own pace without impacting everyone else ... which brings me to ...
(2) Continuous Integration toolsets While there exist some similar 'concepts' in the DB world, my experience has shown me that the combo of (i'm picking my current best-of-breed favs here):
Running a large project using all of the above (free tools) allows a consistent / easy way to deliver XP to the masses and enforce quality controls over a whole IT staff. Oracle / PL-SQL doesn't have the toolsets to match
(3) tools / libraries / etc... Java has access to an amazing set of services that other platforms cannot touch - some free, some not. even basic ones, like log4j (yes they have it for PL/SQL, but pulease...its not nearly the same) allows for things like allowing developers to create flexibly adjustable logging that can be changed on the fly (perfect for dubugging). Automated API documentation (via javadoc). Automated unit test coverage reports. Incredible IDEs (Eclipse) with integrated debuggers / autodeploy to app servers. An API to interface with every type of service under the sun, open source libraries to do ANYTHING, and 100% support by every vendor
(4) reuse of services. what someone commented on is true. If you have heavy duty data driven business rules then you can argue that these should live in the DB layer. Why? to prevent having the middle tier(s) all having to duplicate that logic.
But the same can be said for business rules that are not data driven or sufficiently complex that OO is a more natural choice. If you stick ALL business logic in the DB, then they're available only via the DB.
What if you want to have validation done in the client or middle app tier and save a round trip to the DB?
What if you want to cache read only data in the middle tier (for performance) and have business rules execute against the cached data?
What if you have a middle tier service that doesn't require DB access, or you have a client that can supply their own data?
What if the data dependent portion of the business rules then needs to access external services? Then you end with with fragmented business logic that looks like this:
i
retCode = validateSomeDate(date);
if (retCode == 1) then
evaluateIfCustomerGetsEmail(...)//probably more stored proc invocations here...
sendEmailMsg(....)
else if (retCode == 2) then
performOtherBizLogicStuf(...) //again, may need data, may not need data
triggerExternalsystemToDoSomething(...) //may not be accessible via PL/SQL
fi
I'm sure we've all seen systems written like the one above and had to debug them at 2AM. Its extremely difficult to get a coherent sense of a complex process when the business logic is fragmented between tiers, and in some cases it gets to be impossible to maintain.
SOA 提供了一种中间方式：服务拥有自己的数据。 只有服务可以访问数据； 获取数据意味着通过服务。 在这种情况下，可以将规则放在任一位置。
应用程序来来去去，但数据仍然存在。
"You can not integrate it into source control very easily." - if you put the code that creates the stored proc into a script that's version controlled, that objection goes away. If you follow Scott Ambler's agile database ideas, that's exactly what you should be doing.
Not all developers are good data modelers. I can think of horrible schemas created by developers who thought that a dabbling knowledge of SQL made them database experts. I think there's a lot of value to having developers working with DBAs and data modelers.
If only one application uses the database, I'd say that business logic can appear in the middle tier. If many apps share the database, perhaps it's better to put it in the database.
SOA offers a middle way: services own their data. Only the service has access to the data; getting to data means going through the service. In that case, it's possible to put the rules in either place.
One more reason NOT to store business logic in sprocs - limited scaling abilities of the DB. It is very common situation where your database is your bottleneck, that is why it is a good idea to take as much load of the DB as possible.
Business logic should be encapsulated in one place. We can guarantee that the logic is always run and run consistently. Using classes that all activity involving an entity on the database must run through we can guarantee that all validation is run properly. There is one place for this code and any developer on the project can easily open this class and see the logic (because documentation can and does get out of date, the code is the only reliable form of documentation).
This is difficult to do with stored procedures. You may have more than one sproc dealing with the same table(s). Chaining multiple sprocs together so that the logic resides in only one gets unwieldy. That is strike one. How do you determine "What are all of the business rules surrounding entity X" within the database? Have fun searching thousands of sprocs trying to track that down.
Number two is that you are tying your business logic to your persistence mechanism. You may not store all of your data in the same database, or some may reside in XML etc. This type of inconsistency is difficult on the developer.
Validation is difficult to perform if the logic resides only in the database. Do you really call a sproc to validate every field on your data entry form? Validation rules and business logic are close cousins. This logic should all be performed in the same place!
我个人反对它，但我不得不在一个非常繁忙的网站上使用它一次。 在 MS SQL 中使用 SP 带来了巨大的好处，但是一旦我实现了缓存，这些好处就不再那么大了。
+: SQL server sometimes optimizes the code
+: You are forced to pass parameters, which limits SQL injection issues
-: Your code depends on a single database (some dbs don't even have SP)
-: To change code you need to connect to database
-: Logic is not organized well
Personally I'm against it, but I had to use it once on a really busy website. Using SP in MS SQL brought huge benefits, but once I implemented caching those benefits were not so big anymore.
When all you have is a hammer, everything looks like a nail.
In my humble opinion there is no one answer that will fit all circumstances. It seems to me that many people just assume that putting Business Logic in the database is always wrong.
A lot of work has been done to make transaction processing, especially bulk operations, very efficient when done on the database side. Also the code management in databases has vastly improved since most of the opinions against databases were formed.
I think it is wrong to consider the database server as just a persistence layer. If your processing activities are most efficient when done on the DB Server then do them there.
If not then do them elsewhere.
It all comes down to what best fits the application you are working on at the moment, the team with whom you are working and the customer who hired you.
You can unit test business logic when its in a business logic layer. If it's completely separate the persistance operations can be mocked so you are testing the BL only. A stored proc is far more difficult to maintain/debug/unit test than e.g. linq and c#.
Functional programming (DB Stored procedure) vs OOP. For big programs is OOP just standard.
IDE - eclipse, intellij, netbeans with all plugins for debugging, testing and analysis works only with real programming languages. Static code tools.
Version control if you get one for PLSQL & co. is great. If you get "synchronize view" direct from you IDE - you are real the lucky one.
Scale your system. For DB system is hell. You need expensive hardware for other "nodes". Replication. And probably licence for every node. Don't forget you are still in "functional programming" and effort to understand and maintain such systems is much bigger.
You are stuck with your DB, try to change or add a new one from another company
And so on...
Stored procedure for business logic is bad practise today. Use 3-Tier architecture instead.
There are different kinds of "business logic". Consider partitioning it based on how it's related to other layers or services. Here are some rules of thumb from an MVC perspective:
a) In the database (stored procedure) if it's mostly data-related, and can be done with joins and relatively simple WHERE and SELECT clauses.
b) In the controller if it's mostly routing or dispatching related; that is, larger-scale UI flow control in terms of screen or resource selection.
c) In the model or view-model if it involves complex or intricate computations and/or conditionals.
d) In the view (such as Razor) if it's primarily a display issue, such as "friendly" re-formatting and relatively simple to implement. (If it's complex, consider putting it in a view-model.)
My rule of thumb (once I recognized what it was by thinking about the question) is that stored procedures should contain code that ensures data integrity within the database, whether the stored procedure prohibits certain data insertion, deletion or modification, or makes other changes necessary for data consistency. Business logic, especially logic that cannot be implemented in a handful of set-based operations, should be implemented elsewhere. Databases are not applications. Databases should be the ultimate authority for relationships and constraints, even if the business rules are also implemented elsewhere for, say, the provision of feedback in user interface code that reduces postbacks from a web server or eliminates otherwise unnecessary hits on a busy server. One can argue whether "data consistency" includes the result of complex processing of complex business rules, but I think it's usually clear once the context is understood. Not all business rules are implemented as data relationships or constraints. Not all operations in a stored procedure are faster than code running in a separate process, even on a process running on a separate machine across a network. I recently did a demonstration showing that many operations in SSIS, for example, (INSERT INTO () SELECT FROM) perform faster in SSIS running across a network on a separate machine than running in a stored procedure (that also inserts the results to a database across the network). This is an almost unbelievable result (where SSIS is faster than raw SQL statements), and demonstrates that the discovery of the best optimization of any performance issues comes from reality (testing) and not from logic based on only a few concepts. (We still have to make decisions on what to test by rules of thumb learned by experience.) (SSIS performed faster by automatically implementing multithreading and pipelines, using BULK INSERT even where it wasn't specified in the raw SQL statement, and sending batches of inserts on one thread while creating additional BULK INSERTs on other threads. In this instance, it performed about twice as fast as raw SQL statements.) When I used to teach programming and SQL Server courses, PowerBuilder users seemed to have the statement "Native drivers provide the fastest data access" burned into their tongue, and while it might be justified through additional (unrecognized by them) explanation, the thinking behind it is misleading.
Performance will be massively improved by moving logic into the stored procs, especially if explicit transactions are involved.
In my experience, application developers aren't very good at writing optimised database code, and don't tend to think about concurrency or performance issues. If business logic is kept in the application layer, you tend to have to shift large amounts of data (often in many round-trips) across the network, duplicate it in memory on the DB server, and at least once in the app server, and do a load of row-by-row processing in the app while you're holding open a transaction. Then the app developers complain that the database is slow and keeps deadlocking.
If you put the logic in the DB wherever it's possible, you tend to just pass a few parameters across the network, transactions aren't held while you wait for network resources, and the whole thing goes like greased lightning. Databases should of course go in source control like any other source code.. there are plenty of tools for that.
@Nick "I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored procedure at the same time."
Not that I'm arguing for putting business logic on stored procedures (all the contrary). But these reasons you put forward make no sense. A stored procedure is simply a sql/DDL artifact that can be stored on source control, and which is also a deployment artifact (that is, something handed over to the dba for deployment, much the same way you would hand over your war/ear artifacts to the IT/deployment liasons) One or more developers can work on the same stored procedure off source control just in the same way you'll do with plain old source code - by branching, versioning and merging.
Now, if the only copy of the stored procedure (and the packages that contain them) only exist in the database, then obviously you cannot control that with source control (and all the problems associated to that). However, that's not a problem of stored procedure but a problem of ineptitude in regard of how to use that code. It is as equally a display of ineptitude as having only one copy of your source code living on production.
I've worked in systems with massive amounts of code, both Java and PLSQL/DDLs and they were all versioned on clearcase. They were all treated as source code that would be compiled and deployed with a strict process, with different teams working on them. Never had any problem as what you are describing.
There are context-specific reasons not to put business logic in stored procedures, but these aren't valid ones.
With all this micro-services and micro business components ideology we are way ahead of questioning the right place to put our business logic.
Even though the ideology is well accepted we still have temptation and some instances where we ended up putting some decision making and business logic in database. It’s worth visiting detailed answer to why it should not be done, but at macro level I would advise thinking about one good reason why it should stay in stored-procedure and not in the application layer.
Having that counter thought process would always direct taking the right decision. Ask these questions before deciding:
What if down the line we change our database from SQL to Mongo?
What if want to expose API which takes the data (that database is providing) and apply this business logic on top?
Unit testing this business logic?
SDLC steps involved if we make change in conditions of this business logic?
What if we need another user input for the decision making in this business logic?
What if it requires high computation power (not data processing) and we want to be run off a separate process (or platform) ?
In all cases it would make natural choice to not put any logic (other than just data retrieval) in business logic.
发布评论
评论（17）
反对存储过程：编程空间中的业务逻辑
我非常看重表达能力，但我不认为 SQL 空间具有如此丰富的表达能力。 使用您手头最好的工具来完成最合适的任务。 摆弄逻辑和高阶概念最好在最高级别进行。 因此，存储和海量数据操作最好在服务器级别完成，可能在存储过程中完成。
但这取决于情况。 如果您有多个应用程序与一种存储机制交互，并且您希望确保它保持其完整性和工作流程，那么您应该将所有逻辑卸载到数据库服务器中。 或者，准备好管理多个应用程序中的并发开发。
Against stored procedures: business logic in programming space
I place a high value on the power of expression, and I don't find the SQL space to be all that expressive. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.
But it depends. If you have multiple applications interacting with one storage mechanism and you want to make sure it maintains its integrity and workflow, then you should offload all of the logic into the database server. Or, be prepared to manage concurrent development in multiple applications.
我完全反对。 最大的原因之一是earino所说的第一个原因——它生活在一个地方。 您不能很容易地将其集成到源代码管理中。 让两个开发人员同时处理一个存储过程几乎是不可能的。
我的另一个主要抱怨是 SQL 不太擅长表示复杂的逻辑。 您没有范围的概念，代码往往是复制粘贴的，因为重用代码的能力较差（与 OO 语言相反）。
您必须授予开发人员访问数据库的权限才能在那里进行开发。 在我工作过的许多组织中，数据人员与开发人员处于不同的世界，具有不同的权限等。在这些情况下，让开发人员远离数据库会更加困难。
I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored proc at the same time.
My other main complaint is that SQL is just not very good at representing complex logic. You have no concept of scope, code tends to be copy-pasted because there is a less ability to reuse code (as opposed to an OO language).
You have to give developers access to the database to develop there. In many organizations I have worked at the data people are in a different world than the devs, with different permissions, etc. Keeping the devs out of the database in these cases would be harder.
我的思想流派认为，只要业务逻辑：
访问不关心逻辑是否存在于存储过程中、J2EE 中间层中、剪辑专家系统中或任何地方。 无论您将业务逻辑存储在哪里，“痛苦守恒定律”都将保证有人会说这是错误的想法，因为组件/存储库 X 需要替换为技术/方法 Y。
I'm of the school of thought that says that as long as business logic:
I don't care if the logic lives in a stored procedure, in a J2EE middle tier, in a clips expert system, or wherever. No matter where you store our business logic the "law of conservation of misery" is going to guarantee that someone will say it was the wrong idea because component/repository X needs to be swapped out for technology/method Y.
一些想法：请注意，这是一个以 Java 为中心的响应，但它是我最近（过去 10 年）的大部分经验
(1) 由一个（大型）开发团队并行开发。 如果您的应用程序足够复杂，以至于每个开发人员无法设置自己的私有版本的数据库（带有参与链接/参考数据/等...），那么让整个开发团队都致力于开发是非常困难的同一套 PL-SQL（例如）包同时存储在共享 DEVL DB 中？ 然后，当人们进行更改时，您会陷入（我的经验）在具有无效过程/代码与表不匹配的数据库中工作的困境...
作为 Java 架构师，我认为让每个开发人员在其桌面上拥有一个私有 JBoss 实例要容易得多轻松地使用自己的功能集，并按照自己的节奏进行集成，而不影响其他人......这让我想到......
（2）持续集成工具集
虽然数据库世界中存在一些类似的“概念”，但我的经验告诉我，组合（我在这里选择我当前最喜欢的）：
更多
使用上述所有工具（免费工具）运行大型项目可以通过一致/简单的方式向大众交付 XP 并对整个 IT 员工实施质量控制。
Oracle / PL-SQL 没有匹配的工具集
(3) 工具/库/等...
Java 可以访问一系列其他平台无法触及的令人惊叹的服务 - 有些是免费的，有些不是。
即使是基本的日志，如 log4j（是的，他们有用于 PL/SQL 的，但请注意...它几乎不一样）允许开发人员创建灵活可调整的日志记录，可以动态更改（非常适合调试） 。 自动化 API 文档（通过 javadoc）。 自动化单元测试覆盖率报告。 令人难以置信的 IDE (Eclipse)，具有集成调试器/自动部署到应用程序服务器。 一个可以与各种类型的服务进行交互的 API，可以做任何事情的开源库，以及每个供应商 100% 的支持
(4) 服务的重用。 有人评论的是真的。 如果您有繁重的数据驱动的业务规则，那么您可以认为这些规则应该存在于数据库层。 为什么？ 以防止中间层都必须重复该逻辑。
但对于非数据驱动或足够复杂以至于面向对象是更自然的选择的业务规则也可以这样说。 如果您将所有业务逻辑都保存在数据库中，那么它们只能通过数据库使用。
我
确信我们都见过像上面这样编写的系统，并且必须在凌晨 2 点调试它们。 当业务逻辑在各层之间分散时，要获得复杂流程的连贯性是极其困难的，并且在某些情况下甚至无法维护。
Some thoughts: Please note this is a Java centric response, but its the bulk of my recent (last 10years) experience
(1) Concurrent development by a (a large) team of developers. If you're application is sufficiently complex that each developer can't set up their own private version of the DB (with attended links/ref data/etc...)it is very difficult to have an entire TEAM of developers all working on the same set of PL-SQL (for example) packages at the same time stored in a shared DEVL DB? Then your stuck (my experience) with working in a DB with invalid procedures / mismatch of code to tables as people make changes...
As a Java architect, I think its much easier to have each developer have a private JBoss instance on their desktop and work easily on their own set of functionality, and integrating at their own pace without impacting everyone else ... which brings me to ...
(2) Continuous Integration toolsets
While there exist some similar 'concepts' in the DB world, my experience has shown me that the combo of (i'm picking my current best-of-breed favs here):
more
Running a large project using all of the above (free tools) allows a consistent / easy way to deliver XP to the masses and enforce quality controls over a whole IT staff.
Oracle / PL-SQL doesn't have the toolsets to match
(3) tools / libraries / etc...
Java has access to an amazing set of services that other platforms cannot touch - some free, some not.
even basic ones, like log4j (yes they have it for PL/SQL, but pulease...its not nearly the same) allows for things like allowing developers to create flexibly adjustable logging that can be changed on the fly (perfect for dubugging). Automated API documentation (via javadoc). Automated unit test coverage reports. Incredible IDEs (Eclipse) with integrated debuggers / autodeploy to app servers. An API to interface with every type of service under the sun, open source libraries to do ANYTHING, and 100% support by every vendor
(4) reuse of services. what someone commented on is true. If you have heavy duty data driven business rules then you can argue that these should live in the DB layer. Why? to prevent having the middle tier(s) all having to duplicate that logic.
But the same can be said for business rules that are not data driven or sufficiently complex that OO is a more natural choice. If you stick ALL business logic in the DB, then they're available only via the DB.
i
I'm sure we've all seen systems written like the one above and had to debug them at 2AM. Its extremely difficult to get a coherent sense of a complex process when the business logic is fragmented between tiers, and in some cases it gets to be impossible to maintain.
“你不能很容易地将它集成到源代码管理中。” - 如果您将创建存储过程的代码放入版本控制的脚本中，那么该反对意见就会消失。 如果您遵循 Scott Ambler 的敏捷数据库思想，那么这正是您应该做的。
并非所有开发人员都是优秀的数据建模者。 我可以想到由开发人员创建的可怕模式，他们认为涉足 SQL 知识就可以使他们成为数据库专家。 我认为让开发人员与 DBA 和数据建模人员一起工作非常有价值。
如果只有一个应用程序使用数据库，我会说业务逻辑可以出现在中间层。 如果很多应用程序共享数据库，也许最好将其放在数据库中。
SOA 提供了一种中间方式：服务拥有自己的数据。 只有服务可以访问数据； 获取数据意味着通过服务。 在这种情况下，可以将规则放在任一位置。
应用程序来来去去，但数据仍然存在。
"You can not integrate it into source control very easily." - if you put the code that creates the stored proc into a script that's version controlled, that objection goes away. If you follow Scott Ambler's agile database ideas, that's exactly what you should be doing.
Not all developers are good data modelers. I can think of horrible schemas created by developers who thought that a dabbling knowledge of SQL made them database experts. I think there's a lot of value to having developers working with DBAs and data modelers.
If only one application uses the database, I'd say that business logic can appear in the middle tier. If many apps share the database, perhaps it's better to put it in the database.
SOA offers a middle way: services own their data. Only the service has access to the data; getting to data means going through the service. In that case, it's possible to put the rules in either place.
Applications come and go, but data remains.
不在存储过程中存储业务逻辑的另一个原因是数据库的扩展能力有限。 数据库是瓶颈是很常见的情况，这就是为什么最好承担尽可能多的数据库负载。
One more reason NOT to store business logic in sprocs - limited scaling abilities of the DB. It is very common situation where your database is your bottleneck, that is why it is a good idea to take as much load of the DB as possible.
业务逻辑应该封装在一处。 我们可以保证逻辑始终运行并且运行一致。 使用涉及数据库上实体的所有活动都必须运行的类，我们可以保证所有验证都正确运行。 此代码有一个位置，项目中的任何开发人员都可以轻松打开此类并查看逻辑（因为文档可能而且确实会过时，代码是唯一可靠的文档形式）。
这对于存储过程来说很难做到。 您可能有多个存储过程处理相同的表。 将多个存储过程链接在一起，使逻辑仅驻留在一个存储过程中，这会变得很笨重。 那就是罢工一。 如何确定数据库中“围绕实体 X 的所有业务规则是什么”？ 搜索数千个存储过程并试图找到它，这很有趣。
第二点是将业务逻辑与持久性机制联系起来。 您可能不会将所有数据存储在同一个数据库中，或者某些数据可能驻留在 XML 等中。这种类型的不一致对开发人员来说很困难。
如果逻辑仅驻留在数据库中，则很难执行验证。 您真的调用存储过程来验证数据输入表单上的每个字段吗？ 验证规则和业务逻辑是近亲。 这个逻辑应该全部在同一个地方执行！
Business logic should be encapsulated in one place. We can guarantee that the logic is always run and run consistently. Using classes that all activity involving an entity on the database must run through we can guarantee that all validation is run properly. There is one place for this code and any developer on the project can easily open this class and see the logic (because documentation can and does get out of date, the code is the only reliable form of documentation).
This is difficult to do with stored procedures. You may have more than one sproc dealing with the same table(s). Chaining multiple sprocs together so that the logic resides in only one gets unwieldy. That is strike one. How do you determine "What are all of the business rules surrounding entity X" within the database? Have fun searching thousands of sprocs trying to track that down.
Number two is that you are tying your business logic to your persistence mechanism. You may not store all of your data in the same database, or some may reside in XML etc. This type of inconsistency is difficult on the developer.
Validation is difficult to perform if the logic resides only in the database. Do you really call a sproc to validate every field on your data entry form? Validation rules and business logic are close cousins. This logic should all be performed in the same place!
我的一些观察结果：
支持存储过程：
在项目运行一段时间后，在大多数情况下，瓶颈是数据库而不是 Web 服务器 - 并且存储过程要快得多
使用 sql profiler 和 ORM 生成 sql 查询是非常困难的； 这对于存储过程来说很容易
您可以立即部署存储过程的修复，无需服务窗口
为了性能，它更容易优化存储过程比 ORM 代码更容易
任何复杂的数据场景都是对存储过程的投票
支持应用程序/ORM：
您可以使用代码存储库（使用存储过程仍然可以，但昂贵）
java / c# 语言是表达业务逻辑的更好工具
>
java / c#更容易调试（除了动态生成的ORM sql）
数据库引擎的独立性（但是，项目不太可能会这样做）
将数据库更改为另一个数据库）
ORM 提供了易于使用的数据模型
在我看来：对于大型项目 - 使用存储过程，对于其他项目 - 应用程序/ ORM 会工作得很好。
归根结底，唯一重要的是数据库。
My few observations:
In favour of stored procedures:
after some time of project life, in most cases the bottleneck is the database not the web server - and stored procedures are much, much faster
using sql profiler with ORM generated sql queries is very difficult; this is easy with stored procedures
you can deploy a fix for stored procedure instantly, without a service window
for performance, it is easier to optimize a stored procedure than ORM-code
you may have many applications using the same db / stored procs
any complex data scenario is a vote for stored procedures
In favour of application/ORM:
you may use code repository (with stored procs it is still possible but expensive)
java / c# language is a better tool to express business logic
java / c# is easier to debug (except for the dynamically-generated ORM sql)
independence of database engine (however this is not very likely that a project will
change database to another one)
ORM provides data model which is easy to use
In my opinion: for large projects - go for stored procedures, for the others - Application/ORM will work fine.
In the end of a day, the only thing which matters is the database.
+: SQL Server 有时会优化代码
+: 你被迫传递参数，这限制了 SQL 注入问题
-: 你的代码依赖于单个数据库（有些数据库甚至没有 SP）
-: 要更改代码，你需要连接到数据库
-：逻辑组织得不好
我个人反对它，但我不得不在一个非常繁忙的网站上使用它一次。 在 MS SQL 中使用 SP 带来了巨大的好处，但是一旦我实现了缓存，这些好处就不再那么大了。
+: SQL server sometimes optimizes the code
+: You are forced to pass parameters, which limits SQL injection issues
-: Your code depends on a single database (some dbs don't even have SP)
-: To change code you need to connect to database
-: Logic is not organized well
Personally I'm against it, but I had to use it once on a really busy website. Using SP in MS SQL brought huge benefits, but once I implemented caching those benefits were not so big anymore.
有句话说……
当你只有一把锤子时，一切看起来都像钉子。
以我的拙见，没有一个答案可以适合所有情况。 在我看来，许多人只是认为将业务逻辑放入数据库总是错误的。
为了使事务处理（尤其是批量操作）在数据库端完成时非常高效，我们已经做了很多工作。 此外，自从大多数针对数据库的意见形成以来，数据库中的代码管理也得到了极大的改进。
我认为将数据库服务器仅仅视为持久层是错误的。 如果您的处理活动在数据库服务器上完成时效率最高，那么就在那里进行。
如果没有，那就在别处做。
这一切都取决于什么最适合您当前正在开发的应用程序、与您一起工作的团队以及雇用您的客户。
这只是我的 2 美分。
There is a saying...
When all you have is a hammer, everything looks like a nail.
In my humble opinion there is no one answer that will fit all circumstances. It seems to me that many people just assume that putting Business Logic in the database is always wrong.
A lot of work has been done to make transaction processing, especially bulk operations, very efficient when done on the database side. Also the code management in databases has vastly improved since most of the opinions against databases were formed.
I think it is wrong to consider the database server as just a persistence layer. If your processing activities are most efficient when done on the DB Server then do them there.
If not then do them elsewhere.
It all comes down to what best fits the application you are working on at the moment, the team with whom you are working and the customer who hired you.
That just my 2 cents.
当业务逻辑位于业务逻辑层时，您可以对其进行单元测试。 如果它是完全独立的，则可以模拟持久性操作，因此您只测试 BL。 存储过程比 linq 和 c# 更难维护/调试/单元测试。
You can unit test business logic when its in a business logic layer. If it's completely separate the persistance operations can be mocked so you are testing the BL only. A stored proc is far more difficult to maintain/debug/unit test than e.g. linq and c#.
DBMS != 应用程序服务器
业务逻辑的存储过程如今是不好的做法。 请改用 3 层架构。
DBMS != Application server
Stored procedure for business logic is bad practise today. Use 3-Tier architecture instead.
有不同种类的“业务逻辑”。 考虑根据它与其他层或服务的关系对其进行分区。 以下是从 MVC 角度来看的一些经验法则：
a) 在数据库（存储过程）中，如果它主要与数据相关，并且可以通过联接和相对简单的 WHERE 和 SELECT 子句来完成。
b) 在控制器中，如果它主要与路由或调度相关； 即在屏幕或资源选择方面进行更大规模的UI流程控制。
c) 在模型或视图模型中，如果涉及复杂的计算和/或条件。
d) 在视图（例如 Razor）中，如果主要是显示问题，例如“友好”的重新格式化并且实现起来相对简单。 （如果很复杂，请考虑将其放入视图模型中。）
There are different kinds of "business logic". Consider partitioning it based on how it's related to other layers or services. Here are some rules of thumb from an MVC perspective:
a) In the database (stored procedure) if it's mostly data-related, and can be done with joins and relatively simple WHERE and SELECT clauses.
b) In the controller if it's mostly routing or dispatching related; that is, larger-scale UI flow control in terms of screen or resource selection.
c) In the model or view-model if it involves complex or intricate computations and/or conditionals.
d) In the view (such as Razor) if it's primarily a display issue, such as "friendly" re-formatting and relatively simple to implement. (If it's complex, consider putting it in a view-model.)
我的经验法则（一旦我通过思考问题认识到它是什么）是存储过程应该包含确保数据库内数据完整性的代码，无论存储过程是否禁止某些数据插入、删除或修改，或者进行其他必要的更改为了数据的一致性。 业务逻辑，尤其是无法在少数基于集合的操作中实现的逻辑，应该在其他地方实现。 数据库不是应用程序。 数据库应该是关系和约束的最终权威，即使业务规则也在其他地方实现，例如，在用户界面代码中提供反馈，以减少来自 Web 服务器的回发或消除对繁忙服务器的不必要的点击。 人们可能会争论“数据一致性”是否包括复杂业务规则的复杂处理的结果，但我认为一旦理解了上下文，它通常就很清楚了。 并非所有业务规则都作为数据关系或约束来实现。 并非存储过程中的所有操作都比在单独进程中运行的代码更快，即使在跨网络的单独计算机上运行的进程也是如此。 我最近做了一个演示，显示 SSIS 中的许多操作（例如，INSERT INTO () SELECT FROM）在单独计算机上跨网络运行的 SSIS 中比在存储过程中运行（还将结果插入数据库）执行得更快通过网络）。 这是一个几乎令人难以置信的结果（其中 SSIS 比原始 SQL 语句更快），并且表明任何性能问题的最佳优化的发现都来自现实（测试），而不是来自仅基于几个概念的逻辑。 （我们仍然必须根据从经验中学到的经验法则来决定要测试什么。）（SSIS 通过自动实现多线程和管道、使用 BULK INSERT（即使在原始 SQL 语句中未指定）以及发送批处理来执行得更快在一个线程上进行插入，同时在其他线程上创建额外的 BULK INSERT 在这种情况下，它的执行速度大约是原始 SQL 语句的两倍。）当我过去教授编程和 SQL Server 课程时，PowerBuilder 用户似乎有这样的说法：“Native”。驱动程序提供了最快的数据访问”这句话已经刻在了他们的舌头上，虽然这可能通过额外的（他们不认识的）解释来证明是合理的，但其背后的想法是具有误导性的。
My rule of thumb (once I recognized what it was by thinking about the question) is that stored procedures should contain code that ensures data integrity within the database, whether the stored procedure prohibits certain data insertion, deletion or modification, or makes other changes necessary for data consistency. Business logic, especially logic that cannot be implemented in a handful of set-based operations, should be implemented elsewhere. Databases are not applications. Databases should be the ultimate authority for relationships and constraints, even if the business rules are also implemented elsewhere for, say, the provision of feedback in user interface code that reduces postbacks from a web server or eliminates otherwise unnecessary hits on a busy server. One can argue whether "data consistency" includes the result of complex processing of complex business rules, but I think it's usually clear once the context is understood. Not all business rules are implemented as data relationships or constraints. Not all operations in a stored procedure are faster than code running in a separate process, even on a process running on a separate machine across a network. I recently did a demonstration showing that many operations in SSIS, for example, (INSERT INTO () SELECT FROM) perform faster in SSIS running across a network on a separate machine than running in a stored procedure (that also inserts the results to a database across the network). This is an almost unbelievable result (where SSIS is faster than raw SQL statements), and demonstrates that the discovery of the best optimization of any performance issues comes from reality (testing) and not from logic based on only a few concepts. (We still have to make decisions on what to test by rules of thumb learned by experience.) (SSIS performed faster by automatically implementing multithreading and pipelines, using BULK INSERT even where it wasn't specified in the raw SQL statement, and sending batches of inserts on one thread while creating additional BULK INSERTs on other threads. In this instance, it performed about twice as fast as raw SQL statements.) When I used to teach programming and SQL Server courses, PowerBuilder users seemed to have the statement "Native drivers provide the fastest data access" burned into their tongue, and while it might be justified through additional (unrecognized by them) explanation, the thinking behind it is misleading.
通过将逻辑移至存储过程中，性能将大大提高，特别是在涉及显式事务的情况下。
根据我的经验，应用程序开发人员不太擅长编写优化的数据库代码，并且不倾向于考虑并发或性能问题。
如果业务逻辑保留在应用程序层中，则往往必须通过网络转移大量数据（通常需要多次往返），将其复制到数据库服务器的内存中，并且至少在应用程序服务器中复制一次，并在您保持打开交易的同时在应用程序中进行大量的逐行处理。 然后应用程序开发人员抱怨数据库速度很慢并且一直处于死锁状态。
如果你将逻辑放在数据库中的任何可能的地方，你往往只是通过网络传递一些参数，在等待网络资源时不会保留事务，整个事情就像涂了油的闪电一样。
数据库当然应该像任何其他源代码一样进入源代码管理......有很多工具可以做到这一点。
Performance will be massively improved by moving logic into the stored procs, especially if explicit transactions are involved.
In my experience, application developers aren't very good at writing optimised database code, and don't tend to think about concurrency or performance issues.
If business logic is kept in the application layer, you tend to have to shift large amounts of data (often in many round-trips) across the network, duplicate it in memory on the DB server, and at least once in the app server, and do a load of row-by-row processing in the app while you're holding open a transaction. Then the app developers complain that the database is slow and keeps deadlocking.
If you put the logic in the DB wherever it's possible, you tend to just pass a few parameters across the network, transactions aren't held while you wait for network resources, and the whole thing goes like greased lightning.
Databases should of course go in source control like any other source code.. there are plenty of tools for that.
@Nick“我完全反对它。最大的原因之一是earino所说的第一个原因——它存在于一个地方。你不能很容易地将它集成到源代码控制中。让两个开发人员在同一个地方工作几乎是不可能的。同时存储过程。”
我并不是主张将业务逻辑放在存储过程上（恰恰相反）。 但你提出的这些理由毫无意义。 存储过程只是一个可以存储在源代码管理上的 sql/DDL 工件，它也是一个部署工件（即移交给 dba 进行部署的东西，与您移交 war/ear 的方式非常相似）一个或多个开发人员可以在脱离源代码控制的情况下处理同一个存储过程，就像处理普通旧源代码一样 - 通过分支、版本控制和合并。
现在，如果存储过程（以及包含它们的包）的唯一副本仅存在于数据库中，那么显然您无法使用源代码控制来控制它（以及与之相关的所有问题）。 然而，这不是存储过程的问题，而是关于如何使用该代码的无能问题。 这与生产环境中只有一份源代码副本一样是无能的表现。
我曾在具有大量代码的系统中工作过，包括 Java 和 PLSQL/DDL，并且它们都是在 Clearcase 上进行版本控制的。 它们都被视为源代码，将通过严格的流程进行编译和部署，并由不同的团队进行处理。 从来没有遇到过你所描述的任何问题。
有特定于上下文的理由不将业务逻辑放入存储过程中，但这些理由都不是有效的。
@Nick "I am thoroughly against it. One of the biggest reasons is the first reason earino stated - it lives in one place. You can not integrate it into source control very easily. It is next to impossible to have two devs working on a stored procedure at the same time."
Not that I'm arguing for putting business logic on stored procedures (all the contrary). But these reasons you put forward make no sense. A stored procedure is simply a sql/DDL artifact that can be stored on source control, and which is also a deployment artifact (that is, something handed over to the dba for deployment, much the same way you would hand over your war/ear artifacts to the IT/deployment liasons) One or more developers can work on the same stored procedure off source control just in the same way you'll do with plain old source code - by branching, versioning and merging.
Now, if the only copy of the stored procedure (and the packages that contain them) only exist in the database, then obviously you cannot control that with source control (and all the problems associated to that). However, that's not a problem of stored procedure but a problem of ineptitude in regard of how to use that code. It is as equally a display of ineptitude as having only one copy of your source code living on production.
I've worked in systems with massive amounts of code, both Java and PLSQL/DDLs and they were all versioned on clearcase. They were all treated as source code that would be compiled and deployed with a strict process, with different teams working on them. Never had any problem as what you are describing.
There are context-specific reasons not to put business logic in stored procedures, but these aren't valid ones.
凭借所有这些微服务和微业务组件意识形态，我们远远领先于质疑放置业务逻辑的正确位置。
尽管这种意识形态已被广泛接受，但我们仍然面临诱惑，并且在某些情况下我们最终将一些决策和业务逻辑放入数据库中。 值得访问为什么不应该这样做的详细答案，但在宏观层面上，我建议考虑一个很好的理由，为什么它应该保留在存储过程而不是应用程序层中。
拥有这种反思考过程总是会指导做出正确的决定。 在做出决定之前先问这些问题：
在所有情况下，都会自然选择不在业务逻辑中放置任何逻辑（除了数据检索之外）。
With all this micro-services and micro business components ideology we are way ahead of questioning the right place to put our business logic.
Even though the ideology is well accepted we still have temptation and some instances where we ended up putting some decision making and business logic in database. It’s worth visiting detailed answer to why it should not be done, but at macro level I would advise thinking about one good reason why it should stay in stored-procedure and not in the application layer.
Having that counter thought process would always direct taking the right decision. Ask these questions before deciding:
In all cases it would make natural choice to not put any logic (other than just data retrieval) in business logic.