오라클 - 기초 정리

 

본 글은 다음 블로그 '일상 흔적' 님의 오라클 실습 내용을
정리한 글입니다.

 

 

OPTIMIZER

• DML을 수행할 때 OPTIMIZER가 관여한다.
• 수행하고자하는 DML을 가장 효율적으로 처리하는 최적 경로를 찾는 역할을 한다.
• OPTIMIZER가 찾아낸 최적 경로는 PLAN을 통해 확인할 수 있다.
• TOAD에서는 'Ctrl + E'를 사용해 조회해 볼 수 있다.

 

DB BLOCK의 구조

• BLOCK은 오라클 데이터베이스의 물리적인 저장 단위로 입출력시의 최소 단위
• BLOCK의 구조는 위에서부터 차례대로 다음과 같이 나뉜다.
  1. HEADER : 블록의 주소와 같은 BLOCK에 대한 일반적인 정보를 포함
  2. TABLE DIRECTORY : 블록 안에 존재하는 ROW를 소유하는 테이블에 대한 정보를 포함
  3. ROW DIRECTORY : 각 ROW의 주소 등 블록 안에 존재하는 ROW에 대한 실제 정보를 포함
  4. FREE SPACE : ROW의 변경이나 새로운 ROW의 삽입 시 필요로 하게 되는 추가적인 공간
  5. ROW DATA : 테이블이나 인덱스의 데이터를 물리적으로 저장하는 영역
• PCTUSED와 PCTFREE
  
  1. PCTFREE : 이미 블록에 쓰여진 ROW의 UPDATE나 INSERT를 위하여 예약되는 공간
  2. PCTUSED : PCTFREE에서 지정한 영역만큼만 FREE SPACE가 남게 되면 오라클은
                    더이상 새로운 ROW를 삽입하지 않는다. 사용공간이 ROW의 삭제 등으로
                    PCTUSED에서 지정한 값 아래로 떨어지게 되면 그때 다시 새로운 ROW를 삽입할 수 있다.

 

DUAL TABLE

• SYS USER가 소유하고 있는 단일 행에 단일 컬럼만을 가지는 테이블
• 일시적 산술 연산이나 날짜 연산을 위해 사용

 

COMMIT과 ROLLBACK

• commit은 저장, rollback은 취소를 의미
• rollback은 commit된 이후의 작업에 대한 취소
• 하나의 트랜잭션이 완료 된다는 것은 commit 까지를 의미
• commit을 하지 않아도 자동 commit 되는 경우가 존재
  1. DDL(CREATE, ALTER, DROP, TRUNCATE)이나 DCL(GRANT, REVOKE, COMMIT, ROLLBACK) 문장을 사용할 때
  2. SQL*PLUS에서 exit을 이용하여 정상 종료 할 때
  3. set AutoCommit on으로 환경이 설정되어 있을 때

 

오라클 명령어

1. DML : 데이터의 삽입, 삭제, 수정, 조회 명령어
2. DDL : 자료 정의를 위한 명령어
3. DCL : 사용자에게 부여된 권한을 정의하는 명령어

 

DML문

1. 자료의 입력 : INSERT문
2. 자료의 조회 : SELECT문
3. 자료의 수정 : UPDATE문
4. 자료의 삭제 : DELETE문

 

SELECT문 뒤에 기술될 수 있는 문장

1. 조건 부여 : WHERE절
2. 자료의 그룹 지정 : GROUP BY절
3. 그룹에 조건 부여 : HAVING절
4. 도출된 결과를 정렬 : ORDER BY절

※ 단, HAVING 절은 GROUP BY 절이 있을 때만 사용한다.

 

 

NULL의 사용

• NULL은 컬럼에 값이 없다는 것
• DML을 이용하여 자료를 다룰 때는 항상 NULL을 신경써야한다.
• NULL이 포함될 우려가 있는 컬럼을 이용할 때는 항상 NVL 함수를 이용해
  값을 치환 시켜 사용하는 것이 좋다.
  • 숫자형 컬럼이나 변수에 NULL이 들어간 경우 : 0이나 1 등 적절한 숫자로 치환 후 연산
  • 문자형 컬럼이나 변수에 NULL이 들어간 경우 : 스페이스나 특정 문자 값으로 치환 후 연산
