본문 바로가기

Structured Query Language/Oracle SQL

Oracle SQL 기본_06

CASE 표현식 : 조건 충족 여부에 따라 설정한 값을 반환하는 조건변환, 범주별 분기함수 (* 타 프로그래밍 언어의 IF~THEN, IFELSE 대응)


DECODE 함수의 경우 'A=B'이냐 'A≠B'이냐에 따라 분기가 발생하였기 때문에, 크거나 작은 범주 조건에 따라 분기가 발생하는 경우에는 사용하기에 까다로웠습니다. CASE 표현식은 다른 프로그래밍 언어의 IF ~ THEN 구문, IFELSE 구문처럼 범주별로 분기가 발생하는 조건에 대응할 수 있도록 해 줍니다.

 

ex.

- 주민등록번호 뒷자리 첫째자리가 1이면 남성, 2이면 여성으로 분류하여 표시하기

- 전화번호의 앞자리 지역번호에 따라 지역 표시하기

- 컬럼의 값을 특정한 범주로 나누어서 분류하기

...

이러한 경우 CASE - WHEN - THEN 표현식를 사용하여 새로운 컬럼의 형태로 원하는 값을 출력하여 조회할 수 있습니다.

 

1. CASE 표현식의 개념과 표현

1) CASE 표현식의 간단한 개념

CASE 표현식은 여러가지 범주를 지닌 조건 각각에 대응하여 값을 출력할 수 있게끔 합니다.

 

2) CASE 표현식의 기본 구성

 

select case when 조건1 then 출력1
            when 조건2 then 출력2
            ...
            when 조건N then 출력N
	        else 출력N+1
       end "출력할컬럼이름Z"
from 테이블이름;

 

조건 내에 언급된 컬럼의 레코드에 대하여,

- 조건1을 만족 시 출력1

- 조건2를 만족 시 출력2

- 조건3을 만족 시 출력3

...

- 조건N을 만족 시 출력N

- 모든 조건을 만족하지 않을 시 출력N+1

- 위 모든 내용을 컬럼Z로 출력

 

2. 예제 : CASE - WHEN - THEN 표현식을 사용한 범주별 출력 값의 변화

* 이하의 예제에서는 hr 연습계정의 employees 테이블을 사용합니다.

1) 예제 : employees 테이블에서 employee_id, salary 컬럼을 출력하고,

- salary 컬럼의 값이 10000 이상이면 '상', 5000이상 10000미만이면 '중', 5000미만이면 '하'를 출력하는 '연봉3단계'라는 이름의 컬럼도 함께 출력하기

case when then 표현식을 사용하면 수월하게 조건으로 설정된 범주별로 출력값을 지정할 수 있습니다.

 

select employee_id,
       salary, 
       case when salary>=10000 then '상'
            when salary>=5000 and salary<10000 then '중'
            else '하'
       end "연봉3단계"
from employees;

'연봉3단계'라는 컬럼이 조회되고 있습니다.

- salary 컬럼의 값이 10000 이상이면 '상'

- 5000 이상 10000 미만이면 '중'

- 그 외(5000 미만)이면 '하'로 분류된 값이 레코드별로 각각 출력된 모습입니다.

 

 

 

2) 예제 : employees 테이블에서 employee_id, hire_date(입사일)컬럼을 출력하고,

- 2005년부터 입사한 직원은 '2005~', 2000년~2004년 사이에 입사한 직원은 '2000~2004', 그 이전에 입사한 직원은 '~1999'라고 표시하는 'HDRange'라는 이름의 컬럼도 함께 출력하기

* hire_date(입사일) 컬럼으로부터 'YYYY' 형태로 표기된 입사년도를 출력하려면 to_char 함수를 써서 형태를 변환시켜주어야 합니다.

to_char(hire_date, 'YYYY')

위 요소를 사용하여 CASE - WHEN - THEN 표현식을 쓰면 아래와 같습니다.

 

select employee_id,
       hire_date, 
       case when to_char(hire_date,'YYYY')>=2005 then '2005~'
            when to_char(hire_date,'YYYY') between 2000 and 2004 then '2000~2004'
            else '~1999'
       end "HDrange"
from employees;

입사년도(YYYY)가 2005 이상인가 / 2000과 2004 사이인가 / 그 외인가에 따라 3개로 범주가 나뉘고, 각각의 범주에 속할 경우 'HDrange' 컬럼에서 각 레코드가 갖는 값은 '2005~', '2000~2004', '~1999' 중 하나가 될 것입니다.

위 SQL 문장을 실행하면, 오라클 SQL 디벨로퍼는 다음과 같은 결과를 출력합니다.

 

 

 

 

COUNT(*), COUNT(컬럼이름) : 레코드 개수를 출력하는 집계함수. (인구수 / 구성원수 / 직원수 / 그룹별 개체수 파악 등에 사용)


