oracle 如何更改动态 SQL 中的序列？

How can I alter a sequence in dynamic SQL?

提问by Please click here

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.

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

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

我从 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;

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.

编辑：向所有人道歉，因为不清楚。我实际上是在改变同一个数据库中的序列。我只是从远程数据库获取要设置的值。也许没有必要提及远程数据库，因为它不会影响事物。我提到它只是为了解释我的目标是什么。

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

步骤 1. 我从远程数据库获取序列的 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;    

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

步骤 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;

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

没有出现错误，但什么也没发生。当我用 DBMS_OUTPUT.PUT_LINE 编写 SQL 语句并在外面运行它们时，它们起作用了。

回答by APC

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;

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

该过程不需要 COMMIT，因为 DDL 语句会发出一个隐式提交（实际上是两个）。

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

您可以执行它并查看这样的同步值（在 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.

顺便说一下，重置序列（到其原始起始值或不同的较低值）的唯一方法是删除并重新创建序列。

---

---

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

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

alter sequence myseq restart;

...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 newstarting point:

...将序列重置为原始 CREATE SEQUENCE 语句中的 START WITH 子句指定的值。另一个选项允许我们指定一个新的起点：

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).

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

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.

一个障碍是这个新功能没有记录（仅用于 Oracle 的内部使用），因此我们不应该使用它。在 20c 中仍然如此。更改序列值的唯一批准机制是我上面概述的机制。

回答by Erkan Haspulat

I wasn't quite able to understand what you mean, but here is a wild guess:

我不太明白你的意思，但这是一个疯狂的猜测：

I don't see it in your code, but you're talking about executing DDL (CREATE, ALTERetc.) on another database, so I assume you are using Database Links. It is not possibleto use Database Links to execute DDL on another database. You might want to consider that.

我没有看到它在你的代码，但你在谈论执行DDL（ CREATE，ALTER在另一个数据库等），所以我假设你使用的数据库链接。不可能使用数据库链接在另一个数据库上执行 DDL。你可能要考虑一下。

---

---

After the information you provided, this might be what you need. And if you want to set the current value of the sequence, you can't, according to this documentation, you need to drop/create:

在您提供信息之后，这可能就是您所需要的。如果你想设置序列的当前值，你不能，根据这个文档，你需要删除/创建：

declare
  ln_lastNumber DBA_SEQUENCES.LAST_NUMBER%type;
  lv_sequenceName DBA_SEQUENCES.SEQUENCE_NAME%type := 'MYSEQ';
begin
  select LAST_NUMBER
  into ln_lastNumber
  from DBA_SEQUENCES--or @remote_db;
  where
    --Your predicates;

  execute immediate 'drop sequence ' || lv_sequenceName;
  execute immediate 'create sequence ' || lv_sequenceName || ' starts with ' || ln_lastNumber;
exception
  when no_data_found then
    dbms_output.put_line('No sequence found!'); -- Or log somehow.
    raise;
  when others then
    raise;
end;

回答by moleboy

Also, DDL in dynamic SQL pacakges requires

此外，动态 SQL 包中的 DDL 需要

AUTHID CURRENT_USER

AUTHID CURRENT_USER

when declaring the package specification, if you want it to have the credentials of the current user

在声明包规范时，如果您希望它具有当前用户的凭据

回答by anand maurya

I took the code provided by APC and modified as below:

我把APC提供的代码修改如下：

create or replace procedure resync_seq
    (p_seq_name in user_sequences.sequence_name%type, 
     p_table_name in user_tables.table_name%type, 
     p_pk in user_cons_columns.column_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;

       execute immediate 'select max('||p_pk||') from '||p_table_name ||' ' 
       into remote_val ;

       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;