[Oracle] SQL 단일 행 함수 1) 문자, 숫자, 날짜

함수는 단일 행 함수와 다중 행 함수로 구분할 수 있다.

 

단일행 함수의 특징 

• SELECT, WHERE, ORDER BY 절에 사용 가능하다.
• 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.
• 여러 인자를 입력해도 단 하나의 결과만 리턴한다.
• 함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.
• 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다.

 

이번 포스트에서는 단일행 함수 중 문자, 숫자, 날짜 관련 함수들을 간단히 표로 정리해 보았다.

---

 

1) 문자열 관련 함수

: 문자를 입력하면 문자나 숫자값을 반환한다.

<문자열 관련 함수>

<예시>

--문자 연산 함수
--CONCAT ||
SELECT CONCAT(DNO, ' : ' || DNAME || ' : ' || LOC)
    FROM DEPT;
    
SELECT DNO || ' : ' || DNAME || ' : ' || LOC
    FROM DEPT;

--SUBSTR
--SUBSTR(n, cnt)은 n 번째 글자부터 cnt개를 가져온다.
SELECT * 
    FROM PROFESSOR
    WHERE SUBSTR(ORDERS, 1, 1) = '정';
    
SELECT ENAME
     , SUBSTR(ENAME, 2)    --두 번째 글자부터 모두
     , SUBSTR(ENAME, -2)   --뒤에서 두 번째 글자부터 모두
     , SUBSTR(ENAME, 1, 2) --첫 번째 글자부터 두 글자
     , SUBSTR(ENAME, -2, 2)--뒤에서 두 번째 글자부터 두 글자
    FROM EMP;
    
--LENGTH, LENGTHB
SELECT DNAME
     , LENGTH(DNAME)
     , LENGTHB(DNAME)
    FROM DEPT;

--현재 오라클이 사용준인 문자셋 AL32UTF8 => 한글 3byte
SELECT *
    FROM NLS_DATABASE_PARAMETERS
    WHERE PARAMETER = 'NLS_CHARACTERSET'
       OR PARAMETER = 'NLS_NCHAR_CHARACTERSET';

--INSTR
SELECT INSTR('DATABASE', 'A')      -- 첫 번째 A의 위치
     , INSTR('DATABASE', 'A', 3)   -- 세 번째 글자인 T 다음의 첫 번째 A의 위치
     , INSTR('DATABASE', 'A', 1, 3)-- 첫 번째 글자 D 다음의 세 번째 A의 위치
     , SYSDATE
     , 1 + 2
    FROM DUAL;
--DUAL 테이블 : 오라클에서 제공해주는 가상의 기본 테이블.
--간단하게 날짜나 연산 또는 결과값을 보기 위해 사용.
--원래 DUAL 테이블 소유자는 SYS로 되어있는데 모든 USER에서 접근 가능.
    
--TRIM
SELECT TRIM('조' FROM '조병조')           --both 생략, 앞뒤의 조를 제거
     , TRIM(leading '조' FROM '조병조')   --앞에 있는 조 제거
     , TRIM(trailing '조' FROM '조병조')  --뒤에 있는 조 제거
     , TRIM(' 조 병 조 ')                 --공백 제거(앞뒤로 있는 공백만)
    FROM DUAL;
    
--LPAD, RPAD: CHARSET 한글을 3byte로 잡아도 컴퓨터에서는 2byte로 사용하기 때문에
--            한글연산 자체는 2byte로 진행된다.
SELECT LPAD(ENAME, 10, '*') --사원명 앞에 *을 붙여서 총 길이를 10으로 만듬
     , RPAD(ENAME, 10, '*') --사원명 뒤에 *을 붙여서 총 길이를 10으로 만듬
    FROM EMP;

------<활용 예시>-------
--직원이름을 출력하는데 마지막 글자만 제거해서 출력(SUBSTR, LENGTH)
SELECT SUBSTR(ENAME, 1, LENGTH(ENAME) - 1)
    FROM EMP;

