스키마 객체 생성 및 데이터 로드

이 섹션에서는 테이블 생성 방법, 응용 프로그램의 뷰, 트리거 및 시퀀스 버전 지정 방법, 데이터를 테이블로 로드하는 방법 및 이러한 스키마 객체에 대한 권한을 필요로 하는 유저에게 부여하는 방법을 보여줍니다.

스키마 객체를 생성하고 데이터를 로드하는 단계:

  1. 사용자 app_data로 Oracle Database에 접속합니다.

    자세한 내용은 "SQL*Plus에서 Oracle Database에 접속" 또는 "SQL Developer에서 Oracle Database에 접속"을 참조하십시오.

  2. 데이터를 로드한 후 추가해야 하는 외래 키 제약 조건을 제외한 모든 필요한 제약 조건을 사용하여 테이블을 생성합니다.

  3. 버전 지정 뷰를 생성합니다.

  4. 트리거를 생성합니다.

  5. 시퀀스를 생성합니다.

  6. 데이터를 테이블로 로드합니다.

  7. 외래키 제약 조건을 추가합니다.

테이블 생성

이 섹션에서는 데이터를 로드한 후 추가해야 하는 필요한 제약 조건을 제외하고 응용 프로그램에 대한 테이블을 생성하는 방법을 보여줍니다.

주: Oracle Database에 사용자 app_data로 접속해야 합니다.

다음 프로시저에서 명령문을 SQL*Plus 또는 SQL Developer의 Worksheet에 입력할 수 있습니다. 또는 SQL Developer 도구 Create Table을 사용하여 테이블을 생성할 수도 있습니다.

