我需要找到使用PL / sql的特定序列,例如MY_SEQ退出.如果序列存在,则删除它并创建一个新序列,否则只创建一个新序列.
例如. (伪代码)
IF EXISTS(MY_SEQ) THEN BEGIN DROP SEQUENCE MY_SEQ; CREATE SEQUENCE MY_SEQ... END; ELSE BEGIN CREATE SEQUENCE MY_SEQ; END;
您可以检查字典视图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;
