在执行表函数之前，动态查询重写或计算查询
首先，我想说明的是，问题不是关于物化视图特性的。
假设我有一个表函数，它返回一组预定义的列.
当函数调用被提交为
SELECT col1, col2, col3
FROM TABLE(my_tfn(:p1))
WHERE col4 = 'X';我可以计算参数并选择要执行的查询。我可以打开一个预定义的游标，也可以动态地组装我的查询。
如果我不计算参数，而是要计算请求查询的文本，怎么办？
例如，如果我的函数返回20列，但查询只请求4，则可以将NULL分配给返回类型的其余16个块，并执行更少的联接。或者我可以将过滤器向下推到我的动态查询。
有什么办法让这事发生吗？更普遍地说，在导出函数之前，是否有一种方法可以查看请求查询？
回答 1
Stack Overflow用户
发布于 2016-08-25 04:37:13
没有可靠的方法来识别调用PL/SQL对象的SQL。
下面是一种不太可靠的方法来识别调用的SQL。我以前使用过这样的代码，但只有在特殊情况下我才知道PL/SQL永远不会并发运行。
这看起来应该很简单。数据字典跟踪所有会话并运行SQL。您可以使用sys_context('userenv', 'sid')找到当前会话，将其与GV$SESSION匹配，然后获取SQL_ID和PREV_SQL_ID。但这两种方法都不包含调用SQL。在CURRENT_SQL中甚至有一个SYS_CONTEXT，但它只适用于细粒度审计。
相反，必须通过字符串搜索找到调用SQL。使用PL/SQL对象的唯一名称将有助于筛选出不相关的语句。为了防止旧语句重新运行，必须在找到共享池后立即从共享池中单独清除SQL。这可能会导致竞争条件，因此这种方法只有在从未并发调用的情况下才能起作用。
--Create simple test type for function.
create or replace type clob_table is table of clob;
--Table function that returns the SQL that called it.
--This requires elevated privileges to run.
--To simplify the code, run this as SYS:
-- "grant execute on sys.dbms_shared_pool to your_user;"
--(If you don't want to do that, convert this to invoker's rights and use dynamic SQL.)
create or replace function my_tfn return clob_table is
v_my_type clob_table;
type string_table is table of varchar2(4000);
v_addresses string_table;
v_hash_values string_table;
begin
--Get calling SQL based on the SQL text.
select sql_fulltext, address, hash_value
bulk collect into v_my_type, v_addresses, v_hash_values
from gv$sql
--Make sure there is something unique in the query.
where sql_fulltext like '%my_tfn%'
--But don't include this query!
--(Normally creating a quine is a challenge, but in V$SQL it's more of
-- a challenge to avoid quines.)
and sql_fulltext not like '%quine%';
--Flush the SQL statements immediately, so they won't show up in next run.
for i in 1 .. v_addresses.count loop
sys.dbms_shared_pool.purge(v_addresses(i)||', '||v_hash_values(i), 'C');
end loop;
--Return the SQL statement(s).
return v_my_type;
end;
/现在，这样的查询将返回自己，显示PL/SQL代码正在读取调用它的SQL：
SELECT * FROM TABLE(my_tfn) where 1=1;
SELECT * FROM TABLE(my_tfn) where 2=2;但是即使你经历了所有这些麻烦--你打算对结果做些什么？解析SQL非常困难，除非您能够确保每个人都遵守严格的语法规则。
https://stackoverflow.com/questions/39135953
复制相似问题
腾讯云开发者
