즐거운 개발 시간

DQL이란?

DQL은 Data Query Language의 약자로 데이터 질의어이다. 데이터베이스에서 데이터를 조회하기 위해 사용되는 언어다.

SQL을 사용하여 데이터베이스에서 데이터를 검색하고 선택하는 쿼리를 작성할 수 있고, 이를 통해 데이터베이스에서 필요한 정보를 추출하고 분석할 수 있다.

DQL의 종류와 사용에 대해 알아보자

SELECT

SELECT는 데이터를 조회할 때 사용한다. SELECT를 통해 얻은 결과를 RESULT SET이라고 하며, 조회하려는 칼럼은 반드시 FROM 절에 기술한 테이블에 존재해야한다.

문법

    [문법]
        SELECT 컬럼, 컬럼, ...
          FROM 테이블명;

예시

EMPLOYEE 테이블에서 EMP_ID, EMP_NAME 조회

SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE;

위에 코드를 통해 EMPLOYEE 테이블에서 EMP_ID, EMP_NAME만을 SELECT하여 조회되고 있다.

별칭

오라클에서는 칼럼명에 별칭을 지정해줄 수 있다. 별칭 지정 시 공백, 특수문자가 포함될 경우 반드시 큰 따옴표로 감싸야한다.

별칭은 산술 연산 등으로 인해 칼럼명이 지저분해질 경우, 깔끔하게 작성하기 위해서 사용한다.

문법

        [문법]
            칼럼명 AS 별칭
            칼럼명 AS "별칭"
            칼럼명 별칭
            칼럼명 "별칭"

위에 4가지 경우 중 한 가지를 선택해서 사용할 수 있다. 하지만 주의사항은 별칭에 공백, 특수문자가 포함될 경우 반드시 큰 따옴표로 감싸야한다.

리터럴

리터럴(Literal)은 상수 값의 표현을 말하는데, 코드 상에 직접 나타나는 값을 의미한다. 정수 리터럴, 부동 소수점 리터럴, 문자 리터럴, 문자열 리터럴 등이 있다.

SELECT 절에  리터럴을 사용하면 테이블에 존재하는 데이터처럼 조회가 가능하다. 리터럴은 RESULT SET의 모든 행에 반복적으로 출력된다.

예시

별칭 미지정

SELECT
    EMP_NAME
    , SALARY
    , '단위(원)'
FROM EMPLOYEE;

별칭 지정

SELECT
    EMP_NAME
    , SALARY AS 급여
    , '단위(원)' AS "급여 단위"
FROM EMPLOYEE;

별칭을 지정하면 SELECT문에 작성한 별칭대로 조회된 것을 확인할 수 있고, 별칭을 미지정하면 SELECT문에 작성한 칼럼명이 조회된 것을 확인할 수 있다.

그리고 '단위(원)'처럼 문자열 리터럴을 SELECT를 통해 조회할 수 있고, 리터럴에도 별칭을 사용할 수 있음을 확인할 수 있다.

DISTINCT

칼럼에 포함된 중복값을 한 번씩만 표시하기 위해 사용한다. SELECT 절에 한 번만 사용 가능하며, 여러 개 칼럼을 대상으로 하면 모든 칼럼값이 중복되어야 제거한다.

예시

직급코드 조회 (중복 값 제거)

SELECT DISTINCT JOB_CODE
FROM EMPLOYEE;

23명이 포함되어 있는 EMPLOYEE 테이블에서 DISTINCT를 사용하지 않았다면 23개의 직급코드가 중복 값을 포함한 체로 출력되었을 것이다.

하지만 DISTINCT를 사용하여 J1 ~ J7까지의 7개의 값이 중복을 제외하고 출력되었다.

연결 연산자

여러 칼럼값을 하나의 칼럼인 것처럼 연결하거나, 칼럼과 리터럴을 연결할 수 있다.

예시

사번, 사원명, 급여 연결해서 조회

SELECT EMP_ID || EMP_NAME || '의 월급은 ' || SALARY || '원 입니다.' AS "사원 정보 및 급여"
FROM EMPLOYEE;

역슬래시 두개, '||' 를 이용하여 칼럼과 리터럴을 연결하여 조회할 수 있다.

WHERE절에 대해 알아보자

WHERE

조회하고자 하는 테이블에서 해당 조건에 만족하는 결과만을 조회하고자 할 때 사용한다. 조건식에는 다양한 연산자들을 사용할 수 있다.

문법

        [문법]
            SELECT 칼럼, 칼럼, ..., 칼럼
              FROM 테이블명
             WHERE 조건식;

비교 연산자

• 대소 비교
  • >, <, >=, <=
• 동등 비교
  • =
• 같지 않다
  • !=, ^=, <>

논리 연산자

• AND
• OR
• NOT

BETWEEN

