多个“立即执行” Oracle 中的查询

发布于 2024-10-19 00:00:06 字数 560 浏览 0 评论 0原文

我有这样的查询序列：

begin
execute immediate 'drop table mytable1';
exception when others then null;
end;

begin
execute immediate 'drop table mytable2';
exception when others then null;
end;

begin
execute immediate 'drop table mytable3';
exception when others then null;
end;

但是当我尝试在 SQL Scratchpad 中执行它时，它显示“遇到符号开始”，这表明所有查询必须在一个开始中...... 如果我删除除了第一个开始和最后一个结束之外的所有开始结束，它会给我 “无效的SQL语句”如何使用上面的模式执行多个删除表或多个创建表并检查表是否存在？我知道我的风格 exception while other then null; 被认为是不好的做法，类似于其他语言中的空 catch()'es，但这是我检查表是否存在/不存在的最简单方法在甲骨文中

原文

I have this sequence of queries:

begin
execute immediate 'drop table mytable1';
exception when others then null;
end;

begin
execute immediate 'drop table mytable2';
exception when others then null;
end;

begin
execute immediate 'drop table mytable3';
exception when others then null;
end;

but when I try to execute it in SQL Scratchpad it says "encountered the symbol begin" which pointed me that all the queries must be in one begin...
if I remove all the begin end exept for the first begin and last end it gives me
"invalid SQL statement" how to perform multiple drop table or multiple create table with the upper pattern and to check if the tables exist? I know that my style with exception when others then null; is considered bad practice similar to empty catch()'es in other languages but thats the easiest way for me to check if a table exists/not exists in oracle

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

执手闯天涯 2024-10-26 00:00:06

BEGIN
  EXECUTE IMMEDIATE 'drop table mytable1';

  EXECUTE IMMEDIATE 'drop table mytable2';

  EXECUTE IMMEDIATE 'drop table mytable3';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

工作正常。

如果你问我，当其他人为空时出现异常 - 应该避免。如果您想检查表是否存在 - 查询 USER_TABLES

DECLARE
  V_EXISTS NUMBER;

BEGIN

SELECT 1 INTO V_EXISTS FROM USER_TABLES WHERE TABLE_NAME = 'TEST';

IF V_EXISTS = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TEST';
END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Table not found');
END;

Regd：您的评论，如果您仍然想要继续使用问题中的方法，请将其包装在外部匿名块

BEGIN

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable1';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable2';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable3';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

END;

结果：

SQL> BEGIN
  2  
  3    BEGIN
  4      EXECUTE IMMEDIATE 'drop table mytable1';
  5    EXCEPTION
  6      WHEN OTHERS THEN
  7        NULL;
  8    END;
  9  
 10    BEGIN
 11      EXECUTE IMMEDIATE 'drop table mytable2';
 12    EXCEPTION
 13      WHEN OTHERS THEN
 14        NULL;
 15    END;
 16  
 17    BEGIN
 18      EXECUTE IMMEDIATE 'drop table mytable3';
 19    EXCEPTION
 20      WHEN OTHERS THEN
 21        NULL;
 22    END;
 23  
 24  END;
 25  
 26  /

PL/SQL procedure successfully completed.

SQL>

BEGIN
  EXECUTE IMMEDIATE 'drop table mytable1';

  EXECUTE IMMEDIATE 'drop table mytable2';

  EXECUTE IMMEDIATE 'drop table mytable3';
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

Works fine.

If you ask me, exception when others then null - should be be avoided. If you want to check if a table exists - query USER_TABLES

DECLARE
  V_EXISTS NUMBER;

BEGIN

SELECT 1 INTO V_EXISTS FROM USER_TABLES WHERE TABLE_NAME = 'TEST';

IF V_EXISTS = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TEST';
END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Table not found');
END;

Regd: your comment, if you still want to go about using the method in your question, wrap it in a outside anonymous block

BEGIN

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable1';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable2';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 'drop table mytable3';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

END;

Result:

SQL> BEGIN
  2  
  3    BEGIN
  4      EXECUTE IMMEDIATE 'drop table mytable1';
  5    EXCEPTION
  6      WHEN OTHERS THEN
  7        NULL;
  8    END;
  9  
 10    BEGIN
 11      EXECUTE IMMEDIATE 'drop table mytable2';
 12    EXCEPTION
 13      WHEN OTHERS THEN
 14        NULL;
 15    END;
 16  
 17    BEGIN
 18      EXECUTE IMMEDIATE 'drop table mytable3';
 19    EXCEPTION
 20      WHEN OTHERS THEN
 21        NULL;
 22    END;
 23  
 24  END;
 25  
 26  /

PL/SQL procedure successfully completed.

SQL>

回复收藏 0 原文

~没有更多了~