과정 목표

본 과정에서는 주된 데이터베이스 객체와 서로에 대한 그들의 관련성에 관하여 배웁니다. 또한 테이블을 생성, 변경 그리고 삭제하는 방법을 배웁니다.

 

데이터베이스 객체

오라클 데이터베이스는 여러 개의 데이터 구조를 포함할 수 있습니다. 데이터베이스 설계에서 작성된 각각의 구조는 데이터베이스 개발 단계 동안에 생성할 수 있습니다.

Oracle8 테이블 구조

주: 보다 많은 데이터베이스 객체를 이용할 수 있지만 본 과정에서는 다루지 않습니다.

 

이름 지정 규칙

어떤 오라클 데이터베이스 객체 이름을 지정하는 표준 규칙에 따라서 데이터베이스 테이블과 열의 이름을 정합니다.

이름 지정 지침

주: 이름은 대소문자를 구분하지 않습니다. 예를 들면, EMP는 eMP 또는 Emp와 똑같이 취급 합니다.

“Object Names and Qualifiers.”에 대하여 보다 많은 정보를 알고자 한다면,  Oracle Server SQL Reference, Release 8.0 을 참조하십시오.

 

CREATE TABLE 문장

SQL CREATE TABLE 문장을 실행하여 데이터를 저장하기 위한 테이블을 생성합니다. 이 문장은 데이터 정의어(DDL) 문장의 하나인데, 이후의 여러 장에 걸쳐서 다루어집니다. DDL 문장은 Oracle8 데이터베이스 구조를 생성, 수정 또는 삭제하는데 사용되는 SQL 문장의 부분 집합입니다. 이러한 문장은 데이터베이스에 즉각 영향을 미치며, 데이터베이스 사전에 정보를 기록합니다.

테이블을 생성하기 위해서 사용자는 CREATE TABLE 권한과 객체를 생성하기 위한 저장 영역을 가져야 합니다. 데이터베이스 관리자는 다음 과정에서 다룰 데이터 제어어(DCL)를 사용하는데, 이것은 사용자에게 권한을 부여합니다.

구문형식에서:

        schema                 소유자의 이름과 똑같습니다.
        table                      테이블의 이름입니다.
        DEFAULT expr        INSERT 문장에서 값을 생략할 경우, 디폴트 값을 명시합니다.
        column                  열의 이름입니다.
        datatype                열의 데이터형과 길이입니다.

“CREATE TABLE.” 에 대해서 보다 많은 정보를 알고자 한다면, Oracle Server SQL Reference, Release 7.3 또는 8.0 을 참조하십시오.

 

테이블 생성

위의 예는 DEPTNO, DNAME 그리고 LOC 이라는 세 개의 열을 생성합니다. DESCRIBE 명령어를 사용하여 테이블의 생성을 확인합니다.

DDL 문장으로 테이블을 생성하므로 이 문장을 실행되면 자동으로 커밋됩니다.

 

데이터 사전 질의

여러분이 소유한 다양한 데이터베이스 객체를 보기 위해서 데이터 사전 테이블을 질의할 수 있습니다. 다음이 종종 사용되는 데이터 사전 테이블입니다.

주: USER_CATALOG 는 CAT과 동의어입니다. 이 동의어를 SQL에서 USER_CATALOG 대신에 사용할 수 있습니다.

SQL> SELECT *
  2  FROM   CAT;

 

데이터형(음성 설명은 없습니다.)
 

데이터형

설명

VARCHAR2(size)

가변 길이 문자 데이터 (최대 크기는 명시해야 하며, 최소 크기는 1, 최대 크기는 4000입니다.)

CHAR(size)

size Byte  길이의 고정 길이 문자 데이터 (디폴트이며 최소 크기는 1, 최대 크기는 2000입니다.)

NUMBER(p,s)

정밀도  p 와 스케일 s를 가지는 숫자; 정밀도는 십진 자리수의 최대 개수이며, 스케일은 소수점 오른쪽의 자리수입니다. (정밀도는 1 에서 38까지의 범위이며 스케일은 -84 에서 127까지의 범위일 수 있습니다.)

DATE

