DB/Doit!오라클로 배우는 데이터베이스 입문_개념정리

DB 기초 개념 정리_6)데이터 처리와 가공을 위한 오라클 함수_DoIt!오라클로 배우는 데이터 베이스 입문

김쟈워니 2024. 6. 2. 16:26
함수란?
  • 오라클 함수
    • 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어
    • 종류
      • 내장함수
        • 오라클에서 기본으로 제공함
      • 사용자 정의 함수
        • 사용자가 필요에 의해 직접 정의함
내장 함수의 종류
  • 입력 방식에 따라 데이터 처리에 사용하는 행이 달라짐
    • 단일행 함수
      • 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
    • 다중행 함수
      • 여러행이 입력되어 하나의 행으로 결과가 반환되는 함수
문자 함수
  • 문자 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할 때 사용
  • 대/소문자를 바꿔주는 UPPER,LOWER,INITCAP 함수
함수 설명
UPPER(문자열) 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환
LOWER(문자열) 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환
INTICAP(문자열) 괄호 안 문자 데이터중 첫글자는 대문자로,나머지 문자를 소문자로 변환 후 반환

 

  • UPPER,LOWER,INITCAP 함수 사용하기
SELECT ENAME,UPPER(ENAME),LOWER(ENAME),INITCAP(ENAME)
FROM EMP;

  • UPPER,LOWER,INITCAP 함수를 사용하려면 입력 데이터에 열 이름이나 데이터를 직접 지정해야함
    • 예를 들어 LIKE연산자를 사용하여 데이터를 출력할 경우 , 대/소문자가 다른 여러가지 경우 단어를 찾아내지 못함.
    • 이때 조건식 양쪽 항목의 문자열 데이터를 모두 대/소문자로 동일하게 바꿔서 비교한다면 실제 검색어의 대/소문자 여부와 상관없이 검색 단어와 일치한 문자열을 포함한 데이터를 찾을 수 있음
  • UPPER함수로 문자열 비교하기(사원인 이름이 SCOTT인 데이터 찾기)
SELECT *
    FROM EMP
    WHERE UPPER(ENAME)=UPPER('scott');
    ================================

SELECT*
    FROM EMP
    WHERE UPPER(ENAME) LIKE UPPER('%scott%');

문자열 길이 구하는 LENGTH 함수
  • 특정 문자열의 길이를 구할 때 LENGTH 함수 사용
  • 선택한 열의 문자열 길이 구하기
SELECT ENAME,LENGTH(ENAME)
    FROM EMP;

  • WHERE 절에서 LENGTH 함수 사용하기
    • LENGTH 함수는 숫자 비교도 가능
    • 사원 이름의 길이가 5이상인 행 출력하기
SELECT ENAME,LENGTH(ENAME)
    FROM EMP
    WHERE LENGTH(ENAME)>=5;

 

  • LENGTH 함수와 LENGTHB함수 비교하기
    • LENGTHB 함수는 바이트수를 반환
      • UTF-8에서 한글을 글자당 3BYTE
SELECT LENGTH('한글'),LENGTHB('한글')
    FROM DUAL;

문자열 일부를 추출하는 SUBSTR 함수
함수 설명
SUBSTR(문자열 데이터, 시작 위치, 추출 길이) 문자열 데이터의 시작 위치부터 추출 길이만큼 추출,
시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작
SUBSTR(문자열 데이터, 시작 위치) 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출,
시작 위치가 음수일 경우에는
마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출
  • SUBSTR 함수 사용하기
SELECT JOB, SUBSTR(JOB,1,2),SUBSTR(JOB,3,2),SUBSTR(JOB,5)
FROM EMP;

  • SUBSTR(JOB,1,2)의미
    • JOB열은 첫번째 글자부터 2개의 글자 출력
  • SUBSTR(JOB,3,2)의미
    • JOB열은 세번째 글자부터 2개의 글자 출력
  • SUBSTR(JOB,5) 의미
    • JOB열은 5번째 글자부터 끝까지 출력
  • SUBSTR함수와 다른 함수 함께 사용하기
SELECT JOB,
    SUBSTR(JOB,-LENGTH(JOB)),
    SUBSTR(JOB,-LENGTH(JOB),2),
    SUBSTR(JOB,-3)
    FROM EMP;

  • 바이트 수로 시작위치나 길이를 지정할 수 있는 SUBSTRB라는 함수도 있음
문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR함수
  • 문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어있는지 알고자 할 때 INSTR함수 사용
  • INSTR함수는 총 4개의 입력 값을 지정할 수 있음
    • 최소 두개의 입력 값
      • 원본 문자열 데이터, 원본 문자열 데이터에서 찾으려는 문자
INSTR([대상 문자열 데이터(필수)],
	[위치를 찾으려는 부분 문자(필수)],
    [위치 찾기를 시작할 대 문자열 데이터 위치(선택, 기본값은 1)],
    [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)])
  • 특정 문자 위치 찾기 예시
    • INSTR 함수로 문자열 데이터에서 문자열 찾기