---

2) 숫자 관련 함수

: 숫자를 입력하면 숫자값을 반환한다.

<숫자 관련 함수>

<예시>

--숫자 함수
--ROUND(지정한 자리수까지 반올림)
SELECT ROUND(123.454678, 3)
    FROM DUAL;

--TRUNC(지정한 자리수 뒤의 숫자 버림)
SELECT TRUNC(123.454678, 3)
    FROM DUAL;

--MOD(나머지 값)
SELECT MOD(10, 4)
    FROM DUAL;

--POWER(몇 제곱값)
SELECT POWER(10, 3)
    FROM DUAL;

--CEIL, FLOOR(제일 가까운 정수 값)
SELECT CEIL(2.59)
     , FLOOR(2.59)
    FROM DUAL;
    
--ABS(절대값)
SELECT ABS(10)
     , ABS(-10)
    FROM DUAL;
    
--SQRT(제곱근 값)
SELECT SQRT(9)
     , SQRT(25)
     , SQRT(100)
    FROM DUAL;

--SIGN(부호판단)    
SELECT SIGN(-123)
     , SIGN(52)
     , SIGN(0)
    FROM DUAL;

---

3) 날짜 관련 함수 

: DATE 타입의 값을 반환한다.

<날짜 관련 함수>

<예시>

 

--날짜 함수
--ROUND
SELECT ROUND(SYSDATE, 'mm')
    FROM DUAL;
    
SELECT ROUND(TO_DATE('20230424 00:00:00', 'yyyymmdd HH24:mi:ss'), 'mm')
    FROM DUAL;

--TRUNC
SELECT TRUNC(SYSDATE)
    FROM DUAL;
    
SELECT TRUNC(SYSDATE, 'dd')
    FROM DUAL;
    
--MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2023/02/13', 'yyyy/MM/dd'))
    FROM DUAL;

--ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE, 3)
    FROM DUAL;
    
--NEXT_DAY
SELECT NEXT_DAY(SYSDATE, '수요일')
    FROM DUAL;
    
--LAST_DAY
SELECT LAST_DAY(TO_DATE('20210618', 'yyyyMMdd'))
    FROM DUAL;

---

4) 날짜 연산

 

<날짜 연산>

<예시>

--날짜 연산
SELECT SYSDATE
     , SYSDATE + 100    --100일 후의 날짜
     , SYSDATE - 100    --100일 이전의 날짜
     , SYSDATE + 3 / 24 --3시간 후의 날짜
     , SYSDATE - 5 / 24 --5시간 이전의 날짜
     , SYSDATE - TO_DATE('20220413', 'YYYY/MM/DD') --두 날짜간 차이 일수(시간, 분, 초때문에 정확히 나오지 않음)
     , TRUNC(SYSDATE) - TO_DATE('20220413', 'YYYY/MM/DD')
    FROM DUAL;

<활용 예시>

--사원들의 입사일과 입사 100일후의 날짜와 10년뒤 날짜 조회
SELECT HDATE
     , HDATE + 100
     , ADD_MONTHS(HDATE, 120)
    FROM EMP;

---

[ 단일 행 함수를 사용할 때 주의할 점 ]

 

1. 함수의 반환 값을 받을 변수를 반드시 정의해야 한다.

2. NULL 값을 항상 신경쓰고 NULL 처리를 반드시 고려해야 한다.

3. 함수의 인자가 잘못되지 않았는지, 예외를 발생시키지는 않는지 확인하며 유효성 검사를 수행한다.

4. 함수의 반환 값에 대한 자료형을 확인하고 예상과 다른 결과가 나오지 않도록 한다.

5. 단일행 함수를 사용할 때는 함수의 성능도 함께 고려해야 쿼리의 성능에 부정적인 영향을 끼치지 않을 수 있다.

---