blocks|key|3823827|text|通常，我会说尽可能少地使用PL/SQL+--它的可维护性通常要低得多--在我的最后一份工作中，我真的看到了使用它会变得多么混乱和困难。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3823828|但是，由于它是批处理，并且输入和输出都是DB，所以将逻辑放到PL/SQL中是很有意义的，以最大限度地减少“移动部分”。但是，如果是业务逻辑--或者系统的其他部分使用的组件--我会说不要这样做。|3823829|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Normally I say put as little in PL/SQL as possible - it is typically a lot less maintainable - at one of my last jobs I really saw how messy and hard to work with it could get. 

However, since it is batch processing - and since the input and output are both the DB - it makes good sense to put the logic into PL/SQL - to minimize "moving parts". However, if it were business logic - or components used by other pieces of your system - I would say don't do it..

blocks|key|3823891|text|只要您需要执行的计算能够在PL/SQL中充分且可读地捕获到，那么只使用PL/SQL将是最有意义的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3823892|真正的问题在于可维护性--编写不可维护的SQL非常容易，因为一旦脱离了简单的SQL+DML，每个RDBMS都有不同的语法和不同的函数集，而且没有真正的格式化标准。评论等。|3823893|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

As long as the calculations you need to perform can be adequately AND readably captured in PL/SQL, then using only PL/SQL would make the most sense.

The real catch is maintainability -- it's very easy to write unmaintainable SQL, if only because every RDBMS has a different syntax and different function set once you step outside of simple SQL DML, and no real standards for formatting. commenting, etc.

blocks|key|745925|text|它通常不会更具表现力，因为大多数存储过程语言的设计都很糟糕。但它可能会比外部应用程序运行得更快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|745926|我猜归根结底就是您对PL/SQL有多熟悉，有多少时间来编写这篇文章，性能有多重要，以及您是否可以合理地期望维护人员足够熟悉PL/SQL来维护用它编写的大型程序。|745927|如果速度无关紧要，并且维护人员可能不精通PL/SQL，那么使用“传统”语言可能会更好。|745928|您还可以使用混合方法，其中使用PL/SQL生成中间数据(比方说，表连接和求和等)，并使用单独的应用程序来控制流和校验值和错误。|745929|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

It's not usually more expressive because most stored procedure languages suck by design. But it will probably run faster than in an external app.

I guess it boils down to how familiar you are with PL/SQL, how much time you have to write this, how important is performance and if you can reasonably expect maintainers to be familiar enough with PL/SQL to maintain a big program written in it.

If speed is not relevant and maintainers will probably be not PL/SQL proficient, you might be better using a 'traditional' language.

You could also use a hybrid approach, where you use PL/SQL to generate intermediate data (say, table joins and sums or whatever) and a separate application to control flow and check values and errors.

blocks|key|742077|text|不，这不一定是个坏主意。如果解决方案对您来说很简单，并允许您测试和验证每个流程，那么它听起来可能是一个好主意。OO平台对于大型数据集来说可能是不好的(尽管它们不是必须的)，因为对象创建和开销可能会降低性能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742078|Oracle在设计PL/SQL时考虑到了像您这样的问题，如果公司对数据库和PL/SQL有足够的了解，这似乎是一个合理的解决方案。请牢记大型批处理集，因为从PL/SQL到实际SQL引擎的每次调用都是上下文切换，因此应尽可能将单个记录进程批处理在一起以提高性能。|742079|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

No, it isn't necessarily a bad idea. If the solution seems straightforward to you and allows you to test and verify each process, its sounds like it could be a good idea. OO platforms can be (though they don't have to be) bad for large data sets, as object creation and overhead can kill performance.

Oracle designed PL/SQL with problems like yours in mind, if there is sufficient corporate knowledge of the database and PL/SQL this seems like a reasonable solution. Keep large batch sets in mind, as each call from PL/SQL to the actual SQL engine is a context switch, so single record processes should be batched together where possible to improve performance.

blocks|key|745994|text|这是一个加载的问题:)有几个数据库编程架构设计，你应该知道，以及它们的成本/收益是什么。2层通常意味着你有一个客户端连接到一个数据库，发出直接的SQL调用。3层通常意味着您有一个“应用程序服务器”，它向数据库发出直接的SQL调用，但客户端正在与应用程序服务器对话。通常，这提供了“向外扩展”。最后，您有2个1/2分层的应用程序，它们采用类似于2层的格式，只是工作在存储过程中被划分。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|745995|您的流程听起来像是“后台”式的东西，而客户端/流程只需要每月一次聚合和缓存的结果。也就是说，没有连接的代理，并且经常连接，并且说“做这些计算”。取而代之的是，你提到了一个偶尔发生的过程，你可以摆脱非实时。|745996|因此，考虑到这些要求，我想说，一般来说，更接近数据会更快，让SQL+server来做所有的计算。我想你会发现接近数据会给你带来很大的好处。|745997|但是，在执行这些计算时，您可能会发现某些计算不适用于SQL+Server。例如，计算债券或任何固定收益工具的应计利息。在SQL中不是很漂亮，但更适合更丰富的编程语言。但是，如果您只有简单的平均值和其他相对合理的聚合，那么在SQL方面，我将坚持使用存储过程。|745998|因此，仍然没有足够的信息来说明您的计算的性质，或者您的公司要求devs的SQL功能来支持什么，或者您的老板says...but什么因为我了解SQL，并且喜欢保持与数据的密切关系，所以对于这样的任务，我宁愿使用纯SQL/存储过程。|745999|YMMV+:)|746000|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