WHERE절에서 사용되는 구문으로 범위에 대한 조건을 제시할 때 사용한다. 비교 대상 칼럼값이 하한 값(A) 이상이고, 상한 값(B) 이하인 경우 TRUE를 리턴한다.

문법

    [문법]
        WHERE 비교대상칼럼 BETWEEN 하한 값(A) AND 상한값(B)

LIKE

• 비교하려는 칼럼 값이 지정된 특정 패턴에 만족할 경우 TRUE를 리턴한다.
• 특정 패턴에는 '%', '_'를 와일드카드로 사용할 수 있다.
  • '%': 0글자 이상
    • ex) 비교대상칼럼 LIKE '문자%' → 비교대상칼럼 값 중에 '문자'로 시작하는 모든 행을 조회한다.
            비교대상칼럼 LIKE '%문자' → 비교대상 칼럼 값 중에 '문자'로 끝나는 모든 행을 조회한다.
            비교대상칼럼 LIKE '%문자%' → 비교대상칼럼 값 중에 '문자'가 포함되어 있는 모든 행을 조회한다.
  • '_': 1글자
    • ex) 비교대상칼럼 LIKE '_문자' → 비교대상칼럼 값 중에 '문자' 앞에 무조건 한 글자가 오는 모든 행을 조회한다.
            비교 대상 칼럼 LIKE '__문자' → 비교대상칼럼 값 중에 '문자' 앞에 무조건 두 글자가 오는 모든 행을 조회한다.

문법

        [문법]
            WHERE 비교대상칼럼 LIKE '특정패턴';

IS NULL, IS NOT NULL

칼럼 값에 NULL이 있을 경우 NULL 값 비교에 사용된다.

• IS NULL
  • 비교대상칼럼 값이 NULL인 경우 TRUE를 리턴한다.
• IS NOT NULL
  • 비교대상칼럼 값이 NULL이 아닌 경우 TRUE 리턴한다.

문법

        [문법]
            WHERE 비교대상칼럼 IS [NOT] NULL;

IN

값 목록 중에 일치하는 값이 있을 때 TRUE 리턴한다.

문법

        [문법]
            WHERE 비교대상칼럼 IN('값', '값', '값', ..., '값');

연산자 우선순위

1. 괄호(())
2. 산술 연산자
3. 연결 연산자
4. 비교 연산자
5. IS NULL, IS NOT NULL, LIKE, IN
6. BETWEEN
7. 논리 연산자 (NOT)
8. 논리 연산자 (AND)
9. 논리 연산자 (OR)

WHERE절 예시 (비교 연산자)

부서 코드가 D9와 일치하는 사원들의 모든 칼럼 정보 조회

SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

위에 코드를 통해 DEPT_CODE가 D9인 인원들의 모든 칼럼 정보가 조회된 것을 확인할 수 있다.

WHERE절 예시 (논리 연산자)

급여가 400만원 이상이면서 직급 코드가 J2인 사원의 모든 칼럼 조회

SELECT *
FROM EMPLOYEE
WHERE SALARY >= 4000000
AND JOB_CODE = 'J2';

AND 연산자를 통해 SALARY >= 4000000와 JOB_CODE = 'J2' 두 가지 조건을 모두 만족하는 인원들의 모든 칼럼 정보가 조회된 것을 확인할 수 있다.

WHERE절 예시 (BETWEEN)

급여가 350만원 이상 600만원 이하가 아닌 직원의 이름, 부서 코드, 급여 조회

SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE NOT SALARY BETWEEN 3500000 AND 6000000;

WHERE절 예시 (LIKE)

이름 중 '하'가 포함된 사원의 모든 칼럼 조회

SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';

'%하%'를 통해 이름(EMP_NAME) 중에 '하'가 포함된 인원의 모든 정보를 조회할 수 있다.

전화번호 4번째 자리가 9로 시작하는 사원의 사원명, 전화번호 조회

SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '___9%';

'___9%'를 통해 전화번호(PHONE) 4번째 자리가 9로 시작하는 인원의 정보를 조회할 수 있다. 참고로 '___9%'에서 언더바(_)는 3개이다.

WHERE절 예시 (IS NULL, IS NOT NULL)

보너스를 받지 않는 사원의 모든 칼럼 조회

SELECT *
FROM EMPLOYEE
WHERE BONUS IS NULL;

부서가 있고, 보너스를 받지 않는 사원의 모든 칼럼 조회

SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL
AND BONUS IS NULL;

위와 같은 코드들로 NULL 값에 대한 조건을 걸어줄 수 있다.

WHERE절 예시 (IN)

직급 코드가 J2, J3, J4에 해당하는 사원의 모든 칼럼 조회

SELECT *
FROM EMPLOYEE
WHERE JOB_CODE IN ('J2', 'J3', 'J4');

조회 결과 직급코드(JOB_CODE)가 'J2', 'J3', 'J4'인 인원들만 조회되는 것을 확인할 수 있다.

이밖에도 WHERE에서는 비교 연산자와 논리 연산자등을 이용하여 원하는 조건의 정보만을 조회할 수 있다.

