SQL - 오라클 함수(단일행 함수)

문자 함수

-- UPPER, LOWER, INITCAP 함수
-- INITCAP: 첫 문자만 대문자
select ENAME, upper(ENAME), lower(ENAME), initcap(ENAME)
from EMP;

-- SCOTT 단어를 포함한 데이터 조회
select *
from EMP
where upper(ENAME) like upper('%smith%');

-- LENGTH 함수
select ENAME, length(ENAME)
from EMP;

-- SUBSTR 함수
-- SUBSTR(문자열 데이터, 시작 위치, 추출 길이)
-- SUBSTR(문자열 데이터, 시작위치)
select JOB, substr(JOB, 1, 2), substr(JOB, 3, 2), substr(JOB, 5)
from EMP;

-- 'CLERK'의 자리는 -5, -4, -3, -2, -1
select JOB,
       substr(JOB, -lengthb(JOB)),
       substr(JOB, -lengthb(JOB), 2),
       substr(JOB, -3)
from EMP;

-- INSTR 함수
-- 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;

-- REPLACE 함수
select '010-1234-5678'                    as REPLACE_BEFORE,
       replace('010-1234-5678', '-', ' ') as REPLACE_1,
       replace('010-1234-5678', '-')      as REPLACE_2
from DUAL;

-- LPAD, RPAD 함수: 데이터의 빈 공간을 특정 문자로 채움
select 'Oracle',
       lpad('Oracle', 10, '#') as LPAD_1,
       rpad('Oracle', 10, '*') as RPAC_1,
       lpad('Oracle', 10)      as LPAD_2,
       rpad('Oracle', 10)      as RPAD_2
from DUAL;

-- 개인정보 뒷자리 * 표시로 출력
select rpad('970307-', 14, '*')   as RPAD_JMNO,
       rpad('010-1234-', 13, '*') as RPAD_PHONE
from DUAL;

-- CONCAT 함수: 두 문자열 데이터를 합침
select concat(EMPNO, ENAME),
       concat(EMPNO, concat(' : ', ENAME))
from EMP
where ENAME = 'SMITH';

-- || 연산자: CONCAT 함수와 동일
select EMPNO || ENAME,
       EMPNO || ' : ' || ENAME
from EMP
where ENAME = 'SMITH';

-- 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;

-- '_' 삭제하는 경우
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 함수
select '[' || trim(' _Oracle_ ') || ']'        as TRIMG,
       '[' || ltrim(' _Oracle_ ') || ']'       as LTRIM,
       '[' || ltrim('<_Oracle_>', '_<') || ']' as LTRIM_2
from DUAL;

숫자 함수

-- ROUND 함수: 반올림
select round(1234.5678)     as ROUND,
       round(1234.5678, 2)  as ROUNG_2,
       round(1234.5678, -2) as ROUND_3
from DUAL;

-- TRUNC 함수: 버림
select trunc(1234.5678)     as TRUNC,
       trunc(1234.5678, 2)  as TRUNC_2,
       trunc(1234.5678, -2) as TRUNC_3
from DUAL;

-- CEIL, FLOOR 함수: 가까운 정수 출력
select ceil(3.14),
       floor(3.14)
from DUAL;

-- MOD 함수: 나머지
select mod(15, 6)
from DUAL;

날짜 함수

-- SYSDATE 함수: 날짜
select sysdate   as NOW,
       sysdate-1 as YESTERDAY,
       sysdate+1 as Tomorrow
from DUAL;

-- ADD_MONTHS
select sysdate,
       add_months(sysdate, 3)
from dual;

-- 입사 10주년 사원들 조회
select EMPNO,
       ENAME,
       HIREDATE,
       add_months(HIREDATE, 120) as WORK10YEAR
from EMP;

-- 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;

-- NEXT_DAY, LAST_DAY 함수
select sysdate,
       next_day(sysdate, '월요일'),
       last_day(sysdate)
from DUAL;

-- ROUND, TRUNC 함수
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;

형 변환 함수

-- TO_CHAR 함수: 날짜, 숫자 데이터를 문자 데이터로 변환
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') as 현재날짜시간
from DUAL;

-- 날짜 형식 지정하여 출력
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;

-- 특정 언어에 맞춰서 날짜 출력
select sysdate,
       to_char(sysdate, 'MM')                                  as MM,
       to_char(sysdate, 'MON', 'NLS_DATE_LANGUAGE = KOREAN')   as MON_KOR,
       to_char(sysdate, 'MON', 'NLS_DATE_LANGUAGE = JAPANESE') as MON_JAN,
       to_char(sysdate, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH')  as MON_ENG
from DUAL;

-- 시간 형식 지정하여 출력
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;

-- 달러, 원화 출력
select SAL,
       to_char(SAL, '$999,999') as SAL_$,
       to_char(SAL, 'L999,999') as SAL_L
from EMP;

-- TO_NUMBER 함수
select to_number('1,300', '999,999') - to_number('1,500', '999,999')
from DUAL;

-- TO_DATE 함수
select to_date('2024-01-04', 'YYYY-MM-DD') as TODATE1,
       to_date('20240104', 'YYYY-MM-DD')   as TODATE2
from DUAL;

NULL 처리 함수

-- NVL 함수
-- NVL(데이터, 앞의 데이터가 NULL일 경우 반환할 데이터)
select EMPNO,
       ENAME,
       SAL,
       COMM,
       SAL + COMM,
       nvl(COMM, 0),
       SAL + nvl(COMM, 0)
from emp;

-- NVL2 함수
-- NVL2(데이터, NULL이 아닐 경우, NULL일 경우 반환할 데이터)
select EMPNO,
       ENAME,
       COMM,
       nvl2(COMM, '0', 'X'),
       nvl2(COMM, SAL * 12 + COMM, SAL * 12) as ANNSAL
from EMP;

-- 실무에서는 NVL2보다 NVL을 더 많이 사용

DECODE 함수와 CASE문

-- DECODE 함수: 프로그래밍 언어의 if문과 유사, 기준 데이터 필요
select EMPNO,
       ENAME,
       JOB,
       SAL,
       decode(JOB,
              'MANAGER', SAL * 1.1,
              'SALESMAN', SAL * 1.05,
              'ANALYST', SAL,
              SAL * 1.03) as UPSAL
from EMP;

-- CASE문: if문과 유사, 기준 데이터 필요 X
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;