[ 국가 ] 테이블 생성을 아래와 같이 진행합니다.

-- ------------------------------------------------------------------------------------------------
-- [ 시스템관리 ] 국가 테이블 생성
conn system/built123$@ndb;
-- ------------------------------------------------------------------------------------------------
-- 테이블 생성
drop   table bsys.sys_country cascade constraints;
create table bsys.sys_country
(
     country_id              number         not null
    ,country_cd              varchar2(20)   not null
    ,country_name            varchar2(200)  not null
    ,country3_cd             varchar2(20)   not null
    ,language_cd             varchar2(20)   not null
    ,language_name           varchar2(200)  not null
    ,currency_cd             varchar2(20)   not null
    ,currency_name           varchar2(200)  not null
    ,start_date              date           default trunc(sysdate) not null
    ,end_date                date
    ,seed_data_yn            varchar2(1)    default 'N'
    ,remark                  varchar2(4000)
    ,create_date             timestamp      default systimestamp
    ,create_by               varchar2(50)   default '-1'
    ,update_date             timestamp      default systimestamp
    ,update_by               varchar2(50)   default '-1'
);

-- 테이블 설명
comment on table  bsys.sys_country                   is '[시스템관리] 국가';
comment on column bsys.sys_country.country_id        is '국가ID';
comment on column bsys.sys_country.country_cd        is '국가코드';
comment on column bsys.sys_country.country_name      is '국가명';
comment on column bsys.sys_country.country3_cd       is '국가코드3';
comment on column bsys.sys_country.language_cd       is '언어코드';
comment on column bsys.sys_country.language_name     is '언어명';
comment on column bsys.sys_country.currency_cd       is '화폐코드';
comment on column bsys.sys_country.currency_name     is '화폐명';
comment on column bsys.sys_country.start_date        is '시작일자';
comment on column bsys.sys_country.end_date          is '종료일자';
comment on column bsys.sys_country.seed_data_yn      is '기초데이터여부';
comment on column bsys.sys_country.remark            is '비고';

comment on column bsys.sys_country.create_date       is '생성일시';
comment on column bsys.sys_country.create_by         is '생성자';
comment on column bsys.sys_country.update_date       is '수정일시';
comment on column bsys.sys_country.update_by         is '수정자';

-- primary 인덱스
alter table bsys.sys_country add constraint sys_country_pk primary key (country_id) using index tablespace bdb_idx;

-- unique 인덱스
create unique index bsys.sys_country_uk on bsys.sys_country (country_cd, language_cd) tablespace bdb_idx;

-- normal 인덱스
create index bsys.sys_country_ix01 on bsys.sys_country (currency_cd) tablespace bdb_idx;
create index bsys.sys_country_ix02 on bsys.sys_country (language_cd) tablespace bdb_idx;

-- 권한부여
grant select, insert, update, delete on bsys.sys_country to bcom with grant option;
grant select, insert, update, delete on bsys.sys_country to bhrm with grant option;

-- 트리거 생성
create or replace trigger bsys.sys_country_trg
    before insert or update on bsys.sys_country
    for each row
begin
    if :new.country_id is null then
        :new.country_id := sys_country_s.nextval;
    end if;
    
    if inserting then
        :new.create_date := systimestamp;
        :new.create_by   := nvl(wwv_flow.g_user, user);
    end if;    
    
    :new.update_date := systimestamp;
    :new.update_by   := nvl(wwv_flow.g_user, user);
end;
/

-- 시노님 생성
conn bcom/1234@ndb;
create or replace synonym sys_country for bsys.sys_country;

conn bhrm/1234@ndb;
create or replace synonym sys_country for bsys.sys_country;