-- ------------------------------------------------------------------------------------------------
-- [ 공통관리] 메뉴 테이블 생성
conn system/built123$@ndb;
-- ------------------------------------------------------------------------------------------------
-- 테이블 생성
drop table bcom.com_menu cascade constraints;
create table bcom.com_menu
(
corp_id number not null
,menu_id number not null
,menu_name varchar2(200) not null
,menu_short_name varchar2(100) not null
,biz_group_cd varchar2(50) not null
,parent_menu_cd varchar2(50) not null
,child_menu_cd varchar2(50) not null
,program_cd varchar2(50)
,program_name varchar2(200)
,start_date date default trunc(sysdate) not null
,end_date date
,search_yn varchar2(1) default 'Y' not null
,save_yn varchar2(1) default 'N' not null
,delete_yn varchar2(1) default 'N' not null
,print_yn varchar2(1) default 'N' not null
,excel_down_yn varchar2(1) default 'N' not null
,excel_up_yn varchar2(1) default 'N' not null
,create_yn varchar2(1) default 'N' not null
,sort_order number default 1 not null
,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'
)
tablespace bdb_data;
-- 테이블 설명
comment on table bcom.com_menu is '[공통관리] 메뉴';
comment on column bcom.com_menu.corp_id is '법인ID';
comment on column bcom.com_menu.menu_id is '메뉴ID';
comment on column bcom.com_menu.menu_name is '메뉴명';
comment on column bcom.com_menu.menu_short_name is '메뉴명(약칭)';
comment on column bcom.com_menu.biz_group_cd is '업무그룹코드';
comment on column bcom.com_menu.parent_menu_cd is '상위메뉴코드';
comment on column bcom.com_menu.child_menu_cd is '하위메뉴코드';
comment on column bcom.com_menu.program_cd is '프로그램코드';
comment on column bcom.com_menu.program_name is '프로그램명';
comment on column bcom.com_menu.start_date is '시작일자';
comment on column bcom.com_menu.end_date is '종료일자';
comment on column bcom.com_menu.search_yn is '조회여부';
comment on column bcom.com_menu.save_yn is '저장여부';
comment on column bcom.com_menu.delete_yn is '삭제여부';
comment on column bcom.com_menu.print_yn is '출력여부';
comment on column bcom.com_menu.excel_down_yn is '엑셀업로드여부';
comment on column bcom.com_menu.excel_up_yn is '엑셀다운로드여부';
comment on column bcom.com_menu.create_yn is '생성여부';
comment on column bcom.com_menu.sort_order is '정렬순서';
comment on column bcohttp://m.com_menu.remark is '비고';
comment on column bcom.com_menu.create_date is '생성일시';
comment on column bcom.com_menu.create_by is '생성자';
comment on column bcom.com_menu.update_date is '수정일시';
comment on column bcom.com_menu.update_by is '수정자';
-- primary 인덱스
alter table bcom.com_menu add constraint com_menu_pk primary key (menu_id) using index tablespace bdb_idx;
-- unique 인덱스
create unique index bcom.com_menu_uk01 on bcom.com_menu (corp_id, parent_menu_cd, child_menu_cd) tablespace bdb_idx;
-- normal 인덱스
create index bcom.com_menu_ix01 on bcom.com_menu (corp_id, program_cd, start_date, end_date) tablespace bdb_idx;
create index bcom.com_menu_ix02 on bcom.com_menu (menu_name) tablespace bdb_idx;
create index bcom.com_menu_ix03 on bcom.com_menu (program_name) tablespace bdb_idx;
-- 권한부여
grant select, insert, update, delete on bcom.com_menu to bsys with grant option;
grant select, insert, update, delete on bcom.com_menu to bhrm with grant option;
-- 트리거
create or replace trigger bcom.com_menu_trg
before insert or update on bcom.com_menu
for each row
begin
if :new.menu_id is null then
:new.menu_id := com_menu_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 bsys/1234@ndb;
create or replace synonym com_menu for bcom.com_menu;
conn bhrm/1234@ndb;
create or replace synonym com_menu for bcom.com_menu;
'오라클 > 테이블(공통관리)' 카테고리의 다른 글
[ 사용자데이터 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
---|---|
[ 권한메뉴 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사용자권한 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 권한 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사용자 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |