Function

단일행 함수

하나의 입력 행(row)에 대해 하나의 결과 값을 반환하는 함수

단일행 함수 종류

1. 문자 함수
2. 숫자 함수
3. 날짜 함수
4. 변환 함수
5. 일반 함수

문자 함수

1. UPPER(string) - 문자열의 모든 문자를 대문자로 변환합니다.
2. LOWER(string) - 문자열의 모든 문자를 소문자로 변환합니다.
3. INITCAP(string) - 문자열의 첫 글자를 대문자로, 나머지 글자는 소문자로 변환합니다.
4. CONCAT(string1, string2) - 두 개의 문자열을 연결합니다.
5. SUBSTR(string, start_position, [length]) - 문자열에서 지정된 위치에서 시작하여 특정 길이만큼의 부분 문자열을 반환합니다.
   -> 시작 인덱스가 0이 아니고 1
6. LENGTH(string) - 문자열의 길이를 반환합니다.
7. REPLACE(string, search_string, replace_string) - 문자열에서 특정 문자열을 찾아 다른 문자열로 교체합니다.
   -> REPLACE(string, search_string) 으로 쓰면 문자열에서 특정 문자열을 찾아서 삭제
8. TRIM([trim_character FROM] string) - 문자열의 앞뒤에서 지정된 문자(또는 공백)를 제거합니다.
9. LPAD(string, length, [pad_string]) - 문자열의 왼쪽에 특정 문자열을 채워 지정된 길이를 만족하도록 합니다.
10. RPAD(string, length, [pad_string]) - 문자열의 오른쪽에 특정 문자열을 채워 지정된 길이를 만족하도록 합니다.
11. INSTR(string, search_string, [start_position], [nth_occurrence]) - 문자열에서 특정 문자열이 나타나는 위치를 반환합니다.

SELECT
    emp_nm, SUBSTR(addr, 1, 3 ) -- 처음이 0이 아니고 1
FROM tb_emp
;


SELECT 
    LOWER('Hello WORLD'),  -- 전부 소문자로 변환
    UPPER('hello World'),  -- 전부 대문자로 변환
    INITCAP('abcDEF')      -- 첫글자만 대문자로 나머지는 소문자로 변환
FROM dual;

SELECT
    ASCII('A'), -- 문자를 아스키코드로
    CHR(97)     -- 아스키코드를 문자로
FROM dual;

SELECT 
    CONCAT('SQL', 'Developer'),   -- 문자열을 결합
    SUBSTR('SQL Developer', 1, 3), -- 문자열 자름 1번부터 3개자름 (첫글자가 1번)
    LENGTH('HELLO WORLD'),         -- 문자열의 길이
    TRIM('    HI   '),             -- 좌우 공백 제거
    LTRIM('  HELLO   '),           -- 좌 공백 제거
    RTRIM('  HELLO   '),           -- 우 공백 제거
    LTRIM('HELLOhello', 'HE') -- 왼쪽에서 발견된 HE를 제거
FROM dual;

SELECT 
    RPAD('Steve', 10, '-'), -- 오른쪽에 주어진 문자를 채움
    LPAD('Steve', 10,  '*'),  -- 왼쪽에 주어진 문자를 채움
    REPLACE('Java Programmer Java', 'Java', 'BigData') AS "REPLACE" -- 문자를 변경
    , REPLACE('Java Programmer', 'Java') AS "REPLACE" -- 문자를 제거
FROM dual;

숫자 함수

1. ROUND(number, [decimal_places]) - 숫자를 지정된 소수점 자릿수로 반올림합니다. 소수점 자릿수를 생략하면 정수로 반올림합니다.
2. TRUNC(number, [decimal_places]) - 숫자를 지정된 소수점 자릿수로 절사합니다. 소수점 자릿수를 생략하면 정수로 절사합니다.
3. MOD(number1, number2) - 첫 번째 숫자를 두 번째 숫자로 나눈 나머지를 반환합니다.
4. CEIL(number) - 숫자를 지정된 소수점 자릿수로 올림합니다
5. FLOOR(number) - 숫자를 지정된 소수점 자릿수로 내합니다
6. POWER(number, exponent) - 숫자를 지정된 거듭제곱 값으로 계산합니다.
7. ABS(number) - 숫자의 절대값을 반환합니다.
8. SIGN(number) - 숫자의 부호를 반환합니다. 양수이면 1, 음수이면 -1, 0이면 0을 반환합니다.
9. SQRT(number) - 숫자의 제곱근을 반환합니다.
10. LN(number) - 숫자의 자연로그 값을 반환합니다.
11. LOG(base, number) - 지정된 밑을 가지는 숫자의 로그 값을 반환합니다.
12. EXP(number) - e를 지정된 거듭제곱 값으로 계산합니다.
13. SIN(number), COS(number), TAN(number) - 숫자의 사인, 코사인, 탄젠트 값을 계산합니다.

SELECT 
    MOD(27, 5) AS MOD, -- 나머지 값 반환
    CEIL(38.678) AS ceil, -- 올림값 반환
    FLOOR(38.678) AS floor, -- 내림값 반환
    ROUND(38.678, 2) AS round, -- 자리수까지 반올림
    TRUNC(38.678, 2) AS trunc  -- 자리수 이하를 절삭
    , ABS(-20) AS abs   -- 절대값
    , SIGN(99) AS sign  -- 음수면 -1, 양수면 1, 0이면 0
FROM dual;

-- 11. employees 테이블에서 각 사원의 salary를 100달러 단위로 올림하여 출력하세요.
SELECT
    CEIL(salary/100)*100
FROM employees
;

날짜 함수

1. ADD_MONTHS(date, months) - 날짜에 지정된 개월 수를 더한 날짜를 반환합니다.
2. LAST_DAY(date) - 지정된 날짜가 속한 달의 마지막 날짜를 반환합니다.
3. NEXT_DAY(date, day_of_week) - 지정된 날짜 이후의 특정 요일을 반환합니다.
4. MONTHS_BETWEEN(date1, date2) - 두 날짜 간의 개월 수 차이를 반환합니다.
5. NEW_TIME(date, timezone1, timezone2) - 날짜 값을 한 시간대에서 다른 시간대로 변환합니다.
6. SYSTIMESTAMP - 현재 시스템 타임스탬프를 반환합니다.
7. TRUNC(date, [format]) - 날짜를 지정된 형식으로 절사합니다. 형식을 생략하면 날짜만 남겨두고 시간 부분을 제거합니다.
8. ROUND(date, [format]) - 날짜를 지정된 형식으로 반올림합니다. 형식을 생략하면 날짜만 남겨두고 시간 부분을 제거합니다.
9. SYSDATE - 현재 날짜를 반환

-- 현재 날짜를 조회
SELECT SYSDATE
FROM dual;

SELECT SYSTIMESTAMP
FROM dual;

-- 날짜 연산
-- 날짜 + 숫자 = 날짜  => 일(DAY) 수를 날짜에 더함
-- 날짜 - 숫자 = 날짜  => 날짜에서 일 수를 뺌
-- 날짜 - 날짜 = 일수  => 어떤 날짜에서 다른 날짜를 뺀 일수
-- 날짜 + 숫자/24 = 날짜  => 날짜에 시간을 더함

SELECT 
    SYSDATE AS "현재 시간",
    SYSDATE - 1 AS "SYSDATE - 1", -- 1일 뺌
    (SYSDATE + 10) - SYSDATE AS "날짜 - 날짜", -- 10일
    SYSDATE - (1/24) AS "1시간 차감",
    SYSDATE - (1/24/60) * 100 AS "100분 차감", -- (1/24/60)만 있으면 1분
    SYSDATE - (1/24/60/60) * 30 AS "30초 차감" -- (1/24/60/60)있으면 1초
FROM dual;

변환 함수

1. TO_CHAR(date, [format]) - 날짜를 지정된 형식의 문자열로 변환합니다.
2. TO_CHAR(number, [format]) - 숫자를 지정된 형식의 문자열로 변환합니다.
3. TO_NUMBER(string, [format]) - 문자열을 지정된 형식의 숫자로 변환합니다.
4. TO_DATE(string, [format]) - 문자열을 지정된 형식의 날짜로 변환합니다.
5. TO_TIMESTAMP(string, [format]) - 문자열을 지정된 형식의 타임스탬프로 변환합니다.
6. TO_TIMESTAMP_TZ(string, [format]) - 문자열을 지정된 형식의 타임스탬프 (시간대 포함)로 변환합니다.
7. TO_YMINTERVAL(string) - 문자열을 연-월 간격 유형으로 변환합니다.
8. TO_DSINTERVAL(string) - 문자열을 일-초 간격 유형으로 변환합니다.

SELECT * FROM char_compare
WHERE sn = 101;

-- 날짜를 문자로 변환 (TO_CHAR함수)
-- 날짜 포맷형식: Y - 연도, MM - 두자리 월, D - 일수
-- 시간 포맷형식: HH12 - 시(1~12), HH24 (0-23), MI - 분, SS - 초
SELECT
    SYSDATE,
    TO_CHAR(SYSDATE, 'MM - DD') AS "월 - 일",
    TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS "연/월/일",
    TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"') AS "날짜 - 한글포함",
    TO_CHAR(SYSDATE + (1/24) * 6, 'YY/MM/DD HH24:MI:SS') AS "날짜와 시간",
    TO_CHAR(SYSDATE - (1/24) * 6, 'YY/MM/DD AM HH12:MI:SS') AS "날짜와 시간2"
FROM dual;

-- 숫자를 문자로 변환
-- 숫자 포맷 형식 -  $: 달러표시, L: 지역화폐기호
SELECT
    TO_CHAR(9512 * 1.33, '$999,999.99') AS "달러",
    TO_CHAR(1350000, 'L999,999,999') AS "원화"
FROM dual;

-- 문자를 숫자로 변환
SELECT 
    TO_NUMBER('$5,500', '$999,999') - 4000 AS "계산결과"
FROM dual;

-- 날짜 변환 함수
SELECT
    TO_DATE('20150101000000', 'YYYYMMDDHH24MISS'),
    TO_DATE('20240101','YYYYMMDD'), -- 시분초 생략시 00:00:00 세팅
    TO_DATE('202401','YYYYMM'), -- 날짜 생략시 01로 세팅
    TO_CHAR(SYSDATE,'YYYYMM')
FROM dual
;

NULL 관련 함수

1. NVL(expression1, expression2) - expression1이 NULL인 경우 expression2를 반환하고, 그렇지 않으면 expression1을 반환합니다.
2. NVL2(expression1, expression2, expression3) - expression1이 NULL이 아닌 경우 expression2를 반환하고, NULL인 경우 expression3를 반환합니다.
3. NULLIF(expression1, expression2) - expression1과 expression2가 같으면 NULL을 반환하고, 그렇지 않으면 expression1을 반환합니다.
4. COALESCE(expression1, expression2, ...) - 인수 목록에서 첫 번째 NULL이 아닌 값을 반환합니다. 모든 인수가 NULL이면 NULL을 반환합니다.

SELECT 
    emp_no
    , emp_nm
    , direct_manager_emp_no
FROM tb_emp
;

SELECT 
    emp_no
    , emp_nm
    , NVL(direct_manager_emp_no, '최상위관리자') AS 관리자
FROM tb_emp
;

SELECT 
--     emp_nm
     NVL(emp_nm, '존재안함') AS emp_nm,
     ADDR 
FROM tb_emp
WHERE emp_nm = '이정직';

SELECT 
    direct_manager_emp_no
FROM tb_emp
WHERE emp_nm = '김회장'
;


SELECT 
--     MAX(emp_nm) -- null 나옴
--     NVL(emp_nm, '존재안함') AS emp_nm -- null 안나옴
     NVL(MAX(emp_nm), '존재안함') AS emp_nm
FROM tb_emp
WHERE emp_nm = '이승엽';

-- NVL2(expr1, expr2, expr3)
-- expr1의 값이 Null이 아니면 expr2를 반환, Null이면 expr3를 반환
SELECT 
    emp_nm,
    NVL2(direct_manager_emp_no, '일반사원', '회장님') AS 직위
FROM tb_emp;

-- NULLIF(expr1, expr2)
-- 두 값이 같으면 NULL리턴, 다르면 expr1 리턴
SELECT
    NULLIF('박찬호', '박찬호')
FROM dual;

SELECT
    NULLIF('박찬호', '박지성')
FROM dual;

-- COALESCE(expr1, ...)
-- 많은 표현식 중 Null이 아닌 값이 최초로 발견되면 해당 값을 리턴
SELECT 
    COALESCE(NULL, NULL, 3000, 4000)
FROM dual;

SELECT 
    COALESCE(NULL, 5000, NULL, 2000)
FROM dual;

SELECT 
    COALESCE(7000, NULL, NULL, 8000)
FROM dual;

CASE표현과 DECODE함수

SELECT * FROM tb_sal;

-- Searched expression
SELECT 
    sal_cd, 
    sal_nm,
    CASE WHEN sal_cd = '100001' THEN '기본급여'
         WHEN sal_cd = '100002' THEN '보너스급여'
         ELSE '기타'
     END sal_name -- END = AS
FROM tb_sal;


-- Simple expression
SELECT 
    sal_cd, 
    sal_nm,
    CASE sal_cd 
    	  WHEN '100001' THEN '기본급여'
          WHEN '100002' THEN '보너스급여'
         ELSE '기타'
     END sal_name
FROM tb_sal;


SELECT
    sal_cd,
    DECODE(sal_cd, '100001', '기본급여', '100002', '보너스급여', '기타') AS sal_name
FROM tb_sal;