This is a loaded question :)
There's a couple of database programming architecture designs you should know of, and what their costs/benefits are.
2 Tier generally means you have a client connecting to a DB, issuing direct SQL calls.
3 Tier generally means you have an "application server" that is issuing direct SQL calls to the DB, but the client is talking to the app server. Generally, this affords "scaling out".
Finally, you have 2 1/2 tiered apps that employ a 2 Tier like format, only the work is compartmentalized within stored procedures.

Your process sounds like a "back office" kind of thing, and clients/processes just need results that are being aggregated and cached on a once a month basis.
That is, there is no agent that connects, and connects often, and says "do these calculations". Instead you allude to a process that happens once in a while, and you can get away with non-real time.

Therefore, given those requirements, I'd say that generally, it will be faster to be closer to the data, and let SQL server do all the calculations.
I think you'll find that proximity to the data will serve you well.

However, in performing these calculations, you may find that some calculations are not amenable to SQL Servers. Take for example calculating the accrued interest of a bond, or any fixed income instrument. Not very pretty in SQL, and much more suited for a richer programming language. However, if you just have simple averages and other relatively sane aggregates, I'd stick to stored procedures, on the SQL side. 

So again, there's not enough information as to the nature of your calculations, or what your house mandates in terms of SQL capabilities of devs for support, or what your boss says...but since I know my way around SQL, and like to stay close to the data, I'd stay pure SQL/Stored Procedures for a task like this.

YMMV :)

blocks|key|3824155|text|我已经使用C#和SQL创建了批处理程序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3824156|C#的优点：|3824157|3824158|您已经获得了.NET的完整库和OO语言的所有功能。|unordered-list-item|3824159|3824160|C#的缺点：|3824161|3824162|批处理程序和数据库分开-这意味着，您必须将批处理程序与数据库分开管理。|3824163|您需要转义所有该死sql代码|3824164|3824165|SQL的优点：|3824166|3824167|与数据库管理系统很好地集成在一起。如果此作业仅操作数据库，则将其包含在数据库中将是有意义的。您最终将一个数据库及其所有组件放在一个包中。|3824168|不需要转义sql代码|3824169|使其保持真实-您是在您的问题域|3824170|中进行编程。|3824171|SQL的缺点：|3824172|3824173|它的SQL，而我个人并不像C#那样了解它。|3824174|3824175|一般来说，我会坚持使用SQL，因为上面概述了这些优点。|3824176|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1C|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|1E|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1G|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|L|3|M|5|G|7|1I|8|@]|9|@]|A|$]]|$1|N|3|O|5|G|7|1J|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1L|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|T|3|U|5|G|7|1N|8|@]|9|@]|A|$]]|$1|V|3|W|5|G|7|1O|8|@]|9|@]|A|$]]|$1|X|3|Y|5|G|7|1P|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1R|8|@]|9|@]|A|$]]|$1|13|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|14|3|15|5|G|7|1T|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|1V|8|@]|9|@]|A|$]]|$1|19|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|1A|$]]

I've created batch programs using C# and SQL.
Pros of C#:
<ul>
<li>You've got the full library of .NET and all the power of an OO
language.</li>
</ul>
Cons of C#:
<ul>
<li>Batch program and db separate - this means, you'll have to manage your batch program separate from the database.</li>
<li>You need to escape all that dang sql code</li>
</ul>
Pros of SQL:
<ul>
<li>Integrates nicely with the DBMS. If this job only manipulates the database, it would make sense to include it with the database. You end up with a single db and all of its components in one package.</li>
<li>No need to escape sql code</li>
<li>keeping it real - you are programming in your problem domain</li>
</ul>
Cons of SQL:
<ul>
<li>Its SQL and I personally just don't know it as well as C#.</li>
</ul>
In general, I would stick with using SQL because of the Pros outlined above.

blocks|key|5234535|text|我为一个项目用PL/SQL语言和Pro_C编写了大量的批处理和报告生成程序。他们通常更喜欢我用PL/SQL语言编写，因为他们自己的开发人员将来会发现这比Pro_C代码更容易理解。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5234536|它最终只是用Pro*C编写的真正时髦的处理或报告。|5234537|没有必要像其他人提到的那样将它们编写为存储过程，它们可以是根据需要运行的脚本文件，有点像shell脚本。使源代码修订控制和测试和生产系统之间的迁移也变得非常容易。|5234538|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I wrote a huge amount of batch processing and report generation programs in both PL/SQL and ProC for one project. They generally preferred I write in PL/SQL as their own developers who would maintain in the future found that easier to understand than ProC code.