테이블 생성 단계:

  1. 회사의 직무에 대한 정보를 저장하는 jobs#을 생성합니다(각 직무에 대해 한 행씩).

     CREATE TABLE jobs#
 ( job_id      VARCHAR2(10)
               CONSTRAINT jobs_pk PRIMARY KEY,
   job_title   VARCHAR2(35)
               CONSTRAINT jobs_job_title_not_null NOT NULL,
   min_salary  NUMBER(6)
               CONSTRAINT jobs_min_salary_not_null NOT NULL,
   max_salary  NUMBER(6)
               CONSTRAINT jobs_max_salary_not_null NOT NULL
 )
 /

  2. 회사의 부서에 대한 정보를 저장하는 departments#를 생성합니다(각 부서에 대해 한 행씩).

     CREATE TABLE departments#
 ( department_id    NUMBER(4)
                   CONSTRAINT departments_pk PRIMARY KEY,
   department_name  VARCHAR2(30)
                   CONSTRAINT department_name_not_null NOT NULL
                   CONSTRAINT department_name_unique UNIQUE,
   manager_id       NUMBER(6)
 )
 /

  3. 회사의 사원에 대한 정보(직원당 한 행씩)를 저장하는 employees#를 생성합니다.

     CREATE TABLE employees#
 ( employee_id     NUMBER(6)
                   CONSTRAINT employees_pk PRIMARY KEY,
   first_name      VARCHAR2(20)
                   CONSTRAINT emp_first_name_not_null NOT NULL,
   last_name       VARCHAR2(25)
                   CONSTRAINT emp_last_name_not_null NOT NULL,
   email_addr      VARCHAR2(25)
                   CONSTRAINT emp_email_addr_not_null NOT NULL,
   hire_date       DATE
                   DEFAULT TRUNC(SYSDATE)
                   CONSTRAINT emp_hire_date_not_null NOT NULL
                   CONSTRAINT emp_hire_date_check
                     CHECK(TRUNC(hire_date) = hire_date),
   country_code    VARCHAR2(5)
                   CONSTRAINT emp_country_code_not_null NOT NULL,
   phone_number    VARCHAR2(20)
                   CONSTRAINT emp_phone_number_not_null NOT NULL,

   job_id          CONSTRAINT emp_job_id_not_null NOT NULL
                   CONSTRAINT emp_jobs_fk REFERENCES jobs#,
   job_start_date  DATE
                   CONSTRAINT emp_job_start_date_not_null NOT NULL,
                   CONSTRAINT emp_job_start_date_check
                     CHECK(TRUNC(JOB_START_DATE) = job_start_date),
   salary          NUMBER(6)
                   CONSTRAINT emp_salary_not_null NOT NULL,

   manager_id      CONSTRAINT emp_mgr_to_empno_fk REFERENCES employees#,

   department_id   CONSTRAINT emp_to_dept_fk REFERENCES departments#
 )
 /

    REF 제약 조건의 원인은 다음과 같습니다.

    • 직원에게 기존 직무가 있어야 합니다. 즉, employees#.job_id 열의 값도 jobs#.job_id 열의 값이어야 합니다.

    • 사원에게는 사원이기도 한 관리자가 있어야 합니다. 즉, employees#.manager_id 열의 값도 employees#.employee_id 열의 값이어야 합니다.

    • 사원은 기존 부서에서 근무해야 합니다. 즉, employees#.department_id 열의 값도 departments#.department_id 열의 값이어야 합니다.

    또한 사원의 관리자는 사원이 근무하는 부서의 관리자여야 합니다. 즉, employees#.manager_id 열의 값도 departments#.manager_id 열의 값이어야 합니다. 하지만 employees#가 아직 존재하지 않으므로 departments#를 생성할 때 필요한 제약 조건을 지정할 수 없습니다. 따라서 나중에 departments#에 외래 키 제약 조건을 추가해야 합니다("Adding the Foreign Key Constraint 참조).

  4. 회사의 각 사원에 대한 직무 이력을 저장하는 job_history#을 생성합니다(직원이 보유한 각 직무에 대해 한 행씩).

     CREATE TABLE job_history#
 ( employee_id  CONSTRAINT job_hist_to_employees_fk REFERENCES employees#,
   job_id       CONSTRAINT job_hist_to_jobs_fk REFERENCES jobs#,
   start_date   DATE
               CONSTRAINT job_hist_start_date_not_null NOT NULL,
   end_date     DATE
               CONSTRAINT job_hist_end_date_not_null NOT NULL,
   department_id
             CONSTRAINT job_hist_to_departments_fk REFERENCES departments#
             CONSTRAINT job_hist_dept_id_not_null NOT NULL,
             CONSTRAINT job_history_pk PRIMARY KEY(employee_id,start_date),
             CONSTRAINT job_history_date_check CHECK( start_date < end_date )
 )
 /

    REF 제약 조건의 이유는 사원, 직무 및 부서가 존재해야 하기 때문입니다.

    • job_history#.employee_id 열의 값도 employees#.employee_id 열의 값이어야 합니다.

    • job_history#.job_id 열의 값도 jobs#.job_id 열의 값이어야 합니다.

    • job_history#.department_id 열의 값도 departments#.department_id 열의 값이어야 합니다.

참조: "테이블 생성"

버전 지정 뷰 생성

주: 사용자 app_data로 Oracle Database에 연결되어 있어야 합니다.

에디션 보기를 생성하려면 순서에 관계없이 다음 명령문을 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에 입력할 수 있습니다. 또는 SQL Developer 툴 Create View를 사용하여 Editioning 뷰를 생성할 수도 있습니다.

CREATE OR REPLACE EDITIONING VIEW jobs AS SELECT * FROM jobs#
/
CREATE OR REPLACE EDITIONING VIEW departments AS SELECT * FROM departments#
/
CREATE OR REPLACE EDITIONING VIEW employees AS SELECT * FROM employees#
/
CREATE OR REPLACE EDITIONING VIEW job_history AS SELECT * FROM job_history#
/

주: 애플리케이션은 항상 버전 지정 뷰를 통해 기본 테이블을 참조해야 합니다. 그렇지 않으면 에디션 뷰에 테이블이 포함되지 않으며 EBR을 사용하여 완성된 응용 프로그램을 사용 중일 때 업그레이드할 수 없습니다.

참조:

트리거 생성

주: 사용자 app_data로 Oracle Database에 연결되어 있어야 합니다.

응용 프로그램의 트리거는 다음 업무 규칙을 적용합니다.

참조: 트리거에 대한 자세한 내용은 트리거 사용

첫번째 업무 규칙 적용을 위한 트리거 생성

첫번째 업무 규칙은 직무가 j인 사원의 직무가 j인 경우 최소 급여와 최대 급여 사이의 급여가 있어야 한다는 것입니다.

