Oracle数据库字符串类型主键自增实现

Oracle数据库字符串类型主键自增实现

菜单表：

-- Create table
create table MENU
(
  menu_id   VARCHAR2(10) not null,
  menu_name VARCHAR2(50) not null
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table MENU
  add constraint PK_MENU_ID primary key (MENU_ID);

如上表，菜单表menu，主键是menu_Id，且其类型为varchar2，要实现menu_id从00001自增，至99999，使用sequence+trigger来实现。

序列sequence:

create sequence seq_menu_id
increment by 1 --每次加几个
start with 1
maxvalue 99999
nocycle --直累加，不循环
cache 10;

触发器trigger

create trigger tri_menu_id before insert
on menu for each row when (new.menu_id is null)
declare
   next_id varchar2(5);
begin
   select substr('0000'||seq_menu_id.nextval, -5) into next_id from dual;
   :new.menu_id:=next_id;
end;

插入测试