January 1, 4712 B.C. 와 December 31, 9999 A.D.사이의 날짜와 시간 값입니다.

LONG

2 기가바이트까지의 가변 길이 문자 데이터입니다.

CLOB

4 기가바이트까지의 단일 바이트 문자 데이터입니다.

RAW(size)

size 길이의 원시 이진 데이터입니다. 최대 크기는 2000 입니다. (최대 크기는 명시해야 합니다.)

LONG RAW

2 기가바이트까지의 가변 길이 원시 이진 데이터입니다.

BLOB

4 기가바이트까지의 이진 데이터입니다.

BFILE

4 기가바이트까지의 외부 파일에 저장된 이진 데이터입니다.

 

DEFAULT 옵션

열은 DEFAULT 옵션을 사용하여 디폴트 값을 부여할 수 있습니다. 이 옵션은 열에 대한 값없이 어떤 행을 입력할 경우 null 값이 열에 입력되지 않도록 해 줍니다. 디폴트 값은 리터럴, 표현식 또는 SYSDATE나 USER같은 SQL 함수일 수 있지만, 다른 열의 이름이나 NEXTVAL이나 CURRVAL 같은 의사 열은 안됩니다. 디폴트 표현식은 열의 데이터형과 일치해야 합니다.

다른 테이블에 있는 행으로부터 테이블 생성

테이블을 생성하는 두번째 방법은 테이블을 생성하고 서브쿼리로부터 리턴된 행을 삽입하기 위해서 AS subquery 절을 적용하는 것입니다.

구문형식에서:

table

테이블의 이름입니다.

column

열 이름, 디폴트 값 그리고 무결성 제약조건입니다.

subquery

새로운 테이블에 삽입할 행의 집합을 정의한 SELECT 문장입니다.

지침

 

다른 테이블에 있는 행으로부터 테이블 생성(계속)

위의 예는 부서 30에서 근무하는 모든 종업원에 대한 상세정보를 포함하는 DEPT30 이라는 테이블을 생성합니다. DEPT30이라는 테이블은 EMP에서 유래합니다.

SQL*Plus DESCRIBE 명령을 사용하여 데이터베이스 테이블의 존재를 검증하며 열 정의를 검사합니다.

표현식을 선택할 때 열 별칭을 부여합니다.

 

ALTER TABLE 문장

테이블을 생성한 이후에 열이 생략되었거나 열 정의를 변경할 필요가 있기 때문에 테이블 구조 를 변경할 필요가 있을 수 있습니다. ALTER TABLE 문장을 사용하여 이것을 수행할 수 있습니다.
ALTER TABLE 문장에 ADD 절을 사용하여 테이블에 열을 추가할 수 있습니다.

구문형식에서:

        table                   테이블의 이름입니다.
        column               새로운 열의 이름입니다.
        datatype              새로운 열의 데이터형과 길이입니다.
        DEFAULT expr     새로운 열에 대한 디폴트 값을 명시합니다.

ALTER TABLE 문장에 MODIFY 절을 사용하여 테이블에 있는 기존의 열을 변경할 수 있습니다.

주: 슬라이드는 ALTER TABLE에 대한 단축된 구문형식을 보여줍니다. ALTER TABLE은 추후의 과정에서 다룹니다.

 

열 추가에 대한 지침

위의 예는 DEPT30 테이블에 JOB 열을 추가합니다. JOB 열은 테이블에서 마지막 열이 됩니다.

주: 열을 추가할 때 테이블이 이미 어떤 행을 포함하고 있다면, 새로운 열은 이미 존재하는 모든 행에 대해서 null로 초기화합니다.

 

열 수정

ALTER TABLE 문장에 MODIFY 절을 사용하여 열 정의를 수정할 수 있습니다. 열 수정은 열의 데이터형, 크기 그리고 디폴트 값 변경을 할 수 있습니다.

지침

  • 숫자 열의 정밀도나 폭을 증가시킵니다.
  • 열이 오직 null 값만을 포함하거나 테이블에 행이 없으면 열의 폭을 감소시킵니다.
  • 열이 null 값을 포함하면 데이터형을 변경시킵니다.
  • 열이 null 값을 포함하거나 크기를 변경하지 않으면 CHAR 열을 VARCHAR2 데이터형으로 변경하거나
  • VARCHAR2 열을 CHAR 데이터형으로 변경합니다.
  • 열의 디폴트 값을 변경시키는 것은 오직 테이블에 가해지는 이후의 삽입에만 영향을 미칩니다.

 