ORDER BY

SELECT 문에서 가장 마지막에 기입하는 구문으로 실행 또한 가장 마지막에 진행된다. 정렬 기준을 작성하지 않으면 기본 값으로 ASC 또는 DESC가 적용된다. (Oracle 21c 기준 기본 값 ASC)

• ASC: 오름차순으로 정렬한다.
• DESC: 내림차순으로 정렬한다.
• NULLS FIRST: 정렬하고자 하는 칼럼 값에 NULL이 있는 경우 해당 데이터 값을 맨 앞으로 정렬한다.
• NULLS LAST: 정렬하고자 하는 칼럼 값에 NULL이 있는 경우 해당 데이터 값을 맨 뒤로 정렬한다.

문법

        [문법]
            SELECT 칼럼, 칼럼, ..., 칼럼
              FROM 테이블명
             WHERE 조건식
          ORDER BY 정렬시키고자 하는 칼럼명|별칭|칼럼 순번 [ASC|DESC] [NULLS FIRST | NULLS LAST];

예시

보너스 기준 정렬

SELECT EMP_NAME, BONUS
FROM EMPLOYEE
ORDER BY BONUS DESC NULLS FIRST;

DESC NULLS FIRST로 정렬하였기 때문에, NULL 값을 먼저 정렬하고, 그 후부터 BONUS 값에 따라서 내림차순으로 정렬되는 것을 확인할 수 있다.

추가적으로 ORDER BY는 콤마(,)를 통해 여러 정렬 기준을 적용할 수 있다. 그 때의 정렬 순서는 ORDER BY 작성 순서이다.

PROCEDURE란?

PROCEDURE란 PL/SQL 문을 저장하는 객체이다. 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있다.

프로시저는 사용자가 만들어서 사용할 수 있다는 장점이 있다. 원하는 기능을 매크로처럼 만들어 사용할 수 있어 편리하다.

특정 로직을 처리하기만 하고 결과값을 반환하지 않는다.

PROCEDURE에 대해 알아보자

PROCEDURE

프로시저에는 매개변수가 있는 것과 매개변수가 없는 것이 있다.

우선, 매개변수가 없는 프로시저에 대해 알아보자.

문법

        [문법]
            CREATE PROCEDURE 프로시저명
            (
                매개변수 1 [IN/OUT] 테이터타입 [:=DEFAULT 값],
                매개변수 2 [IN/OUT] 테이터타입 [:=DEFAULT 값],
                ...
            )
            IS [AS]
                선언부
            BEGIN
                실행부
            EXCEPTION
                예외처리부
            END [프로시저명];
            /

실행방법

        [실행방법]
            EXECUTE(EXEC) 프로시저명[(매개값1, 매개값2, ...)];

예제

테스트용 테이블 생성

CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;

예제에 사용할 EMP_DUP 테이블을 생성한다. 각자에 맞게 칼럼을 추가하면 된다.

테스트용 테이블 조회

SELECT * FROM EMP_DUP;

원하는 데이터를 EMP_DUP 테이블에 INSERT 한 후, 테스트용 테이블을 조회해보자.

테스트 테이블의 데이터를 모두 삭제하는 프로시저 생성

CREATE PROCEDURE DEL_ALL_EMP
IS
BEGIN
    DELETE FROM EMP_DUP;
    
    COMMIT;
END;
/

프로시저를 관리하는 데이터 딕셔너리

SELECT * FROM USER_SOURCE;

DEL_ALL_EMP 프로시저 호출

EXECUTE DEL_ALL_EMP;

EXECUTE를 이용하여 DEL_ALL_EMP 프로시저를 호출할 수 있다.

DEL_ALL_EMP 프로시저, EMP_DUP 테이블 삭제

DROP PROCEDURE DEL_ALL_EMP;
DROP TABLE EMP_DUP;

실습이 끝났으니 해당 테이블과 프로시저를 삭제하면 된다.

매개변수가 있는 프로시저

프로시저 실행 시 매개변수로 인자값을 전달해야 한다.

매개변수가 있는 프로시저 생성

CREATE OR REPLACE PROCEDURE DEL_EMP_ID
(
    P_EMP_ID EMPLOYEE.EMP_ID%TYPE
)
IS
BEGIN
    DELETE FROM EMPLOYEE
    WHERE EMP_ID = P_EMP_ID;
END;
/

프로시저 실행 (매개 값 전달)

EXEC DEL_EMP_ID('204');

DEL_EMP_ID는 매개변수가 있는 프로시저로, 실행 시에는 매개변수를 넘겨줘야한다. 위에 코드는 '204'의 매개변수를 넘겨주어야한다.

사용자가 입력한 값도 매개변수로 전달 가능

EXEC DEL_EMP_ID('&사번');

FUNCTION

FUNCTION이란 PL/SQL 문을 저장하는 객체이다. 특정 로직을 수행하기 위해 사용하며, 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있다.

