I have been developing web/desktop applications for about 6 years now. During the course of my career, I have come across application that were heavily written in the database using stored procedures whereas a lot of application just had only a few basic stored procedures (to read, insert, edit and delete entity records) for each entity.
我已经开发web/桌面应用程序大约6年了。在我的职业生涯中，我遇到过使用存储过程在数据库中大量编写的应用程序，而许多应用程序只有一些基本的存储过程(读取、插入、编辑和删除实体记录)。
I have seen people argue saying that if you have paid for an enterprise database use its features extensively. Whereas a lot of "object oriented architects" told me its absolute crime to put anything more than necessary in the database and you should be able to drive the application using the methods on those classes?
我曾看到有人说，如果您为企业数据库付费，就会广泛地使用它的特性。然而，许多“面向对象的架构师”告诉我，在数据库中放置任何不必要的东西都是绝对的罪行，您应该能够使用这些类的方法来驱动应用程序吗?
Where do you think is the balance?
你认为平衡在哪里?
Thanks, Krunal
谢谢,Krunal
10 个解决方案
#1
6
I think it's a business logic vs. data logic thing. If there is logic that ensures the consistency of your data, put it in a stored procedure. Same for convenience functions for data retrieval/update.
我认为这是业务逻辑和数据逻辑的对比。如果存在确保数据一致性的逻辑，请将其放入存储过程中。对于方便的数据检索/更新函数也是如此。
Everything else should go into the code.
所有其他的东西都应该包含在代码中。
A friend of mine is developing a host of stored procedures for data analysis algorithms in bioinformatics. I think his approach is quite interesting, but not the right way in the long run. My main objections are maintainability and lacking adaptability.
我的一个朋友正在为生物信息学中的数据分析算法开发一系列存储过程。我认为他的方法很有趣，但从长远来看不是正确的方法。我主要的反对意见是可维护性和缺乏适应性。
#2
5
I'm in the object oriented architects camp. It's not necessarily a crime to put code in the database, as long as you understand the caveats that go along with that. Here are some:
我属于面向对象架构师阵营。把代码放到数据库中并不一定是一种犯罪，只要您理解随之而来的警告。这里有一些:
- It's not debuggable
- 这不是debuggable
- It's not subject to source control
- 它不受源代码控制的约束
- Permissions on your two sets of code will be different
- 您的两组代码的权限将不同
- It will make it more difficult to track where an error in the data came from if you're accessing info in the database from both places
- 如果您从这两个地方访问数据库中的信息，那么跟踪数据中的错误来自何处将变得更加困难
#3
4
Anything that relates to Referential Integrity or Consistency should be in the database as a bare minimum. If it's in your application and someone wants to write an application against the database they are going to have to duplicate your code in their code to ensure that the data remains consistent.
任何与引用完整性或一致性相关的内容都应该作为最低限度存储在数据库中。如果在应用程序中，有人想针对数据库编写应用程序，他们必须在代码中复制您的代码，以确保数据保持一致。
PLSQL for Oracle is a pretty good language for accessing the database and it can also give performance improvements. Your application can also be much 'neater' as it can treat the database stored procedures as a 'black box'.
Oracle的PLSQL是一种非常好的访问数据库的语言，它还可以提高性能。您的应用程序也可以更“整洁”，因为它可以将数据库存储过程视为“黑盒”。
The sprocs themselves can also be tuned and modified without you having to go near your compiled application, this is also useful if the supplier of your application has gone out of business or is unavailable.
sprocs本身也可以进行调优和修改，而不需要靠近编译后的应用程序，如果应用程序的供应商已经破产或不可用，这也很有用。
I'm not advocating 'everything' should be in database, far from it. Treat each case seperately and logically and you will see which makes more sense, put it in the app or put it in the database.
我并不是主张“一切”都应该放在数据库中，远非如此。把每一个案例分开、逻辑地对待，你就会发现哪个更有意义，把它放到应用程序中，或者放到数据库中。
#4
3
I'm coming from almost the same background and have heard the same arguments. I do understand that there are very valid reasons to put logic into the database. However, it depends on the type of application and the way it handles data which approach you should choose.
我来自几乎相同的背景，也听到过同样的观点。我明白，将逻辑放入数据库有非常正当的理由。但是，这取决于应用程序的类型和处理数据的方式，您应该选择哪种方法。
In my experience, a typical data entry app like some customer (or xyz) management will massively benefit from using an ORM layer as there are not so many different views at the data and you can reduce the boilerplate CRUD code to a minimum.
在我的经验中，典型的数据输入应用程序(如某些客户(或xyz)管理程序)将从使用ORM层中获得巨大的好处，因为数据中没有太多不同的视图，您可以将样板CRUD代码减少到最小。
On the other hand, assume you have an application with a lot of concurrency and calculations that span a lot of tables and that has a fine-grained column-level security concept with locking and so on, you're probably better off doing stuff like that directly in the database.
另一方面，假设您有一个具有许多并发性和计算的应用程序，它跨越了许多表，并且有一个带有锁的细粒度的列级安全概念，因此，您最好在数据库中直接执行类似的操作。
As mentioned before, it also depends on the variety of views you anticipate for your data. If there are many different combinations of columns and tables that need to be presented to the user, you may also be better off just handing back different result sets rather than map your objects one-by-one to another representation.
正如前面提到的，它还取决于您对数据的预期的各种视图。如果有许多不同的列和表的组合需要呈现给用户，那么最好只提交不同的结果集，而不是将对象逐个映射到另一个表示。
After all, the database is good at dealing with sets, whereas OO code is good at dealing with single entities.
毕竟，数据库擅长处理集合，而OO代码擅长处理单个实体。
#5
2
Reading these answers, I'm quite confused by the lack of understanding of database programming. I am an Oracle Pl/sql developer, we source control for every bit of code that goes into the database. Many of the IDEs provide addins for most of the major source control products. From ClearCase to SourceSafe. The Oracle tools we use allow us to debug the code, so debugging isn't an issue. The issue is more of logic and accessibility.
读到这些答案，我对数据库编程的缺乏理解感到困惑。我是一个Oracle Pl/sql开发人员，我们对进入数据库的每个代码位进行源代码控制。许多ide都为大多数主要的源控制产品提供addin。从ClearCase SourceSafe。我们使用的Oracle工具允许我们调试代码，所以调试不是问题。问题更多的是逻辑和可访问性。
As a manager of support for about 5000 users, the less places i have to look for the logic, the better. If I want to make sure the logic is applied for ALL applications that use the data , even business logic, i put it in the DB. If the logic is different depending on the application, they can be responsible for it.
作为一个支持5000个用户的经理，我需要寻找的逻辑越少越好。如果我想确保对所有使用数据的应用程序都应用逻辑，甚至是业务逻辑，我将它放在DB中。如果根据应用程序的不同，逻辑是不同的，他们可以对此负责。
#6
1
@DannySmurf:
@DannySmurf:
It's not debuggable
这不是debuggable
Depending on your server, yes, they are debuggable. This provides an example for SQL Server 2000. I'm guessing the newer ones also have this. However, the free MySQL server does not have this (as far as I know).
根据服务器的不同，它们是可以调试的。这为SQL Server 2000提供了一个示例。我猜新的也有这个。但是，免费的MySQL服务器没有这个(据我所知)。
It's not subject to source control
它不受源代码控制的约束
Yes, it is. Kind of. Database backups should include stored procedures. Those backup files might or might not be in your version control repository. But either way, you have backups of your stored procedures.
是的,它是。种。数据库备份应该包括存储过程。这些备份文件可能在您的版本控制存储库中，也可能不在。但是无论哪种方式，您都有存储过程的备份。
#7
1
My personal preference is to try and keep as much logic and configuration out of the database as possible. I am heavily dependent on Spring and Hibernate these days so that makes it a lot easier. I tend to use Hibernate named queries instead of stored procedures and the static configuration information in Spring application context XML files. Anything that needs to go into the database has to be loaded using a script and I keep those scripts in version control.
我个人的偏好是尽可能地在数据库之外保留尽可能多的逻辑和配置。我现在非常依赖于Spring和Hibernate，所以这样做会容易得多。我倾向于使用Hibernate命名查询，而不是在Spring应用程序上下文XML文件中使用存储过程和静态配置信息。任何需要进入数据库的内容都必须使用脚本进行加载，我将这些脚本保存在版本控制中。
#8
0
@Thomas Owens: (re source control) Yes, but that's not source control in the same sense that I can check in a .cs file (or .cpp file or whatever) and go and pick out any revision I want. To do that with database code requires a potentially-significant amount of effort to either retrieve the procedure from the database and transfer it to somewhere in the source tree, or to do a database backup every time a minor change is made. In either case (and regardless of the amount of effort), it's not intuitive; and for many shops, it's not a good enough solution either. There is also the potential here for developers who may not be as studious at that as others to forget to retrieve and check in a revision. It's technically possible to put ANYTHING in source control; the disconnect here is what I would take issue with.
@Thomas Owens: (re source control)是的，但这不是源代码控制，因为我可以检入.cs文件(或.cpp文件或其他文件)，然后挑选我想要的任何修订。要使用数据库代码实现这一点，需要付出相当大的努力，从数据库中检索过程并将其传输到源树中的某个地方，或者在每次进行微小更改时进行数据库备份。无论哪种情况(不管付出多少努力)，它都不是直观的;对许多商店来说，这也不是一个好的解决方案。这里还有一个潜在的开发人员，他们可能不像其他人那样努力学习，在修改中忘记检索和检查。从技术上讲，可以把任何东西放到源代码控制中;这里的脱节是我要讨论的问题。
(re debuggable) Fair enough, though that doesn't provide much integration with the rest of the application (where the majority of the code could live). That may or may not be important.
(重新调试)很公平，尽管这并不能与应用程序的其他部分(大部分代码可以使用的地方)集成。这可能重要，也可能不重要。
#9
0
Well, if you care about the consistency of your data, there are reasons to implement code within the database. As others have said, placing code (and/or RI/constraints) inside the database acts to enforce business logic, close to the data itself. And, it provides a common, encapsulated interface, so that your new developer doesn't accidentally create orphan records or inconsistent data.
如果您关心数据的一致性，那么有理由在数据库中实现代码。正如其他人所说的，在数据库中放置代码(和/或RI/约束)可以增强业务逻辑，接近数据本身。并且，它提供了一个通用的、封装的接口，这样您的新开发人员就不会意外地创建孤立记录或不一致的数据。
#10
-3
Well, this one is difficult. As a programmer, you'll want to avoid TSQL and such "Database languages" as much as possible, because they are horrendous, difficult to debug, not extensible and there's nothing you can do with them that you won't be able to do using code on your application.
这个很难。作为一名程序员，您将尽可能地避免TSQL和类似的“数据库语言”，因为它们非常可怕，难于调试，不可扩展，而且在应用程序上使用代码时，您无法使用它们。
The only reasons I see for writing stored procedures are:
我看到编写存储过程的唯一原因是:
- Your database isn't great (think how SQL Server doesn't implement LIMIT and you have to work around that using a procedure.
- 您的数据库不是很好(想想SQL Server如何没有实现限制，您必须使用一个过程来解决这个问题。
- You want to be able to change a behaviour by changing code in just one place without re-deploying your client applications.
- 您希望能够通过在一个地方更改代码来更改行为，而无需重新部署客户机应用程序。
- The client machines have big calculation-power constraints (think small embedded devices).
- 客户端机器有很大的计算能力限制(想想小型嵌入式设备)。
For most applications though, you should try to keep your code in the application where you can debug it, keep it under version control and fix it using all the tools provided to you by your language.
不过，对于大多数应用程序，您应该尽量将代码保存在应用程序中，以便对其进行调试，将其置于版本控制之下，并使用语言提供的所有工具对其进行修复。
#1
6
I think it's a business logic vs. data logic thing. If there is logic that ensures the consistency of your data, put it in a stored procedure. Same for convenience functions for data retrieval/update.
我认为这是业务逻辑和数据逻辑的对比。如果存在确保数据一致性的逻辑，请将其放入存储过程中。对于方便的数据检索/更新函数也是如此。
Everything else should go into the code.
所有其他的东西都应该包含在代码中。
A friend of mine is developing a host of stored procedures for data analysis algorithms in bioinformatics. I think his approach is quite interesting, but not the right way in the long run. My main objections are maintainability and lacking adaptability.
我的一个朋友正在为生物信息学中的数据分析算法开发一系列存储过程。我认为他的方法很有趣，但从长远来看不是正确的方法。我主要的反对意见是可维护性和缺乏适应性。
#2
5
I'm in the object oriented architects camp. It's not necessarily a crime to put code in the database, as long as you understand the caveats that go along with that. Here are some:
我属于面向对象架构师阵营。把代码放到数据库中并不一定是一种犯罪，只要您理解随之而来的警告。这里有一些:
- It's not debuggable
- 这不是debuggable
- It's not subject to source control
- 它不受源代码控制的约束
- Permissions on your two sets of code will be different
- 您的两组代码的权限将不同
- It will make it more difficult to track where an error in the data came from if you're accessing info in the database from both places
- 如果您从这两个地方访问数据库中的信息，那么跟踪数据中的错误来自何处将变得更加困难
#3
4
Anything that relates to Referential Integrity or Consistency should be in the database as a bare minimum. If it's in your application and someone wants to write an application against the database they are going to have to duplicate your code in their code to ensure that the data remains consistent.
任何与引用完整性或一致性相关的内容都应该作为最低限度存储在数据库中。如果在应用程序中，有人想针对数据库编写应用程序，他们必须在代码中复制您的代码，以确保数据保持一致。
PLSQL for Oracle is a pretty good language for accessing the database and it can also give performance improvements. Your application can also be much 'neater' as it can treat the database stored procedures as a 'black box'.
Oracle的PLSQL是一种非常好的访问数据库的语言，它还可以提高性能。您的应用程序也可以更“整洁”，因为它可以将数据库存储过程视为“黑盒”。
The sprocs themselves can also be tuned and modified without you having to go near your compiled application, this is also useful if the supplier of your application has gone out of business or is unavailable.
sprocs本身也可以进行调优和修改，而不需要靠近编译后的应用程序，如果应用程序的供应商已经破产或不可用，这也很有用。
I'm not advocating 'everything' should be in database, far from it. Treat each case seperately and logically and you will see which makes more sense, put it in the app or put it in the database.
我并不是主张“一切”都应该放在数据库中，远非如此。把每一个案例分开、逻辑地对待，你就会发现哪个更有意义，把它放到应用程序中，或者放到数据库中。
#4
3
I'm coming from almost the same background and have heard the same arguments. I do understand that there are very valid reasons to put logic into the database. However, it depends on the type of application and the way it handles data which approach you should choose.
我来自几乎相同的背景，也听到过同样的观点。我明白，将逻辑放入数据库有非常正当的理由。但是，这取决于应用程序的类型和处理数据的方式，您应该选择哪种方法。
In my experience, a typical data entry app like some customer (or xyz) management will massively benefit from using an ORM layer as there are not so many different views at the data and you can reduce the boilerplate CRUD code to a minimum.
在我的经验中，典型的数据输入应用程序(如某些客户(或xyz)管理程序)将从使用ORM层中获得巨大的好处，因为数据中没有太多不同的视图，您可以将样板CRUD代码减少到最小。
On the other hand, assume you have an application with a lot of concurrency and calculations that span a lot of tables and that has a fine-grained column-level security concept with locking and so on, you're probably better off doing stuff like that directly in the database.
另一方面，假设您有一个具有许多并发性和计算的应用程序，它跨越了许多表，并且有一个带有锁的细粒度的列级安全概念，因此，您最好在数据库中直接执行类似的操作。
As mentioned before, it also depends on the variety of views you anticipate for your data. If there are many different combinations of columns and tables that need to be presented to the user, you may also be better off just handing back different result sets rather than map your objects one-by-one to another representation.
正如前面提到的，它还取决于您对数据的预期的各种视图。如果有许多不同的列和表的组合需要呈现给用户，那么最好只提交不同的结果集，而不是将对象逐个映射到另一个表示。
After all, the database is good at dealing with sets, whereas OO code is good at dealing with single entities.
毕竟，数据库擅长处理集合，而OO代码擅长处理单个实体。
#5
2
Reading these answers, I'm quite confused by the lack of understanding of database programming. I am an Oracle Pl/sql developer, we source control for every bit of code that goes into the database. Many of the IDEs provide addins for most of the major source control products. From ClearCase to SourceSafe. The Oracle tools we use allow us to debug the code, so debugging isn't an issue. The issue is more of logic and accessibility.
读到这些答案，我对数据库编程的缺乏理解感到困惑。我是一个Oracle Pl/sql开发人员，我们对进入数据库的每个代码位进行源代码控制。许多ide都为大多数主要的源控制产品提供addin。从ClearCase SourceSafe。我们使用的Oracle工具允许我们调试代码，所以调试不是问题。问题更多的是逻辑和可访问性。
As a manager of support for about 5000 users, the less places i have to look for the logic, the better. If I want to make sure the logic is applied for ALL applications that use the data , even business logic, i put it in the DB. If the logic is different depending on the application, they can be responsible for it.
作为一个支持5000个用户的经理，我需要寻找的逻辑越少越好。如果我想确保对所有使用数据的应用程序都应用逻辑，甚至是业务逻辑，我将它放在DB中。如果根据应用程序的不同，逻辑是不同的，他们可以对此负责。
#6
1
@DannySmurf:
@DannySmurf:
It's not debuggable
这不是debuggable
Depending on your server, yes, they are debuggable. This provides an example for SQL Server 2000. I'm guessing the newer ones also have this. However, the free MySQL server does not have this (as far as I know).
根据服务器的不同，它们是可以调试的。这为SQL Server 2000提供了一个示例。我猜新的也有这个。但是，免费的MySQL服务器没有这个(据我所知)。
It's not subject to source control
它不受源代码控制的约束
Yes, it is. Kind of. Database backups should include stored procedures. Those backup files might or might not be in your version control repository. But either way, you have backups of your stored procedures.
是的,它是。种。数据库备份应该包括存储过程。这些备份文件可能在您的版本控制存储库中，也可能不在。但是无论哪种方式，您都有存储过程的备份。
#7
1
My personal preference is to try and keep as much logic and configuration out of the database as possible. I am heavily dependent on Spring and Hibernate these days so that makes it a lot easier. I tend to use Hibernate named queries instead of stored procedures and the static configuration information in Spring application context XML files. Anything that needs to go into the database has to be loaded using a script and I keep those scripts in version control.
我个人的偏好是尽可能地在数据库之外保留尽可能多的逻辑和配置。我现在非常依赖于Spring和Hibernate，所以这样做会容易得多。我倾向于使用Hibernate命名查询，而不是在Spring应用程序上下文XML文件中使用存储过程和静态配置信息。任何需要进入数据库的内容都必须使用脚本进行加载，我将这些脚本保存在版本控制中。
#8
0
@Thomas Owens: (re source control) Yes, but that's not source control in the same sense that I can check in a .cs file (or .cpp file or whatever) and go and pick out any revision I want. To do that with database code requires a potentially-significant amount of effort to either retrieve the procedure from the database and transfer it to somewhere in the source tree, or to do a database backup every time a minor change is made. In either case (and regardless of the amount of effort), it's not intuitive; and for many shops, it's not a good enough solution either. There is also the potential here for developers who may not be as studious at that as others to forget to retrieve and check in a revision. It's technically possible to put ANYTHING in source control; the disconnect here is what I would take issue with.
@Thomas Owens: (re source control)是的，但这不是源代码控制，因为我可以检入.cs文件(或.cpp文件或其他文件)，然后挑选我想要的任何修订。要使用数据库代码实现这一点，需要付出相当大的努力，从数据库中检索过程并将其传输到源树中的某个地方，或者在每次进行微小更改时进行数据库备份。无论哪种情况(不管付出多少努力)，它都不是直观的;对许多商店来说，这也不是一个好的解决方案。这里还有一个潜在的开发人员，他们可能不像其他人那样努力学习，在修改中忘记检索和检查。从技术上讲，可以把任何东西放到源代码控制中;这里的脱节是我要讨论的问题。
(re debuggable) Fair enough, though that doesn't provide much integration with the rest of the application (where the majority of the code could live). That may or may not be important.
(重新调试)很公平，尽管这并不能与应用程序的其他部分(大部分代码可以使用的地方)集成。这可能重要，也可能不重要。
#9
0
Well, if you care about the consistency of your data, there are reasons to implement code within the database. As others have said, placing code (and/or RI/constraints) inside the database acts to enforce business logic, close to the data itself. And, it provides a common, encapsulated interface, so that your new developer doesn't accidentally create orphan records or inconsistent data.
如果您关心数据的一致性，那么有理由在数据库中实现代码。正如其他人所说的，在数据库中放置代码(和/或RI/约束)可以增强业务逻辑，接近数据本身。并且，它提供了一个通用的、封装的接口，这样您的新开发人员就不会意外地创建孤立记录或不一致的数据。
#10
-3
Well, this one is difficult. As a programmer, you'll want to avoid TSQL and such "Database languages" as much as possible, because they are horrendous, difficult to debug, not extensible and there's nothing you can do with them that you won't be able to do using code on your application.
这个很难。作为一名程序员，您将尽可能地避免TSQL和类似的“数据库语言”，因为它们非常可怕，难于调试，不可扩展，而且在应用程序上使用代码时，您无法使用它们。
The only reasons I see for writing stored procedures are:
我看到编写存储过程的唯一原因是:
- Your database isn't great (think how SQL Server doesn't implement LIMIT and you have to work around that using a procedure.
- 您的数据库不是很好(想想SQL Server如何没有实现限制，您必须使用一个过程来解决这个问题。
- You want to be able to change a behaviour by changing code in just one place without re-deploying your client applications.
- 您希望能够通过在一个地方更改代码来更改行为，而无需重新部署客户机应用程序。
- The client machines have big calculation-power constraints (think small embedded devices).
- 客户端机器有很大的计算能力限制(想想小型嵌入式设备)。
For most applications though, you should try to keep your code in the application where you can debug it, keep it under version control and fix it using all the tools provided to you by your language.
不过，对于大多数应用程序，您应该尽量将代码保存在应用程序中，以便对其进行调试，将其置于版本控制之下，并使用语言提供的所有工具对其进行修复。