오라클/테이블(시스템관리)
[ 국가 ] 테이블 생성을 아래와 같이 진행합니다.
by 태백성
2024. 4. 12.
-- ------------------------------------------------------------------------------------------------
-- [ 시스템관리 ] 국가 테이블 생성
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;