oracle 如何使用 PL/SQL 查找序列是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3573075/
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 find if a sequence exists using PL/SQL
提问by Nikola Stjelja
I need to find using PL/SQL if a specific sequence named e.g. MY_SEQ exits. If the sequence exists then drop it and create a new, or else to just create a new sequence.
如果一个名为 MY_SEQ 的特定序列退出，我需要使用 PL/SQL 查找。如果序列存在，则删除它并创建一个新序列，否则就创建一个新序列。
E.G. (pseudocode)
EG（伪代码）
IF EXISTS(MY_SEQ) THEN
BEGIN
DROP SEQUENCE MY_SEQ;
CREATE SEQUENCE MY_SEQ...
END;
ELSE
BEGIN
CREATE SEQUENCE MY_SEQ;
END;
回答by Vincent Malgrat
you can check the dictionary view ALL_SEQUENCES(or USER_SEQUENCESif the executing user is the owner), for example:
您可以检查字典视图ALL_SEQUENCES（或者USER_SEQUENCES如果执行用户是所有者），例如：
BEGIN
FOR cc IN (SELECT sequence_name as sequence_exists
FROM all_sequences
WHERE sequence_owner = :seq_owner
AND sequence_name = :seq_name) LOOP
-- sequence exists, drop it (at most there will be *one* sequence)
EXECUTE IMMEDIATE 'DROP SEQUENCE XXX';
END LOOP;
-- create sequence
EXECUTE IMMEDIATE 'CREATE SEQUENCE XXX';
END;
回答by ZeissS
I have several ideas for this (all untested):
我对此有几个想法（均未经测试）：
1) Oracle normally supports something like CREATE OR REPLACE
1）Oracle通常支持类似的东西 CREATE OR REPLACE
2) Use one of the system views from the SYS user to check with a SELECT whether the Sequence exists.
2) 使用 SYS 用户的系统视图之一通过 SELECT 检查序列是否存在。
3) Use execute_immediate with a BEGIN .. EXCEPTION ... ENDblock to drop the object. If it does not exist, an error should occur, which you can ignore.
3) 使用带有BEGIN .. EXCEPTION ... END块的execute_immediate来删除对象。如果它不存在，则会发生错误，您可以忽略该错误。
