드디어 DB(오라클 SQL) 입문 강좌 마지막 시간입니다.
‘입문’의 범위가 어디까지인지는 어디까지나 주관적인 판단에 의한 것입니다.

본 입문 과정에서 생략된 것은,
SQL DDL 중에서 CREATE TABLESPACE, ALTER TABLESAPCE, CREATE USER, ALTER USER, DROP USER, GRANT, REVOKE 등 주로 DBA가 담당하는 것들과,
DML 중에서는 다른 DB의 레코드를 검색하는 SELECT(UPDATE, INSERT,DELETE) ∼ FROM 테이블@DB 형식의 문장 등이 있습니다.
그 외에 SUBSELECT에 대해서도 충분한 실습이 없었고, 함수에 대해서도 기본적인 것만 다뤘습니다.
오늘 다루는 VIEW와 관련해서도 CREATE VIEW 외에 DROP, SELECT, UPDATE, INSERT INTO, DELETE 등에 대해서는 다루지 않았습니다.
백업 및 복구와 관련된 내용, SQL 편집 명령 등에 대해서도 다루지 않았습니다.
만약 기회가 된다면 위의 내용들로 오라클(SQL) 중급 강좌를 한번 진행했으면 하는 생각입니다.

그럼 DB 입문 마지막 시간, CREATE VIEW 학습을 시작합니다.

VIEW는 오라클에서 제공하는 가상의 테이블(Virtual Table)입니다. 이미 우린 여러 실습을 통해 실제 테이블을 만들고 검색하는 일들을 해봤습니다. VIEW는 실제 테이블을 바탕으로 만들어진 가상의 테이블이며, 검색 등 기타 사용법은 실제 테이블과 거의 유사합니다.
따라서 이번 시간에는 VIEW를 만드는 방법을 주로 다루기로 하고, 기타 데이터 검색, 삭제, 변경 등은 실제 테이블로 작업하는 것과 거의 동일하므로 따로 설명하지는 않겠습니다.

1. VIEW 만들기
   CREATE  OR  REPLACE VIEW view_name
   AS query
   [WITH CHECK OPTION]
   [WITH READ ONLY];

   참고로 만들어진 VIEW를 삭제하려면,
   DROP VIEW view_name; 라고 하면 됩니다.

   뷰를 만들 때 CREATE OR RELPACE VIEW 대신 그냥 CREATE VIEW만 사용해도 됩니다. 그러나 그냥 CREATE VIEW를 통해 만들어진 뷰의 구조를 바꾸려면 뷰를 삭제하고 다시 만들어야 되는 반면, CREATE OR REPLACE VIEW는 새로운 뷰를 만들거나 기존의 뷰를 통해 새로운 구조의 뷰를 만들 수도 있습니다. 그래서 대부분 뷰를 만들 때는 CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용하는 편입니다.

   • VIEW에는 VIEW를 생성하는 SELECT 문만 저장됩니다. 즉 실제로 테이블은 존재하지 않으며, VIEW를 SELECT 문으로 검색하는 순간 실제 테이블을 참조하여 보여줍니다.
     
   • VIEW의 query문에는 ORDER BY 절을 사용할 수 없습니다.
     
   • WITH CHECK OPTION을 사용하면, 해당 VIEW를 통해서 볼 수 있는 범위 내에서만 UPDATE 또는 INSERT가 가능하합니다.
     예를 들어,

     CREATE OR REPLACE VIEW V_EMP_SKILL
             AS
             SELECT *
             FROM EMP_SKILL
             WHERE AVAILABLE = ‘YES’
             WITH CHECK OPTION;

     위와 같이 WITH CHECK OPTION을 사용하여 뷰를 만들면, AVAILABLE 컬럼이 ‘YES’가 아닌 데이터는 VIEW를 통해 입력할 수 없습니다. 즉, 아래와 같이 입력하는 것은 ‘불가능’하다는 것입니다.
     

     INSERT INTO V_EMP_SKILL
     VALUES(‘10002′, ‘C101′, ‘01/11/02′,‘NO’);

   • WITH READ ONLY을 사용하면 해당 VIEW를 통해서는 SELECT만 가능하며 INSERT/UPDATE/DELETE를 할 수 없게 됩니다. 만약 이것을 생략한다면, 뷰를 사용하여 Create, Update, Delete 등 모두 가능합니다.

