ORACLE开发：oracle重置序列从0开始递增1

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;

SELECT a.sequence_name 序列名称,
a.min_value 序列最小值,
to_char(to_number( a.max_value)) 序列最大值,
a.last_number 序列当前值,
CASE a.last_number WHEN 1 THEN ‘–不需要重置;’ ELSE
‘CALL seq_reset(”’ || a.sequence_name || ”’);’ END 重置序列脚本,
‘DROP SEQUENCE ‘ || a.sequence_name 删除序列脚本,
‘RENAME ‘ || a.sequence_name || ‘ TO {newname}’ 重命名脚本
FROM USER_SEQUENCES a WHERE a.last_number<>1
ORDER BY a.sequence_name ASC;