SELECT INSTR('HELLO, ORACLE!','L') AS INSTR_1,
    INSTR('HELLO, ORACLE!','L',5) AS INSTR_2,
    INSTR('HELLO, ORACLE!','L',2,2) AS INSTR_3
    FROM DUAL;

  • INSTR('HELLO, ORACLE!','L'): 시작위치와 몇번째 L인지 정하지 않음
    • 첫번째 부터 검색했을 때 처음으로 나오는 L 을 검색
  • INSTR('HELLO, ORACLE!','L',5)
    • 다섯번째 글자 O 부터 L을 찾음
  • INSTR('HELLO, ORACLE!','L',2,2)
    • 2번째 글자 E 부터 시작해서 두번째로 나온 L 을 찾음
  • INSTR 함수의 세번째 입력 데이터(위치 찾기를 시작하는 위치값)를 음수로 쓰면 오른쪽 끝부터 왼쪽 방향으로 검색
  • 찾고자 하는 문자가 문자열 데이터에 포함되어있지 않다면 0을 반환
  • INSTR 함수를 LIKE 와 비슷한 용도로 사용하기
    • 자주 사용하는 방식은 아니지만 이렇게 응용할 수 있다 정도로 확인하기
SELECT *
    FROM EMP
        WHERE INSTR(ENAME,'S')>0;
-----------------------------------
SELECT *
    FROM EMP
        WHERE ENAME LIKE '%S%';

특정 문자를 다른 문자로 바꾸는 REPLACE 함수 
  • 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우에 유용한 함수
  • REPLACE 함수 기본형식
REPLACE([문자열 데이터 또는 열 이름(필수)],[찾는 문자(필수)],[대체할 문자(선택)]
  • REPLACE함수로 문자열 안에 있는 특정 문자 바꾸기 예시
SELECT '010-1234-5678' AS REPLACE_BEFORE,
    REPLACE('010-1234-5678','-',' ') AS REPLACE_1,
    REPLACE('010-1234-5678','-') AS REPLACE_2
    FROM DUAL;

  • 별칭 REPLACE_1 은 - 문자를 한 칸 공백으로 바꾸어 출력하고 있음
  • 별칭 REPLACE_2 는 대체할 문자를 지정하지 않아 - 문자가 삭제된 상태로 출력
데이터의 빈 공간을 특정 문자로 채우는 LPAD,RPAD 함수
  • LPAD와 RPAD는 각각 LEFT PADDING,RIGHT PADDING을 뜻함
    • 데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수
    • LPAD는 남은 빈 공간을 왼쪽에 채우고, RPAD는 오른쪽에 채움
    • 만약 빈 공간에 채울 문자를 지정하지 않으면 LPAD,RPAD함수는 빈공간의 자릿수만큼 공백문자를 띄움
  • LPAD,RPAD 함수의 기본 형식
LPAD([문자열 데이터 또는 열이름(필수)],[데이터의 자릿수(필수)],[빈 공간에 채울 문자(선택)])
RPAD([문자열 데이터 또는 열이름(필수)],[데이터의 자릿수(필수)],[빈 공간에 채울 문자(선택)])
  • LPAD,RPAD 함수 사용하여 출력하기
SELECT 'Oracle',
    LPAD('Oracle',10,'#') AS LPAD_1,
    RPAD('Oracle',10,'*') AS RPAD_1,
    LPAD('Oracle',10) AS LPAD_2,
    RPAD('Oracle',10) AS RPAD_2
    FROM DUAL;

  • 데이터 자릿수에 10을 지정하여 데이터가 10자리가 됨
    • Oracle이라는 여섯 글자를 제외한 남은 자리는 함수에 따라 왼쪽과 오른쪽에 각각 지정한 #,*이 문자로 채워져 있음
    • LPAD_2와 RPAD_2는 3번째 입력 값이 없기 때문에 빈 공백 문자열로 띄어쓰기가 처리되어 자릿수를 맞추고 있음
  • RPAD함수를 사용하여 개인정보 뒷자리 *표시로 출력하기
SELECT 
    RPAD('971225-',14,'*') AS RPAD_JMNO,
    RPAD('010-1234-',13,'*') AS RPAD_PHONE
    FROM DUAL;

 

두 문자열 데이터를 합치는 CONCAT함수
  • CONCAT 함수는 두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할
  • 두 개의 입력 데이터를 지정하고 열이나 문자열 데이터를 모두 지정할 수 있음
  • 두 열 사이에 콜론(:)넣고 연결하기
SELECT CONCAT(EMPNO,ENAME),
        CONCAT(EMPNO, CONCAT(':',ENAME))
        FROM EMP
        WHERE ENAME='SCOTT';

  • 문자열 데이터를 연결하는 || 연산자
    • ||연산자는 CONCAT함수와 유사하게 열이나 문자열을 연결
  • || 연산자로 문자열 연결하여 출력하기
SELECT EMPNO||ENAME,
        EMPNO||':'||ENAME
        FROM EMP;

특정 문자를 지우는 TRIM,LTRIM,RTRIM 함수
  • 문자열 데이터 내에서 특정 문자를 지우기 위해 사용
    • 원본 문자열 데이터를 제외한 나머지 데이터는 생략 할수 있음
    • 삭제할 문자가 생략될 경우에 기본적으로 공백을 제거 
      • 옵션
        • 왼쪽에 있는 글자를 지우는 LEADING
        • 오른쪽에 있는 글자를 지우는 TRAILING
        • 양 옆에 글자를 모두 지우는 BOTH
  • TRIM 함수의 기본 형식
TRIM([삭제 옵션(선택)][삭제할 문자(선택)]FROM [원본 문자열 데이터(필수])
  • TRIM 함수 사용(삭제할 문자 없을 때) 공백 제거하여 출력
SELECT '['||TRIM(' _ _Oracle_ _ ')||']' AS TRIM,
        '['||TRIM(LEADING FROM' _ _Oracle_ _ ')||']' AS TRIM_LEADING,
        '['||TRIM(TRAILING FROM' _ _Oracle_ _ ')||']' AS TRIM_TRAILING,
        '['||TRIM(BOTH FROM' _ _ Oracle_ _ ')||']' AS TRIM_BOTH
        FROM DUAL;

  • TRIM 함수 사용(삭제할 문자가 있을 때) _ 삭제 후 출력하기
SELECT '['||TRIM('_'FROM '_ _Oracle_ _')||']' AS TRIM,
        '['||TRIM(LEADING '_' FROM'_ _Oracle_ _')||']' AS TRIM_LEADING,
        '['||TRIM(TRAILING '_' FROM'_ _Oracle_ _')||']' AS TRIM_TRAILING,
        '['||TRIM(BOTH '_' FROM'_ _ Oracle_ _')||']' AS TRIM_BOTH
        FROM DUAL;

  • LTRIM, RTRIM 함수의 기본 사용법
    • LTRIM,RTRIM 함수는 각각 왼쪽, 오른쪽의 지정 문자를 삭제하는데 사용
    • 삭제할 문자를 지정하지 않을 경우 공백 문자 삭제
      • 삭제할 문자 하나만 지정하는 것이 아니라 여러 문자 지정이 가능
  • LTRIM, RTRIM 함수의 기본형식
LTRIM([원본 문자열 데이터(필수)]), [삭제할 문자 집합(선택)])
RTRIM([원본 문자열 데이터(필수)]), [삭제할 문자 집합(선택)])
  • TRIM,LTRIM,RTRIM 함수 사용하여 문자열 출력하기
