[Oracle] Oracle 응용 예제(HR)

테이블 간의 관례를 얻기 위해 ERWin을 사용하길 권장함

 

ERD(Entity Relation Diagram) 개체와 개체 간의 관계 및 속성

ERD는 모델링 과정에서 논리적 Data 설계의 산출물로서 업무를 보다 분명히 정의하기 위해 작성된다.

전산실이나 운영 중인 애플리케이션을 유지 보슈할 때 스키마의 정보를 ERD를 통해 더욱 쉽게 접근할 수 있다.

 

스키마란 Oracle Database Server의 논리적인 저장 구조로서, 데이터베이스 사용자가 소유하고 있는 Table, View, Sequence, Stored Procedure 등을 말한다. 스키마는 데이터베이스 객체의 데이터 구조와 제약 조건을 정의하고, 이들 개체 간의 관계를 정의하는데 사용된다.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Oracle에서 제공하는 인사 관리 시스템(HR) 소유 스키마 사이의 관계를 ERD통해 확인하기

 

ERWin -> 메뉴 Tools -> Reverse Engineer... -> Logical/Physical V -> Next -> Current User V ->Next

-> Use Oracle authentication V -> User name: hr , Password: ** ,  Connect String: orcl -> Connect

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

인사 관리 업무에 활용된 Oracle 기술 공부하기

CREATE TABLE regions
(
region_id NUMBER CONSTRAINT region_id_nn NOT NULL,
region_name VARCHAR2(25)
);

CREATE UNIQUE INDEX reg_id_pk ON regions (region_id);

ALTER TABLE regions ADD (CONSTRAINT reg_id_pk PRIMARY KEY (region_id));

 

지역 테이블은 region_id, region_name 2개의 칼럼으로 구성되어 있다.

region_id는 유일하면서(UNIQUE) NULL 값을 허용하지 않으므로(NOT NULL) PK 제약 조건으로 규칙을 만족시킬 수 있다.

 

제약 조건중 PK와 UNIQUE로 지정된 칼럼은 자동으로 인덱스가 생성된다. 예를 들어 region_id 값을 WHERE절에서 찾고자 한다면 FULL TABLE SCAN 방식보다는 인덱스를 이용하여 접근하는 것이 대용량의 데이터에서는 나은 검색 성능을 기대할 수 있기 떄문이다. PK나 UNIQUE 제약조건으로 생성된 인덱스는 이름을 지정하지 않았기 떄문에 SYS로 시작하는 임의의 이름을 Oracle로부터 부여받는다. 이 점을 고려하여 인덱스에 이름(reg_id_ik)을 부여하여 먼저 생성한 후, 동일 이름으로 제약 조건 이름을 지정하고 있음을 위문장은 보여준다.

CREATE TABLE countries
(
country_id CHAR(2) CONSTRAINT country_id_nn NOT NULL,
country_name VARCHAR2(40),
region_id NUMBER,
CONSTRAINT country_c_id_pk PRIMARY KEY (country_id)
)

ORGANIZATION INDEX;

 

국가 테이블은 ALTER문을 사용하지 않고, 테이블 레벨에서 country_id 칼럼에 country_c_id_pk라는 이름으로 PK 제약조건을 설정하고 있다. CREATE TABLE문 마지막의 ORGANIZATION INDEX는 테이블을 IOT(Index Organized Table: 인덱스 구성 테이블)로 생성하고 있다.

 

IOT는 질의의 성능을 높일 수 있도록 인덱스와 같은 구조로 데이터를 저장하는 테이블 구조를 말합니다.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------Regular Table and Index

Index                                        Table

Finance ROWID -------------->  Finance  5543

Invest ROWID    -------------->  Invest     6879

 

Index-Organlzed Table

Index 

Frinance ROWID  =  Table Data Stored in Index

Invest ROWID        = Table Data Stored in Index

 

Regular Table and Index 는 테이블의 특정 칼럼에 인덱스를 설정하고 이 인덱스를 이용한 접근을 보여준다.

반면에 IOT는 인덱스에 데이터 자체를 저장하고있다. IOT는 전체 테이블 검색 시 PK로 지정된 칼럼 값을 검색하기 때문에 이미 정렬된 값을 검색하며, 범위 검색이나 정확한 값의 검색에서 테이블에 저장된 데이터의 검색보다 나은 성능을 제공한다. 그러나 테이블에서와같이 추가적인 인덱스의 생성은 불가능하다. IOT는 PK로 지정된 칼럼에 이미 Indexing 되어 있기 때문이다.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;

locations_seq 시퀀스는 시작 값 3300, 증가 값 100을 가지며 최대값 9900까지 값을 증가함. NOCYCLE이므로 최대값을 초과하는 값은 시퀀스에서 할당하지 못함. 시퀀스는 수정이 가능함. MAXVALUE를 증가하거나 감소시킬 수 있으나 감소는 현재의 시퀀스 값보다는 크게 설정해야함.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW emp_details_view
(employee_id, job_id, manager_id, department_id, location_id, country_id, first_name, last_name, salary, commission_pct, department_name, job_title, city, state_province, country_name, region_name)
AS SELECT
e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name
FROM employees e, departments d, jobs j, locations l, countries c, regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id
WITH READ ONLY;           -- 읽기전용 뷰를통한 INSERT, UPDATE, DELETE 명령을 실행할 수 없음

