-- ------------------------------------------------------------------------------------------------
-- [ 인사관리 ] 임직원 테이블 생성
conn system/built123$@ndb;
-- ------------------------------------------------------------------------------------------------
-- 테이블 생성
drop table bhrm.hrm_employee cascade constraints;
create table bhrm.hrm_employee
(
corp_id number not null
,emp_id number not null
,emp_num varchar2(20) not null
,emp_name varchar2(200) not null
,per_ssn varchar2(2000) not null
,gender_cd varchar2(50) default 'U' not null
,birth_date date not null
,birth_type_cd varchar2(50) default 'S' not null
,emp_type_cd varchar2(50) default '10' not null
,hire_type_cd varchar2(50) not null
,hire_date date default trunc(sysdate) not null
,end_date date
,emp_image blob
,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 bhrm.hrm_employee is '[인사관리] 임직원';
comment on column bhrm.hrm_employee.corp_id is '법인ID';
comment on column bhrm.hrm_employee.emp_id is '임직원ID';
comment on column bhrm.hrm_employee.emp_num is '사번';
comment on column bhrm.hrm_employee.emp_name is '성명';
comment on column bhrm.hrm_employee.per_ssn is '주민번호';
comment on column bhrm.hrm_employee.gender_cd is '성별코드';
comment on column bhrm.hrm_employee.birth_date is '생년월일';
comment on column bhrm.hrm_employee.birth_type_cd is '음/양력코드';
comment on column bhrm.hrm_employee.emp_type_cd is '직원유형코드';
comment on column bhrm.hrm_employee.hire_type_cd is '입사구분코드';
comment on column bhrm.hrm_employee.hire_date is '입사일자';
comment on column bhrm.hrm_employee.end_date is '종료일자';
comment on column bhrm.hrm_employee.emp_image is '사진';
comment on column bhrhttp://m.hrm_employee.remark is '비고';
comment on column bhrm.hrm_employee.create_date is '생성일시';
comment on column bhrm.hrm_employee.create_by is '생성자';
comment on column bhrm.hrm_employee.update_date is '수정일시';
comment on column bhrm.hrm_employee.update_by is '수정자';
-- primary 인덱스
alter table bhrm.hrm_employee add constraint hrm_employee_pk primary key (emp_id) using index tablespace bdb_idx;
-- unique 인덱스
create unique index bhrm.hrm_employee_uk01 on bhrm.hrm_employee (corp_id, emp_num) tablespace bdb_idx;
-- normal 인덱스
create index bhrm.hrm_employee_ix01 on bhrm.hrm_employee (emp_name) tablespace bdb_idx;
create index bhrm.hrm_employee_ix02 on bhrm.hrm_employee (corp_id, hire_date, end_date) tablespace bdb_idx;
-- 권한부여
grant select, insert, update, delete on bhrm.hrm_employee to bsys with grant option;
grant select, insert, update, delete on bhrm.hrm_employee to bcom with grant option;
-- 트리거 생성
create or replace trigger bhrm.hrm_employee_trg
before insert or update on bhrm.hrm_employee
for each row
begin
if :new.emp_id is null then
:new.emp_id := hrm_employee_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 hrm_employee for bhrm.hrm_employee;
conn bcom/1234@ndb;
create or replace synonym hrm_employee for bhrm.hrm_employee;
'오라클 > 테이블(인사관리)' 카테고리의 다른 글
[ 발령 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
---|---|
[ 임직원기본 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 조직도상세 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 조직도 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사업장 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |