Oracle APEX如何重置序列或实现清空表后主键ID自动从1开始填充

方案1（最推荐）：插入时直接生成ID，无需序列/触发器 #

你每次操作都是全表删除后批量插入新数据，完全不需要额外维护序列、触发器，直接在插入时用rownum即可实现ID从1开始连续递增：

create or replace PROCEDURE "PR_KEYWORD_SEARCH" (v_patient_id NUMBER, v_keyWord varchar2)
IS
BEGIN
    delete from KEYWORD_RSLT;
    -- 子查询先拿到去重后的结果，外层用rownum生成连续ID
    insert into KEYWORD_RSLT (ID, SESSION_MONTH, PATIENT_NAME)
        select 
            rownum as ID,
            SESSION_MONTH,
            PATIENT_NAME
        from (
            select distinct
                to_char(s.SESSION_DATE, 'MM-YYYY') as SESSION_MONTH,
                p.FIRST_NAME ||' '||p.LAST_NAME as PATIENT_NAME
            from SESSIONS s,
                CLIENTS p
            where s.CLIENTS_ID = p.ID
            and (s.CRITICAL_POINT like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id 
            or s.ACTIONS like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id)
        );
END PR_KEYWORD_SEARCH;

该方案无DDL操作、不会隐式提交事务，不需要额外权限适配，性能和兼容性最优。

方案2：必须使用序列的场景，用动态SQL重置 #

你之前无法在存储过程中执行alter sequence是因为DDL语句不能直接在PL/SQL块中运行，需要用动态SQL包裹，同时需要保证存储过程的所有者有ALTER SEQUENCE权限：

create or replace PROCEDURE "PR_KEYWORD_SEARCH" (v_patient_id NUMBER, v_keyWord varchar2)
IS
BEGIN
    delete from KEYWORD_RSLT;
    -- 动态SQL执行序列重置
    execute immediate 'alter sequence SQ_KEYWORD_RSLT_INCREAMENT restart start with 1';
    insert into KEYWORD_RSLT (SESSION_MONTH, PATIENT_NAME)
        select distinct
            to_char(s.SESSION_DATE, 'MM-YYYY') as SESSION_MONTH,
            p.FIRST_NAME ||' '||p.LAST_NAME as PATIENT_NAME
        from SESSIONS s,
            CLIENTS p
        where s.CLIENTS_ID = p.ID
        and (s.CRITICAL_POINT like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id 
        or s.ACTIONS like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id);
END PR_KEYWORD_SEARCH;

注意：alter sequence属于DDL语句，执行后会隐式提交之前的事务，如果你的存储过程需要支持事务回滚，不要使用该方案。

方案3（Oracle 12c及以上版本适用）：标识列+TRUNCATE自动重置 #

如果你的数据库是12c及以上版本，可以将ID列设置为标识列，清空表时用TRUNCATE会自动重置标识列的起始值：

1. 先修改表结构：

ALTER TABLE KEYWORD_RSLT MODIFY ID GENERATED ALWAYS AS IDENTITY;

2. 修改存储过程：

create or replace PROCEDURE "PR_KEYWORD_SEARCH" (v_patient_id NUMBER, v_keyWord varchar2)
IS
BEGIN
    -- TRUNCATE是DDL需要动态SQL执行，执行后标识列自动从1开始计数
    execute immediate 'truncate table KEYWORD_RSLT';
    insert into KEYWORD_RSLT (SESSION_MONTH, PATIENT_NAME)
        select distinct
            to_char(s.SESSION_DATE, 'MM-YYYY') as SESSION_MONTH,
            p.FIRST_NAME ||' '||p.LAST_NAME as PATIENT_NAME
        from SESSIONS s,
            CLIENTS p
        where s.CLIENTS_ID = p.ID
        and (s.CRITICAL_POINT like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id 
        or s.ACTIONS like LOWER ('%'||v_keyWord||'%') and s.CLIENTS_ID = v_patient_id);
END PR_KEYWORD_SEARCH;

注意：TRUNCATE清空的数据无法回滚，同时会隐式提交事务，仅适合不需要事务回滚的场景。

内容的提问来源于stack exchange，提问作者user13768246