当前位置:数据库 > Oracle >>

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;  

 

 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,