함수 또한 사용자가 만들어서 사용할 수 있다는 장점이 있다. 오라클에서 제공하는 함수 외에 필요한 기능을 함수로 만들 수 있어 유용하다.

프로시저와 사용 용도가 비슷하지만 프로시저와 다르게 OUT 변수를 사용하지 않아도 실행 결과를 되돌려 받을 수 있다.

FUNCTION에 대해 알아보자

FUNCTION

문법

        [문법]
            CREATE FUNCTION 함수명
            (
                매개변수 1 타입,
                매개변수 2 타입,
                ...
            )
            RETURN 데이터타입
            IS
                선언부
            BEGIN
                실행부
                
                RETRUN 반환값; -- 프로시저랑 다르게 RETURN 구문이 추가된다.
            EXCEPTION
                예외처리부
            END [함수명];
            /

예시

사번을 입력받아 해당 사원의 보너스를 포함하는 연봉을 계산하고 리턴하는 함수 생성

CREATE OR REPLACE FUNCTION BONUS_CALC
(
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE
)
RETURN NUMBER
IS
    V_SAL EMPLOYEE.SALARY%TYPE;
    V_BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT SALARY, NVL(BONUS, 0)
    INTO V_SAL, V_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = V_EMP_ID;
    
    RETURN (V_SAL + (V_SAL * V_BONUS)) * 12;
END;
/

함수 호출 1

SELECT BONUS_CALC(203) FROM DUAL;

함수 호출 2

SELECT EMP_ID, 
       EMP_NAME, 
       SALARY, 
       NVL(BONUS, 0), 
       BONUS_CALC(EMP_ID)
FROM EMPLOYEE;

함수는 리턴 값이 있기에 위에 예시들처럼 SELECT문에 함수를 호출할 수 있다.

PL/SQL이란?

PL/SQL이란 오라클 자체에 내장되어 있는 절차적 언어로 SQL 문장 내에서 변수의 정의, 조건 처리(IF), 반복 처리(LOOP, FOR, WHILE) 등을 지원한다. 다수의 SQL문을 한 번에 실행이 가능하다.

PL/SQL에 대해 알아보자

PL/SQL의 구조

• 선언부(DECLAER SECTION)
  • DECLARE로 시작, 변수나 상수를 선언 및 초기화하는 부분이다.
• 실행부(EXECUTABLE SECTION)
  • BEGIN으로 시작, SQL문, 제어문(조건, 반복문) 등의 로직을 기술하는 부분이다.
• 예외 처리부(EXCEPTION SECTION)
  • EXCEPTION로 시작, 예외 발생 시 해결하기 위한 구문을 기술하는 부분이다.

PL/SQL 선언부

• 변수 및 상수를 선언해 놓는 공간이다. 선언과 동시에 초기화도 가능.
• 변수 및 상수의 종류
  • 일반 타입 변수
  • 레퍼런스 타입 변수
  • ROW 타입 변수

1. 일반 타입 변수

일반 타입 변수의 선언 및 초기화

            [문법]
                변수명 [CONSTANT] 자료형(크기) [:= 값];

출력기능 활성화

-- 출력기능 활성화
SET SERVEROUTPUT ON;

이 코드를 실행해놓자.

2. 레퍼런스 타입 변수

• 해당하는 테이블의 칼럼에 데이터 타입을 참조해서 그 타입으로 변수를 지정한다.

레퍼런스 타입 변수 선언 및 초기화

            [문법]
                변수명 테이블명%ROWTYPE;

3. ROW 타입 변수

• 하나의 테이블의 여러 칼럼의 값을 한꺼번에 저장할 수 있는 변수를 의미한다.
• 모든 칼럼을 조회하는 경우에 사용하기 편리하다.

ROW 타입 변수 선언 및 초기화

            [문법]
                변수명 테이블명%ROWTYPE;

PL/SQL 실행부

• 변수 및 상수를 선언해 놓는 공간이다.
• 선언과 동시에 초기화가 가능하다.
• 변수 및 상수는 일반 타입 변수, 레퍼런스 타입 변수, ROW 타입 변수로 선언해서 사용할 수 있다.

1. 선택문

1-1) 단일 IF 문법

            [문법]
                IF 조건식 THEN
                    실행 문장
                END IF;

1-2) IF ~ ELSE 구문

          [문법]
            IF 조건식 THEN
                실행 문장
            ELSE 
                실행 문장
            END IF;

1-3) IF ~ ELSIF ~ ELSE 구문

          [문법]
            IF 조건식 THEN
                실행 문장
            ELSIF 조건식 THEN
                실행 문장
            ...
            [ELSE
                실행 문장]
            END IF;

1-4) CASE 구문

          [문법]
            CASE 비교 대상
                 WHEN 비교값1 THEN 결과값1
                 WHEN 비교값2 THEN 결과값2
                 ...
                 [ELSE 결과값]
            END;

