Oracle SQL - 如果存在，删除表并创建
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31585746/
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 SQL - If Exists, Drop Table & Create
提问by Sidhu Ram
Can some one please guide me what's wrong with this query? In SQL Server we just check the presence of the Object_ID of a table to drop it and re-create it. I am new to Oracle and wrote this query:
有人可以指导我这个查询有什么问题吗？在 SQL Server 中，我们只检查表的 Object_ID 是否存在以删除它并重新创建它。我是 Oracle 的新手并写了这个查询：
declare Table_exists INTEGER;
BEGIN
Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Table_Exists :=0;
if(table_exists)=1
Then
Execute Immediate 'Drop Table TABLENAME1;'
'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
I get the output - ANONYMOUS BLOCK COMPLETED, but the table is not created. The table was previously existing, so I dropped it to check if the PL/SQL is actually creating the table, but NO. What is wrong here? What am I missing? Please guide.
我得到输出 - ANONYMOUS BLOCK COMPLETED，但未创建表。该表先前已存在，因此我将其删除以检查 PL/SQL 是否实际创建了该表，但没有。这里有什么问题？我错过了什么？请指导。
采纳答案by Klas Lindb?ck
The EXCEPTION clause lasts till the next END and not just the next statement. If you want to continue after catching the exception you need to add an additional BEGIN/END:
EXCEPTION 子句持续到下一个 END 而不仅仅是下一个语句。如果您想在捕获异常后继续，您需要添加额外的 BEGIN/END：
declare
Table_exists INTEGER;
BEGIN
BEGIN
Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
EXCEPTION
WHEN NO_DATA_FOUND THEN
Table_Exists :=0;
END;
if(table_exists)=1 Then
Execute Immediate 'Drop Table TABLENAME1;'
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
As pointed out by Gordon, the EXCEPTION clause is not really needed in this case since count(*)will always return one row. So the following is sufficient:
正如 Gordon 所指出的，在这种情况下并不真正需要 EXCEPTION 子句，因为它count(*)总是返回一行。所以以下就足够了：
declare
Table_exists INTEGER;
BEGIN
Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
if(table_exists)=1 Then
Execute Immediate 'Drop Table TABLENAME1;'
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
回答by Praveen
When you are using all_tablesfilter the results for your
schema by adding where owner = 'your_schema'or use sys.user_tables
当您all_tables通过添加where owner = 'your_schema'或使用过滤模式的结果时 sys.user_tables
ALL_TABLES describes the relational tables accessible to the current user
USER_TABLES describes the relational tables owned by the current user.
ALL_TABLES 描述当前用户可以访问的关系表
USER_TABLES 描述了当前用户拥有的关系表。
When use execute_emmidiateremove the ;from the query;
使用时从查询中execute_emmidiate删除;；
Modified query;
修改查询；
DECLARE
Table_exists INTEGER;
BEGIN
Select count(*) into Table_exists from sys.user_tables where table_name='TABLENAME1';
--or
--Select count(*) into Table_exists from sys.all_tables
--where table_name='TABLENAME1' and owner = 'your_DB';
if table_exists = 1 Then
Execute Immediate 'Drop Table TABLENAME1';
Execute Immediate 'Create Table TABLENAME1(num number)';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1(num number)';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
回答by Gordon Linoff
First note:
第一个注意事项：
Select count(*) into Table_exists
from sys.all_tables
where table_name = 'TABLENAME1';
will alwaysreturn one row. You don't need the exception handling.
将始终返回一行。您不需要异常处理。
My best guess is that you have more than one table called TABLENAME1. Run this query to find out:
我最好的猜测是你有不止一张表叫做TABLENAME1. 运行此查询以找出：
Select *
from sys.all_tables
where table_name = 'TABLENAME1';
Oracle stores tables from all owners that you can access. You might also want to check OWNER_NAMEin the whereclause.
Oracle 存储您可以访问的所有所有者的表。您可能还想签OWNER_NAME入该where条款。
However, you seem to understand exception handling. So, just drop the table, ignore any errors, and then recreate the table.
但是，您似乎了解异常处理。因此，只需删除表，忽略任何错误，然后重新创建表。
