내장함수

csatlemin 2022. 11. 2. 18:38

------------------------------------------------------------------------------------------------------------------------
-- 오라클의 내장함수
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
-- 오라클의 내장함수 : 문자함수
------------------------------------------------------------------------------------------------------------------------

-- LENGTH(컬럼명 혹은 표현식) : 길이를 반환한다.
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- LOWER(컬럼명 혹은 표현식), UPPER(컬럼명 혹은 표현식) : 소문자 혹은 대문자로 변환한다.
SELECT FIRST_NAME, LOWER(FIRST_NAME), UPPER(FIRST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- SUBSTR(컬럼명 혹은 표현식, 시작위치) : 지정된 시작위치부터 끝까지 잘라서 반환한다.
-- SUBSTR(컬럼명 혹은 표현식, 시작위치, 길이) : 지정된 시작위치부터 길이만큼 잘라서 반환한다.
SELECT JOB_ID, SUBSTR(JOB_ID, 4), SUBSTR(JOB_ID, 4, 2)
FROM EMPLOYEES
WHERE SALARY >= 10000;

-- INSTR(컬럼명 혹은 표현식, '텍스트') : 지정된 텍스트가 등장하는 위치를 반환한다.
-- INSTR(컬럼명 혹은 표현식, '텍스트', 검색시작위치) : 지정된 텍스트가 등장하는 위치를 검색시작위치부터 찾아서 반환한다.
SELECT FIRST_NAME, INSTR(FIRST_NAME, 'e')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

SELECT FIRST_NAME, INSTR(UPPER(FIRST_NAME), 'A'), instr(lower(first_name), 'A')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- LPAD(컬럼명 혹은 표현식, 길이, '텍스트') : 지정된 길이보다 짧으면 왼쪽에 텍스트를 채워서 지정된 길이로 맞춘다.
-- RPAD(컬럼명 혹은 표현식, 길이, '텍스트') : 지정된 길이보다 짧으면 오른쪽에 텍스트를 채워서 지정된 길이로 맞춘다.
SELECT FIRST_NAME, LPAD(FIRST_NAME, 15, '#'), RPAD(FIRST_NAME, 15, '#')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- CONCAT(컬럼 혹은 표현식, 컬럼 혹은 표현식) : 두 값을 합쳐서 새로운 텍스트를 반환한다.
SELECT CONCAT(FIRST_NAME, LAST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- ||는 텍스트를 연결한다. (자바에서 + 같은 기능을한다.)
SELECT FIRST_NAME || ' ' ||LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- REPLACE(컬럼 혹은 표현식, '텍스트', '대체할 텍스트') : 텍스트에 해당하는 문자를 지정된 텍스트로 대체한다.
SELECT FIRST_NAME, REPLACE(FIRST_NAME, 'a', '*')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

------------------------------------------------------------------------------------------------------------------------
-- 오라클의 내장함수 : 숫자함수
------------------------------------------------------------------------------------------------------------------------

-- ROUND(컬럼 혹은 표현식) : 소숫점 1번째 자리에서 반올림한다.
-- ROUND(컬럼 혹은 표현식, 자리수) : 지정된 자리수로 반올림한다. (음수도 가능하다.)
SELECT ROUND(123.45), ROUND(1234.5), -- 소수점 1번째 자리에서 반올림한다.
       ROUND(123.4567, 3),  -- 소수점 3번째자리로 반올림한다.
       ROUND(123.4567, 0),  -- 1의 자리로 반올림한다.
       ROUND(123.4567, -1), -- 10의 자리로 반올림한다.
       ROUND(123.456, -2)   -- 100의 자리로 반올림한다.
FROM DUAL;

-- TRUNC(컬럼 혹은 표현식) : 소수점부분을 전부 버린다.
-- TRUNC(컬럼 혹은 표현식, 자리수) : 지정된 자리수만큼 남기고 전부 버린다.
SELECT TRUNC(1234.1), TRUNC(1234.5), TRUNC(1234.9), -- 소수점 이하를 전부 버린다.
       TRUNC(1234.1, 0),    -- 1의 자리 이하를 전부 버린다.
       TRUNC(1234.1, -2),   -- 10의 자리 이하를 전부 버린다. (0으로 바꾼다.)
       TRUNC(1234.1, -3)    -- 1000의 자리 이하를 전부 버린다. (0으로 바꾼다.)
FROM DUAL;

SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT, TRUNC(SALARY*COMMISSION_PCT, -2)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

-- MOD(칼럼 혹은 표현식, 숫자) : 나머지 값을 반환한다.
SELECT 5/2,         -- 나누기: 2.5
       MOD(5, 2)    -- 나머지: 1
FROM DUAL;

------------------------------------------------------------------------------------------------------------------------
-- 오라클의 내장함수 : 날짜함수
------------------------------------------------------------------------------------------------------------------------

-- SYSDATE : 시스템의 현재 날짜와 시간정보가 포함된 날짜정보를 반환한다. 입력값이 없는 함수는 ()를 생략한다.
SELECT SYSDATE
FROM DUAL;

-- MONTHS_BETWEEN(날짜, 날짜) : 두 날짜 사이의 개월수를 반환한다.
SELECT FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) MONTHS
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- ADD_MONTHS(날짜, 개월수) : 지정된 날짜에서 개월수 만큼 경과한 날짜를 반환한다.
SELECT ADD_MONTHS(SYSDATE, 1), ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, 3), ADD_MONTHS(SYSDATE, 6),
       ADD_MONTHS(SYSDATE, -1), ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, -3), ADD_MONTHS(SYSDATE, -6)
FROM DUAL;

-- ROUND(날짜) : 날짜를 반올림한다, 정오가 넘어가면 날짜가 하루 증가된 값을 반환한다.(시분초값은 전부 0이다)
-- TRUNC(날짜) : 날짜에서 시분초값을 전부 버린다.
SELECT SYSDATE,         -- 2022-10-18 11:06:36      -- 2022-10-18 14:06:52
       ROUND(SYSDATE),  -- 2022-10-18 00:00:00      -- 2022-10-19 00:00:00
       TRUNC(SYSDATE)   -- 2022-10-18 00:00:00      -- 2022-10-18 00:00:00
FROM DUAL;

-- 날짜와 관련된 사칙연산
-- 날짜 + 정수 : 지정된 날짜에서 정수만큼 경과된 날짜를 반환한다.
-- 날짜 - 정수 : 지정된 날짜에서 지정된 정수만큼 이전 날짜를 반환한다.
-- 날짜 + 정수/24 : 지정된 날짜에서 지정된 정수시간만큼 경과된 날짜를 반환한다.
-- 날짜 - 정수/24 : 지정된 날짜에서 지정된 정수시간만큼 이전 날짜를 반환한다.
-- 날짜 - 날짜 : 두 날짜 사이의 경과될 일 수를 반환한다.

SELECT SYSDATE + 3,     -- 지금을 기준으로 3일 후
       SYSDATE - 3,     -- 지금을 기준으로 3일 전
       SYSDATE + 3/24,  -- 지금을 기준으로 3시간 후
       SYSDATE - 3/24   -- 지금을 기준으로 3시간 전
FROM DUAL;

-- 지금(2022/10/18 11:24:58)을 기준으로 최근 7일 동안의 주문내역을 조회하기
-- 7일 전 : 2022-10-11 00:00:00
-- 오늘 0시 : 2022-10-18 00:00:00
-- ORDERS 테이블이 존재하지 않으므로 아래 SQL은 오류가 발생함.
SELECT *
FROM ORDERS
WHERE DODER_DATE >= TRUNC(SYSDATE - 7) AND ORDER_DATE < TRUNC(SYSDATE);

------------------------------------------------------------------------------------------------------------------------
-- 오라클의 내장함수 : 타입 변환함수
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
-- 묵시적 타입변환 : 컬럼의 데이터타입을 참조해서 자동으로 데이터타입을 변환하는 것
------------------------------------------------------------------------------------------------------------------------

SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;

-- 묵시적 타입변환 : 컬럼의 데이터타입을 참조해서 자동으로 데이터타입으로 변환하는 것
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = '100'; -- EMPLOYEE_ID 컬럼의 데이터타입이 NUMBER타입이기 때문에 '100'이 100으로 타입이 변환된다.

SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE = '2007-01-14'; -- HIRE_DATE 컬럼의 데이터타입이 DATE 타입이기 때문에 '2007-01-14'가 DATE타입으로 변환된다.

SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE = '2007/01/14'; -- HIRE_DATE 컬럼의 데이터타입이 DATE 타입이기 때문에 '2007/01/14'가 DATE타입으로 변환된다.

-- 2007년에 입사한 사원 조회하기
SELECT *
FROM EMPLOYEES
WHERE HIRE_DATE >= '2007/01/01' AND HIRE_DATE < '2008/01/01';

-- 2007년에 입사한 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= '2007/01/01' AND HIRE_DATE < '2008/01/01'
ORDER BY HIRE_DATE;

------------------------------------------------------------------------------------------------------------------------
-- 명시적 형변화
------------------------------------------------------------------------------------------------------------------------

-- TO_CHAR(숫자, '포맷형식') : 숫자를 지정된 포맷형식의 텍스트로 변환한다.
-- SQL에서 숫자값을 ,가 포함된 텍스트로 변환하는 작업은 추천되는 작업이 아니다.
SELECT EMPLOYEE_ID, FIRST_NAME, TO_CHAR(SALARY, '99,999')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- TO_CHAR(날짜, '포맷형식') : 날짜를 지정된 포맷형식의 텍스트로 변환한다.
SELECT
       TO_CHAR(SYSDATE, 'YYYY') YEAR, -- DATE타입을 텍스트로 변환한다. SYSDATE -> '2022'
       TO_CHAR(SYSDATE, 'MM/DD') DAY, -- DATE타입을 텍스트로 변환한다. SYSDATE -> '10/18'
       -- TO_CHAR(HIRE_DATE, 'M/D')   -- D나 M을 한번씩만 적으면 오류가 발생한다.
       TO_CHAR(SYSDATE,'AM'),         -- DATE타입을 텍스트로 변환한다. SYSDATE -> '오후'
       TO_CHAR(SYSDATE,'HH:MI:SS'),   -- DATE타입을 텍스트로 변환한다. SYSDATE -> '12:24:11'
       TO_CHAR(SYSDATE,'HH24:MI:SS')  -- DATE타입을 텍스트로 변환한다. SYSDATE -> '12:24:11'
FROM DUAL;

-- 직원들이 입사한 월을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, TO_CHAR(HIRE_DATE, 'MM') MONTHS
FROM EMPLOYEES;

-- 년도에 상관없이 이번달 입사자 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'MM') = TO_CHAR(SYSDATE, 'MM');

-- TO_NUMBER('텍스트', '패턴') : 지정된 패턴과 일치하는 텍스트를 숫자로 변환한다.
-- 텍스트에 숫자가 아닌 텍스트가 포함되어 있지 않으면 패턴을 지정하지 않아도 된다.
SELECT TO_NUMBER('1234') + TO_NUMBER('1234')    -- 명시적 형변환
FROM DUAL;

-- 텍스트에 숫자가 아닌 텍스트가 포함되어 있지 않으면 명시적 형변환이 필요없다.
SELECT '1234' + '1234'  -- 묵시적 형변환
FROM DUAL;

-- 텍스트에 숫자가 아닌 텍스트가 포함되어 있는 패턴을 지정해야 한다.
SELECT TO_NUMBER('1,234', '9,999') + TO_NUMBER('1,234', '9,999') -- 명시적 형변환
FROM DUAL;

SELECT '1,234' + '1,234'  -- 묵시적 형변환 오류
FROM DUAL;

-- TO_DATE('텍스트', '패턴') : 지정된 패턴과 일치하는 텍스트를 날짜로 변환한다.
SELECT TRUNC(SYSDATE) - TO_DATE('2000/01/01', 'YYYY/MM/DD')
FROM DUAL;

------------------------------------------------------------------------------------------------------------------------
-- 오라클의 내장함수 : 기타 함수
------------------------------------------------------------------------------------------------------------------------

-- NVL(컬럼 혹은 표현식, 대체할 값) : 지정된 컬럼 혹은 표현식의 값이 NULL이면 대체할 값을 반환한다.
--                              : 원래 값과 대체할 값의 타입이 같은 타입이어야 한다.
-- 급여 실수령액을 계산하기
-- 실수령액 = SALARY + SALARY*COMMISSION_PCT다.
-- * COMMISSION_PCT가 NULL인 경우 실수령액이 NULL값으로 계산된다.
-- * NVL()함수를 사용해서 COMMISSION_PCT가 NULL일때, 0을 반환하도록 한다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT,
       NVL(COMMISSION_PCT, 0),  -- COMMISSION_PCT값이 NULL이면 0을 반환하고, 아니면 COMMISSION_PCT값을 반환한다.
       SALARY+ SALARY*NVL(COMMISSION_PCT, 0) REAL_SALARY
FROM EMPLOYEES
WHERE SALARY >= 10000;

-- NVL2(컬럼 혹은 표현식, NULL이 아닐 때 대체할 값, NULL일 때 대체할 값)
-- * 대체할 값은 데이터타입이 동일한 값이어야 한다.

SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, NVL2(COMMISSION_PCT, '커미션 받음', '커미션 받지 않음')
FROM EMPLOYEES
WHERE SALARY >= 10000;

-- CASE ~ WHEN 표현식

-- if ~ else if ~ else if ~ else 형식으로 사용하기
-- CASE
--      WHEN 조건식1 THEN 표현식1 -- 조건식1이 TRUE로 판정되면 표현식1이 최종결과가 된다.
--      WHEN 조건식2 THEN 표현식2 -- 조건식2가 TRUE로 판정되면 표현식2가 최종결과가 된다.
--      WHEN 조건식3 THEN 표현식3 -- 조건식3이 TRUE로 판정되면 표현식3이 최종결과가 된다.
--      ELSE 표현식4             -- 조건식1, 조건식2, 조건식3이 모두 FALSE로 판정되면 표현식4가 최종결과가 된다.
--  END

-- switch문 형식으로 사용하기
--  CASE 컬럼 혹은 표현식
--      WHENE 값1 HTEN 표현식1   -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종 결과가 된다.
--      WHENE 값2 HTEN 표현식2   -- 컬럼 혹은 표현식의 값이 값2와 일치하면 표현식2가 최종 결과가 된다.
--      WHENE 값3 HTEN 표현식3   -- 컬럼 혹은 표현식의 값이 값3과 일치하면 표현식3이 최종 결과가 된다.
--      ELSE 표현식4              -- 컬럼 혹은 표현식의 값과 모두 일치하지 않으면 표현식4가 최종결과가 된다.
--  END

-- 직원아이디, 이름, 급여등급을 조회하기
-- 급여등급 A-20000이상, B-15000이상, C-1000이상, D-5000이상, E-2500이상 F-1000이상 G-그 외 급여
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
    CASE
        WHEN SALARY >= 20000 THEN 'A'
        WHEN SALARY >= 15000 THEN 'B'
        WHEN SALARY >= 10000 THEN 'C'
        WHEN SALARY >= 5000 THEN 'D'
        WHEN SALARY >= 2500 THEN 'E'
        WHEN SALARY>= 1000 THEN 'F'
        ELSE 'G'
    END AS EMPLOYEE_GRADE
FROM EMPLOYEES
ORDER BY EMPLOYEE_GRADE;

-- 직원아이디, 이름, 급여, 보너스 조회하기
-- 보너스는 10000불 이상은 급여의 10%, 5000불 이상은 20%, 그 외는 급여의 30%를 보너스로 지급한다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
    CASE
        WHEN SALARY >= 10000 THEN SALARY*0.1
        WHEN SALARY >= 5000 THEN SALARY*0.2
        ELSE SALARY*0.3
    END AS BONUS
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID ASC;

-- 100번 부서에 근무하는 직원의 직원아이디, 이름, 급여, 보너스를 조회하기
-- 보너스는 직종에 따라서 지급되며, FI_MGR: 10%, FI_ACCOUNT:20%를 지급한다.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY,
    CASE JOB_ID
        WHEN 'FI_MGR' THEN SALARY*0.1
        WHEN 'FI_ACCOUNT' THEN SALARY*0.2
    END AS BONUS
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100;

-- DECODE() 함수
-- DECODE(컬럼 혹은 표현식, 값1, 표현식1,  -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종결과가 된다.
-- 값2, 표현식2,  -- 컬럼 혹은 표현식의 값이 값2와 일치하면 표현식2가 최종결과가 된다.
-- 값3, 표현식3)  -- 컬럼 혹은 표현식의 값이 값3과 일치하면 표현식3이 최종결과가 된다.

-- DECODE(컬럼 혹은 표현식, 값1, 표현식1,   -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종결과가 된다.
-- 값2, 표현식2,  -- 컬럼 혹은 표현식의 값이 값2와 일치하면 표현식2가 최종결과가 된다.
-- 값3, 표현식3,  -- 컬럼 혹은 표현식의 값이 값3과 일치하면 표현식3이 최종결과가 된다.
-- 표현식4) -- 컬럼 혹은 표현식의 값이 값1, 값2, 값3 모두와 일치하지 않으면 표현식4 가 최종결과가 된다.

-- 부서별로 팀 나누기,
-- A팀(10, 20, 30, 40번 부서), B팀(50,60번 부서) C팀(70, 80번 부서), D팀(그 외 부서)로 직원들의 팀을 나누기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID,
        DECODE(DEPARTMENT_ID, 10, 'A팀',
20, 'B팀',
30, 'A팀',
40, 'A팀',
50, 'B팀',
60, 'B팀',
70, 'C팀',
80, 'C팀',
'D팀') TEAM
FROM EMPLOYEES
ORDER BY TEAM ASC, DEPARTMENT_ID ASC;