如何将 Oracle DB 中的所有序列重置为值 0? [英] How can I reset all sequences in my Oracle DB to value 0?

问题描述

我想将所有序列重置为 0.这在某种程度上不起作用.你能说为什么吗?

I want to reset all sequences to 0. This somehow does not work. Can you say why?

create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

此脚本来自堆栈溢出，适用于单个序列.

This script was from stack overflow and it works for single sequences.

begin
for i in (select sequence_name from dba_sequences where sequence_name like '%SEQ_PR%') LOOP
execute immediate 'reset_seq('||i.sequence_name||')';
end loop;
end;
/

这是我自己写的:)

错误报告 -ORA-00900: 无效的 SQL 语句ORA-06512:在第 3 行00900. 00000 - 无效的 SQL 语句"

Error report - ORA-00900: invalid SQL statement ORA-06512: at line 3 00900. 00000 - "invalid SQL statement"

推荐答案

您不需要动态 SQL.

You don't need dynamic SQL.

begin
for i in (select sequence_name from dba_sequences where sequence_name like '%SEQ_PR%') LOOP
  reset_seq(i.sequence_name);           --> this is just fine
end loop;
end;
/

示例(我使用 USER_SEQUENCES 代替):

Example (I'm using USER_SEQUENCES instead):

SQL> select seq_pr1.nextval from dual;

   NEXTVAL
----------
         9

SQL> select seq_pr2.nextval from dual;

   NEXTVAL
----------
         8

SQL> begin
  2  for i in (select sequence_name from user_sequences where sequence_name like '%SEQ_PR%') LOOP
  3    reset_seq(i.sequence_name);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select seq_pr1.nextval from dual;

   NEXTVAL
----------
         1

SQL> select seq_pr2.nextval from dual;

   NEXTVAL
----------
         1

SQL>

这篇关于如何将 Oracle DB 中的所有序列重置为值 0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！