GROUP BY 함수와 함께 가장 많이 쓰이는 집계함수 중의 하나가 COUNT입니다. COUNT 함수는 그룹의 레코드 수를 집계하여 출력해 줍니다.

COUNT : 입력된 데이터의 총 건수를 반환

그런데 COUNT(*)COUNT(컬럼이름)은 같을 수도 있지만, 다른 결과를 나타낼 수도 있습니다.

COUNT(*)
null 값을 포함한 전체 레코드 수 반환
COUNT(컬럼이름)
지정된 컬럼 중 null 값을 제외한 전체 레코드 수 반환

* 아래의 예시는 scott 연습계정의 emp를 사용합니다.

COUNT 함수에 대한 이해를 위해, scott 계정의 emp 테이블 전체 레코드를 출력해 봅니다.

 

select * from emp;

comm 컬럼, deptno 컬럼에 값이 null 인 레코드가 있습니다. 나머지 컬럼은 12개 레코드 전부 데이터가 채워져 있습니다.

이 emp 테이블에서,

- 전체 레코드 수

- empno 컬럼에 대한 레코드 수

- comm 컬럼에 대한 레코드 수

- deptno 컬럼에 대한 레코드 수

4가지를 각각 출력하는 SQL 문장을 구성하면 아래와 같습니다

 

select count(*),
       count(empno),
       count(comm),
       count(deptno)
from emp;

전체 레코드 수 : 12개

- empno 컬럼의 레코드 수 : 12개 (* null이 없었음)

- comm 컬럼의 레코드 수 : 11개 (null이 1개)

- deptno 컬럼의 레코드 수 : 8 (null이 4개였음)

이라는 결과가 출력되었습니다.

COUNT 함수는 전체는 물론 각종 그룹별/집단별 인구수, 개체수, 관측값 수, row 개수, 구성원 수... 등을 집계하기 위해서 빈번하게 사용되고 있습니다.

이때 집계의 대상이 되는 변수(컬럼)가 어떤 것인지 적절히 판단하여야 하는 경우가 있으므로, COUNT(*)와 COUNT(컬럼이름)의 이와 같은 차이를 알아 두는 편이 좋습니다.

COUNT(*)
null 값을 포함한 전체 레코드 수 반환
COUNT(컬럼이름)
지정된 컬럼 중 null 값을 제외한 전체 레코드 수 반환

SYSDATE : 현재 시스템의 날짜를 출력하는 함수 & SYSDATE 출력 형식 설정하기(alter session set nls_date_format)


오라클 SQL 디벨로퍼에서는 SYSDATE 함수를 사용해 시스템의 현재 날짜와 시각을 바로 출력하거나, 불러와서 변수 또는 날짜, 문자로 사용할 수 있습니다.

1. SYSDATE를 이용해 시스템 현재날짜 출력하기

 

select sysdate from dual;

SYSDATE의 출력값 형식은 날짜와 시간입니다.

하지만, 위 출력된 형식(20/12/22)에서는 날짜는 있지만 시간이 보이지 않습니다.

이 경우, SQL에서 SYSDATE 출력 형식을 지정해 주어 날짜 뿐만 아니라 현재 시스템 시각도 함께 출력되도록 양식을 수정해줄 수 있습니다.

2. SYSDATE의 출력 형식 변경하기 :

alter session set nls_date_format

위 '1'에서 SYSDATE는 YY/MM/DD의 형식으로 출력되었습니다.

alter session set NLS_DATE_FORMAT 명령어를 사용하면, 그 외의 다양한 형태로 SYSDATE 출력 형식을 바꿀 수 있습니다.

 

1) SYSDATE 출력 형식을 변경하는 기본식

 

alter session set NLS_DATE_FORMAT='출력형식';

출력형식의 예는 아래와 같습니다.

YYYY-MM-DD

YYYY/MM/DD

YYYYMMDD

YYYY-MM-DD:HH24:MI:SS (* 날짜와 시간을 함께 표기하는 SYSTIMESTAMP도 있습니다)

DD-Mon-YY

 

2) 예제 : SYSDATE 출력형식의 변경

(1) 연도 4자리-달2자리-날짜2자리:24시간:분:초 형태로 SYSDATE 양식을 변경하기

YYYY-MM-DD:HH24:MI:SS

 

alter session set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';

 

문장을 실행하면, 하단 '스크립트 출력' 창에 'Session이(가) 변경되었습니다'라는 문구가 나타납니다.

이제 SYSDATE를 출력해 봅니다.

 

select sysdate from dual;

출력된 SYSDATE 양식이 YYYY-MM-DD:HH24:MI:SS 형태로 수정되어 출력되었습니다.

 

 

(2) 연도 4자리/달2자리/날짜2자리 형태로 SYSDATE 양식을 변경하기

