SQL - 단일행 함수(SINGLE ROW FUNCTION)

 

SQL에서 사용되는 함수는 크게 단일행 함수(SINGLE ROW FUNCTION)와 복수행 함수(MUTIPLE ROW FUNCTION)로 나눌 수 있다.

 

FUNCTION(함수)

 

- 단일행 함수(SINGLE ROW FUNCTION) -> 한개의 입력 한개의 출력

 

문자 대소 : UPPER, LOWER, INITCAP

문자 조작: SUBSTR, INSTR, LENGTH, LPAD, RPAD, LTRIM, RTRIM, REPLACE

숫자 조작 : ROUND, TRUNC, MOD

날짜 : SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY

형변환 : TO_NUMBER, TO_CHAR, TO_DATE

일반함수 : NVL, NVL2, DECODE

CASE 문

 

구조 : SELECT 함수명(컬럼명, 데이터)

 

- MUTIPLE ROW FUNCTION -> 여러개의 입력 한개의 출력

 

   - 추후 학습하도록 하겠다.

 

● 문자 대소(UPPER, LOWER, INITCAP)

 

UPPER : 대문자로

LOWER : 소문자로

INITCAP : 첫 글자만 대문자로

 

 EX) UPPER, LOWER, INITCAP 

 

SELECT UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;

 

 

● 문자 조작(SUBSTR, INSTR, LENGTH, LPAD, RPAD, LTRIM, RTRIM)

 

-SUBSTR(컬럼, n, m) : 칼럼에서 n번째 위치에서 m 개의 문자를 읽어 들인다.

주어진 문자열에서 특정 길이의 문자만 골라낼 때 사용하는 함수이다.

 

EX) 글자 추출

 

SELECT SUBSTR('ABCDE',3,2) "3 2",
          SUBSTR('ABCDE',-3,2) "-3 2",
          SUBSTR('ABCDE',-3,4) "-3 4"

 

 

-> 기본적으로 왼쪽에서 오른쪽으로 검색하여 글자를 추출한다. 마이너스가 붙으면 오른쪽에서 왼쪽으로 검색한 후 왼쪽에서 오른쪽으로 글자 수를 세어 추출한다.

 

 

-INSTR('비교할 대상', '비교하고자하는 값', 비교를 시작할 위치, 검색된 결과의 순번)

주어진 문자열이나 컬럼에서 특정 글자의 위치를 찾아주는 함수

 

EX) 문자 찾기

 

SELECT 'A-B-C-D'  , INSTR('A-B-C-D','-',1,3) "INSTR"
FROM DUAL;

 

 

-> 'A-B-C-D' 문자열에서 문자 '-'을 찾는데 1첫째 문자에서 시작하여 3번째 검색되는 '-'의 위치 출력

 

EX2) SUBSTR과 INSTR의 활용, 이메일 앞부분 몇글자 추출

 

SELECT SUBSTR('jd0922@naver.com',1, INSTR('jd0922@naver.com', '@')-1 ) "10"
FROM DUAL;

 

-> 이메일에서 @의 위치를 찾은 다음 그 위치 전까지의 문자 출력

 

-LENGTH(칼럼) : 칼럼의 길이

 

EX)이름과 이름 길이 출력

 

SELECT ENAME, LENGTH(ENAME)
FROM EMP;

 

 

 

-LPAD("값", "총 문자길이", "채움문자") : 지정한 길이 만큼 왼쪽부터 특정문자로 채워준다.

 

-RPAD("값", "총 문자길이", "채움문자") : 지정한 길이 만큼 오른쪽부터 특정문자로 채워준다.

 

 

EX) LPAD, RPAD 예제

 

SELECT ENAME, DEPTNO, LPAD(DEPTNO, 5, 'X'), RPAD(DEPTNO,5,'X')
FROM EMP;

 

 

 

- LTRIM('문자열' 또는 칼럼명, '제거할 문자') : 첫 글자가 '제거할 문자'일 경우 제거한 결과값 출력

 

- RTRIM('문자열' 또는 칼럼명, '제거할 문자') : 끝 글자가 '제거할 문자'일 경우 제거한 결과값 출력

 

EX) LTRIM, RTRIM 예제

 

