ORA-00933: SQL命令未正确结束

Question

我在Oracle中得到了这个错误：

ORA-00933: SQL命令未正确结束 为 如果存在ownername.seq_name，则滴序；

我为什么要看到这个？

Answer 1

IF EXISTS命令中的DROP SEQUENCE子句不存在。

可以使用PLSQL块忽略错误：

SQL> DECLARE
  2     sequence_doesnt_exist EXCEPTION;
  3     PRAGMA EXCEPTION_INIT(sequence_doesnt_exist, -2289);
  4  BEGIN
  5     EXECUTE IMMEDIATE 'DROP SEQUENCE seq_name';
  6  EXCEPTION
  7     WHEN sequence_doesnt_exist THEN NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed

Answer 2

问题是“如果存在”在Oracle中不起作用。使用：

drop sequence ownername.seq_name;

Answer 3

正如其他人提到的，IF存在在DROP SEQUENCE命令上不起作用。

要测试序列是否存在，需要检查适当的视图：

序列

SELECT * 
  FROM USER_SEQUENCES
 WHERE sequence_name = ?

序列

SELECT * 
  FROM DBA_SEQUENCES
 WHERE sequence_name = ?

序列

SELECT * 
  FROM ALL_SEQUENCES
 WHERE sequence_name = ?

示例：

BEGIN
   FOR i IN (SELECT sequence_name 
               FROM USER_SEQUENCES
              WHERE sequence_name = ?) 
   LOOP
     EXECUTE IMMEDIATE ('DROP SEQUENCE '|| i.sequence_name);
   END LOOP;
END;