열 삭제

ALTER TABLE 문을 DROP COLUMN 절과 함께 사용하여 테이블에서 열을 삭제할 수 있습니다. 이 기능은 Oracle8i부터 사용할 수 있습니다.

지침

 

SET UNUSED 옵션

SET UNUSED 옵션은 시스템 자원에 대한 요구가 적을 때 열을 삭제할 수 있도록 하나 이상의 열을 사용되지 않았음으로 표시합니다. 이 기능은 Oracle8i에서 사용할 수 있습니다. 이 절을 지정한다고 해서 실제로 테이블의 각 행에서 대상 열이 제거되지는 않습니다. 즉 이 열이 사용하는 디스크 공간은 복원되지 않습니다. 따라서 응답 시간은 DROP 절 실행에 소요되는 시간보다 빠릅니다. 사용되지 않은 열은 테이블의 행에 열 데이터가 남아 있는 경우에도 삭제된 것으로 처리되며 사용되지 않았다고 표시된 열은 액세스할 수 없습니다. “SELECT *” 질의는 사용되지 않은 열의 데이터를 검색하지 않습니다. 또한 사용되지 않았다고 표시된 열의 이름 및 유형은 DESCRIBE 문 실행 중에 표시되지 않으며 사용되지 않은 열과 동일한 이름을 가진 새 열을 테이블에 추가할 수 있습니다.

DROP UNUSED COLUMNS 옵션

DROP UNUSED COLUMNS는 테이블에서 현재 사용되지 않았다고 표시된 모든 열을 제거합니다. 테이블의 사용되지 않은 열에서 여분의 디스크 공간을 회수할 경우 이 명령문을 사용하면 됩니다. 테이블에 사용되지 않은 열이 없으면 오류 발생 없이 명령문이 반환됩니다.  

SQL> ALTER TABLE  dept30

  2  SET   UNUSED (ename);

Table altered.

SQL> ALTER TABLE  dept30

  2  DROP  UNUSED COLUMNS;

Table altered.

 

테이블 삭제

DROP TABLE 문장은 Oracle8 테이블의 정의를 삭제합니다. 테이블을 삭제할 때 데이터베이스는 테이블에 있는 모든 데이터와 그와 연관된 모든 인덱스를 상실합니다.

구문형식

DROP TABLE table;

여기서: table                   테이블의 이름입니다.

지침서

일단 실행된 DROP TABLE 문장은 복구할 수 없습니다. 오라클 서버는 DROP TABLE 문장을 실행할 때 삭제에 대한 질문을 하지 않습니다. 여러분이 해당 테이블을 소유하거나 high-level 권한을 가지고 있다면, 테이블은 즉시 삭제될 것입니다. 모든 DDL 문장이 커밋될 것이므로 트랜잭션을 영구적으로 만듭니다.

테이블 이름 변경

추가적인 DDL 문장은 테이블, 뷰, 시퀀스 또는 동의어에 사용되는 RENAME 문장을 포함합니다.

구문형식

RENAME    old_name  TO  new_name;

여기서:     old_name                    예전의 테이블 이름, 뷰, 시퀀스 또는 동의어입니다.
                new_name                  테이블, 뷰, 시퀀스 또는 동의어의 새로운 이름입니다.

여러분은 이름 변경하는 객체의 소유자이어야 합니다.

 

테이블 삭제

테이블로부터 모든 행을 삭제하거나 해당 테이블의 저장 공간을 해제하기 위한 다른 DDL 문장은 TRUNCATE TABLE 문장입니다. TRUNCATE TABLE 문장을 사용할 때 삭제한 행을 롤백할 수 없습니다.

구문형식

 TRUNCATE  TABLE   table;

여기서: table                   테이블의 이름입니다.

여러분은 테이블의 소유자이거나 테이블을 삭제할 수 있는 DROP ANY TABLE 시스템 권한을 가져야 합니다.

