Neo's Programming

뷰는 하나의 가상 테이블이다. 실제 데이터가 저장되는 것이 아니라 이를 통하여 데이터를 관리하면서 간단한 쿼리 작성만으로 원하는 결과를 얻을 수가 있다.

다음은 뷰의 제약 조건이다.

1. 테이블 상 NOT NULL로 만든 컬럼들이 뷰에 포함되어야한다.

2. ROWID, ROWNUM, NEXTVAL, CURRVAL등 같은 가상에 대한 참조를 한다면 INSERT 작업이 불가능하다.

3. WITH READ ONLY 옵션을 설정한 뷰에도 데이터 갱신이란 없다.

4. WITH CHECK OPTION을 설정한 뷰의 경우 조건에 해당하는 경우만 INSERT, DELETE, UPDATE가 가능하다.

다음은 뷰 생성 예제이다.

CREATE OR REPLACE VIEW v_view AS

SELECT A.ID, B.NAME

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID

WITH READ ONLY를 살펴보면 SELECT만 가능한 뷰를 생성한다.

CREATE OR REPLACE VIEW v_view AS

SELECT ID, NAME

FROM t_table

WHERE VALUE = 10

WITH READ ONLY

하지만 여기에서 WITH CHECK ONLY를 적용을 한다면 VALUE = 10에 의한 조건에 의해 INSERT, DELETE, UPDATE가 가능하다.

VIEW에 대한 정보는 USER_VIEW를 조회하면 확인이 가능하다.

인덱스는 테이블이나 클러스트 상 쓰여지는 것으로 원하는 레코드를 빠르게 찾기 위한 데이터 주고이다.

이는 조건절이나 조인절에서 주로 자주  쓰이는 것이 대상이 적합하며 테이블 규모가 작거나 자주 바뀌는 경우에는 적합하지 않다.

인덱스의 종류는 다음과 같다.

1. BITMAP INDEX - 적은 개수의 특정한 값이 있는 경우 적합하다. 이는 인덱스 구조가 B-TREE 구조임을 감안 할 때 테이블의 크기가 크거나 변경 사항이 잘 없는 경우와 이를 사용하지 않을 경우 적합하다.

CREATE BITMAP INDEX idx_no ON t_table(NO);

2. UNIQUE INDEX - 컬럼의 중복을 포함하지 않고 PK와 UNIQUE 조건시 생성된다.

CREATE UNIQUE INDEX idx_no ON t_table(NO);

3. NON-UNIQUE INDEX - 컬럼의 중복을 허락하는 데이터를 가질 수 있다.

CREATE INDEX idx_no ON t_table(NO);

4. 결합 인덱스 - 여러개의 컬럼에 생성이 가능하며 총 16개까지 가능하다.

CREATE UNIQUE INDEX idx_no ON t_table(NO,NAME,ID);

인덱스의 삭제는 DROP을 통하여 하며 데이터사전 정보를 확인하려면 USER_INDEXES를 조회하면 확인이 가능하다.

둘다 누적에 대한 통계에 이용하면 편리한 내용이다.

ROLLUP의 경우 각 해당하는 분야에 대하여 데이터가 있다면 누적이 되서 그 기준에 대하여 통계를 나타낸다.

다음은 그 예제이다.

SELECT A.NAME, A.TITLE, SUM(B.QTY)

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID

GROUP BY ROLLUP (A.NAME, A.TITLE)

위 쿼리에 대한 결과는 대략 아래와 같다.

A.NAME        A.TITLE      QTY

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

JIM              A                     3

JIM              B                     2

JIM                                     5

BOBBY        C                     1

BOBBY        A                     2

BOBBY                               3

                                         8

하지만 각기 TITLE별의 총 합을 보려면 TITLE에 대한 것만 또다시 SELECT 하여 ROLLUP 한 것을 UNION 키셔 줘야한다. 즉 두번의 ROLLUP이 들어간다는 것이다.

하지만 큐브를 사용하면 아래와 같은 결과를 가질 수가 있다.

SELECT A.NAME, A.TITLE, SUM(B.QTY)

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID

GROUP BY CUBE (A.NAME, A.TITLE)

A.NAME        A.TITLE      QTY

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

JIM              A                     3

JIM              B                     2

JIM                                     5

BOBBY        C                     1

BOBBY        A                     2

BOBBY                               3

                  A                     5

                  B                     2

                  C                     1 

조인은 둘 이상의 테이블간 연계하여 데이터를 검색한다.

보통 PK와 FK를 이용하는데 조인 조건을 정의 하지않거나 잘못된 경우, 첫 테이블이 다음 테이블의 모든 행과 조인 될 경우 카테션 곱(Cartesian Product)이 일어나 상호 테이블간의 모든 데이터가 반환 되는 현상이 일어난다.

조인의 종류는 다음과 같다.

1. Equi Join - 조건절에 = 에 의해 일어난다.

SELECT A.DATA, B.DATA

FROM t_table A, t_table_sec B

WHERE A.ID = B.ID;

2. Non-Equi Join - = 외의 조인 조건을 가진는 경우

SELECT A.DATA, B.DATA

