本文介绍了如何将 Oracle DB 中的所有序列重置为值 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 statementORA-06512: at line 300900. 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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!