如何更改动态SQL中的序列? [英] How can I alter a sequence in dynamic SQL?

问题描述

我正在尝试创建一个脚本，以将数据从一个数据库迁移到另一个数据库.我目前无法做的一件事是将一个序列的nextval设置为另一个DB中的序列的nextval.

I'm trying to create a script to migrate data from one DB to another. One thing I'm not currently able to do is set the nextval of a sequence to the nextval of a sequence in another DB.

我从user_sequences获得了值的差异，并生成了以下动态SQL语句:

I got the difference in values from user_sequences and generated the following dynamic SQL statements:

execute immediate 'alter sequence myseq increment by 100';
execute immediate 'select myseq.nextval from dual';
execute immediate 'alter sequence myseq increment by 1';

commit;

但是什么也没发生.我想念什么?如果我在过程外运行相同的语句，则它们可以正常工作:

But nothing happens. What am I missing? If I run the same statements outside the procedure, they work fine:

alter sequence myseq increment by 100;
select myseq.nextval from dual;
alter sequence myseq increment by 1;

commit;

编辑:对所有人的歉意表示不清楚.我实际上是在同一数据库中更改顺序.我只是从远程数据库获取要设置的值.也许没有必要提及远程数据库，因为它不会影响事物.我只提到它是为了解释我的目标.

EDIT: Apologies to all for not being clear. I'm actually altering the sequence in the same DB. I'm only getting the value to be set from a remote DB. Perhaps it was unnecessary to mention the remote DB as it doesn't affect things. I only mentioned it to explain what my goals were.

第1步.我从远程数据库中获得了下一个序列.

Step 1. I get the nextval of the sequence from a remote DB.

select (select last_number
        from dba_sequences@remoteDB
        where upper(sequence_name) = upper(v_sequence_name)) - (select last_number
                                                                from user_sequences
                                                                where upper(sequence_name) = upper(v_sequence_name)) increment_by
from dual;    

第2步.我用此值生成动态SQL语句:

Step 2. I generate dynamic SQL statements with this value:

execute immediate 'alter sequence myseq increment by 100';
execute immediate 'select myseq.nextval from dual';
execute immediate 'alter sequence myseq increment by 1';

commit;

没有引发任何错误，但是什么也没有发生.当我用DBMS_OUTPUT.PUT_LINE编写SQL语句并在它们无法运行的情况下运行它们.

No error was raised, but nothing happened. When I wrote the SQL statements with DBMS_OUTPUT.PUT_LINE and ran them outside they worked.

推荐答案

以下是一些代码，可以动态地将序列设置为新的(更高)值.我已经编写了此代码，因此它将适用于您架构中的任何序列.

Here is some code which dynamically sets a sequence to a new (higher) value. I have written this so it will work for any sequence in your schema.

create or replace procedure resync_seq
    (p_seq_name in user_sequences.sequence_name%type)
is
    local_val pls_integer;
    remote_val pls_integer;
    diff pls_integer;
begin
    execute immediate 'select '|| p_seq_name ||'.nextval from dual'
           into local_val;
    select last_number into remote_val
    from user_sequences@remote_db
    where sequence_name = p_seq_name ;
    diff := remote_val - local_val;

    if diff > 0
    then
        execute immediate 'alter sequence  '|| p_seq_name ||' increment by ' ||to_char(diff);
        execute immediate 'select '|| p_seq_name ||'.nextval from dual'
           into local_val;
        execute immediate 'alter sequence  '|| p_seq_name ||' increment by 1';
    end if;

end;

该过程不需要COMMIT，因为DDL语句发出隐式提交(实际上是两个).

The procedure doesn't need a COMMIT because DDL statements issue an implicit commit (two in fact).

您可以执行它，并以如下方式查看已同步的值(在SQL * PLus中):

You can execute it and see the synced value like this (in SQL*PLus):

exec resync_seq('MYSEQ')
select myseq.currval
from dual

顺便说一句，重置序列(至其原始起始值或其他较低值)的唯一方法是删除并重新创建序列.

Incidentally, the only way to reset a sequence (to its original starting value or a different lower value) is dropping and re-creating the sequence.

Oracle在18c中为ALTER SEQUENCE添加了重新启动功能.直截了当的选择...

In 18c Oracle added a RESTART capability to ALTER SEQUENCE. The straightforward option ...

alter sequence myseq restart;

...将序列重置为原始CREATE SEQUENCE语句中START WITH子句指定的值.另一个选项使我们可以指定一个新起点:

...resets the sequence to the value specified by the START WITH clause in the original CREATE SEQUENCE statement. The other option allows us to specify a new starting point:

alter sequence myseq restart start with 23000;

令人兴奋的是，这个新起点可以在当前值之前或之后(在序列的通常范围内).

Excitingly this new starting point can be ahead or behind the current value (within the usual bounds of a sequence).

一个缺点是该新功能尚未记录(仅用于Oracle内部使用)，因此我们不应该使用它.

The one snag is that this new capability is undocumented (only for Oracle's internal usage) and so we're not supposed use it. Still true in 20c. The only approved mechanism for changing a sequence's value is what I outlined above.

这篇关于如何更改动态SQL中的序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！