2. VIEW 만들기 실습

   EMP_SKILL 테이블과 SKILL 테이블의 EMP_NUM, SKILL_NO, SKILL_NAME 컬럼을 볼 수 있는 뷰(V_EMP_SKILL2) 만들기
   

   CREATE OR REPLACE VIEW V_EMP_SKILL2
           AS
           SELECT A.EMP_NUM, A.SKILL_NO, B.SKILL_NAME
           FROM EMP_SKILL A, SKILL B
           WHERE A.SKILL_NO = B.SKILL_NO;

3. VIEW 검색 실습

   위에서 만든 V_EMP_SKILL2 뷰의 내용을 검색하려면 일반 테이블을 검색하듯이 다음과 같이 하면 됩니다.

   SELECT * FROM V_EMP_SKILL2;

이상으로 DB 입문 강좌를 모두 마칩니다.

지금까지 동주 아빠 손병목이었습니다.

테이블을 만들고 변경하는 실습을 해 보았습니다.
이번 시간에는 테이블을 삭제하는 연습을 해보겠습니다.
테이블을 삭제하는 것은 테이블을 생성하거나 변경하는 것에 비해 정말 간단합니다.

테이블을 삭제할 때는 DROP 명령을 사용합니다.

DROP TABLE 테이블명;

정말 간단하죠?
만약 TEST 테이블을 삭제하려면?
DROP TABLE TEST;
라고만 하면 됩니다.

39강에서 DELETE 명령을 배운 적이 있습니다. DELETE가 테이블의 ‘데이터’를 삭제하는 것이라면, DROP 명령은 테이블 자체를 삭제해 버립니다. 즉 테이블의 데이터를 포함한 구조(Structure)를 삭제합니다.

이 외에도 DELETE 명령은 실수했을 경우 되돌릴 수 있는 반면, DROP 명령은 되돌릴 수 없습니다.(참고로 DELETE 명령은 SQL DML 명령이고, DROP은 SQL DDL 명령입니다.)
40강에서 트랜잭션을 설명할 때 DDL 명령을 실행하면 자동으로 COMMIT 명령을 준 것과 동일한 결과가 나타난다고 했었죠. 바로 DROP 명령도 DDL 명령의 일종이기 때문에 DROP 명령을 내리는 순간 트랜잭션의 완료를 의미하는 COMMIT 명령이 동시에 실행되는 것입니다. 그러니, 조심 또 조심해서 DROP 명령을 사용하라는 뜻입니다.
만약 이것이 불안하다면 미리 백업을 해두면 되죠.(그러나 아쉽게도 본 강의는 백업과 복구 관련 내용까지는 설명하지 않습니다. 다음에 기회가 되면 중급 과정을 하나 더 만들어 설명하기로 하죠…)

오늘은 정말 간단합니다. 이상 DROP 설명은 끝~

이상 동주 아빠 손병목이었습니다.

지난 시간에 CREATE를 이용해 테이블을 만드는 DDL을 실습했습니다.
이번 시간에는 만들어 놓은 테이블을 변경하는 명령인 ALTER에 대해 실습하겠습니다.(그리고 다음 시간에는 삭제 명령인 DROP을 실습할 예정입니다.)

1. ALTER는,
   • 기존 테이블에 새로운 컬럼을 추가하거나 변경(컬럼의 데이터형 변경 등)할 때 ALTER 명령을 사용합니다.

   • ALTER 명령은 Constraint를 추가하거나 삭제할 때도 사용할 수 있습니다.
   • 그러나 CREATE TABLE 문으로 만든 컬럼은 삭제할 수 없습니다.
2. ALTER 문은 다음과 같이 사용됩니다.

   ALTER TABLE table_name
           ADD ( column_name datatype )
           MODIFY( column_name datatype )
           ADD  CONSTRAINT constraint_name  expr
           DROP  CONSTRAINT  constraint_name;

