Auto increasing id within oracle
Auto increasing id within oracle
1. Create table:
Java代码
declare num number; begin select count(*) into num from all_tables where table_name=upper('eric_emp_tbl') and owner=upper('eric'); if num=1 then execute immediate 'drop table eric_emp_tbl'; end if; end; create table eric_emp_tbl(id number(10), name varchar2(20), birthday date, gender char(2));
2. Create sequence:
Java代码
declare num number; begin select count(*) into num from user_objects where object_name=upper('eric_emp_seq') and object_type=upper('sequence'); if num=1 then execute immediate 'drop sequence eric_emp_seq'; end if; end; create sequence eric_emp_seq start with 1 increment by 1 nomaxvalue nocycle nocache
3. Create trigger:
Java代码
declare num number; begin select count(*) into num from user_objects where object_name=upper('eric_emp_tri') and object_type=upper('trigger'); if num=1 then execute immediate 'drop trigger eric_emp_tri'; end if; end; create or replace trigger eric_emp_tri before insert on eric_emp_tbl for each row begin select eric_emp_seq.nextval into :new.id from dual; end;