You're not going to like this, and I'm probably going to get downvoted into oblivion, but I'm with the rest of your comapny.
Stored Procedures used to offer many benefits (security, performance, etc.) but with parameterized queries and better query optimization, stored procedures really just add another layer of overhead to your application and give you another place you need to update/modify code.
I prefer to keep everything in a single spot so that when I need to edit code, I can go to one place and make my changes there.
If you want more details about the arguments for moving away from Stored Prcoedures, check out this CodingHorror article:
...and I just noticed that the article is from 2004. I have to believe that databases have gotten better since then which means this would ring even more true today than it did then.
请注意，如果涉及编程技能，您可以使用任何 IBM i 语言（包括 Java）编写存储过程。此外，您可以访问完整的机器，而不仅仅是一些数据库内部。在这方面，AS/400 与任何其他数据库产品有很大不同，以至于其他数据库的经验（在我看来）并不适用。
我会推荐 Midrange 邮件列表，因为据我所知，它们集中了最集中的 AS/400 编程技能。
Doing everything over JDBC essentially means that you are inserting a network layer between you and the database. All in all it means that data is more "remote" and come to you slower. Stored procedures can work directly on the data inside the database, and the resulting difference in speed may astonish you.
Please note that you can write stored procedures in any IBM i language including Java, in case it is a matter of programmings skills. Also, you have access to the FULL machine, not just some database internals. Here the AS/400 is so vastly different from any other database product, that experiences from other databases simply - in my opinion - does not apply.
I would recommend the Midrange mailing lists as they have the largest concentration of AS/400 programming skills I know of.
然而，从长远来看，直接与数据库交互的应用程序可能会消失。一切都将与服务总线通信，这将决定从哪里获取数据。当然，通过精心设计的存储过程 API 公开数据库的商店可能会发现比那些必须从 ORM 逻辑中提取所有内容的地方更容易迁移到这个美丽的新世界。
This is one of those Marmite issues. if you are primarily a database programmer you will think that stored procedures should be used extensively. If you are an application programmer - say a Java or a .Net coder - the chances are you will say that they should be avoided completely.
Not that this meets application programmers want to write their own SQL statements. No, these days they tend to want to abstract everything behind convoluted ORM services. These are not easier to understand than stored procedures but are available within the same IDE, so they require less context switching.
There are two big things in favour of stored procedures. The first is that people who know PL/SQL are likely to be familiar with Oracle databases (T-SQL & SQL Server, etc), and so will tend to write better programs for that database (defined as programs which take advantage of the platform's features and are fitted to its functionality) than people who don't.
The second thing is that data persists. Application developers are fond of talking about "database independence" but what really matters is application independence. Front-ends come and go but the data model endures forever. In the last ten years Java applications have been written as Applets, Servlets, JSPs, Tiles and Faces, with add-ons in JavaScript, Groovy, AJAX and JSON, connecting to the database through hand-rolled JDBC, EJB (v1,2,3), TopLink, Hibernate and IBatis... forgive me if I've missed a few. Applications whose UI is a skin over a layer of stored procedures are easier to upgrade to the latest and greatest than applications where the business logic has to be re-written every time. And they will perform better too.
However, in the long run applications which interact directly with the database are probably going to die away. Everything is going to talk to the service bus, and that will decide from where to get the data. Of course, shops where the database is exposed through a well-designed API of stored procedures may find it easier to move to this brave new world than those places which are going to have to extract everything out of their ORM logic.
First if you use them exclusively, they make refactoring the database much simpler as you can use the dependencies stored in the database to find out what would be affected by a change (well in SQL Server anyway, can't speak for other datbases).
Second, if you need to change just the query, they are far simpler to deploy.
They are also easier to performance tune as they can easily be called without firing up the application.
If you have complex logic then you save some performance by not having to send all that over the network to the database server. May not seem like a big gain, but if the complex query is run thousands of times a day, it can add up.
Security is also extremely important. If you do not use store procedures, you must set rights at the table or view level. This opens up the database to internal fraud. Yes, parameterized queries reduce the risk of sql injection, but that is not the only threat you need to guard against. If you have personal or financial data and you do not use stored procs (and ones with NO dynamic SQl) and limit your users to only being able to do things through the procs, then your system is in extreme danger from internal employees who can steal data or bypass internal controls to steal money. Read about internal controls in the accounting standards to see why this is a problem.
ORMs also tend to write just downright bad SQL code especially if the query is complex. Further as people start to use them instead of stored procs, I have found that the people who have never used stored procs have a poorer understanding of how to get data out of the database and frequently get the wrong data. Using an ORM is fine if you already understand SQL and can determine when to rewrite the autogenerated code into something that works better. But too many users don't have the skill to write complex code because they never learned the basics.
Finally since you already have stored procs for your application, getting rid of them altogether is a way to introduce new bugs becasue you had to generate new code.
当您拥有一组分层的应用程序时，它们非常有用。例如，具有提供原子操作（恰好是存储过程）的 Web 服务的单核心数据库和使用这些 WS 的 ESB 或一组应用程序。 在单应用程序/单数据库的情况下，想法是将代码保留在其他人建议的一个位置。
但好吧，那只是我。
They're useful when you have a layered set of apps. For example, a single core DB with web services offering the atomic operations (which happen to be stored procedures) and a ESB or a set of applications consuming those WSs. In a single-app/single-db case, the idea is to keep the code in one place as others suggested.
I am a long-time Java developer who has recently come across several projects that made heavy use of stored procedures that have put the use of stored procedures in a really bad light for me.
Having said that, I am reluctant to make a blanket statement that stored procedures are bad as a system design option, because really it depends on the project in question and what the particular stored procedures are trying to accomplish.
My preference is to avoid any kind of stored procedure for simple CRUD operations (it may sound laughable to some to have stored procedures handle these operations, but I've encountered several systems that were doing this) -- This ends up resulting in a lot of code having to be written (and tested and maintained) on the Java side to manage these procedure calls from what I've observed. It's better to just use Hibernate (or some other ORM library) to handle these kinds of operations...if for no other reason than it tends to reduce the amount of code needing to be maintained. It also can cause problems when trying to refactor or make any significant changes to the system, as you're not just having to concern yourself with class/table changes, but stored procedures that handle CRUD ops as well. And this can be exacerbated further if you're in a situation where developers cannot make changes to the database themselves, or there is some formal process in place to coordinate changes between the two parts of the system.
On the other hand, having stored procedures that require limited interaction with the Java code (basically, you just fire off a call to one with a few arguments), and run in a semi-autonomous fashion is not a terrible thing either. I've encountered a few situations (particularly where we were migrating or importing data into a system) where using a stored procedure was a much better route than writing a bunch of Java code to handle the functionality.
I guess the real answer here would be that you should be examining what each store procedure in the system is doing currently and evaluate them on a case-by-case basis to determine if perhaps it's easier to handle the operation in Java or the database. Some may very well work better in Java (either by ORM library, or actual hand-written code), some may not. In either case, the goal should always be to make sure the system is understandable and easy to maintain for everyone, not just whether stored procedures are good or bad in and of themselves.
发布评论
评论（6）
你不会喜欢这个，我可能会被否决而被遗忘，但我和你公司的其他人在一起。
存储过程曾经提供了许多好处（安全性、性能等），但通过参数化查询和更好的查询优化，存储过程实际上只是给您的应用程序增加了另一层开销，并为您提供了另一个需要更新/修改代码的地方。
我更喜欢将所有内容保留在一个位置，这样当我需要编辑代码时，我可以去一个地方并在那里进行更改。
如果您想了解有关放弃存储过程的更多详细信息，请查看这篇 CodingHorror 文章：
编码恐怖：谁需要存储过程？
...我刚刚注意到这篇文章是 2004 年的。我必须相信从那时起数据库已经变得更好了，这意味着这在今天听起来比当时更加真实。
You're not going to like this, and I'm probably going to get downvoted into oblivion, but I'm with the rest of your comapny.
Stored Procedures used to offer many benefits (security, performance, etc.) but with parameterized queries and better query optimization, stored procedures really just add another layer of overhead to your application and give you another place you need to update/modify code.
I prefer to keep everything in a single spot so that when I need to edit code, I can go to one place and make my changes there.
If you want more details about the arguments for moving away from Stored Prcoedures, check out this CodingHorror article:
Coding Horror: Who Needs Stored Procedures Anyway?
...and I just noticed that the article is from 2004. I have to believe that databases have gotten better since then which means this would ring even more true today than it did then.
通过 JDBC 完成所有操作本质上意味着您在您和数据库之间插入一个网络层。总而言之，这意味着数据更加“远程”并且到达您的速度更慢。存储过程可以直接处理数据库内的数据，由此产生的速度差异可能会让您感到惊讶。
请注意，如果涉及编程技能，您可以使用任何 IBM i 语言（包括 Java）编写存储过程。此外，您可以访问完整的机器，而不仅仅是一些数据库内部。在这方面，AS/400 与任何其他数据库产品有很大不同，以至于其他数据库的经验（在我看来）并不适用。
我会推荐 Midrange 邮件列表，因为据我所知，它们集中了最集中的 AS/400 编程技能。
Doing everything over JDBC essentially means that you are inserting a network layer between you and the database. All in all it means that data is more "remote" and come to you slower. Stored procedures can work directly on the data inside the database, and the resulting difference in speed may astonish you.
Please note that you can write stored procedures in any IBM i language including Java, in case it is a matter of programmings skills. Also, you have access to the FULL machine, not just some database internals. Here the AS/400 is so vastly different from any other database product, that experiences from other databases simply - in my opinion - does not apply.
I would recommend the Midrange mailing lists as they have the largest concentration of AS/400 programming skills I know of.
这是马麦酱问题之一。如果您主要是一名数据库程序员，您会认为应该广泛使用存储过程。如果您是一名应用程序程序员（例如 Java 或 .Net 编码员），您很可能会说应该完全避免使用它们。
这并不是说这满足了应用程序程序员想要编写自己的SQL语句。不，现在他们倾向于抽象复杂的 ORM 服务背后的所有内容。这些过程并不比存储过程更容易理解，但可以在同一 IDE 中使用，因此它们需要较少的上下文切换。
有两件大事支持存储过程。首先，了解 PL/SQL 的人可能熟悉 Oracle 数据库（T-SQL 和 SQL Server 等），因此倾向于为该数据库编写更好的程序（定义为利用平台的特性并与其功能相适应），而不是那些不了解平台的人。
第二件事是数据持续存在。应用程序开发人员喜欢谈论“数据库独立性”，但真正重要的是应用程序独立性。前端来来去去，但数据模型永远存在。在过去十年中，Java 应用程序已被编写为 Applet、Servlet、JSP、Tiles 和 Faces，并带有 JavaScript、Groovy、AJAX 和 JSON 中的附加组件，通过手动 JDBC、EJB（v1,2、 3)、TopLink、Hibernate 和 IBatis...如果我遗漏了一些，请原谅我。 UI 是存储过程层之上的应用程序比每次都必须重新编写业务逻辑的应用程序更容易升级到最新和最好的版本。他们也会表现得更好。
然而，从长远来看，直接与数据库交互的应用程序可能会消失。一切都将与服务总线通信，这将决定从哪里获取数据。当然，通过精心设计的存储过程 API 公开数据库的商店可能会发现比那些必须从 ORM 逻辑中提取所有内容的地方更容易迁移到这个美丽的新世界。
This is one of those Marmite issues. if you are primarily a database programmer you will think that stored procedures should be used extensively. If you are an application programmer - say a Java or a .Net coder - the chances are you will say that they should be avoided completely.
Not that this meets application programmers want to write their own SQL statements. No, these days they tend to want to abstract everything behind convoluted ORM services. These are not easier to understand than stored procedures but are available within the same IDE, so they require less context switching.
There are two big things in favour of stored procedures. The first is that people who know PL/SQL are likely to be familiar with Oracle databases (T-SQL & SQL Server, etc), and so will tend to write better programs for that database (defined as programs which take advantage of the platform's features and are fitted to its functionality) than people who don't.
The second thing is that data persists. Application developers are fond of talking about "database independence" but what really matters is application independence. Front-ends come and go but the data model endures forever. In the last ten years Java applications have been written as Applets, Servlets, JSPs, Tiles and Faces, with add-ons in JavaScript, Groovy, AJAX and JSON, connecting to the database through hand-rolled JDBC, EJB (v1,2,3), TopLink, Hibernate and IBatis... forgive me if I've missed a few. Applications whose UI is a skin over a layer of stored procedures are easier to upgrade to the latest and greatest than applications where the business logic has to be re-written every time. And they will perform better too.
However, in the long run applications which interact directly with the database are probably going to die away. Everything is going to talk to the service bus, and that will decide from where to get the data. Of course, shops where the database is exposed through a well-designed API of stored procedures may find it easier to move to this brave new world than those places which are going to have to extract everything out of their ORM logic.
好吧，我会支持存储过程。
首先，如果您专门使用它们，它们会使数据库重构变得更加简单，因为您可以使用数据库中存储的依赖关系来找出更改会影响哪些内容（无论如何，在 SQL Server 中，不能代表其他数据库）。
其次，如果您只需要更改查询，那么部署起来要简单得多。
它们也更容易进行性能调整，因为可以轻松调用它们而无需启动应用程序。
如果您有复杂的逻辑，那么您不必通过网络将所有逻辑发送到数据库服务器，从而节省一些性能。看起来可能不是很大的收获，但如果复杂的查询每天运行数千次，它就会增加。
安全也极其重要。如果不使用存储过程，则必须在表或视图级别设置权限。这使得数据库容易遭受内部欺诈。是的，参数化查询降低了 SQL 注入的风险，但这并不是您需要防范的唯一威胁。如果您拥有个人或财务数据，并且不使用存储过程（以及没有动态 SQl 的存储过程）并限制您的用户只能通过过程执行操作，那么您的系统将面临内部员工的极大危险，他们可能会窃取数据数据或绕过内部控制来窃取资金。阅读会计准则中的内部控制，了解为什么这是一个问题。
ORM 还倾向于编写完全错误的 SQL 代码，尤其是在查询很复杂的情况下。此外，当人们开始使用它们而不是存储过程时，我发现从未使用过存储过程的人对如何从数据库中获取数据的理解较差，并且经常得到错误的数据。如果您已经了解 SQL 并且可以确定何时将自动生成的代码重写为更有效的代码，那么使用 ORM 就很好。但太多的用户不具备编写复杂代码的技能，因为他们从未学习过基础知识。
最后，由于您已经为应用程序存储了存储过程，因此完全删除它们是引入新错误的一种方法，因为您必须生成新代码。
OK I'll come out in favor of stored procs.
First if you use them exclusively, they make refactoring the database much simpler as you can use the dependencies stored in the database to find out what would be affected by a change (well in SQL Server anyway, can't speak for other datbases).
Second, if you need to change just the query, they are far simpler to deploy.
They are also easier to performance tune as they can easily be called without firing up the application.
If you have complex logic then you save some performance by not having to send all that over the network to the database server. May not seem like a big gain, but if the complex query is run thousands of times a day, it can add up.
Security is also extremely important. If you do not use store procedures, you must set rights at the table or view level. This opens up the database to internal fraud. Yes, parameterized queries reduce the risk of sql injection, but that is not the only threat you need to guard against. If you have personal or financial data and you do not use stored procs (and ones with NO dynamic SQl) and limit your users to only being able to do things through the procs, then your system is in extreme danger from internal employees who can steal data or bypass internal controls to steal money. Read about internal controls in the accounting standards to see why this is a problem.
ORMs also tend to write just downright bad SQL code especially if the query is complex. Further as people start to use them instead of stored procs, I have found that the people who have never used stored procs have a poorer understanding of how to get data out of the database and frequently get the wrong data. Using an ORM is fine if you already understand SQL and can determine when to rewrite the autogenerated code into something that works better. But too many users don't have the skill to write complex code because they never learned the basics.
Finally since you already have stored procs for your application, getting rid of them altogether is a way to introduce new bugs becasue you had to generate new code.
当您拥有一组分层的应用程序时，它们非常有用。例如，具有提供原子操作（恰好是存储过程）的 Web 服务的单核心数据库和使用这些 WS 的 ESB 或一组应用程序。
在单应用程序/单数据库的情况下，想法是将代码保留在其他人建议的一个位置。
但好吧，那只是我。
They're useful when you have a layered set of apps. For example, a single core DB with web services offering the atomic operations (which happen to be stored procedures) and a ESB or a set of applications consuming those WSs.
In a single-app/single-db case, the idea is to keep the code in one place as others suggested.
But well, that's just me.
我是一名资深的 Java 开发人员，最近遇到了几个大量使用存储过程的项目，这让我对存储过程的使用产生了非常不好的印象。
话虽如此，我不愿意笼统地指出存储过程作为系统设计选项是不好的，因为它实际上取决于所讨论的项目以及特定存储过程试图完成的任务。
我的偏好是避免使用任何类型的存储过程来进行简单的 CRUD 操作（对于某些人来说，让存储过程处理这些操作可能听起来很可笑，但我遇到过几个正在这样做的系统）——这最终会导致很多问题根据我的观察，必须在 Java 端编写（并测试和维护）代码来管理这些过程调用。最好只使用 Hibernate（或其他一些 ORM 库）来处理此类操作……如果没有其他原因，它往往会减少需要维护的代码量。当尝试重构或对系统进行任何重大更改时，它也可能会导致问题，因为您不仅需要关心类/表的更改，还需要关心处理 CRUD 操作的存储过程。如果开发人员无法自行更改数据库，或者有一些正式流程来协调系统两个部分之间的更改，则这种情况可能会进一步加剧。
另一方面，拥有需要与 Java 代码进行有限交互的存储过程（基本上，您只需使用几个参数触发对一个调用）并以半自主方式运行也不是一件可怕的事情。我遇到过一些情况（特别是当我们将数据迁移或导入到系统中时），使用存储过程比编写一堆 Java 代码来处理功能要好得多。
我想这里真正的答案是，您应该检查系统中的每个存储过程当前正在做什么，并根据具体情况对它们进行评估，以确定在 Java 或数据库中处理操作是否更容易。有些可能在 Java 中工作得更好（通过 ORM 库，或者实际的手写代码），有些可能不会。无论哪种情况，目标始终应该是确保系统对每个人来说都是可以理解和易于维护的，而不仅仅是存储过程本身是好是坏。
I am a long-time Java developer who has recently come across several projects that made heavy use of stored procedures that have put the use of stored procedures in a really bad light for me.
Having said that, I am reluctant to make a blanket statement that stored procedures are bad as a system design option, because really it depends on the project in question and what the particular stored procedures are trying to accomplish.
My preference is to avoid any kind of stored procedure for simple CRUD operations (it may sound laughable to some to have stored procedures handle these operations, but I've encountered several systems that were doing this) -- This ends up resulting in a lot of code having to be written (and tested and maintained) on the Java side to manage these procedure calls from what I've observed. It's better to just use Hibernate (or some other ORM library) to handle these kinds of operations...if for no other reason than it tends to reduce the amount of code needing to be maintained. It also can cause problems when trying to refactor or make any significant changes to the system, as you're not just having to concern yourself with class/table changes, but stored procedures that handle CRUD ops as well. And this can be exacerbated further if you're in a situation where developers cannot make changes to the database themselves, or there is some formal process in place to coordinate changes between the two parts of the system.
On the other hand, having stored procedures that require limited interaction with the Java code (basically, you just fire off a call to one with a few arguments), and run in a semi-autonomous fashion is not a terrible thing either. I've encountered a few situations (particularly where we were migrating or importing data into a system) where using a stored procedure was a much better route than writing a bunch of Java code to handle the functionality.
I guess the real answer here would be that you should be examining what each store procedure in the system is doing currently and evaluate them on a case-by-case basis to determine if perhaps it's easier to handle the operation in Java or the database. Some may very well work better in Java (either by ORM library, or actual hand-written code), some may not. In either case, the goal should always be to make sure the system is understandable and easy to maintain for everyone, not just whether stored procedures are good or bad in and of themselves.