[SQL] Oracle, 오라클 단일행 함수

오라클 함수

: 특정한 결과값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어

내장함수(단일행함수, 다중행함수)와 사용자 정의함수가 있다.

 

단일행함수

1. 문자함수

- UPPER, LOWER, INITCAP

: 대, 소문자 전환 시

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

1

2, 3

- LENGTH

: 문자열 길이 반환

SELECT ENAME, LENGTH(ENAME)
FROM EMP;

SELECT ENAME, LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) >= 5;

--LENGTHB: 바이트 수 반환
SELECT LENGTH('한글'), LENGTHB('한글')
FROM DUAL;
--DUAL은 더미테이블, 임시연산을 위해 사용

1

2

3

-SUBSTR

: 문자열 일부를 추출하고자 할때

SELECT JOB, SUBSTR(JOB, 1, 2)
	--첫번째 글자부터 두 글자 출력
	, SUBSTR(JOB, 3, 2)
    , SUBSTR(JOB, 5)
FROM EMP;

SELECT JOB
    , SUBSTR(JOB, -LENGTH(JOB))
    , SUBSTR(JOB, -LENGTH(JOB), 2)
    , SUBSTR(JOB, -3)
FROM EMP;

-INSTR

: 특정 문자 위치를 찾고자 할때

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1
    --처음부터 L을 검색
    --3
    , INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2 
    --다섯번째 글자부터 검색
    --12
    , INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
    --두번째 글자부터 시작해서 두번째 L을 검색 
    --4
FROM DUAL;
--찾고자 하는 값이 없으면 0반환

SELECT *
FROM EMP
WHERE INSTR(ENAME, 'S') > 0;

SELECT *
FROM EMP
WHERE ENAME LIKE '%S%';

 

1

2, 3

-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 RPAD_1
    --오른쪽에 패딩부여
    , LPAD('Oracle', 10) AS LPAD_2
    --채울 데이터를 정의하지 않으면 빈 공간을 부여
    , RPAD('Oracle', 10) AS RPAD_2
FROM DUAL;

SELECT RPAD('971231-', 14, '*') AS RPAD_JMNO
    , RPAD('010-1234-', 13, '*') AS RPAD_PHONE
FROM DUAL;

- CONCAT

SELECT CONCAT(EMPNO, ENAME)
    , CONCAT(EMPNO, CONCAT(' : ', ENAME))
    -- CONCAT 결과를 다른 CONCAT 함수의 입력 값으로 사용 가능
FROM EMP
WHERE ENAME = 'SCOTT';

--위와 동일한 결과
SELECT EMPNO || ENAME
    , EMPNO || ' : ' || ENAME
FROM EMP
WHERE ENAME='SCOTT';

1, 2

- TRIM, LTRIM, RTRIM

: 특정문자를 지우는 역할

--삭제 문자가 없을 때: 공백이 디폴트
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 TRIM,
        '[' || LTRIM(' _Oracle_ ') || ']' AS LTRIM,
        '[' || LTRIM('<_Oracle_>', '_<') || ']' AS LTRIM_2,
        '[' || RTRIM(' _Oracle_ ') || ']' AS RTRIM,
        '[' || RTRIM('<_Oracle_>', '>_') || ']' AS RTRIM_2
FROM DUAL;

결과

---

2. 숫자함수

- ROUND

: 반올림 함수

SELECT ROUND(1234.5678) AS ROUND
    , ROUND(1234.5678, 1) AS ROUND_1
    , ROUND(1234.5678, -1) AS ROUND_MINUS1
FROM DUAL;

- TRUNC

: 특정 위치에서 버리는 함수

SELECT TRUNC(1234.5678) AS TRUNC
    , TRUNC(1234.5678, 1) AS TRUNC_1
    , TRUNC(1234.5678, -1) AS TRUNC_MINUS1
FROM DUAL;

- CEIL, FLOOR

SELECT CEIL(3.14)
    , FLOOR(3.14)
    , CEIL(-3.14)
    , FLOOR(-3.14)
FROM DUAL;

-MOD

: 나머지 값 제공

SELECT MOD(15, 6)
FROM DUAL;

---

3. 날짜함수

- SYSDATE

: OS의 현재 날짜와 시간

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, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTH
FROM EMP;

 

-NEXT_DAY, LAST_DAY

SELECT SYSDATE
    , NEXT_DAY(SYSDATE, '월요일')
    , LAST_DAY(SYSDATE)
FROM DUAL;

 

-ROUND, TRUNC

SELECT ROUND(SYSDATE, 'YYYY')
    , TRUNC(SYSDATE, 'YYYY')
FROM DUAL;

---

4. 형 변환 함수

: TO_CHAR, TO_NUMBER, TO_DATE

 

-TO_CHAR

날짜->문자

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW
FROM DUAL;

숫자->문자

SELECT SAL, TO_CHAR(SAL, '$999,999') AS SAL_$
FROM EMP;

 

-TO_NUMBER

SELECT TO_NUMBER('1,300', '999,999')-TO_NUMBER('1,500', '999,999')
FROM DUAL;

-TO_DATE

SELECT TO_DATE('2018-07-14', 'YYYY-MM-DD') AS DATE1
    , TO_DATE('20180714', 'YYYY-MM-DD') AS DATE2
FROM DUAL;

 

SELECT *
FROM EMP
WHERE HIREDATE > TO_DATE('1981/06/01', 'YYYY/MM/DD');

 

- 'YY'와 'RR'의 차이

• YY는 현 시점의 동일한 연도로 계산
• RR은 현 시점의 연도의 끝 두자리 수(00~49, 50~99)와 입력된 수치의 끝 두자리 수(00~49, 50~99)를 서로 비교하여 비교적 가까운 날짜 데이터를 계산해준다.

---

5. NULL 처리함수

-NVL

SELECT EMPNO, COMM
    , NVL(COMM, 0)
    , NVL2(COMM, 'O', 'X')
FROM EMP;

---

6. DECODE함수와 CASE문

: 특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할 지 결정

 

-DECODE

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

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;

위와 동일

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;

---

7. 종합 예제

--1
SELECT EMPNO
    , RPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO
    , ENAME
    , RPAD(SUBSTR(ENAME, 1, 1), 5, '*') AS MASKING_ENAME
FROM EMP
WHERE LENGTH(ENAME) = 5;

--2
SELECT EMPNO, ENAME, SAL
    , TRUNC(SAL/21.5, 2) AS DAY_PAY
    , ROUND(SAL/21.5/8, 1) AS TIME_PAY
FROM EMP;

--3
SELECT EMPNO, ENAME, HIREDATE
    , NEXT_DAY(ADD_MONTHS(HIREDATE, 3),'월요일') AS R_JOB
    , NVL2(COMM, TO_CHAR(COMM), 'N/A') AS COMM
FROM EMP;

--4
SELECT EMPNO, ENAME, MGR
    , DECODE(SUBSTR(MGR, 1, 2)
        , NULL, '0000'
        , 75, '5555'
        , 76, '6666'
        , 77, '7777'
        , 78, '8888'
        , TO_CHAR(MGR)) AS CHG_MGR
FROM EMP