如何检查数据库中是否存在 Oracle 视图？执行查询之前
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10147448/
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
How to check with if an Oracle's view exist in the DB ? Before execute a query
提问by MadMad666
I need to know a way to check from a Java Desktop App, if a Oracle's view exist in the current DB before execute a query otherwise I will get a lot of troubles...
我需要知道一种从 Java 桌面应用程序检查的方法，在执行查询之前当前数据库中是否存在 Oracle 视图，否则我会遇到很多麻烦...
thanks in advance
提前致谢
采纳答案by Justin Cave
You can always query the Oracle data dictionary. Something like
您可以随时查询 Oracle 数据字典。就像是
SELECT COUNT(*)
FROM all_views
WHERE view_name = <<the name of the view>>
AND owner = <<the owner of the view>>
will tell you whether you have access to a view owned by the specified user with the specified name.
将告诉您是否有权访问具有指定名称的指定用户拥有的视图。
Alternately, you can use a more Java-centric approach. You can create a DatabaseMetaDataobject from your Connectionand call getTablesto get a list of all the tables and views that you have access to. You can pass getTablesa specific table or view name (or a pattern) to restrict the results.
或者，您可以使用更以 Java 为中心的方法。您可以DatabaseMetaData从您的Connection和 调用创建一个对象getTables以获取您有权访问的所有表和视图的列表。您可以传递getTables特定的表或视图名称（或模式）来限制结果。
回答by a_horse_with_no_name
SELECT count(*)
FROM user_views
WHERE view_name = 'MY_VIEW'
More details in the manual:
手册中的更多详细信息：
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5499.htm#i1635848
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5499.htm#i1635848
回答by Alexander
In case you want see not only if view exist and if view enable for current user but if view VALID or INVALID you can use select from all_objects table
如果您不仅要查看视图是否存在以及是否为当前用户启用视图，而且如果查看 VALID 或 INVALID，您可以使用 select from all_objects 表
SELECT count(*)
FROM all_objects t
WHERE
t.object_type = 'VIEW'
and t.object_name = 'VIEW_NAME'
and t.status = 'VALID'
回答by MadMad666
Thanks to everyone, finally I got a method that solves this issue, thanks for your suggestions, the code is the following:
谢谢大家，终于找到了解决这个问题的方法，谢谢大家的建议，代码如下：
public boolean existViewInDB(String viewName) {
logger.debug("[boolean existViewInDB(String viewName[" + viewName
+ "])]");
boolean existView = false;
try {
String sql =
"SELECT count(*) FROM user_views WHERE view_name = :viewName";
SQLQuery query = getSession().createSQLQuery(sql);
query.setString("viewName", viewName);
BigDecimal totalOfViews = (BigDecimal) query.uniqueResult();
existView = (totalOfViews.longValue() > 0);
} catch (Exception e) {
logger.error(e, e);
}
logger.debug("Exist View [" + viewName + "] ? -> " + existView);
return existView;
}
This works! :)
这有效！:)
回答by Jeffrey Kemp
Just query it. If it doesn't exist, or your session doesn't have the necessary privileges, Oracle will raise a suitable exception.
查询一下就行了。如果它不存在，或者您的会话没有必要的权限，Oracle 将引发合适的异常。
回答by duffymo
If it's a one-time check I'd say it's fine, but if you perform a query on that view repeatedly I'd say it's a bad idea to check again and again.
如果是一次性检查，我会说它很好，但是如果您对该视图重复执行查询，我会说一次又一次检查是个坏主意。
回答by rlobban
You could use user_views for all views owned by you or ALL_VIEWS for all the views you have access to. I would use all_views
您可以将 user_views 用于您拥有的所有视图，或将 ALL_VIEWS 用于您有权访问的所有视图。我会使用 all_views
SELECT COUNT(*)
FROM ALL_VIEWS
WHERE VIEW_NAME = '[YOUR VIEW NAME']
