是否有类似“如果不存在则创建序列...”之类的内容?在 Oracle SQL 中?
对于使用 Oracle 8 DB 的应用程序,我提供了一个 SQL 脚本来设置触发器、序列等内容,这些内容可以复制并粘贴到 SQL*Plus 中。如果我尝试创建的序列已经存在,我希望脚本不会因错误而停止。对于触发器,可以使用“创建或替换触发器...”轻松完成此操作,但对于序列,这不起作用。我也尝试过“如果不存在mysequence,则创建序列...”,但它也没有。有其他选择吗?
或者,如果这是不可能的,有没有办法在没有SQL *的情况下执行“删除序列mysequence”如果 mysequence 不存在,还要中止脚本吗?
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?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
如果您确定脚本将始终在 SQL*Plus 下运行,则可以使用指令将 CREATE SEQUENCE 语句括起来,以在出现错误时继续:
请注意创建过程中是否存在其他错误(权限问题、语法失败等)序列语句将被忽略
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:
Be aware if there are other errors (permission problems, syntax failures, etc.) in the create sequence statements they will be ignored
我喜欢:
I like:
您可以检查
user_sequence
表来查看正在创建的序列是否已经存在。与
davek
的解决方案类似:这个想法是,在创建任何序列之前,删除序列并创建它,所有这些都在动态 SQL 中,创建一个函数,并说当您需要创建 10 个序列时,让该函数照顾...
您可以参数化所有其他选项并有一个复杂的功能来为您创建序列。
You can check
user_sequence
table to see whether the sequence being created exists already or not.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...
You can parametrize all other options and have a elaborate function to create sequence for you.
或者
OR