2. 반복문

BASIC LOOP

            [문법]
                LOOP
                    반복적으로 실행시킬 구문
                    
                    [반복문을 빠져나갈 조건문 작성]
                        1) IF 조건식 THEN 
                              EXIT;
                           END IF
                           
                        2) EXIT WHEN 조건식;
                END LOOP;

WHILE LOOP

          [문법]
            WHILE 조건식
            LOOP
                반복적으로 실행할 구문;
            END LOOP;

FOR LOOP

          [문법]
            FOR 변수 IN [REVERSE] 초기값..최종값
            LOOP
                반복적으로 실행할 구문;
            END LOOP;

예제

구구단(2 ~ 9단)  출력 (짝수단만 출력)

BEGIN
    FOR DAN IN 2..9
    LOOP
        IF (MOD(DAN, 2) = 0) THEN
            FOR SU IN 1..9
            LOOP        
                DBMS_OUTPUT.PUT_LINE(DAN || ' X ' || SU || ' = ' || DAN * SU );
            END LOOP;
            
            DBMS_OUTPUT.PUT_LINE('');
        END IF;
    END LOOP;
END;

반복문(FOR 구문)을 이용한 데이터 삽입

CREATE TABLE TEST (
    NUM NUMBER,
    CREATE_DATE DATE
);

실습을 위한 TEST 테이블을 먼저 생성한다.

-- TEST 테이블에 10개의 행을 INSERT하는 PL/SQL 작성
BEGIN
    FOR NUM IN 1..10
    LOOP
        INSERT INTO TEST VALUES(NUM, SYSDATE);
        
        IF (MOD(NUM, 2) = 0) THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;    
    END LOOP;
END;

PL/SQL 예외처리부

예외란 실행 중 발생하는 오류를 뜻하고 PL/SQL 문에서 발생한 예외를 예외처리부에서 코드로 처리가 가능하다.

문법

        [문법]
            DECLARE
                ...
            BEGIN
                ...
            EXCEPTION
                WHEN 예외명 1 THEN 예외처리구문 1;
                WHEN 예외명 2 THEN 예외처리구문 2;
                ...
                WHEN OTHERS THEN 예외처리구문;

오라클에서 미리 정의되어 있는 예

• NO_DATA_FOUND
  • SELECT 문의 수행 결과가 한 행도 없을 경우에 발생한다.
• TOO_MANY_ROWS
  • 한 행이 리턴되어야 하는데 SELECT 문에서 여러 개의 행을 리턴할 때 발생한다.
• ZERO_DIVIDE
  • 숫자를 0으로 나눌 때 발생한다.
• DUP_VAL_ON_INDEX
  • UNIQUE 제약 조건을 가진 컬럼에 중복된 데이터가 INSERT 될 때 발생한다.

VIEW란?

SELECT문을 저장할 수 있는 객체이다. VIEW는 논리적인 가상 테이블로 데이터를 저장하고 있지 않으며, 테이블에 대한 SQL만 저장되어 있어서, VIEW 접근할 때 SQL을 수행하면서 결과값을 가져온다.

여러 테이블에서 원하는 데이터들만 보기 위해 사용한다.

VIEW 사용법에 대해 알아보자

문법

        [문법]
            CREATE [OR REPLACE] VIEW 뷰명
            AS 서브 쿼리;

예시

-- 동일한 뷰명이 존재할 수 있으니 기존 것 삭제
DROP VIEW V_EMP_DEPT;

-- 사원명 부서명
CREATE VIEW V_EMP_DEPT
AS
SELECT
    E.EMP_NAME                      사원명
    , NVL(D.DEPT_TITLE, '부서없음') 부서명
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D
ON E.DEPT_CODE = D.DEPT_ID
;

위에 코드를 실행하면, EMP_NAME(사원명)과 DEPT_TITLE(부서명)만을 볼 수 있는 V_EMP_DEPT 뷰가 생성된다.

SELECT *
FROM V_EMP_DEPT
;

V_EMP_DEPT 뷰를 조회해보면 위와 같은 데이터를 얻을 수 있다.

오류 처리

하지만 처음 만들어진 계정은 VIEW를 만들 수 있는 권한이 없다. 따라서 위와 같이 뷰를 만들 수 있는 권한을 해당 계정에 부여해줘야한다.

-- 시스템 계정으로 권한 부여 해줘야함
GRANT CREATE VIEW TO C##KH;

위 코드를 시스템 계정으로 들어가서 위에 GRANT를 통해 해당 계정에서 CREATE VIEW를 할 수 있는 권한을 먼저 부여해주면 된다.

VIEW 사용 시 주의 사항

• 뷰 칼럼에 별칭 부여
  • 서브 쿼리의 SELECT절에 함수나 산술연산이 기술되어 있는 경우,  반드시 별칭을 지정해야 한다.
• VIEW를 이용해서 DML(INSERT, UPDATE, DELETE) 사용
  • 뷰를 통해 데이터를 변경하게 되면 실제 데이터가 담겨있는 기본 테이블에도 적용된다.

DML 구문으로 VIEW 조작이 불가능한 경우

1. 뷰 정의에 포함되지 않은 칼럼을 조작하는 경우
2. 뷰에 포함되지 않은 칼럼 중에 기본 테이블 상에 NOT NULL 제약조건이 지정된 경우
3. 산술 표현식으로 정의된 경우
4. 산술 연산과 무관한 칼럼은 변경 가능
5. 그룹 함수나 GROUP BY절을 포함한 경우
6. DISTINCT를 포함한 경우
7. JOIN을 이용해 여러 테이블을 연결한 경우

VIEW 옵션

• OR REPLACE
  • 기존에 동일한 뷰가 있을 경우 덮어쓰고, 존재하지 않으면 뷰를 새로 생성한다.
• FORCE
  • 서브 쿼리에 기술된 테이블이 존재하지 않는 테이블이어도 뷰가 생성된다.
• NOFORCE
  • 기본값으로 설정되는 옵션으로, 서브 쿼리에 기술된 테이블이 존재해야만 뷰가 생성된다.
• WITH CHECK OPTION
  • 서브 쿼리에 기술된 조건에 부합하지 않는 값으로 수정하는 경우 오류를 발생시킨다.
• WITH READ ONLY
  • 뷰에 대해 조회만 가능. (DML 수행 불가)

문법

        [문법]
            CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
            AS 서브 쿼리
            [WITH CHECK OPTION]
            [WITH READ ONLY];

FORCE나 NOFORCE는 실제로는 별로 쓸 일이 없다. 왜냐하면 VIEW에 DML을 안 쓸 것이기 때문이다.

WITH CHECK OPTION도 별로 쓸 일이 없고, WITH READ ONLY 정도만 잘 알고 있어도 괜찮을 것 같기에 예시를 따로 들지 않겠다.

DDL이란?

DDL은 Data Definition Language의 약자로 데이터 정의어이다. 오라클에서 제공하는 객체를 만들고(CREATE), 변경하고(ALTER), 삭제하는(DROP) 등 실제 데이터 값이 아닌 데이터의 구조 자체를 정의하는 언어로 DB 관리자, 설계자가 주로 사용한다.

오라클에서의 객체는 테이블, 뷰, 시퀀스, 인덱스, 트리거, 프로시져, 함수, 사용자 등이 있다.

DDL 명령어 종류에는 CREATE, ALTER, DROP 등이 있다. DDL 명령어 실행 후에는 COMMIT할 필요가 없다.

DDL의 종류와 사용에 대해 알아보자

1. CREATE

데이터베이스의 객체를 생성하는 구문이다. Oracle 데이터베이스 객체의 대표적인 예는 테이블(TABLE)이다.

테이블은 행과 열로 구성되는 가장 기본적인 데이터베이스 객체로 데이터베이스 내에서 모든 데이터는 테이블에 저장된다.

CREATE의 문법

<테이블 생성>
        [문법]
            CREATE TABLE 테이블명 (
                칼럼명 자료형(크기) [DEFAULT 기본값] [제약조건],
                칼럼명 자료형(크기) [DEFAULT 기본값] [제약조건],
                ...
            );

예시 (DEFAULT, 제약조건 누락)

CREATE TABLE MEMBER(
    MEM_NO          NUMBER
  , ID              VARCHAR2(100)
  , PWD             VARCHAR2(100)
  , NICK            VARCHAR2(100)
  , QUIT_YN         CHAR(1)
  , ENROLL_DATE     TIMESTAMP
  , CONSTRAINT MEM_NICK_UQ_ABCDE UNIQUE(NICK)
);

• MEM_NO
• ID
• PWD
• NICK
• QUIT_YN
• ENROLL_DATE

위의 칼럼명을 가진 'MEMBER'란 이름의 테이블 만들었다. 위에 예시에는 [DEFAULT 기본값]과 [제약조건]이 들어가 있지 않다. MEMBER 테이블에 들어가면 아래와 같은 테이블을 확인할 수 있다.

제약조건이란?

제약조건(CONSTRAINT)은 사용자가 원하는 조건의 데이터만 유지하기 위해서 설정하는 조건이다. 테이블 작성 시 각 칼럼에 대해 저장될 값에 대한 제약조건을 설정할 수 있다.

제약조건은 데이터 무결성 보장을 목적으로 한다. 데이터 무결성 보장이란 데이터의 정확성과 일관성을 유지시키는 것이다.

제약조건 종류

• NOT NULL
  • 해당 칼럼에 반드시 값이 있어야만 하는 경우 사용한다.
  • 삽입/수정 시 NULL 값을 허용하지 않도록 제한한다.
