oracle oracle中如何判断对象是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29206976/
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 object exists or not in oracle
提问by Nishantha
We can check whether object exist or not in T-SQL using following query.
我们可以使用以下查询检查 T-SQL 中是否存在对象。
IF OBJECT_ID('TBL_COMP_ASSESSMENT') IS NOT NULL
DROP TABLE TBL_COMP_ASSESSMENT
Is there any way to achieve this in Oracle?
有什么办法可以在 Oracle 中实现这一点吗？
回答by mbsingh
You can use ALL_OBJECTS table. Something like this should do:
您可以使用 ALL_OBJECTS 表。这样的事情应该做：
SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = '<OBJ_NAME_HERE>'
You can add more conditions as per your requirement:
您可以根据需要添加更多条件：
AND OWNER='<OWNER>' AND OBJECT_TYPE='<TYPE>'
Full details here: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm
此处的完整详细信息：http: //docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm
回答by Lalit Kumar B
It is a bad idea to DROPand CREATEtables on the fly. T-SQLand PL/SQLare very different. PL/SQL is compiled prior to execution and reside in database. What you do in T-SQL might not be good in PL/SQL. You could TRUNCATEthe table rather than dropping it on the fly.
动态删除和创建表是一个坏主意。T-SQL和PL/SQL非常不同。PL/SQL 在执行之前被编译并驻留在数据库中。你在 T-SQL 中所做的在 PL/SQL 中可能并不好。您可以TRUNCATE表而不是立即删除它。
Anyway, if you really want to do it, then you need to (ab)use EXECUTE IMMEDIATEto do this.
无论如何，如果您真的想这样做，那么您需要（ab）使用EXECUTE IMMEDIATE来执行此操作。
For example,
例如，
SQL> DECLARE
2 cnt NUMBER;
3 BEGIN
4 SELECT Count(*)
5 INTO cnt
6 FROM user_tables
7 WHERE table_name = 'TBL_COMP_ASSESSMENT';
8
9 IF cnt = 1 THEN
10 BEGIN
11 EXECUTE IMMEDIATE 'DROP TABLE TBL_COMP_ASSESSMENT';
12 EXCEPTION
13 WHEN OTHERS THEN
14 IF SQLCODE != -942 THEN
15 RAISE;
16 END IF;
17 END;
18 END IF;
19 END;
20
21 /
PL/SQL procedure successfully completed.
SQL>
回答by Ameya Deshpande
you can check it using
你可以使用
begin
if ObjType = 'TABLE' then
select count(*) into v_counter from user_tables where table_name = upper(ObjName);
if v_counter > 0 then
execute immediate 'drop table ' || ObjName || ' cascade constraints';
end if;
end if;
end;