SELECT '['||TRIM(' _Oracle_ ')||']' AS TRIM,
        '['||LTRIM(' _Oracle_ ')||']' AS LTRIM,
        '['||LTRIM('<_Oracle_>','_<')||']' AS LTRIM_2,
        '['||RTRIM(' _Oracle_ ')||']' AS RTRIM,
        '['||RTRIM('<_Oracle_>','>_')||']' AS RTRIM_2
        FROM DUAL;

  • 삭제할 문자를 지정하지 않을 경우 각각 함수 종류에 따라 양쪽, 왼쪽, 오른쪽 공백이 제거
  • LTRIM,RTRIM 을 사용한 예시에서는 삭제 대상이 문자일 경우 해당 문자의 순서와 반복을 통해 만들어 낼 수 있는 모든 조합이 각각 왼쪽, 오른쪽 부터 삭제되어 갑니다.
    • LTRIM_2의 경우 <_문자열이 _,< 문자의 조합으로 표현 가능한 문자이므로 삭제
      • Oracle의 문자 O에서 <_로 조합 할수 없는 문자가 시작되므로 LTRIM을 통한 삭제 작업은 끝나게 됨
  • 보통 실무에서 TRIM 함수는 검색 기준이 되는 데이터에 혹시나 들어 있을지도 모르는 양쪽 끝의 공백을 제거할 때 많이 사용
숫자 데이터를 연산하고 수치를 조정하는 숫자 함수
  • 비교적 많이 사용되는 숫자 함수의 종류
함수 설명
ROUND 지정된 숫자의 특정 위치에서 반올림한 값을 반환
TRUNC 지정된 숫자의 특정 위치에서 버림한 값을 반환
CEIL 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
FLOOR 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
MOD 지정된 숫자를 나눈 나머지 값을 반환
  • 특정 위치에서 반올림하는 ROUND 함수
    • TRUNC함수와 함께 가장 많이 사용하는 숫자 함수
      • 특정 숫자를 반올림하되 반올림할 위치를 지정할 수 있음
      • 반올림할 위치를 지정하지 않으면 소수점 첫째 자리에서 반올림한 결과가 반환
  • ROUND 함수의 기본형식
ROUND([숫자(필수)],[반올림 위치(선택)])
  • ROUND 함수를 사용하여 반올림된 숫자 출력하기 예시