FROM t_table A, t_table_sec B

WHERE A.VALUE BETWEEN B.VALUE AND B.NEXT_VALUE;

3. Self-Join - 자기 스스로 조인 일어나는 것으로 두개의 테이블을 조인 하는 것 처럼 보이는 경우

SELECT A.DATA, B.DATA

FROM t_table A, t_table B

WHERE A.ID = B.ID;

4. Outer Join - LEFT, RIGHT, FULL OUTER JOIN이 있으며 두개의 테이블의 컬럼들에서 공통되는 값이 없을 경우 종종 이용한다. (+) 를 이용하여 사용하는데 값이 없는 측에 붙여 사용한다.

SELECT A.DATA, B.DATA

FROM t_table A, t_table_sec B

WHERE A.ID(+) = B.ID;

위와 같이 작성을 했으나 한쪽에 데이터가 맞지 않은 경우가 있다 가령 NULL 값이 들어간 경우이다.

결과가

A.DATA     B.DATA

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

1              1

               2

나와야 하는 경우 1에 대한 행만 나왔다면 다른 조건을 걸어 (+) 시켜 줘야한다.

4.1 LEFT OUTER JOIN - 오른쪽에 조인시킬 컬럼 값이 없을 경우

SELECT A.DATA, B.DATA

FROM t_table A LEFT OUTER JOIN t_table_sec B

WHERE A.ID = B.ID;

4.2 RIGHT OUTER JOIN - 쪽에 조인시킬 컬럼 값이 없을 경우

SELECT A.DATA, B.DATA

FROM t_table A RIGHT OUTER JOIN t_table_sec B

WHERE A.ID = B.ID;

4.3 FULL OUTER JOIN - 양측에 OUTER JOIN을 거는 경우

SELECT A.DATA, B.DATA

FROM t_table A LEFT FULL JOIN t_table_sec B

WHERE A.ID = B.ID;

5. INNER JOIN - 일반 조인에서 콤마 대신 INNER JOIN을 넣고 WHERE 대신 ON 넣는 방법

SELECT A.DATA, B.DATA

FROM t_table A INNER JOIN t_table_sec B

ON A.ID = B.ID;

6. NATURAL JOIN - Equi Join과 동일한 형태로 동일한 이름을 지니는 컬럼은 모두 조인이 된다. 또한 Alias는 사용하지 못하며 동일한 컬럼이 두개 이상일 경우 JOIN~USING을 이용하여 제어가 가능하다.

SELECT DATA, NAME FROM t_table NATURAL JOIN t_table_sec

7. JOIN~USING - NATURAL JOIN에 나타나는 문제를 제어하기 위한 것으로 해당하는 컬럼을 선택해서 조인이 가능하다. 또한 USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 난다.

SELECT A.DATA, B.NAME, ID FROM t_table A JOIN t_table_sec B USING (ID)

8. ON - 조인 조건을 지정하며 논리 연산과 서브쿼리 지정이 가능하다.

SELECT A.DATA, B.NAME

FROM t_table A JOIN t_table_sec B ON (A.DATA = B.DATA)

                       JOIN t_table_third C ON (C.ID = A.ID)

테이블은 데이터베이스 상에 가장 기본적인 데이터 저장 단위이다.

이들은 컬럼과 레코드로 구성이 되며 두개 이상의 엔티티간의 관계 또한 표기가 가능하다.

테이블 생성하는 방법은 다음과 같다.

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id PRIMARY KEY,

NAME VARCHAR(100)

);

위에 보면 CONSTRAINT를 이용하여 PRIMARY KEY로 제약 조건을 걸어 놓은 것을 볼 수 있다.

또한 로그인한 계정으로 만든 테이블 내역들을 보려면 USER_TABLES 를 조회하면 확인 할 수가 있다.

위에서 제약 조건이란 무결성 원칙을 위한 규칙 정용이라 생각을 하면 되며 이들은 데이터 사전에 모두 저장이된다.

다음은 그 예들이다.

1. NOT NULL

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id NOT NULL,

NAME VARCHAR(100)

);

2. CHECK - 해당 컬럼 값 범위 제한

ALTER TABLE t_table

ADD CONTRAINT table_ck CHECK (NO>=1 AND NO<=100); --추가

DROP CONTRAINT table_ck; --삭제

3. DEFAULT 지정

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id NOT NULL,

DATE DEFAULT SYSDATE

);

4. UNIQUE - 유일한 데이터, 자동 인덱스 생성

ALTER TABLE t_table

ADD CONSTRAINT uk_ID UNIQUE(ID); -- 추가

DROP CONTRAINT uk_ID; -- 삭제

5. PRIMARY KEY - 유일한 기본키, UNIQUE와 NOT NULL의 특성을 함께 가짐. 참조 무결성을 위해 이용함. 자동 인덱스 생성

CREATE TABLE t_table

(

ID NUMBER CONSTRAINT table_id NOT NULL,

NAME VARCHAR(100)

);

ARTER TABLE t_table ADD CONTRAINT pk_name PRIMARY KEY(NAME);

6. FOREIGN KEY - 기본키들을 참조하는 컬럼이나 컬럼들의 집합을 말하며 반드시 참조하는 PK와 데이터형이 일치해야한다.

