在 Oracle 中使用管道表函数来实现参数化视图功能的注意事项？
我们决定在 Oracle 中的一些常规视图上分层一些“参数化视图”，以便正确鼓励在查询中始终使用正确的 where 谓词。
大部分重复代码（适当连接的表）将在视图中，这样我们就不再有许多不同的过程和函数，它们拥有自己的通用连接和过滤器副本。
然后，我们将在这些视图上分层管道表函数，以确保调用者提供必要的过滤器，以便不会“在所有时间和空间”调用视图。我已经研究了使用 sys_context 和 userenv 以及包变量的替代方案，虽然它们似乎是 Oracle 用户所说的参数化视图，但它们根本不可行，每次使用视图时都将这些垫片放在视图周围，并且它们不可在自加入。
我在很多地方读到过很多相关内容，包括 StackOverflow：
这是一个架构决策，旨在尝试提高应用程序的可维护性，该应用程序因大量重复查询而变得庞大。视图会在某种程度上提供帮助，但我担心我们无法对调用者强制执行谓词以阻止他们做愚蠢的事情。
我在 SQL Server 中使用这种具有内联表值函数的技术取得了很大的成功，它确实有助于使系统更加连贯并且更容易跟踪所提议更改的依赖关系和效果，因为代码更少b) 更多的重用和更少的重复。
我有点担心最后一个链接，这似乎意味着如果我要加入其中一个管道表函数并使用它来更新另一个表，我可能会遇到并发或计时问题。
请分享您使用管道表函数的经验以及我需要注意什么？另外，如果有更好的选择，也请在您的回答中告诉我吗？
We've made a decision to layer some "parameterized views" over some regular views in Oracle in order to properly encourage correct where predicates to always be used in the queries.
The bulk of the repetitive code (tables joined appropriately) will be in the view, so that we will no longer have many different procedures and functions with their own copies of common joins and filters.
Then we will layer pipelined table functions over those views to ensure callers provide the necessary filters so that the views are not called "for all time and space". I have looked at alternatives using sys_context and userenv and package variables and although they appear to be what Oracle users call parameterized views, they simply are not viable to have those shims around a view every time it is used and they are not re-usable in self-joins.
I've read a lot about this in a variety of places, including StackOverflow:
Table-Valued Functions in ORACLE 11g ? ( parameterized views )
Is using a SELECT inside a pipelined PL/SQL table function allowed?
This is an architectural decision to try to improve the maintainability of an application which has become sprawling with a LOT of repeated queries. Views would go some way to helping, but I am worried that we have no way to enforce predicates on callers to stop them from doing silly things.
I've had a great deal of success using this technique in SQL Server with inline table-valued functions and it really helped make the system a lot more coherent and easier to track dependencies and effects of proposed changes, since there was a) less code and b) more re-use and less repetition.
I'm a little worried about that last link, it seems to imply that I might have concurrency or timing issues if I was to join one of these pipelined table functions and use it to update another table.
Please share your experiences with pipelined table functions and what I need to look out for? Also if there is a better alternative, let me know in your answer too?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，在管道函数中查询表的时间点行为与直接或通过视图查询表的行为不同，因此需要考虑这一点。也就是说，如果管道函数查询不经常更新的表，通常不会出现问题。但我想不出任何并发或计时问题。
我为开发人员提供管道函数（而不是使用视图）的主要问题是它们（如某些视图）可能很容易被误用。开发人员可能选择将一个管道函数的结果连接到另一个管道函数，从而导致查询效率非常低，无法利用索引、推送谓词和表约束等功能。
如果可维护性是您的主要问题，那么我更喜欢视图 - 它们可以通过在一个地方定义公共转换以及公共连接来帮助减少重复代码；然而，即使这些也很容易被误用（例如，联接到视图，即使它联接到原始查询不需要的另一个表）。
性能和效率可能是需要注意的事情。对应用程序中的所有 SQL 制定严格的审查制度，以查找编写不当或不一致的查询。
Yes, the point-in-time behaviour of querying a table within a pipelined function is different to that of querying the table directly or via a view, so that needs to be taken into account. That said, it's not usually a problem if the pipelined function is querying an infrequently updated table. I can't think of any concurrency or timing issues though.
My main problem with providing pipelined functions for developers to use (as opposed to using views), is that they (like some views) may be easily misused. Developers may choose to join the results of one pipelined function to another, resulting in very inefficient queries that cannot take advantage of things like indexes, pushed predicates, and table constraints.
If maintainability is your main problem, then I would prefer views - they can help reduce duplicated code by defining common transformations in one place, and perhaps common joins as well; however even these are too easily misused (e.g. joining to a view, even though it joins to another table that is not required by the original query).
Performance and efficiency, will probably be the things to watch out for. Put a rigorous review regime in place for all SQL in the application to look for poorly written or inconsistent queries.