oracle给每行加上序号,在Oracle中生成唯一和连续数字的最佳方法

保留当前序列-可以使用以下命令将值重置为当前存储在表中的最大值:

-- --------------------------------

-- Purpose..: Resets the sequences

-- --------------------------------

DECLARE

-- record of temp data table

TYPE data_rec_type IS RECORD(

sequence_name VARCHAR2(30),

table_name VARCHAR2(30),

column_name VARCHAR2(30));

-- temp data table

TYPE data_table_type IS TABLE OF data_rec_type INDEX BY BINARY_INTEGER;

v_data_table data_table_type;

v_index NUMBER;

v_tmp_id NUMBER;

-- add row to temp table for later processing

--

PROCEDURE map_seq_to_col(in_sequence_name VARCHAR2,

in_table_name VARCHAR2,

in_column_name VARCHAR2) IS

v_i_index NUMBER;

BEGIN

v_i_index := v_data_table.COUNT + 1;

v_data_table(v_i_index).sequence_name := in_sequence_name;

v_data_table(v_i_index).table_name := in_table_name;

v_data_table(v_i_index).column_name := in_column_name;

END;

/**************************************************************************

Resets a sequence to a given value

***************************************************************************/

PROCEDURE reset_seq(in_seq_name VARCHAR2, in_new_value NUMBER) IS

v_sql VARCHAR2(2000);

v_seq_name VARCHAR2(30) := in_seq_name;

v_reset_val NUMBER(10);

v_old_val NUMBER(10);

v_new_value NUMBER(10);

BEGIN

-- get current sequence value

v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';

EXECUTE IMMEDIATE v_sql

INTO v_old_val;

-- handle empty value

v_new_value := in_new_value;

if v_new_value IS NULL then

v_new_value := 0;

END IF;

IF v_old_val <> v_new_value then

IF v_old_val > v_new_value then

-- roll backwards

v_reset_val := (v_old_val - v_new_value) * -1;

elsif v_old_val < v_new_value then

v_reset_val := (v_new_value - v_old_val);

end if;

-- make the sequence rollback to 0 on the next call

v_sql := 'alter sequence ' || v_seq_name || ' increment by ' ||

v_reset_val || ' minvalue 0';

EXECUTE IMMEDIATE (v_sql);

-- select from the sequence to make it roll back

v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';

EXECUTE IMMEDIATE v_sql

INTO v_reset_val;

-- make it increment correctly again

v_sql := 'alter sequence ' || v_seq_name || ' increment by 1';

EXECUTE IMMEDIATE (v_sql);

-- select from it again to prove it reset correctly.

v_sql := 'SELECT ' || v_seq_name || '.currval FROM DUAL';

EXECUTE IMMEDIATE v_sql

INTO v_reset_val;

END IF;

DBMS_OUTPUT.PUT_LINE(v_seq_name || ': ' || v_old_val || ' to ' ||

v_new_value);

END;

/*********************************************************************************************

Retrieves a max value for a table and then calls RESET_SEQ.

*********************************************************************************************/

PROCEDURE reset_seq_to_table(in_sequence_name VARCHAR2,

in_table_name VARCHAR2,

in_column_name VARCHAR2) IS

v_sql_body VARCHAR2(2000);

v_max_value NUMBER;

BEGIN

-- get max value in the table

v_sql_body := 'SELECT MAX(' || in_column_name || '+0) FROM ' ||

in_table_name;

EXECUTE IMMEDIATE (v_sql_body)

INTO v_max_value;

if v_max_value is null then

-- handle empty tables

v_max_value := 0;

end if;

-- use max value to reset the sequence

RESET_SEQ(in_sequence_name, v_max_value);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Failed to reset ' || in_sequence_name ||

' from ' || in_table_name || '.' ||

in_column_name || ' - ' || sqlerrm);

END;

BEGIN

--DBMS_OUTPUT.ENABLE(1000000);

-- load sequence/table/column associations

/***** START SCHEMA CUSTOMIZATION *****/

map_seq_to_col('Your_SEQ',

'your_table',

'the_invoice_number_column');

/***** END SCHEMA CUSTOMIZATION *****/

-- iterate all sequences that require a reset

FOR v_index IN v_data_table.FIRST .. v_data_table.LAST LOOP

BEGIN

RESET_SEQ_TO_TABLE(v_data_table(v_index).sequence_name,

v_data_table(v_index).table_name,

v_data_table(v_index).column_name);

END;

END LOOP;

END;

/

-- -------------------------------------------------------------------------------------

-- End of Script.

-- -------------------------------------------------------------------------------------

该示例是一个匿名存储过程-将其更改为包中的正确过程,并在插入新发票之前调用它以保持编号一致。