SELECT ROUND(1234.5678) AS ROUND,
        ROUND(1234.5678,0) AS ROUND_0,
        ROUND(1234.5678,1) AS ROUND_1,
        ROUND(1234.5678,2) AS ROUND_2,
        ROUND(1234.5678,-1) AS ROUND_MINUS1,
        ROUND(1234.5678,-2) AS ROUND_MINUS2
        FROM DUAL;

  • 반올림 위치를 지정하지 않은 반환 값은 반올림 위치를 0으로 지정한 것과 같은 결과를 출력
    • 반올림 위치 값이 0에서 양수로 올라가면 반올림 위치가 한자리씩 더 낮은 소수점 자리를 향함
    • 0에서 음수로 내려가면 자연수 쪽으로 한자리 씩 위로 반올림하게 됨
      • 0은 소수점 첫째 자리 반올림
      • 1은 소수점 둘째 자리 반올림
      • 2는 소수점 셋째 자리 반올림
      • -1은 자연수 첫째 자리 반올림
      • -2는 자연수 둘째 자리 반올림
특정 위치에서 버리는 TRUNC 함수
  • 지정된 자리에서 숫자를 버림 처리하는 함수
    • ROUND함수와 마찬가지 방식으로 버림처리할 자릿수 지정이 가능
    • 반올림 위치를 지정하지 않으면 소수점 첫째자리에서 버림 처리
  •  TRUNC 함수의 기본형식
TRUNC([숫자(필수)],[버림 위치(선택)])
  • TRUNC 함수의 사용 예시
SELECT TRUNC(1234.5678) AS TRUNC,
    TRUNC(1234.5678,0) AS TRUNC_0,
    TRUNC(1234.5678,1) AS TRUNC_1,
    TRUNC(1234.5678,2) AS TRUNC_2,
    TRUNC(1234.5678,-1) AS TRUNC_MINUS1,
    TRUNC(1234.5678,-2) AS TRUNC_MINUS2
    FROM DUAL;

지정한 숫자와 가까운 정수를 찾는 CEIL,FLOOR 함수
  • CEIL 함수와 FLOOR 함수는 각각 입력된 숫자와 가까운 큰 정수, 작은 정수를 반환하는 함수
  • CEIL,FLOOR 함수 
CEIL([숫자(필수)])
FLOOR([숫자(필수)])
  • CEIL,FLOOR 함수로 숫자 출력하기
SELECT CEIL(3.14),
    FLOOR(3.14),
    CEIL(-3.14),
    FLOOR(-3.14)
    FROM DUAL;

숫자를 나눈 나머지 값을 구하는 MOD 함수
  • 특정 숫자로 나눈 나머지를 구하는 MOD 함수
  • MOD함수의 기본 형식
MOD([나눗셈 될 숫자(필수)],[나눌 숫자(필수)])
  • MOD 함수를 사용하여 나머지 값 출력하기
SELECT MOD(15,6),
    MOD(10,2),
    MOD(11,2)
    FROM DUAL;

날짜 데이터를 다루는 날짜 함수
  • 날짜형 데이터,즉 DATE 형 데이터 간단한 연산 가능
연산 설명
날짜데이터 + 숫자 날짜 데이터보다 숫자만큼 일수 이후의 날짜
날짜데이터-숫자 날짜 데이터보다 숫자만큼 일수 이전의 날짜
날짜 데이터 - 날짜 데이터 두 날짜 데이터 간의 일수 차이
날짜 데이터 + 날짜 데이터 연산 불가, 지원하지 않음
  • 대표 함수 SYSDATE
    • 별 다른 입력 데이터 없이, 오라클 데이터베이스 서버가 놓인 OS(Operating System: 운영체제)의 현재 날짜와 시간 
SELECT SYSDATE AS NOW,
        SYSDATE-1 AS YESTERDAY,
        SYSDATE+1 AS TOMORROW
        FROM DUAL;

 

몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수
  • ADD_MONTHS
    • 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수
  • ADD_MONTHS의 기본형식
ADD_MONTHS([날짜 데이터(필수)],[더할 개월 수(정수)(필수)])
  • SYSDATE 와 ADD_MONTHS함수로 3개월 후 날짜 구하기
SELECT SYSDATE,
    ADD_MONTHS(SYSDATE,3)
    FROM DUAL;

 

  • 입사 10주년이 되는 사원들 데이터 출력하기
SELECT EMPNO,ENAME,HIREDATE,
    ADD_MONTHS(HIREDATE,120) AS WORK10YEAR
    FROM EMP;

 

두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수
  • MONTHS_BETWEEN 함수는 두 개의 날짜 데이터를 입력하고 두 날짜 간의 개월수 차이를 구하는데 사용
  • MONTHS_BETWEEN 함수의 기본 형식
MONTHS_BETWEEN([날짜 데이터1(필수)],[날짜 데이터2(필수)])
  • HIREDATE와 SYSDATE 사이의 개월수를 MONTHS_BETWEEN함수로 출력하기
SELECT EMPNO,ENAME,HIREDATE,SYSDATE,
    MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
    MONTHS_BETWEEN(SYSDATE,HIREDATE) AS MONTHS2,
    TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE))AS MONTHS3
    FROM EMP;

  • 개월 수 차이가 소수점 단위까지 결과가 나오므로 TRUNC 함수를 조합하여 개월 수 차이를 정수로 출력할 수 있음
돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY,LAST_DAY 함수
  • 입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환
  • NEXT_DAY 함수의 기본 형식
