关于如何在已有库中利用现有表的id 重置oracle表的sequence的方法

最新推荐文章于 2023-08-23 11:24:42 发布

冰加点可乐

最新推荐文章于 2023-08-23 11:24:42 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle integer tsql header query list

本文链接：https://blog.youkuaiyun.com/jameslee666/article/details/1514668

本文介绍了一个用于批量重置Oracle数据库中序列的存储过程。通过该过程可以基于指定表的最大ID值来调整序列的起始值，确保序列生成的值不会与现有记录冲突。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先写一个存储过程 reset_sequence

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, tSql IN Varchar2) AS

cval   INTEGER;
startvalue Integer;
inc_by VARCHAR2(25);

BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';

EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual'
INTO cval;

EXECUTE IMMEDIATE tSql
INTO startvalue;

cval := cval - startvalue + 1;
IF cval < 0 THEN
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval);
ELSE
    inc_by := ' INCREMENT BY -';
END IF;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by ||
cval;

EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual'
INTO cval;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name ||
' INCREMENT BY 1';

END reset_sequence;
/

--开始批量重置oracle的sequence
exec reset_sequence('yp_chain_detail_seq','select max(id)+1 from yp_chain_detail');
exec reset_sequence('yp_chain_seq',' select max(chain_id)+1 from yp_chain');
exec reset_sequence('yp_column_field_map_seq','select max(id)+1 from yp_column_field_map');
exec reset_sequence('yp_column_property_map_seq','select max(id)+1 from yp_column_property_map');
exec reset_sequence('yp_data_extra_seq','select max(id)+1 from yp_data_extra');
exec reset_sequence('yp_data_seq','select max(id)+1 from yp_data');
exec reset_sequence('yp_data_temp_seq',' select max(id)+1 from yp_data_temp');
exec reset_sequence('yp_fields_meta_data_seq','select max(fieldid)+1 from yp_fields_meta_data');
exec reset_sequence('yp_field_query_seq',' select max(id)+1 from yp_field_query');
exec reset_sequence('yp_impexp_seq','select max(id)+1 from yp_impexp');
exec reset_sequence('yp_impexp_template_seq',' select max(templateid)+1 from yp_impexp_template');
exec reset_sequence('yp_list_header_seq',' select max(id)+1 from yp_list_header');
exec reset_sequence('yp_modules_meta_data_seq',' select max(moduleid)+1 from yp_modules_meta_data');
exec reset_sequence('yp_property_items_seq','select max(itemid)+1 from yp_property_items');
exec reset_sequence('yp_property_seq','select max(propertyid)+1 from yp_property');
exec reset_sequence('yp_recommend_history_seq',' select max(id)+1 from yp_recommend_history');
exec reset_sequence('yp_recommend_seq',' select max(id)+1 from yp_recommend');
exec reset_sequence('yp_recommend_stat_seq','select max(id)+1 from yp_recommend_stat');
exec reset_sequence('yp_structured_data_seq',' select max(id)+1 from yp_structured_data');