oracle 在删除不存在的序列、创建现有用户时防止出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1625567/
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
Prevent error when dropping not existing sequences, creating existing users
提问by Tom
I have a bunch of sql scripts that create / drop sequences, users and other objects. I'm running these scripts through liquibase, but they fail because oracle complains when I try to drop a non existing sequence, or create an existing user.
我有一堆创建/删除序列、用户和其他对象的 sql 脚本。我正在通过 liquibase 运行这些脚本，但它们失败了，因为当我尝试删除不存在的序列或创建现有用户时，oracle 会抱怨。
Is there an oracle way to prevent errors from happening?
有没有oracle方法来防止错误发生？
Something of the sort
类似的东西
Create User / Sequence if not exists
Drop User/ Secuence if exists
如果不存在则创建用户/序列
如果存在，则删除用户/安全
As far as I know, I have these options:
据我所知，我有以下选择：
- Write a plsql script
- Use liquibase contexts.
- Use liquibase preconditions, but this would mean too much work.
- 写一个plsql脚本
- 使用 liquibase 上下文。
- 使用 liquibase 前提条件，但这意味着太多的工作。
Any thoughts / ideas will be greatly appreciated.
任何想法/想法将不胜感激。
回答by Nathan Voxland
Liquibase has a failOnError attribute you can set to false on changeSets that include a call that could fail.
Liquibase 有一个 failOnError 属性，您可以在包含可能失败的调用的 changeSet 上将其设置为 false。
<changeSet failOnError="false">
<createSequence sequenceName="new_sequence"/>
</changeSet>
This allows you to have simple create user, create sequence, drop user, and drop sequence changeSets and if the statement throws an error because they users/sequences exist/don't exist they will still be marked as ran and the update will continue.
这允许您拥有简单的创建用户、创建序列、删除用户和删除序列更改集，如果语句因用户/序列存在/不存在而引发错误，它们仍将被标记为已运行并且更新将继续。
The downside of this approach is that it will also mark them as ran and continue if they error for some other reason (bad permissions, connection failure, invalid SQL, etc.) The more accurate approach is to use preconditions, like this:
这种方法的缺点是它也会将它们标记为运行并在它们由于其他原因（错误权限、连接失败、无效 SQL 等）出错时继续。更准确的方法是使用前提条件，如下所示：
<changeSet>
<preconditions onFail="MARK_RAN"><not><sequenceExists/></not></preconditions>
<createSequence name="new_sequence"/>
</changeSet>
There is no userExists precondition currently, but you can create custom preconditions or fall back to the precondition. See http://www.liquibase.org/documentation/preconditions.htmlfor documentation
当前没有 userExists 前提条件，但您可以创建自定义前提条件或回退到前提条件。有关文档，请参阅http://www.liquibase.org/documentation/preconditions.html
回答by Rob van Laarhoven
Write a function do_ddl similar to this and catch all exceptions you want to catch:
编写一个与此类似的函数 do_ddl 并捕获所有要捕获的异常：
DECLARE
allready_null EXCEPTION;
PRAGMA EXCEPTION_INIT(allready_null, -1451);
BEGIN
execute immediate 'ALTER TABLE TAB MODIFY(COL NULL)';
EXCEPTION
WHEN allready_null THEN
null; -- handle the error
END;
/
回答by David Aldridge
I'd just use a PL/SQL anonymous block.
我只是使用 PL/SQL 匿名块。
begin
for x in (select sequence_name
from user_sequences
where sequence_name in ('SEQ1','SEQ2' ... 'SEQn'))
loop
execute immediate 'drop sequence '||x.sequence_name;
end loop;
end;
/
回答by consultantleon
In my experience, based on Liquibase 3.5.1 behaviour, when using failOnError="false", the changeSet does not get recorded as 'RAN' if the operation failed. To me this seems a bug and Nathan's answer does not seem to be correct?
根据我的经验，基于 Liquibase 3.5.1 的行为，当使用 failOnError="false" 时，如果操作失败，changeSet 不会被记录为“RAN”。对我来说，这似乎是一个错误，而 Nathan 的回答似乎不正确？
The downside of this approach is that it will also mark them as ran and continue if they error for some other reason (bad permissions, connection failure, invalid SQL, etc.) The more accurate approach is to use preconditions, like this:
这种方法的缺点是它也会将它们标记为运行并在它们由于其他原因（错误权限、连接失败、无效 SQL 等）出错时继续。更准确的方法是使用前提条件，如下所示：
I.e.: it does not mark them as ran!
即：它不会将它们标记为已运行！
回答by Ramz_the_dev
liquibase preconditions didn't check for existing sequences for me. So, after multiple attempts, I tried simple <changeSet id="test-id"><sql>
DROP SEQUENCE IF EXISTS "TABLENAME_ID_seq"; </sql></changeSet>
liquibase 先决条件没有为我检查现有序列。所以，经过多次尝试，我尝试了简单的<changeSet id="test-id"><sql>
DROP SEQUENCE IF EXISTS "TABLENAME_ID_seq"; </sql></changeSet>