• UNIQUE
  • 칼럼의 입력 값에 중복 값을 제한한다.
  • 데이터를 삽입/수정 시, 기존에 있는 데이터 값 중에 중복되는 값이 있을 경우 삽입/수정되지 않는다.
  • 제약조건 지정 방식으로 칼럼 레벨, 테이블 레벨 방식 모두 사용 가능하다.
• CHECK
  • 칼럼에 기록되는 값에 조건을 설정하고 조건을 만족하는 값만 기록할 수 있다.
  • 비교 값은 변하는 값이나 함수 사용하지 못하고, 리터럴만 사용 가능하다.

[문법]
            CHECK(비교연산자)
                CHECK(칼럼 [NOT] IN(값, 값, ...))
                CHECK(칼럼 = 값)
                CHECK(칼럼 BETWEEN 값 AND 값)
                CHECK(칼럼 LIKE '_문자' OR 칼럼 LIKE '문자%')
                ...

• PRIMARY KEY (기본 키)
  • 기본 키의 제약조건
    • NOT NULL 제약조건 만들어짐
    • UNIQUE 제약조건 만들어짐
    • INDEX 만들어줌
    • 테이블 대표칼럼에 설정 (보통 인위적 식별자로 만듦)
    • 한 테이블에 한 개만 설정할 수 있다.  (단, 한 개 이상의 칼럼을 묶어서 PRIMARY KEY로 제약조건을 설정할 수 있다.)
  • 테이블에서 한 행의 정보를 식별하기 위해 사용할 칼럼에 부여하는 제약조건이다.
  • 각 행들을 구분할 수 있는 식별자 역할 (사번, 부서 코드, 직급 코드, ..)
  • 기본 키 제약조건을 설정하게 되면 자동으로 해당 칼럼에 NOT NULL + UNIQUE 제약조건이 설정된다.
  • 칼럼 레벨, 테이블 레벨 방식 모두 설정 가능하다.
• FOREIGN KEY (외래 키)
  •  다른 테이블에 존재하는 값만을 가져야 하는 칼럼에 부여하는 제약조건이다. (단, NULL 값도 가질 수 있다.)
  • 즉, 참조된 다른 테이블이 제공하는 값만 기록할 수 있다. (FOREIGN KEY 제약조건에 의해서 테이블 간에 관계가 형성된다.)

[문법]
            1) 칼럼 레벨
                칼럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명 [(기본키)] [삭제룰]
                
            2) 테이블 레벨
                [CONSTRAINT 제약조건명] FOREIGN KEY(칼럼명) REFERENCES 참조할테이블명 [(기본키)] [삭제룰]

[삭제룰]
            부모 테이블의 데이터가 삭제됐을 때의 옵션을 지정해 놓을 수 있다.
            1) ON DELETE RESTRICT : 자식 테이블의 참조 키가 부모 테이블의 키 값을 참조하는 경우 부모 테이블의 행을 삭제할 수 없다. (기본적으로 적용되는 옵션)
            2) ON DELETE SET NULL : 부모 테이블의 데이터가 삭제 시 참조하고 있는 자식 테이블의 컬럼 값이 NULL로 변경된다.
            3) ON DELETE CASCADE  : 부모 테이블의 데이터가 삭제 시 참조하고 있는 자식 테이블의 컬럼 값이 존재하는 행 전체가 삭제된다.

제약조건 문법

문법]
            1) 칼럼 레벨
                CRATE TABLE 테이블명 (
                    칼럼명 자료형(크기) [CONSTRAINT 제약조건명] 제약조건,
                    ...
                );
            
            2) 테이블 레벨
                CRATE TABLE 테이블명 (
                    칼럼명 자료형(크기),
                    ...,
                    [CONSTRAINT 제약조건명] 제약조건(칼럼명)
                );

예시 (DEFAULT, 제약조건 포함)

CREATE TABLE MEMBER(
    MEM_NO          NUMBER PRIMARY KEY
  , ID              VARCHAR2(100)   CONSTRAINT MEM_ID NOT NULL
  , PWD             VARCHAR2(100)
  , NICK            VARCHAR2(100)
  , QUIT_YN         CHAR(1)         DEFAULT 'N' CONSTRAINT QUIT CHECK(QUIT_YN IN('Y','N'))
  , ENROLL_DATE     TIMESTAMP
  , CONSTRAINT MEM_NICK_UQ_ABCDE UNIQUE(NICK)
);

• MEM_NO 칼럼을 PRIMARY KEY
• ID 칼럼에 NOT NULL이라는 제약조건
• QUIT_YN 칼럼에 DEFAULT 값 'N'과 CHECK라는 제약조건

따라서 ID 칼럼에는 NULL 값이 들어오지 못하고, QUIT_YN에는 디폴트로 'N' 값이 들어온다. 또한 QUIT_YN에는 'Y'와 'N' 값 외에는 다른 값을 입력할 수 없다.