이번에는 YYYY/MM/DD 형식으로 변경해 봅니다.

alter session set NLS_DATE_FORMAT='YYYY/MM/DD';

위 SQL 문장을 실행하면, 다시 하단 '스크립트 출력' 창에 'Session이(가) 변경되었습니다'라는 문구가 나타납니다.

select sysdate from dual;

이 상태로 SYSDATE를 다시 출력하면 변경된 형식의 결과가 출력됩니다.

 

(3) '날짜2자리-달이름-연도4자리 24시:분:초' 형식으로 SYSDATE 수정하기

DD-MON-YYYY HH24:MI:SS 형식입니다.

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

위 SQL 문장을 실행하면, 다시 하단 '스크립트 출력' 창에 'Session이(가) 변경되었습니다'라는 문구가 나타납니다.

select sysdate from dual;

 

 

데이터 타입 : 종류와 설명, 예시 (CHAR, VARCHAR2, NUMBER, DATE, CLOB, BLOG, RAW, LONG RAW, BFILE 등)


오라클 SQL 디벨로퍼에서 관리되는 컬럼의 데이터들은 각자 쓰임새와 표현 방식에 따라 일련의 '데이터 유형(data type)'으로 분류/설정됩니다.

각 컬럼들에 저장되는 정보는 각 컬럼이 설정되어 있는 데이터 유형과 같은 유형으로 되어 있습니다.

만약 해당 컬럼에 지정된 데이터 유형과 다른 유형의 값을 입력했을 경우에는 오류가 발생할 수 있습니다.

 

Oracle SQL Developer

데이터 유형의 종류와 그 특성

데이터 유형
간단 분류
상세
NUMBER(n, m)
숫자
n : 저장될 숫자의 최대 전체 자리수. 1≤n≤38
m : 소숫점 이하 자리수
VARCHAR2(n)
문자
가변 길이 문자열 저장. 저장되는 문자열의 길이가 n byte 범위 내에서 제한이 없음
n ≤ 4000 (byte)
DATE
날짜
날짜를 저장
CHAR(n)
문자
고정 길이 문자열 저장.
n ≤2000 (byte)
LONG
문자
가변 길이의 아주 긴 문자열을 저장.
2GB까지 저장 가능 (대용량)
CLOB
문자
가변 길이의 아주 긴 문자열을 저장
4GB까지 저장 가능
BLOB
2진수
가변 길이, 최대 4GB까지 저장(대용량)
RAW(n)
RAW
RAW데이터를 2000byte까지 저장
LONG RAW(n)
RAW
RAW데이터를 2GB까지 저장(대용량)
BFILE
외부 파일
외부에 저장된 대용량 데이터를 4GB까지 저장

 

데이터 유형(데이터 타입)은 여러 상황에서 조회하거나 설정할 수 있습니다.

- DESC(DESCRIBE)로 테이블 구조, 변수의 데이터 유형을 파악할 때

- CREATE TABLE로 테이블을 만들 때, 컬럼(변수)의 데이터 유형을 정하기

- ALTER 명령어로 정한 컬럼의 데이터 유형을 바꿀 때

- INSERT INTO로 새로운 레코드 데이터(row, 행)를 입력할 때

- 형변환 함수(TO_CHAR, TO_NUMBER, TO_DATE 등)를 사용하여 데이터 유형을 전환하여 데이터를 연산/출력할 필요가 할 때

...

이러한 상황에서, 변수의 본래 성질에 따라 적절한 데이터 유형을 설정하고, 그 유형에 맞추어 알맞은 형태의 데이터를 입력하고 관리하게 됩니다.

 

DESC 명령어를 사용하여 테이블의 구조를 조회하였습니다.

RESIDENT 테이블(* 임의로 만든 테이블입니다)의 데이터 유형을 볼 수 있습니다.

 

데이터 유형
의미
NUMBER(3)
최대 3자리 (3byte)까지의 숫자
VARCHAR2(30)
최대 30byte까지 입력할 수 있는 가변 문자열
VARCHAR2(10)
최대 10byte까지 입력할 수 있는 가변 문자열
DATE
날짜

이러한 테이블 구조는 DESC 명령어를 사용하는 방법 외에도, Oracle SQL Developer 좌측 접속 탭에서 테이블을 선택하여 조회할 수도 있습니다.

아래의 이미지는 scott 연습계정, emp 테이블을 클릭하였을 때 나타난 정보입니다.

DATA_TYPE이 각 컬럼(변수)의 데이터 유형을 나타내어 주는 부분입니다.

 