3. 실습 1
   기존 C_EMP 테이블에 AGE 컬럼을 추가하고 기존의 EMP_NAME 컬럼의 길이를 20으로 줄이기.(단, AGE는 0보다 커야하고, 이 조건의 이름을 CK_AGE라고 한다.)

   ALTER TABLE  C_EMP
           ADD (AGE NUMBER(3))
           MODIFY (EMP_NAME VARCHAR(20))
           ADD CONSTRAINT CK_AGE CHECK(AGE > 0);

4. 실습 2
   C_EMP 테이블에서 기존의 FK_DEPTNO라는 CONSTRAINT를 삭제하기

   ALTER TABLE C_EMP
           DROP CONSTRAINT  FK_DEPTNO;

5. 실습 3
   C_EMP 테이블에서 DEPT_NO 컬럼에 Foreign Key Constraint를 추가하기(단, Foreign Key Constraint의 이름은 FK_EMP_DEPTNO로 한다.)

   ALTER TABLE C_EMP
           ADD CONSTRAINT  FK_EMP_DEPTNO
           FOREIGN KEY(DEPT_NO) REFERENCES DEPT;

문법적으로 어려운 것은 전혀 없는데 실제 실무에서 사용하려고 하면 잘 기억나지 않을 때가 있습니다.
모든 것이 그러하듯 자주 사용해야 기억할 수 있겠죠.

이상 동주 아빠 손병목이었습니다.

지난 시간에 이어 바로 실습으로 넘어갑니다.

아래와 같은 구조의 C_DEPT 테이블을 만들려고 합니다.
지난 시간에 다룬 CREATE 문의 형식을 참조해서 한번 만들어 봅시다.

샘플 데이터는 38강에서 다룬 INSERT 문을 참조해서 직접 해보시고, 이번 시간에는 CREATE 문만 함께 하기로 하죠.

아래 빈 곳을 마우스로 드래그하면 위와 같은 테이블을 만드는 CREATE 문이 있습니다. 먼저 곰곰이 생각해 보시고 확인해 보시기 바랍니다.

CREATE TABLE   C_DEPT
(  DEPT_NO     NUMBER(2)      PRIMARY KEY,
   DEPT_NAME   VARCHAR2(30)   NOT NULL );

마찬가지로 아래와 같은 구조의 테이블을 만들려고 합니다.

잘 생각해 보시고 아래 빈곳을 드래그해서 확인하시기 바랍니다.

CREATE   TABLE C_EMP
(EMP_NUM CHAR(5),
EMP_NAME VARCHAR2(30)    NOT NULL,
HIRE_DATE DATE             DEFAULT SYSDATE NOT NULL,
RETIRE_DATE  DATE,
DEPT_NO      NUMBER(2),
CONSTRAINT   PK_EMPNUM       PRIMARY KEY(EMP_NUM),
CONSTRAINT   FK_DEPTNO       FOREIGN KEY(DEPT_NO)
                REFERENCES C_DEPT(DEPT_NO) );

위에서 실습한 두 가지 방법은 약간의 차이가 있습니다.
먼저 실습한 C_DEPT의 경우, 컬럼별로 CONSTRAINT를 지정하였습니다. 이럴 경우 별도로 CONSTRAINT 절을 명시하지 않아도 됩니다.
반면 C_EMP 테이블은 별도의 CONSTRAINT 절을 사용하여 CONSTRAINT를 지정하였습니다.
어느 것이 더 편한 방법인지는 사용자의 몫입니다.

이상 동주 아빠 손병목이었습니다.

이번 시간부터는 데이터 정의어에 대해 알아보겠습니다.
데이터 정의어(DDL)는 테이블을 생성하는 CREATE, 테이블의 구조를 변경하는 ALTER, 테이블을 삭제하는 DROP 명령 등이 있습니다.
두 시간에 걸쳐 CREATE에 대해 살펴보겠습니다.

CREATE 명령은 구체적으로 설명한 적은 없지만 지금까지 꽤 많이 실습을 해왔습니다.
예제 테이블을 만들기 위해 CREATE 문을 사용했습니다. 다만 구체적으로 설명만 안했을 뿐이죠.

CREATE 문은 어떻게 구성되어 있는지 한번 살펴볼까요?

