oracle – 如安在动态SQL中变动序列？

我正在实行建设一个脚原来将数据从一个数据库迁徙到另一个数据库.我今朝无法做的一件事是将序列的nextval配置为另一个DB中序列的nextval.

我从user_sequences中得到了值的差别,并天生了以下动态SQL语句：

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

commit;

但没有任何回响.我错过了什么？假如我在措施之外运行沟通的语句,它们可以正常事变：

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

commit;

编辑：向全部人致歉,不清晰.我现实上正在改变统一个DB中的序列.我只是从长途数据库中获取值.大概没须要提到长途数据库,由于它不会影响事物.我只提到它来表明我的方针是什么.

步调1.我从长途DB获取序列的nextval.

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语句：

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语句并在表面运行它们时它们事变了.

办理要领

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语句发出隐式提交(现实上是两个).

您可以执行它并查察这样的同步值(在SQL * PLus中)：

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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!