[Oracle] 단일행 함수 (문자, 숫자, 날짜, 형 변환, 일반)

서론

• 단일행 함수와 복수행 함수의 차이
• 다양한 문자/숫자/날짜/형변환/일반

---

단일행 함수 VS 복수행 함수

• 단일행 함수
  • 단일 행 함수는 여러 건의 데이터를 한꺼번에 처리하는 것이 아닌
  • 한 번에 하나씩 처리하는 함수
• 복수행 함수 ( = 그룹 함수)
  • 여러 건의 데이터를 동시에 입력 받아 한 건의 결괏값을 만들어 주는 함수

---

단일행 함수

• 함수에 어떤 값이 입력되는지 구분하는 것이 중요함

▣ 문자 함수

함수명	의미	사용 예
INITCAP	입력 첫 글자만 대문자 변환 공백이 있을 경우 공백 다음을 첫 글자로 판단	INITCAP('abcd') => Abcd INITCAP('sky ju') => Sky Ju
LOWER	입력 값 전부 소문자 변환	LOWER('ABCD') => abcd
UPPER	입력 값 전부 대문자 변환	UPPER('abcd') => ABCD
LENGTH	입력된 문자열 길이 값 출력	LENGTH('한글') => 2
LENGTHB	입력된 문자열 길이 바이트 값 출력	LENGTHB('한글') => 4
CONCAT	두 문자 결합해 출력 ( == || 연산자 )	CONCAT('A', 'B') => AB
SUBSTR	주어진 문자에서 특정 문자만 추출 SUBSTR(문자열, 시작 번호, 출력할 문자 수)	SUBSTR('ABC', 1, 2) => AB
SUBSTRB	주어진 문자에서 특정 바이트만 추출	SUBSTRB('한글', 1, 2) => 한
INSTR	주어진 문자에서 특정 문자 위치 추출	INSTR('A*B#', '#') => 4
INSTRB	주어진 문자에서 특정 문자의 위치 바이트 값 추출
LPAD	문자열의 왼쪽으로 특정 문자 채움	LPAD('love', 6, '**') => **love
RPAD	문자열의 오른쪽으로 특정 문자 채움	RPAD('love', 6, '**') => love**
LTRIM	문자열에서 왼쪽 특정 문자 삭제	LTRIM('love*','*') => love
RTRIM	문자열에서 오른쪽 특정 문자 삭제	RTRIM('love*','*') => love
REPLACE	문자열에서 A를 B로 치환	REPLACE('AB','A','E') => EB
REGEXP_REPLACE	문자열에서 특정 패턴 찾아 치환
REGEXP_INSTR	문자열에서 특정 패턴의 시작 위치 반환
REGEXP_SUBSTR	문자열에서 특정 패턴 찾아 반환
REGEXP_LIKE	문자열에서 특정 패턴 찾아 반환
REGEXP_COUNT	문자열에서 특정 패턴 횟수 반환

• upper, lower
  • 실무에서 자주 사용됨
  • DB에 데이터를 저장하거나 불러올 때, 대문자나 소문자로 통일해야 할 경우 많이 사용됨
• substr('문자열' 또는 컬럼, 시작 위치, 갯수)
  • 즉, 시작 위치에서 갯수만큼 문자열을 잘라냄
  • 특정 길이의 문자만 골라내는 함수
  • 주민번호에서 생일을 찾아내는 경우에 유용하게 이용됨
  • 만약 시작 수를 음수로 줄 경우 뒤에서(오른쪽에서) 자릿수를 계산해 문자를 추출함
• instr('문자열' 또는 컬럼, 시작 위치, 몇 번째인지(기본 값 == 1))
  • 시작 위치를 음수로 줄 경우 오른쪽에서 왼쪽으로 결괏값을 검색함
  • 시작 위치와 찾으려는 위치 값( == 몇 번째 인지)는 생략 가능함
    생략할 경우 자동으로 1로 설정됨
• lpad('문자열' 또는 컬럼, 자릿수, '채울 문자')
• ltrim('문자열' 또는 컬럼, '제거할 문자') / rtrim('문자열' 또는 컬럼, '제거할 문자')
  • 맨 오른쪽 혹은 맨 왼쪽에 위치하며 제거할 문자와 동일한 문자를 제거함
  • 회원 가입에서 고객 아이디 첫 글자가 공백일 경우 공백 제거에 사용되기도 함
• replace('문자열' 또는 컬럼명, '문자1', '문자2')
  • 첫 문자열이나 컬럼에서 문자 1을 문자 2로 바꾸어 출력하는 함수

▣ 숫자 함수

함수명	의미	사용 예
ROUND	숫자 반올림 후 출력 - 소숫점 아래에서의 반올림 = 양수 (해당 숫자에서 반올림) - 정수로의 반올림 = 0 - 정수에서의 반올림 = 음수 (해당 자릿수에서 반올림)	ROUND(12.345, 2) => 12.35
TRUNC	버림 후 출력	TRUNC(12.345, 2) => 12.34
MOD	나눈 후 나머지 값 출력	MOD(12, 10) => 2
CEIL	숫자와 가장 근접한 큰 정수 출력	CEIL(12.345) => 13
FLOOR	숫자와 가장 근접한 작은 정수 출력	FLOOR(12.345) => 12
POWER	숫자 1의 숫자 2승 출력	POWER(3, 2) => 9

• ceil(숫자)
  • 여러 데이터를 하나의 기준으로 묶을 때
  • [ex] rownum을 기준으로 세 명씩 한 팀으로 묶을 때,
    
    SELECT rownum AS "r-no", CEIL(rownum/3) AS "team-no", ename
    FROM emp;
    
    
  • 거래명세서 출력, 대량 데이터 일정 개수씩 끊어 출력할 경우 요긴하게 사용됨
  • 페이징 처리에도 좋을 수 있지 않을까?
• 숫자 함수는 돈과 관련되는 경우가 많아 주의해서 사용해야함

▣ 날짜 함수

함수명	의미	결과
SYSDATE	시스템의 현 날짜와 시간	날짜
MONTHS_BETWEEN	두 날짜 사이의 개월 수	숫자
ADD_MONTHS	날짜에 개월 더하기	날짜
NEXT_DAY	날짜를 기준으로 돌아오는 날짜 출력	날짜
LAST_DAY	날짜가 속한 달의 마지막 날짜	날짜
ROUND	날짜 반올림	날짜
TRUNC	날짜 버림	날짜

• months_between
  • 유닉스 오라클과 윈도우 오라클 날짜 형식은 다른데, 이때 날짜 형태가 안 맞을 경우 에러가 발생함
  • 그렇기에 유닉스용 오라클에서 윈도우용 날짜 형식을 사용하고 싶다면 NLS_DATE_FORMAT이라는 명령으로 날짜 형태를 알려줘야 함
    
    날짜형태 지정 쿼리
    ALTER SESSION SET NLS_DATE_FORMAT = 'YY/MM/DD';
    
    
  • 날짜를 계산하는 방법은 어떤 방법을 사용하느냐에 따라 결과가 많이 다를 수 있기에 주의해서 사용해야 함
    그렇기에 실무에서는 먼저 확인하는 습관 중요!
• next_day(날짜,  '알고자 하는 요일')
  • 날짜를 기준으로 알고자 하는 요일의 가장 최근 날짜 반환
  • 리눅스와 윈도우용은 날짜 표기가 다르기에 요일 작성시 주의해야함
    • 월요일일 경우
      • 리눅스 : MON
      • 윈도우 : 월
• ADD_MONTHS, NEXT_DAY, LAST_DAY 함수 사용 예시
  • 이벤트 진행시 사용됨
  • 예를 들어.. '이번 달까지 할인 30% 적용 가격으로 처리'
• 날짜의 ROUND( )와 TRUNC( )
  • round: 하루의 반에 해당되는 시간 낮(정오)를 기준으로 넘어갈 경우 다음 날, 아닐 경우 당일 출력
  • trunc: 무조건 당일로 출력
    
    
  • 오전까지 접수된 건 당일 접수 처리, 오후 접수 건 이튿날 처리 ==> ROUND 함수
  • 접수 시간 상관없이 당일 처리 ==> TRUNC 함수

▣ 데이터 타입

데이터 타입	의미
CHAR(n)	고정길이 문자 저장 (최댓값 2,000 바이트)
VARCHAR2(n)	가변길이 문자 저장 (최댓값 4,000 바이트)
NUMBER(p, s)	숫자 값 저장 [ p = 전체 자릿수 ( 1 ~ 38 ), s = 소수점 이하 자릿수 -84 ~ 127자리 ]
DATE	총 7바이트, BC 4712년 1월 1일부터 AD 9999년 12월 31일까지 날짜 저장 가능
LONG	가변 길이 문자 저장 (최대 2GB)
CLOB	가변 길이 문자 저장 (최대 4GB)
BLOB	가변 길이 바이너리 데이터 저장 (최대 4GB)
RAW(n)	원시 이진 데이터 (최대 2,000 바이트)
LONG RAW(n)	원시 이진 데이터 (최대 2GB)
BFILE	외부 파일에 저장된 데이터 (최대 4GB)

• 묵시적(자동) 형 변환과 명시적(수동) 형 변환
  • SELECT 2 + '2' FROM dual;     ==>     결괏값 : 4
    
    • 숫자와 문자를 연산하는 것.
    • 숫자와 문자를 연산할 수 없기에 해당 요청 시 오라클은 에러를 발생시킴
    • 하지만 위와 같이 숫자처럼 생긴 문자는 오라클이 숫자로 바꾼 후 연산을 수행함
      
      
    • 내부적
      SELECT 2 + TO_NUMBER('2') FROM dual;
      
      
    • 허나, 튜닝에서 속도가 느려지는 주범이기에 주의해야 함
    • 왜냐. 해당 컬럼에 인덱스가 생성되어 있을 경우 묵시적 형 변환이 생기면 인덱스를 못 쓰게 되고,
      그렇게 되면 Index Suppressing error가 발생해 속도가 느려짐
• 명시적(강제적)으로 형 변환을 할 때 '형 변환 함수' 사용

▣ 형변환 함수

 

숫자 -- to_har --> 문자 <-- to_char -- 날짜

숫자<-- to_number -- 문자 -- to_date --> 날짜

 

• TO_CHAR :: 날짜 -> 문자
  • TO_CHAR(원래 날짜, '원하는 모양')
  • 포멧
    • 연도
      • YYYY : 연도 4자리 표현 ( ex. 2014 )
      • RRRR : 2000년 이후 Y2K 버그로 등장한 연도 4자리 날짜 표기법
      • YY : 연도 끝의 두 자리만 표기 ( ex. 14 )
      • RR : 연도 마지막 두 자리 ( ex. 14 )
      • YEAR : 연도의 영문 이름 전체 표시
    • 월
      • MM : 숫자 두 자리로 표현 ( ex. 07 )
      • MON : 유닉스용 오라클에서 월을 뜻하는 영어 세 글자로 표시 ( ex. OCT )
        ( == 윈도우는 MONTH와 동일 )
      • MONTH : 월을 뜻하는 이름 전체 표시
    • 일
      • DD : 일을 숫자 두 자리로 표시 ( ex. 16 )
      • DAY : 요일에 해당하는 명칭 표시. 유닉스용 오라클 == 영문, 윈도우용 오라클 == 한글
      • DDTH : 몇 번째 날인지 표시
    • 시간
      • HH24 : 하루를 24시간으로 표시
      • HH : 하루를 12시간으로 표시
      • MI : 분으로 표시
      • SS : 초로 표시
    • [참고] WHERE 절에서의 조건
      

      

• TO_CHAR :: 숫자 -> 문자
  • 종류
    • 9
      • 9개의 개수만큼 자릿수를 가짐
      • [ex] TO_CHAR(1234, '99999') => 1234
    • 0
      • 빈자리를 0으로 채움
      • [ex] TO_CHAR(1234, '099999') => 001234
    • $
      • $를 붙여 표시
      • [ex] TO_CHAR(1234, '$9999') => $1234
    • .
      • 소수점 이하 표시
      • [ex] TO_CHAR(1234, '9999.99') => 1234.00
    • ,
      • 천 단위 구분기호 표시
      • [ex] TO_CHAR(12345, '99,999') => 12,345