데이터 유형
의미
NUMBER(4, 0)
최대 4자리 (4byte)까지의 숫자, 정수.
(소숫점 아래 0자리)
VARCHAR2(10 byte)
최대 10byte까지 입력할 수 있는 가변 문자열
VARCHAR2(9 byte)
최대 9byte까지 입력할 수 있는 가변 문자열
DATE
날짜
NUMBER(7, 2)
최대 7자리(7byte)까지의 숫자.
소숫점 아래 최대 2자리까지 저장

이 컬럼들에 저장되는 정보는 이 데이터 유형에 속하며, 만약 해당 컬럼에 지정된 데이터 유형과 다른 유형의 값을 입력했을 경우에는 오류가 발생할 수 있습니다.

 

 

 

TO_NUMBER : 문자나 날짜를 숫자로 바꾸어 주는 데이터 유형 형변환함수 (*결과값 : 숫자)


오라클 SQL 디벨로퍼에서 사용되는 데이터 유형(data type)에는 숫자(number), 문자(varchar2), 날짜(date) 등이 있습니다.

오라클 SQL은 산술연산과 집계함수를 사용할 때 컬럼의 데이터 유형을 적절한 형태로 자동으로 바꾸어 주기도 합니다

(묵시적 형변환).

하지만 이러한 묵시적 형변환데이터의 크기가 아주 큰 경우에는 연산에 부담을 줄 수 있기 때문에, 효율을 고려할 경우에는 사전에 변수의 데이터 유형을 알맞게 지정하거나, 형변환함수를 이용하여 데이터 유형을 숫자로 변환하여 준 다음에 분석을 수행하는 편이 좋습니다.

 

 

1. TO_NUMBER 함수의 개념과 표현

1) TO_NUMBER 함수의 개념

TO_NUMBER 함수는 주어진 문자열 또는 컬럼의 데이터를 숫자로 변환하여 줍니다.

* 그렇다고 문자(VARCHAR2)나 날짜(DATE)를 숫자로 바로 바꾸어 주지는 못합니다.

예를 들어, VARCHAR2 유형의 컬럼에 숫자들만 저장되어 있는 경우에 사용할 수 있습니다. TO_NUMBER 함수를 이용하여 그 컬럼의 원본을 변형시키지 않고도 숫자 타입으로 전환된 컬럼을 출력하여 연산할 수 있게 하는 것입니다.

 

 

2) 기본식 : SQL 문장에서 TO_NUMBER 함수의 표현

select to_number(컬럼이름), ...
from 테이블이름;

숫자 타입 데이터만 들어있는 컬럼의 컬럼이름을 TO_NUMBER()에 투입합니다.

 

* 주의 : 문자(한글, 영어 등)가 포함된 컬럼, 날짜 데이터가 포함된 컬럼을 TO_NUMBER()에 투입하면 다음과 같은 오류가 발생합니다.

ORA-01722 : 수치가 부적합합니다.

01722. 00000 - "invalid number"

*Cause : The specified number was invalid.

*Action : Specify a valid number.

 

위의 예시는 scott 연습계정, emp 테이블의 ename 컬럼에 TO_NUMBER 형변환을 시도한 경우입니다. ename 컬럼에는 영문 알파벳으로 사람 이름이 입력되어 있기 때문에 TO_NUMBER로 형변환을 할 수 없습니다. ​ 따라서, TO_NUMBER 함수가 작동하지 않고 ORA-01722 오류가 발생합니다.
위의 예시는 scott 연습계정, emp 테이블의 hiredate 컬럼에 TO_NUMBER 형변환을 시도한 경우입니다. hiredate 컬럼에는 날짜 형식의 '입사일' 정보가 저장되어 있습니다. TO_NUMBER로 형변환을 할 수 없습니다. ​ 따라서, TO_NUMBER 함수가 작동하지 않고 ORA-01722 오류가 발생합니다.

 

 

2. 예제 : TO_NUMBER 함수를 사용해 숫자 타입으로 데이터 유형을 바꾼 결과를 출력하기

 

1) 예제 : VARCHAR2 타입 컬럼에 저장된 숫자들을 집계함수를 이용하여 통계치 구하기(평균, 합계 등)

아래의 예시는 scott 연습계정에 임의로 만든 resident 라는 테이블의 모든 컬럼, 모든 레코드를 검색한 결과입니다.

 

select * from resident;
-- resident라는 테이블은 scott 계정이 갖고있지 않습니다!

7명의 주민 정보(라고 하겠습니다)가 검색되었습니다. age는 나이를 의미하므로, age컬럼의 데이터 유형은 숫자 타입(number)이어야 할 것 같습니다. 또한 이 거주민 7명의 평균 연령을 알고 싶은 상황이라고 가정합니다.

 

DESC(DESCRIBE) 명령어로 위 resident 테이블의 구조를 살펴보니 아래와 같았습니다.

 

desc resident;
-- resident라는 테이블은 scott 계정이 갖고있지 않습니다!

 

 

 