SELECT ENAME, LTRIM(ENAME, 'C'), RTRIM(ENAME, 'R')
FROM EMP
WHERE DEPTNO = 10;

 

 

 

- REPLACE(문자열' 또는 칼럼명, '문자1', '문자2') :  주어진 첫번째 문자열이나 컬럼에서 문자1을 문자2로 바꾸어 출력

 

EX) 문자 대체하기

 

SELECT ENAME, REPLACE(ENAME, SUBSTR(ENAME,1,2),'**') "REPLACE"
FROM EMP
WHERE DEPTNO = 10;

 

 

● 숫자 조작(round, trunc, MOD)

 

-ROUND("값","자리수")  : 특정 소수점을 반올림하고 나머지를 버리는 함수

 

EX)

SELECT ROUND(123.123, 2) "ROUND1",   -> 소수점 둘째자리까지 반올림
          ROUND(123.123, 0) "ROUND2",   -> 일의 자리까지 반올림
          ROUND(123.123, -1) "ROUND3"   -> 십의 자리까지 반올림
FROM DUAL;

 

 

 

-TRUNC("값", "옵션") : 소수점 절사 및 날짜의 시간을 없앨 때 사용

 

EX)  

SELECT TRUNC(987.123, 2) "TRUNC1",
          TRUNC(987.123, 0) "TRUNC2",
          TRUNC(987.123, -1) "TRUNC3"
FROM DUAL;

 

 

 

-MOD(m,n) : m을 n으로 나누었을 때의 나머지 반환

 

EX)

SELECT MOD(121,10) "MOD1",
          MOD(17,5) "MOD2"
FROM DUAL;

 

● 날짜 (SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY)

 

- SYSDATE : 시스템에 저장된 현재 날짜를 반환

 

EX) 날짜 나흘 후

 

 SELECT SYSDATE + 3
 FROM DUAL;

 

- MONTHS_BETWEEN(날짜, 날짜) : 날짜와 날짜 사이의 개월 수를 계산

 

EX)

SELECT MONTHS_BETWEEN('21/07/13', '21/03/20')
FROM DUAL;

 

 

- ADD_MONTHS(날짜, 숫자) : 날짜에 월을 빼거나 더하는 함수입니다. 결과는 날짜 타입으로 출력

 

EX) 6개월 후

 

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 6)
FROM DUAL;

 

 

 

- NEXT_DAY (날짜, '요일' or 숫자) : 지정된 요일의 돌아오는 날짜가 언제인지 계산하는 함수

 

EX)

SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') 

FROM DUAL;

 

 

- LAST_DAY(날짜) : 월의 마지막 날짜를 계산해서 출력하는 함수

 

EX)

SELECT SYSDATE, LAST_DAY(SYSDATE), LAST_DAY('19/07/01')
FROM DUAL;

 

 

EX) 종합, 사원명 입사일 6개월 후 날짜 출력

 

SELECT ENAME "사원명", HIREDATE "입사일", NEXT_DAY(ADD_MONTHS(HIREDATE, 6), '금') "6개월후금"
FROM EMP;

 

 

-- BUT 아래의 경우

 

 SELECT SYSDATE - '2000/01/01'
 FROM DUAL;
                 
1행에 오류:
ORA-01722: 수치가 부적합합니다

-> 형변환을 해줘야한다.

 

● 형 변환 (TO_NUMBER, TO_CHAR, TO_DATE)

 

오라클에서 형변환은 임시적(자동) 형변환, 명시적(수동) 형변환 두 가지로 나눌 수 있다.

 

계산을 하기 위해 같은 형으로 변환해줄 필요가 있다.

 

-TO_CHAR 함수(날짜 -> 문자로 형 변환하기) 

 

날짜를 표현하는 방법은 아래와 같다.

 

-- 연도

YYYY - 연도를 4자리로 표현

RRRR - 2000년 이후에 Y2K 버그로 인해 등장한 날짜 표기법으로 연도 4자리 표기법

YY - 연도의 끝의 2자리만 표시

RR - 연도의 끝의 2자리만 표시

YEAR - 연도의 영문 이름 전체를 표시

 

-- 월

MM - 월을 숫자 2자리로 표현

MON - 유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시

           윈도용 오라클일 경우는 MONTH와 동일

