Oracle SQL 中是否有类似“如果不存在则创建序列...”之类的内容？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2618179/
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
Is there something like "if not exist create sequence ..." in Oracle SQL?
提问by Timo
For my application that uses an Oracle 8 DB, I am providing an SQL script to setup stuff like triggers, sequences etc., which can be copied and pasted into SQL*Plus. I would like the script to not stop with an error if a sequence that I am trying to create already exists. For a Trigger this can easily be done using "create or replace trigger ...", but for a sequence this does not work. I also tried ""if not exists mysequence then create sequence ..." but it did not too. Is there some alternative?
对于我使用 Oracle 8 DB 的应用程序，我提供了一个 SQL 脚本来设置触发器、序列等内容，这些内容可以复制并粘贴到 SQL*Plus 中。如果我尝试创建的序列已经存在，我希望脚本不会因错误而停止。对于触发器，这可以使用“创建或替换触发器...”轻松完成，但对于序列，这不起作用。我也试过“如果不存在mysequence则创建序列......”但它也没有。有没有其他选择？
Alternatively, if this is not possible, is there a way to do a "drop sequence mysequence" without SQL*Plus aborting the script if mysequence does not exist?
或者，如果这是不可能的，如果 mysequence 不存在，有没有办法在不 SQL*Plus 中止脚本的情况下执行“删除序列 mysequence”？
采纳答案by dpbradley
If you're sure the script will always run under SQL*Plus, you can bracket the CREATE SEQUENCE statements with a directive to continue on error:
如果您确定脚本将始终在 SQL*Plus 下运行，您可以使用指令将 CREATE SEQUENCE 语句括起来以继续出错：
WHENEVER SQLERROR CONTINUE
-- create sequences here, ignoring errors
WHENEVER SQLERROR EXIT SQL.SQLCODE
Be aware if there are other errors (permission problems, syntax failures, etc.) in the create sequence statements they will be ignored
请注意，如果 create sequence 语句中存在其他错误（权限问题、语法错误等），它们将被忽略
回答by jva
DECLARE
v_dummy NUMBER;
BEGIN
-- try to find sequence in data dictionary
SELECT 1
INTO v_dummy
FROM user_sequences
WHERE sequence_name = 'MY_SEQUENCE_NAME';
-- if sequence found, do nothing
EXCEPTION
WHEN no_data_found THEN
-- sequence not found, create it
EXECUTE IMMEDIATE 'create sequence my_sequence_name';
END;
回答by quillbreaker
I like:
我喜欢：
DECLARE
C NUMBER;
BEGIN
SELECT COUNT(*) INTO C
FROM ALL_TRIGGERS
WHERE OWNER = 'YOUROWNER'
AND TRIGGER_NAME = 'YOURTRIGGER';
IF (C = 0) THEN
EXECUTE IMMEDIATE '
CREATE TRIGGER "YOUROWNER"."YOURTRIGGER"
blah blah blah your trigger blah blah
';
END IF;
END;
/
回答by Ond?ej ?těpán
DECLARE
lsSeqName VARCHAR2(32 CHAR) := UPPER('MY_SEQUENCE_NAME');
lnSeqCount NUMBER;
BEGIN
-- try to find sequence in data dictionary
SELECT count(1)
INTO lnSeqCount
FROM user_sequences
WHERE UPPER(sequence_name) = lsSeqName;
-- if sequence not found, create it
IF lnSeqCount = 0 THEN
EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || lsSeqName || ' START WITH 1 MINVALUE 1 MAXVALUE 1000000000000000 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER';
END IF;
END;
/
OR
或者
-- helper method
PROCEDURE createSeqIfNotExists (
isSeqName VARCHAR2
) IS
lnSeqCount NUMBER;
BEGIN
-- try to find sequence in data dictionary
SELECT count(1)
INTO lnSeqCount
FROM user_sequences
WHERE UPPER(sequence_name) = UPPER(isSeqName);
-- if sequence not found, create it
IF lnSeqCount = 0 THEN
EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || UPPER(isSeqName) || ' START WITH 1 MINVALUE 1 MAXVALUE 1000000000000000 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER';
END IF;
END createSeqIfNotExists;
-- call method
BEGIN
createSeqIfNotExists('MY_SEQUENCE_NAME');
END;
/
回答by Guru
You can check user_sequencetable to see whether the sequence being created exists already or not.
您可以检查user_sequencetable 以查看正在创建的序列是否已经存在。
Similar to davek's solution:
The idea is, before creating any sequence, drop the sequence and create it, all in dynamic SQL, create a function, and say when you need to create 10 sequence, let the function take care...
类似于davek的解决方案：思路是，在创建任何序列之前，先删除序列并创建它，全部在动态SQL中，创建一个函数，并说当您需要创建10个序列时，让该函数照顾...
function crt_seq(p_seq_name varchar2)
return boolean
begin
for i in (select 1 from user_sequence where sequence_name = upper(p_seq_name))
loop
---- Already exists. You can drop and recreate or return false to error out
execute immediate 'drop sequence '||p_seq_name;
execute immediate 'create sequence '||p_seq_name||' start with 1 increment
by 1 nocache';
end loop;
return true;
exception
when others then
return false;
end;
You can parametrize all other options and have a elaborate function to create sequence for you.
您可以参数化所有其他选项，并使用精心设计的功能为您创建序列。
