在PL/sql oracle存储过程中，尝试使用解析函数
在存储过程中使用游标或分析函数是否可以提高性能？
Is it better to use cursor or analytical functions in a stored procedure for performance improvement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
以集合方式使用纯 SQL 比使用游标来处理数据更好 RBAR。这是因为 PL/SQL 和 SQL 引擎之间的上下文切换是一种开销。游标及其所有附加代码也是一种开销。
分析函数是 SQL 的出色扩展，它允许我们在 SELECT 语句中执行以前需要过程代码的操作。
当然，如果您希望处理大量数据，则 批量收集和 FORALL 语句 绝对是最好的方法。如果您需要使用 LIMIT 子句，则显式光标是不可避免的。
It is better to use pure SQL in a set-wise fashion than to use cursors to process data RBAR. This is because the context switching between the PL/SQL and SQL engines is an overhead. Cursors and all their additional code are also an overhead.
Analytic functions are an excellent extension to SQL which allow us to do stuff in a SELECT statement which previously would have required procedural code.
Of course, if you're looking to process large amounts then bulk collection and the FORALL statement are definitely the best approach. If you need to use the LIMIT clause then explicit cursors are unavoidable.
如果不需要，最好不使用游标。
It's always preferable to not use cursors if you don't have to.
与游标相比，使用纯 SQL 语句始终是更好的选择。当使用纯 SOL 处理数据很麻烦时，可以使用游标。
使用游标，我们每行处理一行。通过批量收集，我们减少了上下文切换，但我们仍然使用限制子句在一个周期中仅处理几千行。使用纯 SQL，只要有足够的资源可用，就可以插入或更新更多行。回滚段是一个限制因素。在一个项目中，我编写了非常复杂的纯 SQL，在非常大的数据仓库环境中插入数百万行，但失败了。 DBA 提供了非常大的回滚段。我无法通过批量收集来实现这种性能。
请注意，大多数公司不会为您提供这种便利。因此，请根据可用资源做出决定。您可以结合使用两者。
Using Pure SQL statement is always is better choice compared to cursor. Cursors are used when it is cumbersome to process the data using pure SOL.
Using cursor we process one row each. With bulk collection we reduce context switching but we still be processing only few thousand rows in a cycle using Limit Clause. Using pure SQL, it is possible to insert or update more rows provided enough resources are available. Rollback segment is a limiting factor. In one project I wrote very complex pure SQL to insert millions of rows in very large data warehousing environment with failure. The DBA had made very large rollback segment available. I could not achieve that kind of performance with bulk collection.
Please beware, most companies will not give you that kind of facility. So please take a decision based on the available resources. You can use combination of both.