그리고 PRIMARY KEY는 NOT NULL과 UNIQUE의 제약조건이 포함되어 있다. MEMBER 테이블의 '제약조건'에 들어가면 아래와 같은 결과를 확인할 수 있다.

MEM_ID는 PRIMARY KEY로 NOT NULL 조건이 들어가 있고, QUIT_YN에는 'Y', 'N'만 들어갈 수 있게 CHECK한다.

QUIT_YN이 'N'의 디폴트 값을 갖는 것 또한 확인할 수 있다.

칼럼에 주석 달기

[문법]
            COMMENT ON COLUMN 테이블명.칼럼명 IS '주석내용';

칼럼에 주석 다는 문법은 위와 같다.

COMMENT ON COLUMN MEMBER.ID IS '아이디';
COMMENT ON COLUMN MEMBER.PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.NICK IS '닉네임';
COMMENT ON COLUMN MEMBER.QUIT_YN IS 'Y: 탈퇴회원 , N: 일반회원';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '가입일';

해당 문법을 사용하여 MEMBER 테이블에 위와 같이 주석을 달아보면

위와 같이 COMMENTS 부분에 해당 주석들이 달린 것을 확인할 수 있다.

2. DROP

DROP은 오라클 데이터베이스 객체를 삭제할 수 있게 한다. 사용법이 간단하기에 바로 예시를 보자.

예시

DROP TABLE MEMBER;

해당 쿼리를 실행하고, 

SELECT *
FROM MEMBER
;

위에 SELECT문을 실행해보면,

MEMBER 테이블이 삭제된 것을 확인할 수 있다.

3. 데이터 딕셔너리란?

자원을 효율적으로 관리하기 위한 다양한 객체들의 정보를 저장하는 시스템 테이블이다. 사용자가 객체를 생성하거나 객체를 변경하는 등의 작업을 할 때 데이터베이스에 의해서 자동으로 갱신되는 테이블이다.

데이터에 관한 데이터가 저장되어 있다고 해서 메타 데이터라고도 한다.

• 데이터 딕셔너리 종류
  • USER_TABLES
    • 사용자가 가지고 있는 테이블들의 전반적인 구조를 확인하는 뷰 테이블이다.
  • USER_TAB_COLUMNS
    • 테이블, 뷰의 칼럼과 관련된 정보를 조회하는 뷰 테이블이다.

예시

SELECT *
FROM USER_TAB_COLUMNS
;

위에 USER_TAB_COLUMNS를 보면, 해당 계정에 들어있는 전체 테이블의 모든 칼럼을 조회할 수 있다.

SELECT *
FROM USER_TABLES
;

위에 USER_TABLES를 보면, 해당 계정에 들어있는 전체 테이블을 조회할 수 있다.

Oracle이란?

오라클 데이터베이스(Oracle Database)는 대규모 데이터를 안정적으로 관리하고 처리할 수 있는 강력한 관계형 데이터베이스 관리 시스템(RDBMS)이다.

관계형 데이터베이스 관리 시스템(RDBMS)는 Relational Date Base Management System의 약자로 RDB를 생성, 갱신, 관리하기 위한 시스템을 의미한다.

RDBMS는 테이블 간의 관계를 통해 데이터를 조직화하고 쿼리하여 검색, 수정 및 관리할 수 있게 해준다.

SQL이란?

SQL(Structured Query Language)은 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하고 조작하기 위해 사용되는 표준화된 프로그래밍 언어이다.

SQL은 데이터베이스 시스템에서 데이터의 쿼리, 삽입, 수정, 삭제 등의 작업을 수행하는 데 사용한다.

오라클에서 사용하는 SQL의 종류에 대해 알아보자

---

오라클 SQL 종류

1. Data Definition Language(DDL)

• DDL은 데이터 정의어로 데이터베이스 객체를 정의하거나 수정하는 데 사용한다.
• DDL 명령어 종류: CREATE, ALTER, DROP 등이 있다.
• DDL 명령어 실행 후에는 COMMIT할 필요가 없다.

2. Data Manipulation Language(DML)

• DML은 데이터 조작어로 데이터를 쿼리하거나 수정하는 데 사용한다.
• DML 명령어 종류: INSERT, UPDATE, DELETE 등이 있다.

3. Transaction Control Language(TCL)

• TCL은 트랜잭션 제어어로 트랜잭션을 제어하는 데 사용한다.
• TCL 명령어 종류: COMMIT, ROLLBACK, SAVEPOINT 등이 있다.

4. Data Control Language(DCL)

• DCL은 데이터 제어어로 데이터베이스 객체에 대한 액세스 권한을 제어하는 데 사용한다.
• DCL 명령어 종류: GRANT, REVOKE 등이 있다.

5. Data Query Language(DQL)

• DQL은 데이터 질의어로 데이터를 조회하는 데 사용한다.
• DQL 명령어 종류: SELECT 등이 있다.
• 보는 입장에 따라 SELECT을 DML로 분류하기도 한다.