DELETE 문장은 테이블의 모든 행을 삭제할 수 있지만, 저장 공간을 해제할 수는 없습니다.

 

테이블에 주석문 추가

COMMENT 문장을 사용하여 열, 테이블, 뷰 또는 스냅샷에 대하여 2000 byte까지 주석문을 추가할 수 있습니다. 주석문은 데이터 사전에 저장되며 COMMENTS 열에서 다음의 데이터 사전중의 하나로 볼 수 있습니다.

구문형식

COMMENT ON TABLE table | COLUMN table.column
        IS 'text';

여기서:  table                  테이블의 이름입니다.           
            column               테이블에 있는 열의 이름입니다.   
            text                    주석 문장입니다.

공백 스트링('')을 설정하여 데이터베이스로부터의 주석문을 삭제할 수 있습니다.

 SQL> COMMENT ON TABLE  emp IS ' ';

 EMPLOYEE table의 Primary KEY employee_pk와 EMPLOYEE table의 Primary Key와 관련된 constraint를 모두 삭제할 경우 적당한 것들은?
A. ALTER TABLE employee drop primary key cascade;
B. ALTER TABLE employee drop constraint employee_pk cascade;
C. ALTER TABLE employee delete primary key cascade;
D. MODIFYTABLE employee drop constraint employee_pk cascade;
E. ALTER TABLE employee drop primary key employee_pk constraint;
F. ALTER TABLE employee drop primary key;
답

    X 정답:AB


  1. 다음 QUERY의 결과는?
    SELECT object_name FROM all_objects WHERE object_type = 'TABLE';

    A. 실행하는 사람이 소유한 모든 table명들
    B. 실행하는 사람이 접근 가능한 모든 object명들
    C. 실행하는 사람이 소유한 모든 object명들
    D. 실행하는 사람이 접근 가능한 모든 table명들
    답
    X 정답:D


  2. 다음 subquery를 사용하는 문장 중 문법에 맞지 않게 작성된 것은?
    A. SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'Smith');
    B. DELETE emp WHERE ename = (SELECT ename FROM emp WHERE empno = 200);
    C. INSERT INTO emp AS SELECT * FROM employee;
    D. CREATE TABLE emp2 AS SELECT * FROM emp;
    답
    X 정답:C


  3. 다음 문장에서 error가 발생하는 행은?(두 가지)
    CREATE TABLE 1998_orders
    (id NUMBER(7),
    customer_id NUMBER(7),
    total NUMBER(11, 2),
    filled CHAR(1)
    CONSTRAINT ord_filled_ck CHECK (filled IN ('Y', 'N')),
    CONSTRAINT ord_id_pk PRIMARY KEY(id, customer_id),
    CONSTRAINT ord_total_nn NOT NULL(total));

    A. CREATE TABLE 1998_orders
    B. total NUMBER(11,2),
    C. CONSTRAINT ord_filled_ck CHECK (filled IN ('Y', 'N')),
    D. CONSTRAINT ord_id_pk PRIMARY KEY(id, customer_id),
    E. CONSTRAINT ord_total_nn NOT NULL(total));
    답
    X 정답:AE


  4. 다음 문장이 데이터베이스에 대해 실행되었다.
    COMMENT ON TABLE emp IS 'Employee Table'.
    이 명령에 의해서 생성된 comment를 확인하기 위한 방법은?
    A. describe 명령을 사용하여.
    B. Select * from emp 명령을 수행하여.
    C. ALL_COMMENTS로부터
    D. ALL_TAB_COMMENTS로부터.
    답
    X 정답:D


  5. 다음 문장이 하는 일은?
    ALTER TABLE s_emp
    MODIFY (title VARCHAR2(20));

    A. s_emp table에 title column을 추가한다.
    B. s_emp table에서 title column의 datatype을 변경한다.
    C. s_emp table에 title column의 제약조건을 삭제한다.
    D. s_emp table에 title column 의 제약조건을 추가한다.
    답
    X 정답:B


  6. 사용할수 없는 DATA 선언은 ?
    A. CHAR(2560)
    B. VARCHAR2(30)
    C. LONG
    D. DATE
    E. NUMBER
    답
    X 정답:A