거주민들의 나이를 나타내는 age 컬럼에는 숫자 데이터만 있는데, 데이터 유형이 VARCHAR2(문자열)로 되어 있습니다.

숫자가 든 원본 age 컬럼의 데이터 유형이나 내용을 바꾸지 않고,

age 컬럼의 데이터 유형을 숫자로 바꾼 자료를 컬럼의 형태로 출력하여 조회하려면

TO_NUMBER 함수를 사용합니다.

먼저 비교를 위해 age 컬럼, TO_NUMBER(age) 컬럼을 출력하는 SQL 문장을 구성합니다.

 

select rname as 주민성명,
       age,
       to_number(age),
       to_number('100')
from resident;
-- 주의 : scott 연습계정에는 resident 테이블이 없습니다.
-- 예시를 들기 위해 임의로 만든 테이블입니다.

위 이미지의 테이블을 살펴보면, AGE 컬럼과 TO_NUMBER(AGE) 컬럼이 가진 숫자는 일견 같아 보입니다.

하지만 AGE는 문자열인 '주민성명' 컬럼처럼 좌측으로 정렬되어 있고, 숫자인 TO_NUMBER(AGE)와 TO_NUMBER('100') 컬럼은 오른쪽 정렬되어 있습니다.

문자열과 숫자는 정렬 방향이 다르게 표시되기 때문입니다.

- 문자열(VARCHAR2) : 좌측 정렬

- 숫자(NUMBER) : 우측 정렬

따라서, AGE 컬럼의 데이터 유형은 문자열이지만, TO_NUMBER(AGE) 컬럼의 데이터 유형은 숫자임을 알 수 있습니다.

 

다음으로, AGE와 TO_NUMBER(AGE)에 대하여 집계함수를 이용해 전체 주민의 평균연령을 구하여 비교합니다.

 

select avg(age),
       avg(to_number(age))
from resident;
-- 주의 : scott 연습계정에는 resident 테이블이 없습니다.
-- 예시를 들기 위해 임의로 만든 테이블입니다.

 

데이터 유형이 문자열임이 분명한 AGE 컬럼을 투입했는데 오류가 발생하지 않고 AVG(AGE)가 계산되었습니다.

AVG(AGE)와 AVG(TO_NUMBER(AGE))의 계산결과가 같게 출력되었습니다.

AGE 컬럼에 대해 오라클 시스템이 연산하기 적절한 형태로 자동으로 형태를 변환시켜 주었기 때문입니다(묵시적형변환).

AGE와 같이 모든 데이터가 숫자인 경우에는 묵시적 형변환이 됩니다.

하지만 그렇지 않은 경우(문자나 날짜 형식의 데이터를 가진 컬럼)는 위에서 언급하였듯 오류(ORA-01722)가 발생합니다.

 

 

2) 예제 : VARCHAR2 타입 컬럼에 저장된 알파벳, 날짜를 TO_NUMBER에 투입한 결과

오류가 발생합니다.

ORA-01722 : 수치가 부적합합니다.

01722. 00000 - "invalid number"

*Cause : The specified number was invalid.

*Action : Specify a valid number.

 

1) 알파벳 문자를 투입하는 경우

 

select to_number('Luca Turilli')
from dual;

문자인 Luca Turilli를 숫자로 유형을 변환시키려고 시도했습니다.

오류가 발생합니다.

 

(2) 비교 : 숫자 형태의 문자('숫자')를 투입하는 경우

 

select to_number('200')
from dual;

 

물론 숫자 형태의 문자('200')를 투입하면 오류가 발생하지 않습니다.

 

 

(3) 알파벳 문자들을 포함한 컬럼을 투입하는 경우

 

select to_number(ename)
from emp;

 

ORA-01722 오류가 발생합니다.

* TO_NUMBER 외에도 TO_DATE, TO_CHAR 형변환함수가 있습니다.

 

 

TO_DATE : 문자 타입의 자료(0000-00-00, 0000/00/00 형식 등) 또는 컬럼을 날짜(DATE) 데이터 유형으로 바꿔 출력하는 형변환 함수


TO_DATE 함수는 주어진 문자열 또는 컬럼의 데이터를 날짜 포맷으로 변환하여 주는 형변환 함수입니다. 오라클 SQL 디벨로퍼에서 사용되는 데이터 유형(data type)에는 숫자(number), 문자(varchar2), 날짜(date) 등이 있습니다. 날짜는 'DATE' 라는 데이터 유형으로 저장됩니다.

 

 

1. TO_DATE 함수의 개념과 표현

1) TO_DATE 함수의 개념

TO_DATE 함수는 주어진 문자열 또는 컬럼의 데이터를 날짜 포맷으로 변환하여 줍니다.

* 그렇다고 아무 문자열(예 : 사람이름)이나 아무 숫자(예 : 주민등록번호)를 날짜로 바꾸어 주지는 못합니다.