이 규칙은 employees 테이블에 새 행이 삽입되거나 employees 테이블의 salary 또는 job_id 열이 갱신될 때 위반될 수 있습니다.

규칙을 적용하려면 에디션 뷰 직원에서 다음 트리거를 생성합니다. SQL*Plus 또는 SQL Developer의 Worksheet에서 CREATE TRIGGER 문을 입력할 수 있습니다. 또는 SQL Developer 도구 Create Trigger를 사용하여 트리거를 생성할 수도 있습니다.

CREATE OR REPLACE TRIGGER employees_aiufer
AFTER INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE jobs IN SHARE MODE;  -- Ensure that jobs does not change
                                  -- during the following query.
  SELECT COUNT(*) INTO l_cnt
  FROM jobs
  WHERE job_id = :NEW.job_id
  AND :NEW.salary BETWEEN min_salary AND max_salary;

  IF (l_cnt<>1) THEN
    RAISE_APPLICATION_ERROR( -20002,
      CASE
        WHEN :new.job_id = :old.job_id
        THEN 'Salary modification invalid'
        ELSE 'Job reassignment puts salary out of range'
      END );
  END IF;
END;
/

LOCK TABLE jobs IN SHARE MODE는 트리거가 테이블 작업을 질의하는 동안 다른 사용자가 테이블 작업을 변경하지 못하도록 합니다. 비차단 검침은 트리거가 사원을 변경하는 동안 트리거가 다른 유저가 작업에 대해 수행하는 변경 사항을 "확인"하지 못하도록 하고 해당 유저가 트리거가 사원에게 수행하는 변경 사항을 "확인"하지 못하도록 하기 때문에 query 중에 작업 변경 사항을 방지해야 합니다.

query 중에 작업이 변경되지 않도록 하는 또 다른 방법은 SELECT 문에 FOR UPDATE 절을 포함하는 것입니다. 그러나 SELECT FOR UPDATE는 SHARE MODE에서 LOCK TABLE 작업보다 많은 동시성을 제한합니다.

LOCK TABLE jobs IN SHARE MODE는 다른 사용자가 작업을 변경하지 못하도록 하지만 공유 모드 자체에서 작업을 잠그지 못하도록 합니다. 직무 변경은 직원 변경보다 훨씬 드물게 발생할 수 있습니다. 따라서 공유 모드로 작업을 잠그면 배타적 모드로 단일 작업 행을 잠그는 것보다 더 많은 동시성이 제공됩니다.

참조:

두번째 업무 규칙 적용을 위한 트리거 생성

두번째 업무 규칙은 직무가 j인 사원의 급여가 s인 경우 j의 최소 급여를 s보다 큰 값으로 변경하거나 j의 최대 급여를 s보다 작은 값으로 변경할 수 없습니다. 이렇게 하면 기존 데이터가 무효화됩니다.

작업 테이블의 min_salary 또는 max_salary 열이 업데이트되면 이 규칙을 위반할 수 있습니다.

규칙을 적용하려면 에디션 보기 작업에서 다음 트리거를 생성합니다. SQL*Plus 또는 SQL Developer의 Worksheet에서 CREATE TRIGGER 문을 입력할 수 있습니다. 또는 SQL Developer 도구 Create Trigger를 사용하여 트리거를 생성할 수도 있습니다.

CREATE OR REPLACE TRIGGER jobs_aufer
AFTER UPDATE OF min_salary, max_salary ON jobs FOR EACH ROW
WHEN (NEW.min_salary > OLD.min_salary OR NEW.max_salary < OLD.max_salary)
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE employees IN SHARE MODE;

  SELECT COUNT(*) INTO l_cnt
  FROM employees
  WHERE job_id = :NEW.job_id
  AND salary NOT BETWEEN :NEW.min_salary and :NEW.max_salary;

  IF (l_cnt>0) THEN
    RAISE_APPLICATION_ERROR( -20001,
      'Salary update would violate ' || l_cnt || ' existing employee records' );
  END IF;
END;
/