ARTER TABLE t_table ADD CONTRAINT fk_ID

FOREIGN KEY(ID) REFERENCES t_table_sec(ID);

정보처리기사나 각종 서적에 보면 DDL, DML, DCL이라는 것을 볼 수가 있다.

여기에선 그 정의를 알아본다. 총 3가지로 나뉠 수가 있다.

DDL(Data Definition Language) - 데이터베이스 객체인 테이블이나 뷰, 인덱스, 시퀀스 등의 구조를 정의 하며 CREATE, DROP, ALTER를 이용한다.

DML(Data Manipulation Language) - 데이터의 삽입, 삭제, 업데이트 등을 처리

DCL(Data Control Language) - 데이터베이스 사용자 권한 제어한다. GRANT로 권한을 부여하고, REVOKE로 취소한다.

트리거는 INSERT, UPDATE, DELETE가 실행 될 경우 TABLE에 대해 묵시적으로 실행되는 프로시저이다.

이는 테이블과 별도로 데이터 베이스에 저장이 되고 뷰에 대해서는 정의가 불가능하다.

또한 행과 문장 트리거 두가지로 이루어지는데,

행 트리거는 컬럼 각각의 행의 데이터 변화가 생길 때 마다 실행되고 행의 실제 값을 제어하며

문장 트리거는 단 한번만 실행되면서 컬럼의 데이터 행을 제어가 가능하다.

트리거의 문법은 다음과 같은 형태이다.

CREATE OR REPLACE TRIGGER test_trigger

BEFORE|AFTER

trigger_event ON table

[FOR EACH ROW]

[WHEN (condition)]

위에서 보면 BEFORE, AFTER, trigger_event, FOR EACH ROW를 볼 수 있다.

이의 설명은 다음과 같다. 

다음은 사용 방법이다.

CREATE OR REPLACE TRIGGER test_trigger

BEFORE

UPDATE ON TABLE

FOR EACH ROW

DBMS_OUTPUT.PUT_LINE(old.NO);

DBMS_OUTPUT.PUT_LINE(new.NO);

BEGIN

END;

위와 같이 처리하고 업데이트 문을 실행시키면 변경 전과 변경 후의 데이터가 나타 날 것이다.

또한 OR 키워드를 이용하여 UPDATE OR INSERT OR DELETE 를 이용하면 각기 문장이 일어 났을 경우에도 그 처리 된 내용을 확인 할 수 있을 것이다.

패키지는 특정 주제에 맞게 작성된 프로시저와 함수들의 집합체이다.

이는 크게 두가지 부분으로 나누어 진다. 바로 선언과 본문이다.

먼저 선언을 보자.

CREATE OR REPLACE PACKAGE INFO AS

PROCEDURE copy_info;

PROCEDURE emp_info;

END INFO;

선언부에서는 본문 내에 정의되는 내용들의 선언이다. 말 그대로 변수들의 집합과 같은 느낌을 받는다.

위에서는 단지 프로시저만 선언을 했지만, 변수, 커서, 예외, 프로시저, 함수 들을 선언 할 수 있다. 여기에 선언 된 것들은 모두 PUBLIC 한정자라 생각하면 된다.

본문은 다음과 같은 형식이다.

CREATE OR REPLACE PACKAGE BODY INFO AS

PROCEDURE copy_info

IS

BEGIN

END;

PROCEDURE emp_info

IS

BEGIN

END;

위와 같은 형식으로 정의를 하고 난 다음 실행은 EXEC INFO.프로시저명 을 하면 실행이 된다.

SQLCODE와 SQLERRM을 이용하면 EXCEPTIN에서 OTHERS에 걸리는 실제 오류 코드와 설명을 확인을 할 수 있다.

SQLCODE의 경우 성공적인 경우는 오류번호 0, 아닐 경우 해당 오류 코드를 반환한다.

SQLERRM의 오류 번호는 다음과 같다. 

EXCEPTION을 발생 시켰을 경우 예를 들면 다음과 같다.

SQLCODE : -1422
SQLERRM : ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다

예외는 다들 알고 있는 개념일 것이다.

어떠한 내용이 동작을 하다가 예상치 못한 경우들이 생겼을 때 예외처리를 해 준다.

JAVA나 C#에서 보면 TRY-CATCH 구문이다. 

다음은 오류의 종류이다.

미리 정의된 예외 종류는 http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm 에 보면 나와 있다.

몇가지 예를 들어서 보면 다음과 같다. 

정의 되지 않은 경우는 먼저 예외의 이름을 선언하며, PRAAGMA EXCEPTION_INIT을 이용하여 오류 번호를 결합한 다음 EXCEPTION 부분에서 처리를 해야한다.

다음은 그 예이다.

CREATE OR REPLACE PROCEDURE TEST

IS

test_exception EXCEPTION;

PRAGMA EXCEPTION_INIT(test_exception,오류 번호);

BEGIN

EXCEPTION

WHEN test_exception THEN 처리 내용

END;

위에서 처리 번호는 오라클 서버 에러에 대해 어느정도 참조하는 것이 좋다.