• 조건절에서 비교 연산자를 사용할 때 
  1. A라는 문자형 컬럼의 값이 1과 같은지 다른지 비교할 때
     • 같은지 비교 : WHERE A = '1'
     • 다른지 비교 : WHERE A <> '1'
  2. A라는 문자형 컬럼의 값이 NULL과 같은지 다른지 비교할 때
     • 같은지 비교 : WHERE A IS NULL
     • 다른지 비교 : WHERE A IS NOT NULL

 

ALIAS

• 단어 뜻 그대로 별명을 말한다.
• 컬럼명이나 테이블명이 길어서 또는 다른 이유로 컬럼에 다른 이름을 부여하여 사용하는 것
• ALIAS를 주는 방법
  • 한 칸 이상을 띄우고 ALIAS명을 주는 방법
  • 컬럼 이름과 ALIAS 사이에 AS라고 기술하는 방법
• FROM으로 두 개 이상의 테이블을 조회할 때 테이블에 FROM TEMP A, TDEPT B와 같은 형태로
  ALIAS를 넣어주면 좋다.
• ALIAS를 반드시 사용해야 하는 경우
  1. 테이블 : 셀프 조인(self join)의 경우 반드시 ALIAS를 사용해야 한다.
  2. 컬럼 : ROWNUM을 사용하거나, TREE 구조의 전개시 LEVEL 값 등을 사용하는 경우

 

CONCATENATION

• CONCATENATION은 함수의 일종
• 두 개 이상의 문자열을 연결하여 하나의 문자열을 만들 때 사용한다.
• CONCAT 함수를 사용하거나 합성연산자(||)를 이용한다.
• 자바에서 문자열을 붙일 때 사용하는 (+)연산자와 비슷하다.
  • ex)SELECT EMP_NAME || '('||LEV||')' 성명
        FROM TEMP;

    

    실행결과

• 작은따옴표를 문자열로 표현하고자 할 때는 반드시 두 개를 함께 사용한다.
  • ex)SELECT EMP_NAME || ''''||LEV||'''' 성명
        FROM TEMP;

    

    실행결과

• 현재 유저에 존재하는 모든 OBJECTS를 DROP 시키는 방법
• 다음과 같은 명령어를 작성한다.
  • SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ';'
    FROM USER_OBJECTS;
• 다음과 같이 나온 결과를 복사해서 사용한다.

  

  실행결과

• 합성연산자를 문자형과 숫자형을 혼합해서 사용한다면
  숫자형이 문자형으로 자동형변환이 일어난다.

 

SUBSTR

• 문자열을 자르는 함수
• SUBSTR("문자열", "시작위치", "길이")
• 인덱스 시작번호는 '1'
• 뒤에서부터 자를 때는 '-(minus)'를 붙여서 사용
• WHERE 절에도 붙여서 조건을 검색할 때 사용할 수 있다.

 

 

WHERE

• 조건절을 의미한다.
• 조건이 여러 개일 경우 AND나 OR로 묶을 수 있다.
• 테이블 두 개 이상이 조인이 걸린다면 조인 조건을 WHERE절에 기술한다.

 

 

ORDER BY

• RECORD가 테이블에 저장될 때는 순서가 정해져 있지 않다.
• ORDER BY 절은 SELECT 문장의 마지막에 기술한다.
• ORDER BY의 기본값은 ASCENDING이다.
• DESCENDING으로 결과를 나오게 하려면 뒤에 DESC를 붙인다.
• ORDER BY에 컬럼명 대신 컬럼 순서를 기술해서 사용할 수 있다.

 

연산자

• WHERE 조건절에서 자료의 검색 시 조건을 주기 위해 사용
• =, <, >, <=, >=, <>, != 등이 사용
• NOT : IN, BETWEEN, LIKE, ANY, ALL, EXISTS 등과 함께 쓰여 연산의 결과를 부정할 때 사용
• LIKE : 값의 일부를 주고 검색할 때 사용
  1. A로 시작되는 ROW 검색 : WHERE COLLUM LIKE 'A%'
  2. A가 포함되는 ROW 검색 : WHERE COLLUM LIKE '%A%'
  3. 총 4자리 부서코드 중 2번째 자리에 A가 들어간 ROW 검색 : WHERE COLLUM LIKE '_A__'
• BETWEEN : 특정 값들 사이의 값을 검색하기 위해 사용
• IN : OR 조건으로 연결될 조건을 한번에 기술하는 기능을 제공
  • ex) WHERE NAME IN('홍길동', '김철수')

 

GROUP BY와 HAVING

