Oracle - 如何在表中查找列+ 依赖于它们的存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1501693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - how to find columns in a table + stored procedures dependent on them?
提问by niceApp
Scenario:
设想：
I need to list all the columns in a table1and all stored procedures that depends on those columns of this table1. I need to populate column name and stored procedures to a new table.
我需要列出 a 中的所有列table1以及依赖于 this 中这些列的所有存储过程table1。我需要将列名和存储过程填充到新表中。
I created new_table(col1, sProc)and tried to populate the column name and respective stored procedure on this new_table. Code I wrote is given below:
我创建new_table(col1, sProc)并尝试填充列名和相应的存储过程new_table。我写的代码如下：
Declare
Begin
for i in (select column_name from user_tab_columns where lower(table_name) like 'table1') loop
insert into new_table
select i.column_name,
name
from user_source
where lower(text) like '%' || i.column_name || '%';
commit;
end loop;
end;
Result:The scripts run successfully but the no data is populated on this new_table.
结果：脚本成功运行，但此 new_table 上未填充任何数据。
Stress:I tried to solve it for 1 whole day and could not figure it out. Any help on this would be greately appreciated. Thank you once again.
压力：我试图解决它整整1天，但无法解决。对此的任何帮助将不胜感激。再一次谢谢你。
采纳答案by akf
The best you will be able to do is to list the package name (as that is the value in the USER_SOURCE.NAMEfield) along with the column. As rexem indicates in his comment, you dont need to resort to a for loop:
您能做的最好的事情是列出包名称（因为这是USER_SOURCE.NAME字段中的值）以及列。正如 rexem 在他的评论中指出的那样，您不需要求助于 for 循环：
INSERT INTO new_table (col1, sproc)
SELECT i.column_name, u.name
FROM user_tab_columns i,
user_source u
WHERE lower(i.table_name) like 'table1'
AND lower(u.text) like '%' || lower(i.column_name) || '%';
回答by Jim Garrison
One obvious problem is that you're converting the procedure text to lowercase, but not the column name you're looking for.
一个明显的问题是您将过程文本转换为小写，而不是您要查找的列名。
This code has other problems, however. What happens if the column name happens to match some part of the text that isn't a column reference?
但是，此代码还有其他问题。如果列名恰好与不是列引用的文本的某些部分相匹配，会发生什么情况？
回答by APC
You can reduce the false positives by including USER_DEPENDENCIESin the query. You may want to restrict the searched types (or alternatively include TYPEin NEW_TABLE).
您可以通过USER_DEPENDENCIES在查询中包含来减少误报。您可能希望限制搜索的类型（或包含TYPE在 中NEW_TABLE）。
insert into new_table (col1, sproc)
select distinct tc.column_name, sp.name
from user_tab_columns tc
, user_source sp
, user_dependencies d
where d.referenced_name = 'TABLE1'
and d.referenced_type = 'TABLE'
and d.type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION'
, 'PROCEDURE', 'TYPE', 'TRIGGER')
and tc.table_name = 'TABLE1'
and sp.name = d.name
and instr(lower(sp.text), lower(tc.column_name)) > 0
/
