将Oracle序列重置为现有列中的下一个值的最佳方法? [英] Best way to reset an Oracle sequence to the next value in an existing column?

问题描述

由于某些原因，过去的人们在不使用sequence.NEXTVAL的情况下插入了数据.因此，当我使用sequence.NEXTVAL填充表时，由于表中已经使用了该数字，所以我遇到了PK冲突.

For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use in the table.

如何更新下一个值以使其可用?现在，我只是一遍又一遍地插入，直到成功(INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL))，这将同步nextval.

How can I update the next value so that it is usable? Right now, I'm just inserting over and over until it's successful (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), and that syncs up the nextval.

推荐答案

这两个过程让我重置序列并根据表中的数据重置序列(为此客户端使用的编码约定致歉):

These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):

CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
   l_num   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
   IF (p_val - l_num - 1) != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
   END IF;

   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';

   DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;

CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
   nextnum   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;

   SET_SEQ_TO(seq_name, nextnum);
END;

这篇关于将Oracle序列重置为现有列中的下一个值的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！