LOCK TABLE employees IN SHARE MODE는 트리거가 테이블 직원을 질의하는 동안 다른 사용자가 테이블 직원을 변경하지 못하도록 합니다. 비차단 검침은 트리거가 작업을 변경하는 동안 다른 유저가 사원에게 변경한 사항을 트리거가 "확인"하지 못하도록 하고 해당 유저가 트리거가 작업에 적용한 변경 사항을 "확인"하지 못하도록 하기 때문에 query 중에 사원 변경 사항을 방지해야 합니다.

이 트리거의 경우 SELECT FOR UPDATE는 LOCK TABLE IN SHARE MODE의 대안이 아닙니다. 이 직무에 대한 급여 범위를 변경하려고 할 때 이 트리거는 다른 유저가 급여를 새 범위를 벗어나도록 변경하지 못하도록 해야 합니다. 따라서 트리거는 이 job_id가 있는 employees 테이블의 모든 행을 잠그고 누군가가 이 job_id를 갖도록 갱신할 수 있는 모든 행을 잠가야 합니다.

LOCK TABLE employees IN SHARE MODE의 한 가지 대안은 DBMS_LOCK 패키지를 사용하여 job_id라는 이름의 명명된 잠금을 생성한 다음 employees 테이블과 jobs 테이블에 대한 트리거를 사용하여 동시 갱신을 방지하는 것입니다. 그러나 DBMS_LOCK 및 다중 트리거를 사용하면 런타임 성능에 부정적인 영향을 줍니다.

LOCK TABLE employees IN SHARE MODE의 또 다른 대안은 employees 테이블에 트리거를 생성하는 것입니다. 이 트리거는 사원의 변경된 각 행에 대해 직무에서 해당 직무 행을 잠급니다. 그러나 이 접근 방식은 employees 테이블의 갱신에 대해 과도한 작업을 유발하며 이는 빈번합니다.

LOCK TABLE employees IN SHARE MODE는 이전 대안보다 간단하며, 테이블을 잠그는 경우 사용자에게 불편을 끼치지 않는 경우 애플리케이션 유지 관리 시 작업 테이블 변경이 거의 발생하지 않습니다.

참조:

시퀀스 생성

주: 사용자 app_data로 Oracle Database에 연결되어 있어야 합니다.

새 부서 및 신입 사원에 대해 고유한 Primary Key를 생성하는 시퀀스를 생성하려면 다음 명령문을 두 가지 순서로 사용합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에 입력할 수 있습니다. 또는 SQL Developer 툴 Create Sequence를 사용하여 시퀀스를 생성할 수도 있습니다.

CREATE SEQUENCE employees_sequence START WITH 210;
CREATE SEQUENCE departments_sequence START WITH 275;

예제 스키마 HR의 테이블에서 로드할 데이터와 충돌하지 않도록 employees_sequence 및 departments_sequence의 시작 번호는 각각 employees.employee_id 및 departments.department_id의 최대값을 초과해야 합니다. "데이터 로드" 후 이 질의는 다음 최대값을 표시합니다.

SELECT MAX(e.employee_id), MAX(d.department_id)
FROM employees e, departments d;

결과:

MAX(E.EMPLOYEE_ID) MAX(D.DEPARTMENT_ID)
------------------ --------------------
               206                  270

참조: "시퀀스 생성 및 관리"

데이터 로드

주: 사용자 app_data로 Oracle Database에 연결되어 있어야 합니다.

예제 스키마 HR의 테이블에서 가져온 데이터로 응용 프로그램의 테이블을 로드합니다.

주: 다음 절차는 해당 버전 지정 뷰를 통해 응용 프로그램의 테이블을 참조합니다.

다음 프로시저에서 명령문을 SQL*Plus 또는 SQL Developer의 Worksheet에 입력할 수 있습니다.

