Oracle SQL - If Exists, Drop Table & Create

Question

请问有人能指导我这个查询有什么问题吗？在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;

我收到 "ANONYMOUS BLOCK COMPLETED" 的输出，但表格并没有被创建。该表之前已经存在，我删除了它以检查 PL/SQL 是否真的在创建表格，但结果是否定的。这里出了什么问题？我缺少了什么？请指导。

- Sidhu Ram

---

四个问题：1）你的第一个创建表语句前没有“Execute Immediate”。2）你的创建表语句没有包括列。3）你的if then else块在异常处理程序内部，因此是其一部分。4）聚合查询总是返回至少1行，因此你的异常处理程序永远不会被调用，你的代码也永远不会运行。 - Sentinel

@Sentinel：4）聚合查询始终返回至少1行，因此您的异常处理程序永远不会被调用，您的代码也永远不会运行-感谢您的指出。 - Sidhu Ram

Answer 1

当您使用all_tables过滤器时，请通过添加where owner = 'your_schema'来为您的模式筛选结果，或者使用sys.user_tables。

ALL_TABLES描述了当前用户可以访问的关系表。

USER_TABLES描述了当前用户拥有的关系表。

当使用execute_emmidiate时，请从查询中删除;。

修改后的查询：

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;

- Praveen

---

嘿，那个起作用了。看起来是异常处理引起了问题。在执行PLSQL之前，我已经删除了表。因此脚本执行时会显示“新表已创建！”。当我再次执行它时，我期望看到“表已删除并重新创建！”，但是我收到了一个错误消息：“名称已被现有对象使用”。那么Execute Immediate的第一部分没有触发？为什么呢？有任何想法吗？ - Sidhu Ram

这是正确的答案。http://www.tutorialspoint.com/plsql/plsql_exceptions.htm 上有一些关于异常的信息。你可以通过输出table_exists变量并发现它为空来调试你的脚本。 - bob dylan

Answer 2

第一条注记：

Select count(*) into Table_exists
from sys.all_tables
where table_name = 'TABLENAME1';

这个函数总是返回一行。你不需要异常处理。

我猜测你可能有多个名为TABLENAME1的表。运行以下查询以查找：

Select *
from sys.all_tables
where table_name = 'TABLENAME1';

Oracle会存储您可以访问的所有所有者的表。您可能还想在where子句中检查OWNER_NAME。

但是，您似乎了解异常处理。因此，只需删除表格，忽略任何错误，然后重新创建表格。

- Gordon Linoff

---

嘿，感谢输入。模式中没有TableName1。我已经验证过了。我想遵循这个编码标准，这样团队中的所有开发人员在发送脚本到DBA进行执行时都会坚持它。当我在SQL Server上工作时就是这样做的。如果脚本中没有找到If Exists - Drop - Create语法，DBA将不会执行脚本。 - Sidhu Ram

@SidhuRam...你没有理解重点。sys.all_tables检查的是所有模式，而不仅仅是当前模式。虽然这种行为在SQL Server上是相同的，但它似乎不同，因为Oracle实际上每个服务器只有一个数据库。看起来像是单独的数据库实际上只是数据库内的单独模式。 - Gordon Linoff

Gordon，感谢您的解释。在SQL Server中，我们可以在同一服务器上创建多个数据库。我以为Oracle的架构也是一样的。感谢您指出了这个区别。 - Sidhu Ram

Answer 3

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;

正如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;

- Klas Lindbäck

---

太棒了！完美运行。非常感谢 :) - Sidhu Ram