是否允许在流水线 PL/SQL 表函数中使用 SELECT？
管道函数的文档说，在 SQL 语句（通常是 SELECT ）中使用 DML 是不允许的，并且在大多数示例中，管道函数用于数据生成或转换（接受客户）作为参数），但不发出任何 DML 语句。
现在，从技术上讲，可以使用 SELECT 而不会出现 Oracle 中的任何错误 (ORA 14551不会发生）。然而，我经历了选择的可重复的奇怪行为；即使PRAGMA AUTONOMOUS_TRANSACTION没有被使用，SELECT检索到的行似乎并不总是采用当前本地交易记入账户，这对我来说感觉像是一个错误。更令人不安的是，当使用分布式事务时（例如通过 ORAMTS 而不是本地事务），会使用事务。
编辑：事实证明，这种奇怪的效果似乎与查询中的一些WITH语句有关，这些语句有时有效，有时无效（取决于Oracle优化器的当前状态，至少在10g中）。在某些情况下，我得到一个 ORA-32036，然后它又不会发生，根本不需要改变代码。现在看来，有时因 ORA-32036 失败的查询也未能使用正确的事务，并且它可能与管道函数无关。
所以我的具体问题是：
是否有任何（最好是官方的）声明是否允许管道表函数中的 SELECT 以及它们的事务上下文是什么？
是否有另一种模块化常用查询的方法可以在 SQL 语句中使用（就像表函数可以与
TABLE()一起使用）？有没有人也经历过这种行为并且可能了解更多？我研究过metalink，但不幸的是我没有找到有关该主题的任何具体内容。
The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements.
Now, technically, it is possible to use SELECTs without any error from Oracle (ORA 14551 will not occur). However, I have experiences reproducible strange behavior of the select; even though PRAGMA AUTONOMOUS_TRANSACTION is not being used, the rows retrieved by the SELECT seem not always taking the current local transaction into account, which feels like a bug to me. Even more disturbing is the fact that, when using a distributed transaction (for instance via ORAMTS instead of a local transaction), the transaction is used.
Edit: As it turns out, the strange effect seems related to some WITH statements in the query, which sometimes work and sometimes not (depending on the current mood of the Oracle optimizer, at least in 10g). In some cases, I get a ORA-32036, then again it doesn't occur, without changing the code at all. Now it looks as if the queries which sometimes fail with the ORA-32036 are the ones which also fail to use the correct transaction, and it may be unrelated to the pipelined function.
So my specific questions are:
Is there any, preferably official, statement whether
SELECTs in pipelined table functions are allowed and what their transactional context is?Is there another way of modularizing commonly used queries which can be used in SQL statements (just as table functions can with
TABLE())?Has anyone also experienced such behavior and does maybe know more about it? I've looked into metalink, but unfortunately I didn't find anything specific on the topic.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
通常 DML 限制仅涉及修改（UPDATE、DELETE ...）语句，因此 SELECT 应该没问题。我将尝试从 Oracle 中查找特定语句。
视图将是您模块化常用查询的第一个工具。
与视图相比，函数有一个缺点：如果从另一个 SELECT 调用它们，则它们不会在与主 SELECT 相同的时间点执行。对 SELECT 的每次调用都是一致的，但由于 SELECT 位于函数代码中而不是主 SQL 中，因此您可能会返回不一致的结果。对于视图和子选择来说这是不可能的：如果一个大语句调用视图，则视图将在与主查询相同的时间点构建。
更新：关于您对参数化查询的评论
您可以构建参数化视图，即依赖于执行前设置的变量的视图。 这是 AskTom 上的示例< /a> 展示如何使用
userenv('client_info')或dbms_session.set_context来完成此操作。usually DML restrictions only concern modification (UPDATE, DELETE ...) statements so SELECT should be OK. I'll try to find a specific statement from Oracle.
Views would be your first tool to modularize commonly-used queries.
Functions have a drawback over views : if they are called from another SELECT they are not executed at the same point-in-time as the main SELECT. Each call to a SELECT is consistent but since the SELECT are in the function code and not in the main SQL you may return inconsistent results. This is not possible with views and sub-select: if a big statement call a view the view is built at the same point-in-time as the main query.
Update: regarding your comment about parameterized queries
You can build parameterized views, that is views that are dependent upon variables set before execution. Here is an example on AskTom showing how you could do it with
userenv('client_info')ordbms_session.set_context.