是否允许在流水线 PL/SQL 表函数中使用 SELECT? [英] Is using a SELECT inside a pipelined PL/SQL table function allowed?
问题描述
流水线函数的文档说，在 SQL 语句(通常是 SELECT)中使用 DML 时不允许使用 DML，并且在大多数示例中，流水线函数用于数据生成或转换(接受客户作为参数)，但不发出任何 DML 语句.
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.
现在，从技术上讲，可以使用来自 Oracle 的 SELECT 而不出现任何错误(ORA 14551 不会出现).但是，我遇到了可重复的选择奇怪行为；即使 PRAGMA AUTONOMOUS_TRANSACTION 没有被使用，SELECT 检索的行似乎不总是采用当前的本地交易考虑在内，这对我来说就像一个错误.更令人不安的是，当使用分布式事务(例如通过 ORAMTS 而不是本地事务)时，会使用事务.
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.
事实证明，奇怪的效果似乎与查询中的一些 WITH 语句有关，这些语句有时起作用有时不起作用(取决于 Oracle 优化器的当前心情，至少在10 克).在某些情况下，我得到一个 ORA-32036，然后它又不会发生，根本不更改代码.现在看起来，有时会因 ORA-32036 而失败的查询也是那些也未能使用正确事务的查询，并且它可能与流水线函数无关.
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.
所以我的具体问题是:
是否有任何(最好是官方的)声明是否允许在流水线表函数中使用
SELECT以及它们的事务上下文是什么?
Is there any, preferably official, statement whether
SELECTs in pipelined table functions are allowed and what their transactional context is?
是否有另一种方法可以模块化可在 SQL 语句中使用的常用查询(就像表函数可以使用 TABLE() 一样)?
Is there another way of modularizing commonly used queries which can be used in SQL statements (just as table functions can with TABLE())?
有没有人也经历过这样的行为并且可能对此有更多了解?我已经研究过 metalink，但不幸的是我没有找到任何关于该主题的具体内容.
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.
推荐答案
通常 DML 限制只涉及修改 (UPDATE, DELETE ...) 语句，所以 SELECT 应该没问题.我会尝试从 Oracle 中找到具体的声明.
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.
函数与视图相比有一个缺点:如果它们是从另一个 SELECT 调用的，它们不会在与主 SELECT 相同的时间点执行.对 SELECT 的每次调用都是一致的，但由于 SELECT 在函数代码中而不是在主 SQL 中，因此您可能会返回不一致的结果.这对于视图和子选择是不可能的:如果一个大语句调用一个视图，则视图是在与主查询相同的时间点构建的.
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
您可以构建参数化视图，即依赖于执行前设置的变量的视图.以下是 AskTom 的示例 展示了如何使用 userenv('client_info') 或 dbms_session.set_context 来做到这一点.
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') or dbms_session.set_context.
这篇关于是否允许在流水线 PL/SQL 表函数中使用 SELECT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