예를 들어, VARCHAR2 유형의 컬럼에 날짜 형식으로 일괄적으로 변환 가능한 숫자들만 저장되어 있는 경우에 사용할 수 있습니다.

TO_DATE 함수는 그 컬럼의 원본을 변형시키지 않고도 날짜 포맷으로 전환된 컬럼을 출력할 수 있게 합니다.

 

 

2) TO_DATE 함수의 표현

(1) 기본식

가장 간단하게 표현하면 아래와 같이 서술할 수 있습니다.

 

select to_date('문자A', '날짜포맷Z')
from dual;


select to_date(컬럼이름B, '날짜포맷Z')
from 테이블이름;

그러나 중요한 것은, '문자' 또는 컬럼에 든 데이터의 형식(A, B)이 '날짜포맷'(Z)과 같은 형식이어야 한다는 것입니다.

만약 이 형식이 서로 불일치하면 오류(ORA-01861)가 발생하여, 날짜 포맷으로 바꿀 수 없기 때문입니다.

이 이슈를 고려하여 TO_DATE 함수의 사용 방식을 세분화하면 아래 '(2)'와 같습니다.

 

 

(2) 날짜 포맷 일치 조건 : 가상테이블 DUAL의 경우

위 기본식에서 A 또는 B항은 Z와 포맷이 같아야 합니다.

즉, 아래와 같은 형식으로 SQL 문장이 구성되어야 합니다

 

select to_date('0000-00-00', 'YYYY-MM-DD')
from dual;

select to_date('0000/00/00', 'YYYY/MM/DD')
from dual;

select to_date('00-0월-0000', 'DD-Mon-YYYY')
from dual;

만약 포맷이 불일치하면 TO_DATE는 성사되지 않습니다.(ORA-01861)

날짜 포맷(형식)의 예
문자 예시
YYYY/MM/DD
1989/11/24
YYYY-MM-DD
1989-11-24
DD-Mon-YYYY
24-11월-1989

 

이해를 돕기 위해 그림으로 표현하면 아래와 같습니다.

 

 

 

2. 예제 : TO_DATE 함수를 사용해 날짜 타입으로 데이터 유형을 바꾸어 출력하기

1) 예제 : 가상테이블 DUAL에서 날짜처럼 생긴 문자열(varchar2)을 날짜(date)로 데이터 유형을 변경하여 출력하기

(1) 예제 : 세 가지 형태의 문자를 날짜로 바꾸기

'1989-11-24', '1989/11/24', '24-11월-1989'라는 각자 다르게 생긴 문자를 TO_DATE 함수를 이용해 날짜로 변환합니다.

 

select to_date('1989-11-24', 'YYYY-MM-DD'),
       to_date('1989/11/24', 'YYYY/MM/DD'),
       to_date('24-11월-1989', 'DD-Mon-YYYY')
from dual;

, (콤마) 양쪽의 항이 같은 구조, 같은 기호로 연결되어야 하고 서로 자릿수도 같은, '같은 구조'여야 한다는 점에 유의합니다.

 

세 경우 모두, 출력된 결과는 89/11/24로 같습니다. 세 가지 값 모두 데이터 유형은 날짜입니다.

 

 

 

(2) 예제 : ORA-01861 오류가 발생하는 경우

만약 콤마 양변의 형식이 아래의 경우처럼 같지 않다면, ORA-01861 오류가 발생합니다.

아래의 SQL 문장은 오류를 발생시키는 문장입니다.

 

select to_date('1989-11-24', 'YYYY/MM/DD'),
       to_date('1989-11-24', 'YYYY-MM-DD'),
       to_date('1989-11-24', 'DD-Mon-YYYY')
from dual;

 

ORA-01861: 리터럴이 형식 문자열과 일치하지 않음

01861. 00000 - "literal does not match format string"

*Cause: Literals in the input must be the same length as literals in

the format string (with the exception of leading whitespace). If the

"FX" modifier has been toggled on, the literal must match exactly,

with no extra whitespace.

*Action: Correct the format string to match the literal.

문자열 형식이 일치하지 않기 때문입니다.

두 항의 형식이 일치하도록 수정해 주면 문제는 해결됩니다.

 

 

2) 날짜처럼 생긴 문자를 보유한 컬럼의 값들을 날짜로 바꾸어 출력하기

다음 이미지에서 보이는 테이블 'emp2'는 scott 연습계정에 없고, 이해를 돕기 위해 임의로 만든 테이블입니다.

(* scott 연습계정에는 emp2 테이블이 없습니다)

가장 오른쪽의 VARD 컬럼이 가진 데이터들은 날짜처럼 보이고(0000-00-00) 모두 동일한 형식으로 입력되어 있지만, 컬럼의 데이터 유형은 VARCHAR2이기 때문에 문자열입니다.

 

 