빈번히 사용되는 복잡한 질의문을 위와 같이 뷰로 만들어 둔다면, 뷰를 통해 질의할 떄, 뷰 생성 시 정의한 sql문을 실행하므로 동일 결과에 더 쉽게 접근할 수 있다. 따라서 코드의 유지보수를 할 때 편리하다는 장점이 있다.

 

SELECT lst_name, department_name, job_title
FROM emp_details_view
WHERE employee_id =100;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

PK나 UNIQUE 제약 조건 설정 시 UNIQUE INDEX가 자동 설정 됨

 

CREATE INDEX emp_department_ix ON employees (department_id);

 

사원 테이블의 부서번호 칼럼에 인덱스를 설정하고있다. 부서 테이블과 JOIN이 발생하는 경우 WHERE절에서 사용되므로 검색할 때 성능을 생각해봐야한다. WHERE절의 한쪽 칼럼이 PK나 UNIQUE로 지정되어(부서 테이블의 부서번호 컬럼) UNIQUE한 인덱스를 가지고 있는 반면, 다른 칼럼(사원 테이블의 부서번호)는 외래키가 설정되어 인덱스를 가지고 있지 않은 경우이다. 일반적으로 외래키로 지정된 칼럼에 인덱스를 설정하면 더 나은 성능을 가져올 수 있기 때문에 위와 같이 인덱스를 지정하는게 좋다.

 

CREATE INDEX loc_city_ix ON locations (city);

 

위의 경우는 WHERE절에서 자주 사용되는 칼럼에 대해 인덱스를 설정한 경우로서 앞으 경우와는 다르다. 즉 위치 테이블에서는 city 칼럼의 값을 자주 비교하는 질의가 사용된 경우 인덱스를 지정하여 질의의 성능 향상을 기대하고자 한다.

 

CREATE INDEX emp_name_ix ON employees (last_name, first_name);

 

사원 테이블에서 last_name과 first_name도 이와 같은데, 이 경우는 두 개의 칼럼으로 emp_name_ix 라는 복합 인덱스를 생성 하고 있다. 복합 인덱스 생성 시 주의할 점은 어떤 칼럼을 선행 칼럼으로 사용해야 하는지 생각해 보아야 한다. 인덱스를 이용할 조건이 된다면 WHERE절에서 last_name 칼럼 단독으로 사용되더라도 emp_name_ix 인덱스를 이용하게 된다. 그러나 first_name 이 단독으로 사용되면 인덱스를 사용하지 않게 된다. 따라서 칼럼들 중 자주 WHERE절에서 사용되는 칼럼을 선행 칼럼으로 두는 것이 바람직하다.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN

          IF (TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN'))
          THEN RAISE_APPLICATION_ERPOR (-20205, 'You may only make changes during normal office hours');
          END IF;

END secure_dml

 

secure_dml SP는 업무시간 이외의 시간에는 에러를 발생시키게 된다.

 

CREATE OR REPLACE TRIGGER secure_employees
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
         
         secure_dml;
         
END secure_employees;

secure_employees Trigger는 사원 테이블에 트랜잭션이 발생하기 전에 앞서 생성한 secure_dml SP를 호출한다.

즉 사원 테이블에 데이터를 조작할 수 있는 권한이 있는 사용자라 하더라도 업무 이외의 시간에는 트랜잭션을 발생시킬 수 없도록 Trigger를 설정하고 있다.

이와 같이 트리거는 제약 조건(Constraints)로 설정할 수 없는 비즈니스 규칙을 적용할떄 사용한다.

 

CREATE OR REPLACE PROCEDURE add_job_history
(
   p_emp_id job_history.employee_id%type
  ,p_start_date job_history.start_date$type
  ,p_end_date job_history.end_date%type
  ,p_job_id job_history.job_id%type
  ,p_department_id job_history.department_id%type
)
IS
BEGIN
   INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
   VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

 

job_histort 테이블은 직무의 변경 사항이나 부서 발령의 정보를 저장하고 있다. add_job_history Stored Procedure는 실행 시 5개의 파라미터(사원번호, 시작일, 종료일, 직무 아이디, 부서번호)를 변수에 값을 할당받아 job_history 테이블에 입력받은 값을 추가하는 SP이다.

 

CREATE OR REPLACE TRIGGER update_job_history
ALTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
      add_job_history(:old.employee_id, :old:hire_date, sysdate, :old_job_id, :old.department_id);
END;

update_job_history 트리거는 사원 테이블의 직무 아이디(job_id)나 부서번호(department_id)에 변경 사항이 발생하면 변경 전의 사원번호(:old.employee_id), 입사일, 현재일, 직무 아이디, 부서 번호 5개의 변수로 add_job_history SP를 호출하고 있다. 즉 변경 전의 이전 직무나 부서의 명세를 job_history 테이블에 자동으로 기록하는 작업이다.