NEXT_DAY([날짜 데이터(필수)],[요일 문자(필수)])
  • LAST_DAY 함수의 기본 형식
LAST_DAY([날짜 데이터(필수)])
  • NEXT_DAY와 LAST_DAY함수 사용하여 출력하기
SELECT SYSDATE,
    NEXT_DAY(SYSDATE,'월요일'),
    LAST_DAY(SYSDATE)
    FROM DUAL;

날짜의 반올림, 버림을 받는 ROUND,TRUNC 함수
  • 숫자 데이터의 반올림, 버림 처리에 사용한 ROUND,TRUNC 함수는 날짜 데이터를 입력데이터로 사용할 수도 있음
    • 이때는 소수점의 위치 정보를 입력하지 않고 반올림, 버림의 기준이 될 포맷(format)값을 지정해 줌
입력 데이터 종류 사용 방식
숫자 데이터 ROUND([숫자(필수)],[반올림 위치])
TRUNC([숫자(필수)],[버림 위치])
날짜 데이터 ROUND([날짜데이터(필수)],[반올림 기준 포맷])
TRUNC([날짜데이터(필수)],[버림 기준 포맷])
  • 날짜 데이터를 사용할 때 기준 포맷 값
포맷 모델 기준 단위
CC, SCC 날짜를 가장 가까운 세기의 시작 연도로 반올림
네 자리 연도의 끝 두자리를 기준으로 사용
(2016이면 2050 이하이므로, 21세기의 시작연도를 출력)
SYYYY,YYYY,YEAR,SYEAR,YYY,YY,Y 날짜 데이터의 해당 연/월/일의 7월 1을 기준
7월 1일과 같거나 크면 다음 해로 1월 1일로 처리
7월 1일보다 작으면 해당 해의 1월 1일로 처리 
IYYY,IYY,IY,I ISO8601에서 제정한 날짜 기준년도 포맷을 기준
Q 각 분기의 두번째 달의 16일 기준
MONTH,MON,MM,RM 각 달의 16일 기준
WW 해당 연도의 몇 주(1~53번째 주)를 기준
IW ISO 8601에서 제정한 날짜 기준 해당 연도의 주(week)를 기준
W 해당 월의 주(1~5번째 주)를 기준
DDD,DD,J 해당 일의 정오(12:00:00)를 기준
DAY,DY,D 한 주가 시작되는 날짜를 기준
HH,HH12,HH24 해당일의 시간을 기준
MI 해당일 시간의 분을 기

 

  • ROUND 함수 사용하여 날짜 데이터 출력하기
SELECT SYSDATE,
    ROUND(SYSDATE,'CC') AS FORMAT_CC,
    ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY,
    ROUND(SYSDATE, 'Q')AS FORMAT_Q,
    ROUND(SYSDATE, 'DDD') AS FORMAT_DDD,
    ROUND(SYSDATE, 'HH') AS FORMAT_HH
    FROM DUAL;

  • TRUNC 함수 사용하여 날짜 데이터 출력하기
SELECT SYSDATE,
    TRUNC(SYSDATE,'CC') AS FORMAT_CC,
    TRUNC(SYSDATE,'YYYY') AS FORMAT_YYYY,
    TRUNC(SYSDATE,'Q') AS FORMAT_Q,
    TRUNC(SYSDATE,'DDD') AS FORMAT_DDD,
    TRUNC(SYSDATE,'HH') AS FORMAT_HH
    FROM DUAL;

자료형을 변환하는 형 변환 함수

  • 오라클에서는 저장할 데이터 종류, 즉 자료형을 다양하게 제공
  • 지정된 자료형을 필요에 따라 바꿔주어야 할 때가 있는데 지정된 자료형을 바꿔주는 함수를 형 변환 함수라고 함
  • 숫자와 문자열(숫자)를 더하여 출력하기
SELECT EMPNO,ENAME,EMPNO+'500'
FROM EMP WHERE ENAME ='SCOTT';

  • 작은 따옴표로 묶인 500 은 문자데이터지만 숫자 자료형인 EMPNO 열 값과 수치 연산이 가능한 것은 '자동 형 변환' 이라고 불리는 암시적 형 변환(implicit type conversion) 이 발생했기 때문
  • 숫자로 인식 가능한 문자 데이터가 자동으로 숫자로 바뀐후 연산이 수행된 것
  • 문자열(문자)과 숫자를 더하여 출력
SELECT 'ABCD' + EMPNO,EMPNO
FROM EMP
WHERE ENAME='SCOTT';

  • ORA-01822 수치 부적합 오류 발생
  • 숫자처럼 생긴 문자데이터는 숫자로 바꿔 주지만 그 외의 경우는 잘 동작하길 기대하기 어려움

 

  • 명시적 형 변환(explicit type conversion)
종류 설명
TO_CHAR 숫자 또는 날짜 데이터를 문자 데이터로 변환
TO_NUMBER 문자 데이터를 숫자 데이터로 변환
TO_DATE 문자 데이터를 날짜 데이터로 변환
  • 형 변환 함수를 사용하면 숫자데이터와 문자데이터, 문자데이터와 날짜데이터간의 변환이 가능