MONTH - 월을 뜻한느 이름 전체를 표시

 

-- 일

DD - 일을 숫자 2자리로 표시

DAY - 요일에 해당하는 명칭을 표시하는데 유닉스용 오라클에서는 영문으로, 윈도용 오라클에서는 한글로 표시

DDTH - 몇 번째 날인지를 표시

 

-- 시간

HH24 - 하루를 24시간으로 표시

HH - 하루를 12시간으로 표시

분 - MI로 표시

초 - SS로 표시

 

EX) 날짜 차이 구하기

 

SELECT SYSDATE - TO_DATE('2000/01/01', 'YYYY/MM/DD')
FROM DUAL;

 

 

EX2) 현재 날짜 및 시간 구하기

 

SELECT TO_CHAR(SYSDATE, 'CC YYYY/MM/DD : HH:MI:SS')
FROM DUAL;

 

 

EX3) 이름과 근무 개월수 출력

 

SELECT ENAME, SYSDATE, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "근속개월수"
FROM EMP;

 

 

 

- TO_CHAR 함수(숫자형 -> 문자형으로 변환하기)

 

9 : 9의 개수만큼 자릿수  , TO_CHAR(1234, '99999')   => 1234 출력

0 : 빈자리를 0으로 채움  , TO_CHAR(1234, '099999')  => 001234 출력

$ : $ 표시를 붙여서 표시 , TO_CHAR(1234, '$9999')   => $1234 출력

. : 소수점 이하를 표시     , TO_CHAR(1234, '999.99')  => 1234.00 출력

, : 천 단위 구분 기호를 표시, TO_CHAR(12345, '99,999') => 12,345 출력

 

EX) $표시 및 구분점 표시

 

SELECT ENAME, TO_CHAR(SAL, '$999,999.99')
FROM EMP;

 

 

 

- TO_NUMBER() 함수

숫자가 아닌 숫자처럼 생긴 문자를 숫자로 바꾸어 주는 함수

 

EX)

-> 문자 5를 숫자 5로 바꿔준다.

 

 

- TO_DATE() 함수

날짜가 아닌 날짜처럼 생긴 문자를 날짜로 바꿔 주는 함수

 

EX) 

-> '2021/07/25' 문자를 날짜로 바꿔준다.

 

EX2) 2021/07/10 00:00:00 부터 오늘까지 기간을 시간으로 출력

 

SELECT TRUNC((SYSDATE - TO_DATE('2021/07/10 00:00:00' , 'YYYY/MM/DD HH24:MI:SS'))*24,0)
FROM DUAL;

 

 

● 일반함수 : NVL, NVL2, DECODE

 

- NVL(컬럼,값) : 컬럼이 NULL 이면 값

- NVL2(컬럼,값1,값2)  : 컬럼이 NULL 이면 값1, 아니면 값2

 

EX) NVL 함수를 이용하여 NULL값에 따른 연봉 계산

 

SELECT ENAME , SAL*12+NVL(COMM,0), COMM, NVL(COMM,0) , NVL2(COMM,1,0)
FROM EMP;

 

 

 

- DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, ...) : 컬럼에서 조건1이면 결과1, 조건2이면 결과2, 조건3이면 결과3 ... (if문과 비슷한 기능 수행)

 

EX) DECODE 함수를 사용해 직업마다 봉급 증가

 

SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.3, 'CLELK', SAL*1.1, SAL*1.05) "UPSAL"
FROM EMP;

 

 

EX2) 부서번호 10번이면 'O' 아니면 'X' 출력 

 

SELECT ENAME ,DEPTNO, DECODE(DEPTNO, '10', 'O', 'X')
FROM EMP;

 

 

● CASE 문

 

구조

CASE 대상값 WHEN 값1 THEN 결과1

                 WHEN 값2 THEN 결과2 

                  . . .

                  . . .

                 WHEN 값n THEN 결과m END;

 

EX) CASE 문을 이용한 봉급 인상

 

    SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.3, 'CLERK', SAL*1.1, SAL*1.05) "UPSAL",
    CASE WHEN JOB = 'MANAGER' THEN SAL*1.3
    WHEN JOB = 'CLERK' THEN SAL*1.1
            ELSE
                   SAL*1.05
    END "UPSAL2"

FROM EMP;