테이블로 데이터를 로드하려면 다음을 수행합니다.

  1. HR.JOBS 테이블의 데이터로 작업 로드:

     INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
 SELECT job_id, job_title, min_salary, max_salary
   FROM HR.JOBS
 /

    결과:

     19 rows created.

  2. HR.DEPARTMENTS 테이블에서 가져온 데이터로 부서를 로드합니다.

     INSERT INTO departments (department_id, department_name, manager_id)
 SELECT department_id, department_name, manager_id
   FROM HR.DEPARTMENTS
 /

    결과:

     27 rows created.

  3. HR.EMPLOYEES 및 HR.JOB_HISTORY 테이블의 데이터로 사원을 로드하고, 검색된 CASE 식 및 SQL 함수를 사용하여 employees.country_code 및 employees.phone_number를 HR.phone_number 및 SQL 함수로 가져오고 스칼라 subquery를 사용하여 HR.JOB_HISTORY에서 employees.job_start_date를 가져옵니다.

     INSERT INTO employees (employee_id, first_name, last_name, email_addr,
   hire_date, country_code, phone_number, job_id, job_start_date, salary,
   manager_id, department_id)
 SELECT employee_id, first_name, last_name, email, hire_date,
   CASE WHEN phone_number LIKE '011.%'
     THEN '+' || SUBSTR( phone_number, INSTR( phone_number, '.' )+1,
       INSTR( phone_number, '.', 1, 2 ) -  INSTR( phone_number, '.' ) - 1 )
     ELSE '+1'
   END country_code,
   CASE WHEN phone_number LIKE '011.%'
     THEN SUBSTR( phone_number, INSTR(phone_number, '.', 1, 2 )+1 )
     ELSE phone_number
   END phone_number,
   job_id,
   NVL( (SELECT MAX(end_date+1)
         FROM HR.JOB_HISTORY jh
         WHERE jh.employee_id = employees.employee_id), hire_date),
   salary, manager_id, department_id
   FROM HR.EMPLOYEES
 /

    결과:

     107 rows created.

    주: 앞의 INSERT 문은 "첫번째 비즈니스 규칙 적용을 위한 트리거 생성"에서 생성된 트리거를 실행합니다.

  4. HR.JOB_HISTORY 테이블의 데이터로 job_history를 로드합니다.

     INSERT INTO job_history (employee_id, job_id, start_date, end_date,
   department_id)
 SELECT employee_id, job_id, start_date, end_date, department_id
   FROM HR.JOB_HISTORY
 /

    결과:

     10 rows created.

  5. 변경 사항을 커밋합니다.

     COMMIT;

참조:

외래 키 제약 조건 추가

주: 사용자 app_data로 Oracle Database에 연결되어 있어야 합니다.

이제 테이블 부서와 사원이 데이터를 포함하므로 다음 ALTER TABLE 문을 사용하여 외래 키 제약 조건을 추가합니다. 명령문은 SQL*Plus 또는 SQL Developer의 Worksheet에서 입력할 수 있습니다. 또는 SQL Developer 도구 Add Foreign Key를 사용하여 제약 조건을 추가할 수 있습니다.

ALTER TABLE departments#
ADD CONSTRAINT dept_to_emp_fk
FOREIGN KEY(manager_id) REFERENCES employees#;

departments# 및 employees#가 데이터를 포함하기 전에 이 외래 키 제약 조건을 추가하면 다음 데이터로 둘 중 하나를 로드하려고 하면 이 오류가 발생합니다.

ORA-02291: integrity constraint (APP_DATA.JOB_HIST_TO_DEPT_FK) violated - parent key not found

자습서: "자습서: 기존 테이블에 제약 조건 추가"

유저에게 스키마 객체에 대한 권한 부여

주: 사용자 app_data로 Oracle Database에 연결되어 있어야 합니다.

사용자에게 권한을 부여하려면 SQL 문 GRANT를 사용합니다. SQL*Plus 또는 SQL Developer의 Worksheet에서 GRANT 문을 입력할 수 있습니다.

app_code에 employees_pkg을 만드는 데 필요한 권한만 부여합니다.

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_code;
GRANT SELECT ON departments TO app_code;
GRANT SELECT ON jobs TO app_code;
GRANT SELECT, INSERT on job_history TO app_code;
GRANT SELECT ON employees_sequence TO app_code;

admin_pkg를 만드는 데 필요한 권한만 app_admin에 부여합니다.

GRANT SELECT, INSERT, UPDATE, DELETE ON jobs TO app_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON departments TO app_admin;
GRANT SELECT ON employees_sequence TO app_admin;
GRANT SELECT ON departments_sequence TO app_admin;

참조: GRANT 문에 대한 자세한 내용은 Oracle Database SQL Language Reference를 참조하십시오.