`

Auto increasing id within oracle

阅读更多
    1. Create table:
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:
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:
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;
0
2
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics