오라클(Oracle) - (DQL) : 함수(단일행 함수)

• 오라클에서는 (1)내장 함수와 사용자가 필요에 의해서 직접 정의한 (2)사용자 정의 함수로 나누어짐.
• 단일행 함수와 다중행 함수(집계함수)로 나누어짐.

DUAL 테이블 활용
SYS 계정에서 제공하는 테이블로 함수나 계산식을 테이블 참조 없이 실행하기 위한 DUMMY 테이블
즉, 가상의 테이블!

숫자함수

ABS (절대값)

SELECT ABS(-10) FROM DUAL; -- 10

ROUND (반올림)

SELECT ROUND(1234.5678) AS ROUND FROM DUAL; -- 반올림 자리를 지정하지 않으면 소수점 이하 첫번째 자리에서 반올림

SELECT ROUND(1234.5678, 0) AS ROUND_0, -- 1235
    ROUND(1234.5678,1) AS ROUND_1, -- 1234.6
    ROUND(1234.5678,3) AS ROUND_3, -- 1234.568
    ROUND(1234.5678,4) AS ROUND_4, -- 1234.5678
    ROUND(1234.5678,-1) AS ROUND_마이너스1, -- 1230
    ROUND(1234.5678,-2) AS ROUND_마이너스2 -- 1200
    FROM DUAL;

TRUNC (버림)

SELECT TRUNC(1234.5678, 0) AS TRUNC_0, -- 1234
    TRUNC(1234.5678,1) AS TRUNC_1, -- 1234.5
    TRUNC(1234.5678,3) AS TRUNC_3, -- 1234.567
    TRUNC(1234.5678,4) AS TRUNC_4, -- 1234.5678
    TRUNC(1234.5678,-1) AS TRUNC_마이너스1, -- 1230
    TRUNC(1234.5678,-2) AS TRUNC_마이너스2 -- 1200
    FROM DUAL;

MOD (나머지)

SELECT MOD(21,5) FROM DUAL; -- 1

CEIL (소수점 이하가 있으면 무조건 올림)

SELECT CEIL(34.34) FROM DUAL; -- 35

FLOOR (소수점 이하가 있으면 무조건 날림)

SELECT FLOOR(34.99999) FROM DUAL; -- 34

POWER (정수 A를 정수 B만큼 제곱)

SELECT POWER(2,4) FROM DUAL; -- 16

문자 함수

• 문자 데이터를 가공하거나 문자 데이터로 부터 특정 결과를 얻고자 할 때 사용

UPPER (대문자로 변경)

LOWER (소문자로 변경)

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

INITCAP (첫 글자 대문자, 나머지 소문자)

SELECT INITCAP('yoo young yoo') FROM DUAL;

🐸 WHERE 조건절과 함께 사용

SELECT * FROM EMP WHERE UPPER(ENAME) = UPPER('james');

🐸 UPPER 함수와 LIKE 사용

SELECT * FROM EMP WHERE LOWER(ENAME) LIKE LOWER('%JA%');

🐸 사원 이름은 대문자, 직책은 첫자 대문자 나머지는 소문자 변경하고 급여가 높은 순으로 출력

SELECT UPPER(ENAME), INITCAP(JOB) FROM EMP ORDER BY SAL DESC;

LENGTH (문자열 길이)

SELECT ENAME, LENGTH(ENAME) FROM EMP;

LENGTHB (문자열 바이트 수)

SELECT LENGTH('한'), LENGTHB('한') FROM DUAL;

LENGTH('한') : 1 , LENGTHB('한') : 3
오라클 XE 버전은 문자 1개는 3byte

🐸 이름의 길이가 5와 같거나 큰 사원의 이름, 사원번호, 직책을 연봉 순으로 표시 (단, 보너스 제외)

SELECT ENAME, EMPNO, JOB, SAL*12 AS 연봉
    FROM EMP
    WHERE LENGTH(ENAME) >= 5
    ORDER BY 연봉 DESC;

🐸 직책 이름이 6자 이상이고 COMM이 있는 사원 출력

SELECT * FROM EMP
    WHERE LENGTH(JOB) >= 6
    AND COMM IS NOT NULL AND COMM != 0;

SUBSTR / SUBSTRB

• 대상 문자열이나 컬럼의 자료에서 시작위치부터 길이만큼의 문자열 반환
• 인덱스 개념 아님! (1부터 시작)

SUBSTR(문자열데이터, 시작위치, 길이)

SELECT JOB, SUBSTR(JOB,1,2), SUBSTR(JOB,3,2), SUBSTR(JOB,5)
    FROM EMP ORDER BY JOB;

🐸 SUBSTR 함수와 다른 함수 함께 사용

SELECT JOB,
-- JOB의 길이는 5이고 5에 대한 음수를 취하면 뒤에서 부터 음수값이 커지면서 읽어 냄
    SUBSTR(JOB,-LENGTH(JOB)),
    SUBSTR(JOB, -LENGTH(JOB), 2),
    SUBSTR(JOB, -3) -- 뒤에서 부터 3글자
FROM EMP;

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('010-1234-5678', '-', ' ') AS 하이픈을공백으로,
    REPLACE('010-1234-5678', '-') AS 하이픈삭제
FROM DUAL;

LPAD / RPAD (특정 문자로 채우는..)

• 공간에 칸 수를 지정하고, 빈 칸만큼 특정 문자로 채우는 기능

SELECT LPAD('ORACLE', 10, '+') FROM DUAL; -- ++++ORACLE
SELECT RPAD('ORACLE', 10, '+') FROM DUAL; -- ORACLE++++
SELECT 'ORACLE', -- ORACLE
    LPAD('ORACLE', 10, '#') AS LPAD_1, -- ####ORACLE
    RPAD('ORACLE', 10, '*') AS RPAD_1, -- ORACLE****
    LPAD('ORACLE', 10) AS LPAD_2, -- (공백)    ORACLE
    RPAD('ORACLE', 10) AS RPAD_2 -- ORACLE    (공백)
FROM DUAL;

🐸 응용 예제) 개인정보 뒷자리를 * 표시로 출력하기

SELECT
    RPAD('940909-', 14, '*') AS 주민번호,
    RPAD('010-1234-', 13, '*') AS  핸드폰번호
FROM DUAL;

CONCAT (두 문자열 합치는)

SELECT CONCAT(EMPNO, ENAME) AS 연결1,
    CONCAT(EMPNO, CONCAT(' - ', ENAME)) AS 연결2
FROM EMP
    WHERE ENAME = 'JAMES';

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

날짜 함수

SYSDATE (현재 날짜)

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE AS NOW, -- 운영체제에서 읽어온 현재 날짜 정보
    SYSDATE-1 AS YESTERDAY, -- 운영체제에서 읽어온 날짜 정보에서 1일을 뺌
    SYSDATE+1 AS TOMORROW -- 운영체제에서 읽어온 날짜 정보에서 1일을 더함
FROM DUAL;

ADD_MONTHS

• 몇 개월 이후의 날짜를 구하는 함수
• 특정 날짜에 지정한 개월 이후 날짜 데이터를 반환하는 함수
• ADD_MONTHS(날짜 데이터, 더할 개월 수)

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

🐸 (EMP). 사원에 대해 사원번호, 이름, 입사일, 입사 10주년 날짜 출력

SELECT EMPNO, ENAME, HIREDATE,
	ADD_MONTHS(HIREDATE, 120) AS 입사10주년
FROM EMP;

🐸 (DUAL). 현재 시간과 8개월 이후 시간 출력

SELECT SYSDATE AS 현재시간,
	ADD_MONTHS(SYSDATE, 8) AS "8개월이후"
FROM DUAL;

MONTHS_BETWEEN

• 두 날짜간의 개월 수 차이를 구하는 함수
• 디테일하게 나오기 때문에 소수점 날리는 TRUNC 기능 써줘야 함.

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
    MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTH1,
    FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTH2,
    TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTH3
FROM EMP;

NEXT_DAY

• 돌아오는 요일의 날짜를 구하는 함수
• NEXT_DAY(날짜데이터, 요일문자) : 날짜 기준으로 돌아오는 요일의 날짜 출력

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

LAST_DAY

• 달의 마지막 날짜를 구하는 함수
• LAST_DAY(날짜 데이터) : 날짜가 속한 달의 마지막 날짜 출력

SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;

EXTRACT (날짜 정보 추출)

SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL; -- 1998

SELECT * FROM EMP
    WHERE EXTRACT(MONTH FROM HIREDATE) = 12; -- 입사일이 12월인 사람 출력

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; -- 현재 몇월인지

형 변환 함수

• 오라클도 자바와 마찬가지로 명시적 형변환과 자동(묵시적) 형변환이 있습니다.
• 자동(묵시적) 형변환 : 숫자와 문자 자료형의 연산은 자동으로 숫자로 변환 됩니다.
  (자바와 차이점)

SELECT EMPNO, ENAME, EMPNO + '500' FROM EMP; -- 자동 형변환
SELECT EMPNO, ENAME, EMPNO + 'ABCD' FROM EMP; -- ERROR!!

TO_CHAR

• 날짜, 숫자 데이터를 문자 데이터로 변환하는 함수
• 주로 날짜 데이터를 문자 데이터로 변환할 때 자주 사용함.
• TO_CHAR(날짜데이터, 출력되길 원하는 문자열 포맷)

SELECT
	TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS "현재 날짜와 시간"
FROM DUAL;

🐸 다양한 형식으로 출력하기

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'CC') AS "몇 세기",
    TO_CHAR(SYSDATE, 'YY') AS "연도",
    TO_CHAR(SYSDATE, 'YYYY/MM/DD PMHH24:MI') AS "연/월/일 시:분",
    TO_CHAR(SYSDATE, 'Q') AS 쿼터,
    TO_CHAR(SYSDATE, 'DD') AS 일,
    TO_CHAR(SYSDATE, 'DDD') AS 경과일,
    TO_CHAR(SYSDATE, 'HH') AS "12시간제",
    TO_CHAR(SYSDATE, 'HH12') AS "12시간제2",
    TO_CHAR(SYSDATE, 'HH24') AS "24시간제",
    TO_CHAR(SYSDATE, 'W') AS 몇주차
FROM DUAL;

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'HH24:MI:SS') AS MM,
    TO_CHAR(SYSDATE, 'HH12:MI:SS AM') AS MON_KR,
    TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') AS MON_JP
FROM DUAL;

NLS(National Language Support)

• 특정 국가 언어 형식에 맞춰서 날짜 출력

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KR,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JP,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_EN,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KR,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JP,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_EN
FROM DUAL;

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'DD', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KR,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JP,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_EN,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KR,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JP,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_EN
FROM DUAL;

숫자 데이터 형식 지정 후 출력

• 9 : 숫자의 한자리를 의미하고, 빈 자리를 채우지 않음
• 0 : 빈 자리를 0으로 채움
• $ : 달러 표시를 붙임
• L : 국가 화폐 단위 표시(운영체제 언어 기준?)
• . : 소수점 표시
• , : 천 단위 표시

SELECT SAL,
    TO_CHAR(SAL, '$999,999') AS SAL_$, -- 달러 표시하고 빈 자리 채우지 않음
    TO_CHAR(SAL, 'L999,999') AS SAL_L, -- 지역 화폐 표시. 천 단위에 ,넣음
    TO_CHAR(SAL, '999,999.00') AS SAL_1, -- 소수점 이하 2자리까지 표시
    TO_CHAR(SAL, '$000,999,999.00') AS SAL_2, -- 빈 자리를 0으로 채움
FROM EMP;

TO_NUMBER

• 숫자 타입의 문자열을 숫자 데이터 타입으로 변환해주는 함수
• 자동 형변환 해주기 때문에 잘 안 쓰임.

SELECT '1300' - '1500', '1300' + '1500' FROM DUAL;
-- TO_NUMBER 안써도 자동 형변환 해줌.

TO_DATE

• 원하는 포맷 형태의 날짜 데이터로 변환하는 함수
• TO_CHAR와 보이는건 똑같지만 변환되는 방향이 다르다.

SELECT TO_DATE('22/08/20', 'YY/MM/DD') FROM DUAL; -- 문자 -> DATE
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD') FROM DUAL; -- DATE -> 문자

SELECT *
    FROM EMP
    WHERE HIREDATE < TO_DATE('1981/01/01', 'YY/MM/DD');

• 근데.. DATE랑 문자열(날자)가 타입이 다른데도 비교가 됨..(?) 🤣

🐸 1981년 6월 1일 이후에 입사한 사원 정보 출력하기

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

NULL 함수

NVL

• NVL(데이터열, 앞의 데이터가 NULL일 경우 반환할 데이터)

SELECT EMPNO, ENAME, SAL, COMM, SAL*12+NVL(COMM,0) AS "연봉(COMM포함)"
    FROM EMP;

NVL2

• NVL2(검사할 데이터열, NULL이 아닌 경우에 대한 계산식, NULL인 경우에 대한 계산식)

SELECT EMPNO, ENAME, COMM, 
    NVL2(COMM, 'O', 'X') AS "NULL 여부 표시",
    NVL2(COMM, SAL*12+COMM, SAL*12) AS "연봉"
    FROM EMP;

NULLIF

• 두 값을 비교하여 동일인지 아닌지에 대한 결과 반환
• 같으면 NULL 반환, 다르면 첫번째 값 반환

SELECT NULLIF(10,10), NULLIF('A', 'B') FROM DUAL;

DECODE 문

• 주어진 데이터 값이 조건 값과 일치하는 값을 출력하고, 일치하는 값이 없으면 기본값 출력
• 조건문 또는 SWITCH문과 유사함!

SELECT EMPNO, ENAME, JOB, SAL,
    DECODE(JOB,
    'MANAGER', SAL*1.1,
    'SALESMAN', SAL*1.05,
    'ANALYST', SAL,
    SAL*1.03) AS 급여인상
    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 급여인상
    FROM EMP;

열 값에 따라 출력값이 달라지는 CASE 문

• if - else 문과 유사함
• THEN 절에는 문자열 넣을 때 ' ' 작은따옴표로 넣어야 함.

SELECT EMPNO, ENAME, COMM,
    CASE
        WHEN COMM IS NULL THEN '해당 사항 없음'
        WHEN COMM = 0 THEN '수당없음'
        WHEN COMM > 0 THEN '수당 : ' || COMM
    END AS 수당표시
FROM EMP;

---

🐸실습문제🐶

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 AND LENGTH(ENAME) < 6;

---

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

---

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

---

SELECT EMPNO, ENAME, NVL(MGR,0) AS MGR,
    CASE
        WHEN MGR IS NULL THEN 0000
        WHEN SUBSTR(MGR,1,2) = 75 THEN 5555
        WHEN SUBSTR(MGR,1,2) = 76 THEN 6666
        WHEN SUBSTR(MGR,1,2) = 77 THEN 7777
        WHEN SUBSTR(MGR,1,2) = 78 THEN 8888
        ELSE MGR
    END AS CHG_MGR
    FROM EMP;

🐶SCOTT 계정 문제🐸

1. 오늘 날짜에 대한 정보 조회

SELECT SYSDATE FROM EMP;

2. EMP테이블에서 사번, 사원명, 급여 조회 (단, 급여는 100단위까지의 값만 출력 처리하고 급여 기준 내림차순 정렬)

SELECT EMPNO, ENAME,
    TRUNC(SAL,-2) AS SAL
    FROM EMP
    ORDER BY SAL DESC;

3. EMP테이블에서 사원번호가 홀수인 사원들을 조회

SELECT *
    FROM EMP
    WHERE MOD(EMPNO,2) = 1;

4. EMP테이블에서 사원명, 입사일 조회 (단, 입사일은 년도와 월을 분리 추출해서 출력)

SELECT ENAME,
    TO_CHAR(HIREDATE, 'YYYY') AS HIREYEAR,
    TO_CHAR(HIREDATE, 'MM') AS HIREMONTH
    FROM EMP;

5. EMP테이블에서 9월에 입사한 직원의 정보 조회

SELECT *
    FROM EMP
    WHERE EXTRACT(MONTH FROM HIREDATE) = 9;

6. EMP테이블에서 81년도에 입사한 직원 조회

SELECT *
    FROM EMP
    WHERE EXTRACT(YEAR FROM HIREDATE) = 1981;

7. EMP테이블에서 이름이 'E'로 끝나는 직원 조회

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

8. EMP테이블에서 이름의 세번째 글자가 'R'인 직원의 정보 조회

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

9. EMP테이블에서 사번, 사원명, 입사일, 입사일로부터 40년 되는 날짜 조회

SELECT EMPNO, ENAME, HIREDATE,
    ADD_MONTHS(HIREDATE, 480) AS "입사일40주년"
FROM EMP;

10. EMP테이블에서 입사일로부터 38년 이상 근무한 직원의 정보 조회

SELECT *
    FROM EMP
    WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) >= 456;

11. 오늘 날짜에서 년도만 추출

SELECT TO_CHAR(SYSDATE, 'YYYY')
    FROM DUAL;