위 emp2 테이블에서

- ENAME, VARD 컬럼을 출력하고

- VARD컬럼에 TO_DATE함수를 적용하여 날짜로 변환한 컬럼도 함께 출력하려면

다음과 같은 SQL 문장을 입력해야 합니다.

 

select ename,
       vard,
       to_date(vard, 'YYYY-MM-DD')
from emp2;

 

VARD에 입력되어있는 데이터들이 '0000-00-00'의 형식으로 되어 있으므로, TO_DATE에서 지정할 날짜 포맷도 'YYYY-MM-DD'로 해 줍니다.

위 SQL 문장을 실행한 결과는 아래와 같습니다.

 

 

날짜 타입으로 변환된 값들이 가장 오른쪽 컬럼에 보입니다.

 

* TO_DATE 외에도 TO_NUMBER, TO_CHAR 과 같은 형변환함수가 있습니다.

 

 

TO_CHAR (1) 날짜를 다양한 형태의 문자로 바꾸는 방법 : 숫자·날짜 타입 데이터 또는 컬럼을 문자로 바꾸어 출력해 주는 형변환 함수 TO_CHAR


TO_CHAR는 숫자, 날짜 타입의 데이터나 컬럼을 원본 데이터를 바꾸지 않고 문자 타입으로 형변환하여 출력해 주는 함수입니다. 특히 날짜 타입의 데이터를 문자로 바꾸어 출력하고자 할 때, TO_CHAR를 사용하여 표현형(포맷)을 다양하게 지정할 수 있습니다.

- 직원의 입사일(hire date)자료로부터 입사년도(YYYY)만 필요할 때

- 생일이 11월인 사람만 검색하고 싶을 때

- 특정 연도 범위(ex. 2000년부터 2005년 사이)에 가입한 고객만 검색하고 싶을 때

...

와 같은 상황에서, TO_CHAR를 사용하여 날짜 타입 데이터를 필요한 문자 타입으로 바꾸어 출력하여, 원하는 형태로 자료를 조회할 수 있게 합니다.

1. TO_CHAR 함수의 개념과 표현

1) TO_CHAR 함수의 개념

TO_CHAR는 숫자 타입이나 날짜 타입의 데이터나 컬럼을 원본 데이터를 바꾸지 않고 문자 타입으로 형변환하여 출력해 주는 함수입니다.

 

 

2) TO_CHAR 함수의 표현 : 날짜 → 문자로 변환 시

(1) 기본식

 

select to_char(날짜타입데이터A, '출력형식Z')
from dual;

* A의 위치에 SYSDATE가 들어갈 수 있습니다.

select to_char(날짜타입컬럼이름B, '출력형식Z')
from 테이블이름;

* B의 위치에 데이터 유형이 날짜인 컬럼의 이름이 들어갈 수 있습니다.

 

(2) 주요 출력형식 요약 (*위 기본식에서의 Z) :

TO_CHAR로 날짜를 문자로 변환 시

날짜 포맷(형식)의 예
의미
문자 표시의 예 :
2020년 12월 23일(수) 오후 8시 12분 44초 기준
YYYY-MM-DD
연(4자리)-월(2자리)-일(2자리)
2020-12-23
YYYY 또는 RRRR
연도(4자리)
2020
YY 또는 RR
연도(2자리)
20
MM
월(2자리)
12
Mon
월(월이름 약칭)
12월 또는 DEC
Month
월(월이름 전체)
12월 또는 DECEMBER
DD
일(2자리)
23
DDTH
일(몇째 날)
23TH
DAY
일(요일)
HH24
시(24시간 기준)
20
HH
시(12시간 기준)
08
MI
분(2자리)
12
SS
초(2자리)
44

2. 예제 : TO_CHAR 함수를 이용한 연도 표기, 날짜 표기, 월 표기, 시간 표기

1) 연월일 표시 :

(1) 예제 : 가상테이블 DUAL을 사용하여, 현재 시스템시각(SYSDATE)을 다양한 형태의 연월일로 표시하기

YYYY/MM/DD 형식, DD-Mon-YYYY 형식 등, TO_CHAR로 연월일을 함께 표기하도록 할 수 있습니다.

아래의 SQL 문장은 같은 시스템 시각(SYSDATE)을 다양한 포맷으로 표현하도록 구성하였습니다.

 

select to_char(sysdate, 'YYYY-MM-DD') as ex1,
       to_char(sysdate, 'YYYY/MM/DD') as ex2,
       to_char(sysdate, 'DD-Mon-YYYY') as ex3,
       to_char(sysdate, 'DD-MONTH-YYYY') as ex4
from dual;

Ctrl+Enter로 위 SQL 문장을 실행한 결과는 다음과 같습니다.