날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수

 TO_CHAR([날짜데이터(필수)],'[출력되길 원하는 문자형태(필수)]')
  • 원하는 출력 형태로 날짜 출력하기
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간
FROM DUAL;

  • 자주 사용하는 날짜 표현 형식
형식 설명
CC 세기
YYYY,RRRR 연(4자리 숫자)
YY,RR 연(2자리 숫자)
MM 월(2자리 숫자)
MON 월(언어별 월 이름 약자)
MONTH 월(언어별 월 이름 전체)
DD 일(2자리 숫자)
DDD 1년 중 며칠(1~366)
DY 요일(언어별 요일 이름 약자)
DAY 요일(언어별 요일 이름 전체)
W 1년 중 몇 번째주(1~53)
  • 월과 요일을 다양한 형식으로 출력하기
SELECT SYSDATE,
    TO_CHAR(SYSDATE,'MM') AS MM,
    TO_CHAR(SYSDATE,'MON')AS MON,
    TO_CHAR(SYSDATE,'MONTH') AS MONTH,
    TO_CHAR(SYSDATE,'DD') AS DD,
    TO_CHAR(SYSDATE,'DY')AS DY,
    TO_CHAR(SYSDATE,'DAY') AS DAY
    FROM DUAL;

  • 특정 언어에 맞춰서 날짜 출력하기
TO_CHAR([날짜 데이터(필수)],'[출력되길 원하는 문자 형태(필수)]',
'NLS_DATE_LANGUAGE=language'(선택))
  • 여러 언어로 날짜(월) 출력하기
SELECT SYSDATE,
    TO_CHAR(SYSDATE,'MM') AS MM,
    TO_CHAR(SYSDATE,'DD') AS DD,
    TO_CHAR(SYSDATE,'MON','NLS_DATE_LANGUAGE=KOREAN') AS DY_KOR,
    TO_CHAR(SYSDATE,'MON','NLS_DATE_LANGUAGE=JAPANESE') AS DY_JPN,
    TO_CHAR(SYSDATE,'MON','NLS_DATE_LANGUAGE=ENGLISH')AS DY_ENG,
    TO_CHAR(SYSDATE, 'MONTH','NLS_DATE_LANGUAGE = KOREAN') AS DAY_KOR,
    TO_CHAR(SYSDATE, 'MONTH','NLS_DATE_LANGUAGE= JAPANESE') AS DAY_JPN,
    TO_CHAR(SYSDATE,'MONTH','NLS_DATE_LANGUAGE=ENGLISH')AS DAY_ENG
    FROM DUAL;

  • 여러 언어로 날짜(요일) 출력하기
SELECT SYSDATE,
    TO_CHAR(SYSDATE,'MM') AS MM,
    TO_CHAR(SYSDATE,'DD') AS DD,
    TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=KOREAN') AS DY_KOR,
    TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=JAPANESE') AS DY_JPN,
    TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=ENGLISH')AS DY_ENG,
    TO_CHAR(SYSDATE, 'DAY','NLS_DATE_LANGUAGE = KOREAN') AS DAY_KOR,
    TO_CHAR(SYSDATE, 'DAY','NLS_DATE_LANGUAGE= JAPANESE') AS DAY_JPN,
    TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=ENGLISH')AS DAY_ENG
    FROM DUAL;

  • 시간 형식 지정하여 출력하기
형식 설명
HH24 24시간으로 표현한 시간
HH,HH12 12시간으로 표현한 시간
MI 분
SS 초
AM,PM,A.M.,P.M. 오전, 오후 표시
  • SYSDATE 시간 형식 지정하여 출력하기
SELECT SYSDATE,
    TO_CHAR(SYSDATE,'HH24:MI:SS') AS HH24MISS,
    TO_CHAR(SYSDATE,'HH12:MI:SS: AM') AS HHMISS_AM,
    TO_CHAR(SYSDATE,'HH:MI:SS P.M.') AS HHMISS_PM
    FROM DUAL;

  • TO_CHAR 함수로 숫자 데이터를 문자 데이터로 변환할 경우 지정할 수 있는 형식
형식 설명
9 숫자의 한 자리를 의미함(빈 자리를 채우지 않음)
0 빈 자리를 0으로 채움을 의미함
$ 달러($) 표시를 붙여서 출력함
L L(Locale) 지역 화폐 단위 기호를 붙여서 출력함
. 소수점을 표시함
, 천 단위의 구분 기호를 표시함
  • 여러가지 숫자 형식을 사용하여 급여 데이터 출력하기
SELECT SAL,
    TO_CHAR(SAL,'$999,999') AS SAL_$,
    TO_CHAR(SAL,'L999,999') AS SAL_L,
    TO_CHAR(SAL,'999,999.00')AS SAL_1,
    TO_CHAR(SAL,'000,999,999,00')AS SAL_2,
    TO_CHAR(SAL,'000999999.99')AS SAL_3,
    TO_CHAR(SAL,'999,999,00') AS SAL_4
    FROM EMP;