1. CREATE 문 형식

   CREATE TABLE table_name
   (column_one data_type [constraint, DEFAULT expression],
   column_two data_type [constraint, DEFAULT expression],
   …,
   CONSTRAINT constraint_name UNIQUE(column),
   CONSTRAINT constraint_name PRIMARY KEY(column),
   CONSTRAINT constraint_name FOREIGN KEY(column1)
                 REFERENCES table_name(column2),
   CONSTRAINT constraint_name CHECK(column expr),
   );

   우선 눈에 띄는 것들이 몇 개 보이죠?
   CONSTRAINT, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, CHECK 등

   먼저 CONSTRAINT는 데이터의 무결성(Integrity)을 유지하기 위하여 사용자가 지정하는 제약 조건을 의미합니다.
   무결성에 대해서는 이미 2, 3, 12, 14강에서 두루 언급되었던 내용이니 여기서는 따로 설명하지 않겠습니다.

   CONSTRAINT를 정의하는 방법은 두 가지가 있습니다.
   컬럼별로 제약 조건을 지정하는 것이 있고, Composite 컬럼에 대해 제약 조건을 정의할 수 있습니다.(Composite 컬럼에 대해서는 제13강에서 설명하였습니다.)

   • 컬럼별로 제약 조건을 지정할 때는 어떠한 제약 조건도 사용 가능합니다.

   • 그러나 Composite 컬럼에 대해서는 NOT NULL을 사용할 수 없습니다.

   그럼, 구체적으로 제약 조건(CONSTRAINT)에는 어떤 것들이 있을까요?
   이미 많이 보아온 것들이 그리 낯설지는 않을 것입니다.

2. CONSTRAINT의 종류

   이미 이 강좌 앞 부분에서 많이 언급된 것들이라 의미는 대부분 이해하셨을 거라 믿고 주요한 몇 가지만 짚고 넘어가겠습니다.

   • NOT NULL
     NOT NULL Constraint가 지정된 컬럼은 NULL 값을 가질 수 없습니다.
     그리고 컬럼 단위로 Constraint를 지정할 때만 사용할 수 있습니다.(Composite 컬럼에는 사용할 수 없습니다.)

   • UNIQUE
     예전에 ERD 설명하면서 언급한 적이 있죠? Unique는 말 그대로 중복되지 않아야 한다는 속성입니다.
     단일 컬럼이나 Composite 컬럼에 Unique 속성을 부여할 때 사용하고, Unique로 지정되면 Unique Index가 자동으로 생성됩니다.(인덱스는 나중에 다룹니다.)
     UNIQUE 컬럼에는 NULL 값이 반드시 없어야될 것 같지만, Composite 컬럼 중의 일부 컬럼에는 NULL 값이 들어갈 수 있는 경우도 있습니다.

   • PRIMARY KEY
     테이블에서 PRIMARY KEY는 단 하나만 있어야 합니다.
     그리고 당연히 NULL 값은  허용하지 않습니다.
     UNIQUE로 지정했을 때와 마찬가지로 Unique Index가 자동으로 생성됩니다.

   • FOREIGN KEY
     Primary Key, Foreign Key에 대해서는 제13강 관계형 데이터베이스(2)-Table Keys에서 자세히 다루고 있습니다.
     자식 테이블(Child Table)에서 정의하는 Constraint이고, 부모 테이블(Parent Table)의 값과 일치하거나 NULL 값이어야 합니다.
     FOREIGN KEY로 참조하고자 하는 테이블이 먼저 생성되어 있어야 합니다.
     ON DELETE CASCADE Option을 주면 Cascade Delete RI를 적용할 수 있습니다.(Cascade Delete RI에 대한 설명은 제14강 관계형 데이터베이스(3)-참조무결성에서 자세히 설명하고 있습니다.)

   • CHECK
     각 행의 컬럼이 만족해야 하는 구체적인 조건을 정의하는 곳입니다.

   • DEFAULT expression
     테이블에 데이터를 입력할 때 특정 값을 지정하지 않은 경우 기본적으로 입력되는 값을 지정합니다. 예를 들어 현재의 날짜를 자동으로 삽입하려면 SYSDATE를 사용할 수 있겠죠.

이번 시간은 여기까지 설명하고,
계속해서 테이블을 직접 만들어 보기로 하겠습니다.

이상 동주 아빠 손병목이었습니다.