• GROUP BY는 특정 컬럼이나 값을 기준으로 ROW를 묶을 때 사용
• 그룹 함수(MAX, MIN, AVG, SUM, COUNT 등)를 적용할 때 사용
• GROUP BY 뒤에 ORDER BY를 사용해서 자료의 정렬을 할 수 있다.
  (기본적으로는 GROUP BY 뒤에 온 컬럼에 따라 정렬된다.)
• HAVING은 GROUP BY된 결과에 조건을 걸 때 사용

 

 

함수

단일 행 함수		GROUP 함수
공통점	결과로 반환하는 ROW가 하나이다.
차이점	하나의 행 값이 함수에 적용되어 값으로 하나의 행이 리턴	여러 개의 행 값이 함수에 적용되어 값으로 하나의 행이 리턴

 

단일행 함수 종류

• 문자
  • LOWER : 문자를 소문자로 변환하여 리턴
  • UPPER : 문자를 대문자로 변환하여 리턴
  • INITCAP : 지정된 문자의 단어 첫 자는 대문자 나머지는 소문자로 변환하여 리턴
  • LPAD(A, N, 'B') : A 문자의 좌측에 B 문자를 채워서 N자리로 만들어 리턴
  • RPAD(A, N, 'B') : A 문자의 우측에 B 문자를 채워서 N자리로 만들어 리턴
  • SUBSTR(A, N, M) : A 문자의 N번째 문자부터 M개의 길이만큼의 문자를 리턴
  • INSTR(A, B) : A 문자에서 B 문자가 최초로 나타나는 위치를 리턴
                     B문자가 A문자에 여러 개 있을 경우,
                     INSTR(A, B, N)으로 N번째 자리에 위치를 리턴하게 작성한다.
  • LTRIM(A, B) : A 문자의 왼쪽에서부터 B문자를 제거, 생략시 공백을 지운 값을 리턴
  • RTRIM(A, B) : A 문자의 오른쪽에서부터 B문자를 제거, 생략시 공백을 지운 값을 리턴
  • SOUNDEX : 지정한 문자와 발음이 동일한 문자열을 리턴
  • TRANSLATE(A, B, C) : A문자열에서 B를 C문자열로 치환, B가 A 문자열에 없는 경우 A 문자열을 그대로 리턴
  • CHR : 아스키코드를 해당 문자열로 치환하여 리턴
  • ASCII : 문자열을 아스키코드를 리턴
• 숫자
  • ROUND(N, M) : N을 소수점 아래 M자리까지 반올림한 값을 리턴
                         M을 생략하면 소수점 아래 값을 반올림한 정수값을 리턴
  • TRUNC(N, M) : N을 소수점 아래 M자리까지 남기고 절삭한 값을 리턴
  • CEIL(N) : 지정된 값 이상의 가장 작은 정수를 리턴
  • FLOOR(N) : 지정된 값 이하의 가장 큰 정수를 리턴
  • POWER(N, M) : N을 M제곱한 값을 리턴
  • SQRT(N) : 지정된 값의 루트값을 리턴
  • SIGN(N) : 지정된 값이 0보다 작으면 -1, 0이면 0, 0보다 크면 1을 리턴
  • ABS(N) : 지정된 값의 절대값을 리턴
  • MOD(N, M) : N을 M으로 나눈 나머지를 리턴
• 날짜
  • ADD_MONTH('날짜', N) : 날짜에 N만큼을 더한 달을 리턴,  N값이 음수인 경우 이전 달을 리턴
  • NEXT_DAY('날짜', 1~7) : 1(일요일)부터 7(토요일)까지 가능, 해당 날짜에 다음 혹은 이전 요일을 리턴
  • LAST_DAY('날짜') : 해당 날짜의 월의 말일을 리턴
• 변환
  • TO_CHAR(N OR '날짜') : N이나 날짜를 문자로 변환
  • TO_NUMBER(A) : A 문자열을 숫자로 변환
  • TO_DATE(A) : A 문자열을 날짜형으로 변환
• 자료형 무관
  • DECODE : IF문의 기능
  • NVL : NULL값을 치환
  • GREATEST :  지정된 값들 중 최대값
  • LEAST : 지정된 값들 중 최소값
  • VSIZE : 지정된 값의 Bytes

 

그룹함수 종류

• AVG : 지정된 값의 평균값
• MIN : 지정된 값의 최소값
• MAX : 지정된 값의 최대값
• COUNT : 지정된 값의 개수
• SUM : 지정된 값의 합
• STDDEV :  지정된 값의 표준편차
• VARIANCE : 지정된 값의 분산