문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

숫자 데이터와 '숫자 처럼 생긴' 문자 데이터 간의 산술 연산이 수행 될 경우, 문자 데이터는 자동으로 숫자 데이터로 형 변환이 일어나 연산이 가능, 이를 암시적 형 변환

 

문자 데이터와 숫자 데이터를 연산으로 출력(암시적 형변환)

SELECT 1300-'1500',
        '1300'+1500
        FROM DUAL;

 

하지만, 숫자사이에 쉼표(,) 가 들어가면 숫자로 변환 되지 않음

가공된 문자 데이터로 저장되어 있고 그 데이터를 산술연산에 사용하고자 할 경우, 문자 데이터를 숫자 형태로 강제로 인식시켜주어야함

이때 사용하는 함수가 바로 TO_NUMBER 함수

  • TO_NUMBER 함수의 기본형식
TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자 형태(필수)]')
  • TO_NUMBER 함수로 연산하여 출력하기
SELECT TO_NUMBER('1,300','999,999') - TO_NUMBER('1,500','999,999') AS TO_NUMBER_CAL
FROM DUAL;

문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

TO_CHAR 함수를 사용하여 날짜 데이터를 문자 데이터로 변환했듯이 TO_DATE 함수를 사용하면 문자열 데이터를 날짜 데이터로 바꿔줄 수 있음.

  • TO_DATE 함수의 기본형식
TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜형태(필수)]'

 

먼저 날짜 데이터로 변환하려면 문자열 데이터(또는 열) 을 입력한 후 그 데이터를 날자 형태로 인식 시킬 형식을 지정

  • TO_DATE 함수로 문자 데이터를 날짜 데이터로 변환하기
SELECT TO_DATE('2018-07-14','YYYY-MM-DD') AS TODATE1,
    TO_DATE('20180714','YYYY-MM-DD') AS TODATE2
    FROM DUAL;

날짜 데이터끼리는 간단한 연산이 가능

  • 1981년 6월 1일 이후에 입사한 사원 정보 출력하기
SELECT *
    FROM EMP
    WHERE HIREDATE>TO_DATE('1981/06/01','YYYY/MM/DD');

날짜 데이터 형식을 지정할때, YYYY,RRRR,YY,RR 도 사용할 수 있음

 다만, YY,RR 을 사용상 주의를 기울여야 함

SELECT TO_DATE('49/12/10','YY/MM/DD') AS YY_YEAR_49,
        TO_DATE('49/12/10', 'RR/MM/DD') AS RR_YEAR_49,
        TO_DATE('50/12/10', 'YY/MM/DD') AS YY_YEAR_50,
       TO_DATE('50/12/10','RR/MM/DD') AS RR_YEAR_50,
       TO_DATE('51/12/10','YY/MM/DD') AS YY_YEAR_51,
       TO_DATE('51/12/10','RR/MM/DD') AS RR_YEAR_51
       FROM DUAL;

  • YY는 어떤 두자리수가 입력되어도 현 시점의 연도와 동일한 연도로 계산 됨
  • RR은 입력된 연도가 50을 넘으면 기본적으로 이전 세기로 해석, 50이 안되면 가까운 세기를 계산

NULL 처리 함수

데이터가 NULL 이면 산술 연산자나 비교 연산자가 우리가 예상한 대로 동작하지 않음

특정 열의 데이터가 NULL 일 경우에 연산 수행을 위해 데이터를 NULL 이 아닌 값으로 대체해 주어야함.

NVL 함수와 NVL2 함수를 사용할 수 있음

NVL 함수의 기본 사용법

NVL함수의 기본형식

NVL([NULL 인지 여부를 검사할 데이터 또는 열(필수)],[앞의 데이터가 NULL 일 경우 반환할 데이터(필수)])

NVL 함수는 첫 번째 입력 데이터가 NULL 이 아니면 그 데이터를 그대로 반환하고 NULL 이라면 두 번째 입력데이터에 지정한 값을 반환

  • NVL 함수를 사용하여 출력
SELECT EMPNO,ENAME,SAL,COMM,SAL+COMM,
    NVL(COMM,0),
    SAL+NVL(COMM,0)
    FROM EMP;

  • NVL 함수를 통해서 COMM 열 값의 NULL 인 데이터를 0으로 대체하여 연산이 가능해짐
  • NVL 함수는 NULL 처리를 위해 사용

NVL2 함수의 기본 사용법

NVL2 함수는 NVL 함수와 비슷하지만 데이터가 NULL 이 아닐때 반환할 데이터를 추가로 지정해 줄 수 있음

NVL2함수의 기본형식

NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)],
	[앞 데이터가 NULL 이 아닐 경우 반환할 데이터 또는 계산식(필수)],
    [앞 데이터가 NULL 일 경우 반환할 데이터 또는 계산식(필수)]
  • NVL2 함수를 사용하여 출력
SELECT EMPNO,ENAME,COMM,
    NVL2(COMM,'O','X'),
    NVL2(COMM,SAL*12+COMM,SAL*12) AS ANNSAL
    FROM EMP;

 NVL2 함수는 NVL함수와 달리 NULL 이 아닌 경우에 반환 데이터까지 지정할 수 있으므로 좀 더 다양한 용도로 활용 가능

