Oracle DB 資料準備

希望能摸鱼的凛耶酱發表於2024-07-24
Oracle

Create Table

create table DEP (  
  deptno        number,  
  dname        varchar2(50) not null,  
  location      varchar2(50),  
  constraint pk_departments primary key (deptno)  
);

create table EMP (  
  empno             number,  
  ename              varchar2(50) not null,  
  job               varchar2(50),  
  manager           number,  
  hiredate          date,  
  salary            number(7,2),  
  commission        number(7,2),  
  deptno           number,  
  constraint pk_employees primary key (empno),  
  constraint fk_employees_deptno foreign key (deptno) 
      references DEP (deptno)  
);

Create / Replace the Trigger

方法一：

# 從一開始，建立一個序列
CREATE SEQUENCE zero_based_seq
START WITH 1
INCREMENT BY 1;

#建立一個觸發器，每增加一行新資料，primary key 加1
create or replace trigger  DEP_BIU
    before insert or update on DEP
    for each row
begin
    SELECT zero_based_seq.NEXTVAL INTO : NEW.deptno FROM DUAL;
end;

create or replace trigger  EMP_BIU
    before insert or update on EMP
    for each row
begin
    SELECT zero_based_seq.NEXTVAL INTO : NEW.empno FROM DUAL;
end;

方法二：

create or replace trigger  DEP_BIU
    before insert or update on DEP
    for each row
begin
    if inserting and :new.deptno is null then
        :new.deptno := to_number(sys_guid(), 
          'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;
/

create or replace trigger EMP_BIU
    before insert or update on EMP
    for each row
begin
    if inserting and :new.empno is null then
        :new.empno := to_number(sys_guid(), 
            'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end;

相關文章