같은 날짜(SYSDATE)를 입력했지만, 각기 지정한 대로 다른 형태의 문자로 출력되었습니다.

 

(2) 예제 : scott 계정, emp테이블에서 직원의 입사일(hiredate) 컬럼을 여러가지 형태의 문자열로 변환하여 반환하기

 

select to_char(hiredate, 'YYYY-MM-DD') as ex1,
       to_char(hiredate, 'YYYY/MM/DD') as ex2,
       to_char(hiredate, 'DD-Mon-YYYY') as ex3,
       to_char(hiredate, 'DD-MONTH-YYYY') as ex4
from emp;

 

hiredate 컬럼의 값들이 각기 다른 형태의 문자열로 이루어진 컬럼이 되어 출력되었습니다.

 

2) 연도만 표기하기

연월일이 모두 있는 날짜 타입 데이터를 투입했지만, 연도만 필요하여 연도부분만 추출해야 하는 경우가 있습니다.(ex. 입사년도, 출생년도, 사망년도, 근속연수 계산 등)

이 경우에는 출력될 포맷에 연도만 지정(YYYY, YY 등)해 주면 됩니다.

 

select sysdate as 현재날짜,
       to_char(sysdate, 'YYYY') as 연도YYYY_1,
       to_char(sysdate, 'RRRR') as 연도YYYY_2,
       to_char(sysdate, 'YY') as 연도YY_1,
       to_char(sysdate, 'RR') as 연도YY_2
from dual;

시스템 날짜(SYSDATE, 2020년 12월 23일)로부터 연도만 4자리 혹은 2자리가 출력되었습니다.

 

 

3) 월(달) 표기

연월일이 모두 있는 날짜 타입 데이터를 투입했지만, 연도만 필요하여 연도부분만 추출해야 하는 경우가 있습니다.(ex. 입사년도, 출생년도, 사망년도, 근속연수 계산 등)

이 경우에는 출력될 포맷에 연도만 지정(YYYY, YY 등)해 주면 됩니다.

select sysdate as 현재날짜,
       to_char(sysdate, 'MM') as 월_MM,
       to_char(sysdate, 'Mon') as 월_Mon,
       to_char(sysdate, 'Month') as 월_Month
from dual;

 

지정한 포맷을 따르는 월(달) 정보만 추출되어 나타났습니다.

현재 한글로 설정되어 있어서 월_MON과 월_MONTH가 똑같이 출력되었는데('12월'), 본디 영어로는

- Mon : 영문 달 이름의 3자리 약어(JAN, FEB, MAR, APR ...)

- Month : 영문 달 이름의 전체 이름(JANUARY, ....)

이 출력되는 것입니다.

4) 일, 일자, ~번째 날, 요일 표기

일자 뿐만 아니라 요일 정보도 TO_CHAR 함수를 사용하여 얻어낼 수 있습니다.

 

select sysdate as 현재날짜,
       to_char(sysdate, 'DD') as 일_DD,
       to_char(sysdate, 'DDTH') as 일_DDTH,
       to_char(sysdate, 'Day') as 요일 
from dual;

특히 요일과 관련된 분석이 필요할 경우에 TO_CHAR와 Day를 함께 사용할 수 있습니다.

 

 

5) 시간 표시, 시각, 시/분/초, 24시간, 12시간 형태로 표기

시간도 24시 형식, 12시 형식 어느 쪽으로든 설정하여 표시할 수 있습니다.

select sysdate as 현재날짜,
       to_char(sysdate, 'HH24:MI:SS') as H24,
       to_char(sysdate, 'HH12:MI:SS') as H12
from dual;

24시 형식, 그리고 12시 형식의 문자로 현재 시각(오후 8시 48분 1초)이 변환되어 출력된 모습입니다.

 

연월일과 마찬가지로 현재 시각도 시, 분, 초를 별도로 분리하여 나타낼 수도 있습니다.

 

select sysdate as 현재날짜,
       to_char(sysdate, 'HH24') as 시_24H,
       to_char(sysdate, 'HH') as 시_12H,
       to_char(sysdate, 'MI') as 분,
       to_char(sysdate, 'SS') asfrom dual;

 

 

현재(SYSDATE, 2020년 12월 23일 오후 7시 37분 55초)의 시, 분, 초가 각기 다른 컬럼의 문자로 분리되어 출력되었습니다.

* TO_CHAR 외에도 TO_NUMBER, TO_DATE 와 같은 형변환함수가 있습니다.

'Structured Query Language > Oracle SQL' 카테고리의 다른 글

Oracle SQL 기본_08  (0) 2023.04.24
Oracle SQL 기본_07  (0) 2023.04.21
Oracle SQL 기본_05  (0) 2023.04.20
Oracle SQL 기본_04  (0) 2023.04.20
Oracle SQL 기본_03  (0) 2023.04.20