It ended up being only the really funky processing or reports that ended up being written in Pro*C.

It is not necessary to write these as stored procedures as other people have alluded to, they can be just script files that are run as necessary, kind of like a shell script. Make source code revision control and migration between test and production systems a heck of a lot easier, too.

blocks|key|5234619|text|只要确保你以某种方式记录了正在发生的事情，而它正在工作。否则你会有一个黑匣子，如果它在某个地方卡住了几个小时，你会想是停止它还是让它工作“多一点”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5234620|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Just make sure you somehow log what is happening while it's working. Otherwise you'll have a black box and if it gets stuck somewhere for hours, you'll be wondering whether to stop it or let it work 'a little bit more'.

blocks|key|3824370|text|其他评论者需要注意的是，这个问题是关于PL/SQL的，而不是关于SQL的。显然，有些答案是关于SQL的，而不是PL/SQL。PL/SQL是一种功能完备的数据库语言，也是一种成熟的数据库语言。有一些缺点，但对于海报想要做的事情类型来说，它是非常好的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3824371|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Something for other commenters to note - the question is about PL/SQL, not about SQL. Some of the answers have obviously been about SQL, not PL/SQL. PL/SQL is a fully functional database language, and it's mature as well. There are some shortcomings, but for the type of thing the poster wants to do, it's very good.

blocks|key|3824434|text|PL/SQL是一种成熟的语言，可以很好地与SQL集成。对于每个版本的Oracle，它都变得越来越强大。同样从Oracle+11开始，PL/SQL在默认情况下编译为机器码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3824435|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

PL/SQL is a mature language that integrates well with SQL. With each version of Oracle it becomes more and more powerful.
Also starting from Oracle 11, PL/SQL compiles to machine code by default.

blocks|key|3824511|text|您将描述以下需求|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3824512|a)必须能够实现批处理b)结果必须是可维护的|3824513|我的回答是：|3824514|3824515|PL/SQL就是为实现您所描述的功能而设计的。还需要注意的是，PL/SQL中有其他工具所不具备的效率。存储过程语言将处理放在数据旁边-这是批处理应该位于的位置。|ordered-list-item|3824516|用任何语言编写难以维护的代码都很容易。|3824517|3824518|话虽如此，您的实现将取决于可用的技能、适当的设计和对高质量流程的坚持。|3824519|为了提高效率，您的实现必须批量处理数据(批量选择和批量插入/更新)。OO方法的危险在于，它很容易导致设计逐行处理数据。这种类型的方法包含不必要的开销，并且与成批处理数据的设计相比，效率要低得多。|3824520|成功地使用这两种方法是可能的。|3824521|马修·巴特勒|3824522|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|I|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

You describe the following requirements

a) Must be able to implement Batch Processing
b) Result must be maintainable

My Response:

<ol>
<li>PL/SQL was designed to achieve just what you describe. It's also important to note that there are efficiencies in PL/SQL that are not available in other tools. An stored procedure language put the processing next to the data - which is where batch processing ought to sit.</li>
<li>It easy enough to write poorly maintainable code in any language.</li>
</ol>

Having said the above, your implementation will depend on the available skills, a proper design and adherence to good quality processes.

To be efficient your implementation must process data in batches ( select in batches and insert/update in batches ). The danger with an OO approach is that it is easy to be led towards a design that processes data row by row. This type of approach contains unnecessary overhead, and will be significantly less efficient than a design that processes data in batches of rows.

It is possible to use both approaches successfully.

Mathew Butler

I'm starting work on a program which is perhaps most naturally described as a batch of calculations on database tables, and will be executed once a month. All input is in Oracle database tables, and all output will be to Oracle database tables. The program should stay maintainable for many years to come.

It seems straight-forward to implement this as a series of stored procedures, each performing a sensible transformation, for example distributing costs among departments according to some business rules. I can then write unit tests to check if the output of each transformation is as I expected.

Is it a bad idea to do this all in PL/SQL? Would you rather do heavy batch calculations in a typical object oriented programming language, such as C#? Isn't it more expressive to use a database centric programming language such as PL/SQL?

Is it stupid to write a large batch processing program entirely in PL/SQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在开始一个程序的工作，这个程序可能最自然地被描述为数据库表上的一批计算，并且将每月执行一次。所有输入都在Oracle数据库表中，所有输出都将写入Oracle数据库表。该程序应该在未来许多年内保持可维护性。将其实现为一系列存储过程似乎很简单，每个存储过程执行一个合理的转换，例如根据某些业务规则在部门之间分配成本。然后...

问完全用PL/SQL编写大型批处理程序是愚蠢的吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问完全用PL/SQL编写大型批处理程序是愚蠢的吗？EN