상황에 따라 다른 데이터를 반환하는 DECODE함수와 CASE 문

특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할지 정할 때는 DECODE 함수 또는 CASE 문을 사용

DECODE 함수

DECODE 함수는 기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 다른 결과 값을 보내는 함수

DECODE 함수의 기본형식

DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
	[조건1],[데이터가 조건1과 일치할 때 반환할 결과],
    [조건2],[데이터가 조건 2와 일치할 때 반환할 결과],
    ...
    [조건 n],[데이터가 조건 n 과 일치할 때 반환할 결과],
    [위 조건1 ~ 조건n과 일치한 경우가 없을때 반환할 결과])
  • DECODE 함수를 사용하여 JOB데이터 열에 직책에 따른 인상된 급여 출력하기
SELECT EMPNO, ENAME,JOB,SAL,
    DECODE(JOB,
            'MANAGER', SAL*1.1,
            'SALESMAN',SAL*1.05,
            'ANALYST', SAL,
            SAL*1.03) AS UPSAL
            FROM EMP;

DECODE 함수는 단일행 함수

DECODE 함수의 맨 마지막 데이터, 즉 조건에 해당하는 값이 없을 때 반환 값을 지정하지 않으면 NULL 이 반환됨.

 

CASE문

CASE문은 DECODE 함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용

기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE함수와 달리 CASE문은 각 조건에 사용하는 데이터가 서로 상관없어도 됨.

기준 데이터 값이 같은(=) 데이터 외에 다양한 조건을 사용할 수 있음.

 

DECODE 함수는 모두 CASE 문으로 바꿀 수 있으나 CASE문은 DECODE 함수가 표현할 수 없는 방식도 지원하므로 그 역은 성립하지 않을 수 있음

즉, CASE문의 범용성이 더 높음

  • CASE 문의 기본형식
CASE[검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
	WHEN[조건 1] THEN[조건 1의 결과 값이 TRUE일 때, 반환할 결과]
    WHEN[조건 2] THEN[조건 2의 결과 값이 TRUE 일 때, 반환할 결과]
    ...
    WHEN[조건 N] THEN[조건 N의 결과 값이 TRUE 일때, 반환할 결과]
    ELSE[위 조건1~조건 N 과 일치하는 경우가 없을 때 반환할 결과]
END
  • 작성 형식 면에서 WHEN 이나 THEN, ELSE를 사용하는 CASE 문은 DECODE 함수보다 더 프로그래밍 언어적인 표현을 사용
  • DECOD 함수와 같은 방식으로 CASE문 사용하기 
SELECT EMPNO,ENAME,JOB,SAL,
    CASE JOB
        WHEN 'MANAGER' THEN SAL*1.1
        WHEN 'SALESMAN' THEN SAL*1.05
        WHEN 'ANALYST' THEN SAL
        ELSE SAL*1.03
        END AS UPSAL
        FROM EMP;

 

기준 데이터 없이 조건식만으로 CASE 문 사용하기

CASE 문은 DECODE 함수와 달리 비교할 기준 데이터를 지정하지 않고 값이 같은 조건 이외의 조건도 사용할 수 있음

  • 열 값에 따라서 출력값이 달라지는 CASE문
SELECT EMPNO,ENAME,COMM,
    CASE
        WHEN COMM IS NULL THEN '해당사항 없음'
        WHEN COMM = 0 THEN '수당 없음'
        WHEN COMM> 0 THEN '수당: ' || COMM
        END AS COMM_TEXT
        FROM EMP;

 CASE 문은 각 조건식의 TRUE,FALSE 여부만 검사하므로 기준 데이터가 없어도 사용 가능

 DECODE 함수와 CASE 문은 모두 조건별로 동일한 자료형의 데이터를 반환해야함

'DB > Doit!오라클로 배우는 데이터베이스 입문_개념정리' 카테고리의 다른 글

DB 기초 개념 정리_7)다중행 함수와 데이터 그룹화_DoIt!오라클로 배우는 데이터 베이스 입문  (0) 2024.06.13
DB 기초 개념 정리_5)더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자_DoIt!오라클로 배우는 데이터 베이스 입문  (0) 2024.05.27
DB 기초 개념 정리_4)SELECT문의 기본형식_DoIt!오라클로 배우는 데이터 베이스 입문  (0) 2024.05.26
DB 기초 개념 정리_2)관계형 데이터베이스와 오라클 데이터베이스_DoIt!오라클로 배우는 데이터 베이스 입문  (0) 2024.05.24
DB 기초 개념 정리_1)데이터베이스 개념 잡기_DoIt!오라클로 배우는 데이터 베이스 입문  (0) 2024.05.23

'DB/Doit!오라클로 배우는 데이터베이스 입문_개념정리'의 다른글

  • 현재글DB 기초 개념 정리_6)데이터 처리와 가공을 위한 오라클 함수_DoIt!오라클로 배우는 데이터 베이스 입문

관련글

티스토리툴바