我有一个名为employee_vw的视图,其中包含以下查询:
select function_standard(ename) from employees;
我想找出哪些函数应用于列。我尝试使用select dbms_metadata.get_ddl('COLUMN','ENAME','HR') FROM DUAL
,但没有看到的列对象类型。
我的要求是能够列出应用于整个数据库中指定列的所有函数。有什么办法可以找出来吗?
提前谢谢。
概述
您正在为所调查视图的每个列定义术语查找以下类型的信息 (employee_vw
):
- 列定义术语与系统中的任何函数之间的依赖关系 列
- 定义术语与任何基本视图/表的列之间的依赖关系
AFAIK 数据字典不维护列级别的依赖项。但是,可以查询列目录以及表/视图和函数之间的依赖关系。使用此信息,可以近似得出所需的结果。
利用三种类型的信息:
- 所调查的视图(
employee_vw
)和任何函数之间的依赖关系。 - 所调查的视图 (
employee_vw
) 与其基表和视图之间的依赖关系 - 基表/视图列在所调查视图的列定义术语中出现 (
employee_vw
)。
前两种数据可通过dba_dependencies
系统视图获得。 对于最后一项,基表/视图中的所有列名都与视图定义文本匹配,从而限制任何列名的出现,使其前面必须有任何引用函数的名称。
查询
以下查询实现了上面的思想:
SELECT fndep.referenced_name function_name
, tcol.column_name column_name
, tcol.table_name container_name
FROM dba_dependencies fndep
JOIN dba_dependencies tabdep ON (tabdep.name = fndep.name AND tabdep.type = fndep.type)
JOIN all_views v ON v.view_name = fndep.name
JOIN all_tab_cols tcol ON (tcol.table_name = tabdep.referenced_name)
WHERE fndep.referenced_type = 'FUNCTION'
AND fndep.type = 'VIEW'
AND fndep.name = UPPER('employee_vw')
AND tabdep.referenced_type IN ( 'TABLE', 'VIEW' )
AND lower(v.text_vc) LIKE lower('%'||fndep.referenced_name||'%'||tcol.column_name||'%')
;
基表/视图实际上可能是同义词。以下查询适用于这种情况:
SELECT fndep.referenced_name function_name
, tcol.column_name column_name
, tcol.table_name container_name
FROM dba_dependencies fndep
JOIN dba_dependencies tabsyndep ON (tabsyndep.name = fndep.name AND tabsyndep.type = fndep.type)
JOIN dba_synonyms syn ON (syn.synonym_name = tabsyndep.referenced_name)
JOIN dba_tab_cols tcol ON (tcol.table_name = syn.table_name)
JOIN dba_views v ON v.view_name = fndep.name
WHERE fndep.referenced_type = 'FUNCTION'
AND fndep.type = 'VIEW'
AND fndep.name = UPPER('employee_vw')
AND tabsyndep.referenced_type IN ( 'SYNONYM' )
AND lower(v.text_vc) LIKE lower('%'||fndep.referenced_name||'%'||tcol.column_name||'%')
;
请注意,plsql 包中的函数可以应用于列定义中。如果需要考虑这一点,请在 where 条件中使用fndep.referenced_type IN ( 'FUNCTION', 'PACKAGE' )
。
警告
解决方案是(至少)具有以下缺陷的近似值:
- 引用的函数和基表/视图列的名称可能出现在不同的列定义术语中。( 误报 )
- 引用函数和基表/视图列的名称可能出现在字符串文本中。( 误报 )
- 通过
dba_views.text_vc
提供的视图定义文本限制为 4000 个字符。但是,实际定义可能长达 32767 个字符。后一种情况可以通过检查视图定义长度来检测。完整的文本也可以在第dba_views.text
列中找到,不幸的是数据类型long
,不能轻易操作。(漏报;极端情况下的误报)
如果视图定义的长度超过 4000,则最好在临时表中复制所调查视图的dba_views
记录,将text
替换为等效的 clob 列。可以使用to_lob
进行转换。请注意本文中概述的复杂性。
我不知道如何在不诉诸成熟的 sql 解析的情况下绕过其他警告。