保留当前序列-可以使用以下命令将值重置为当前存储在表中的最大值:
-- --------------------------------
-- 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.
-- -------------------------------------------------------------------------------------
该示例是一个匿名存储过程-将其更改为包中的正确过程,并在插入新发票之前调用它以保持编号一致。