• TO_NUMBER( )
  • 숫자가 아닌 숫자처럼 생긴 문자를 숫자로 전환
• TO_DATE( )
  • 날짜가 아닌 날짜처럼 생긴 문자를 날짜로 전환
  • 마찬가지로 날짜 형태에 주의해야 함
    • 윈도우 오라클에서 유닉스 오라클 날짜 형태의 데이터를 바꾸는 것은 불가함

▣ 일반 함수

- 입력되는 값 구분 없이 모두 사용 가능함

• NVL(컬럼, 치환 값)
  • NULL 값을 만날 경우 다른 값으로 치환해 출력
  • NVL(sal, 0) => NULL일 경우 0으로 치환
  • NVL(sal, 100) => NULL일 경우 100으로 치환
• NVL2( col1, col2, col3 )
  • NVL 함수의 확장, NULL 값이 아닐 경우 출력 값 지정 가능
  • col1 값이 NULL이 아니면 col2,
    col1 값이 NULL이면 col3
• DECODE( ) 함수
  • 분기문인 IF문을 오라클 SQL 안으로 가져온 함수
  • = (equal) 값 처리
    
    
  • [유형 1] A가 B일 경우 '1'을 출력하는 경우
    • DECODE(컬럼명, 비교 값, TRUE 출력 값, FALSE 출력 값)
    • DECODE(A, B, '1', null)
    • 마지막 null 생략 가능
    • A가 B가 아니라면 null 출력
  • [유형 2] A가 B일 경우 '1'을 출력하고, 아닐 경우 '2를 출력하는 경우 (위랑 비슷한거 같은데)
    • DECODE(A, B, '1', '2')
  • [유형 3] A가 B일 경우 '1', C일 경우 '2', 둘 다 아닐 경우 '3'
    • DECODE(A, B, '1', C, '2', '3')
    • IF문 쓰듯이 쭉 접근
  • [유형 4] A가 B일 경우 중 C가 D를 만족하면 '1', C가 D가 아닐 경우 null (한 마디로 DECODE 함수 안에 DECODE 함수가 중첩되는 경우)
    • DECODE(A, B, DECODE(C, D, '1', null))
  • [유형 5] A가 B일 경우 중 C가 D를 만족하면 '1', C가 D가 아닐 경우 '2'를 출력하는 경우
    • DECODE(A, B, DECODE(C, D, '1', '2'))
  • [유형 6] A가 B일 경우 중 C가 D를 만족하면 '1', C가 D가 아닌 경우 '2', A가 B가 아닐 경우 '3'
    • DECODE(A, B, DECODE(C, D, '1', '2'), '3')
• CASE 문 [ CASE WHEN ~ THEN ~ END ~ ]
  •  DECODE는  equal값 처리에 사용
  • CASE 문은 크거나 작은 조건을 처리할 때 사용
    
    
  • DECODE는 ,(comma)로 조건 구분
    CASE문은 내부에 콤마가 사용되지 않음
    
    
  • [유형 1] DECODE와 동일하게 '=(equal)' 조건으로 사용되는 경우
    • SELECT CASE 비교 대상 WHEN 조건 THEN 출력 값 END "컬럼명"
  • [유형 2] 비교 조건이 '=(equal)'이 아닌 경우
    
    • SELECT CASE WHEN 비교 대상 BETWEEN 시작 값 AND 마지막 값 THEN 출력 값
       END "컬럼명"
  • CASE문은 튜닝에서 많이 사용되고 있으며, DECODE보다 더 넓은 범위에서 사용할 수 있기에 반드시 숙지해두면 좋음

 

---

여담

• CMD창에서 별칭과 함수가 정상 작동됐지만 DBeaver에서는 .. 같은 쿼리임에도 함수가 제대로 작동하지 않았음
  [ 이런거 꽤 많던데.. DBeaver 단점 ]