함수 (1) - 단일행 함수 (23.05.16~17)

·2023년 5월 16일
0

Oracle

목록 보기
4/15
post-thumbnail

📝 함수

컬럼의 값을 읽어서 연산한 결과를 반환
-> 함수는 SELECT절, WHERE절, ORDER BY, GROUP BY, HAVING 사용 가능

  • 종류: 단일행 함수 / 그룹 함수

💡 단일행(SINGLE ROW) 함수

N개의 값을 읽어서 N개의 결과 반환


✏️ LENGTH(문자열 | 컬럼)

문자열 길이 반환

SELECT LENGTH('HELLO WORLD') FROM DUAL;

-- 12글자인 이메일만 조회
SELECT EMAIL, LENGTH(EMAIL) FROM EMPLOYEE
WHERE LENGTH(EMAIL) = 12;

✏️ INSTR('문자열' | 컬럼명, '찾을 문자', [찾을 위치 시작 위치, [순번]])

지정한 위치부터 지정한 순번째로 검색되는 문자의 시작 위치를 반환

-- 문자열에서 맨 앞에 있는 B 위치 조회
SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;

-- 문자열에서 5번째부터 검색해서 맨 앞에 있는 B의 위치 조회
SELECT INSTR('AABAACAABBAA', 'B', 5) FROM DUAL;

-- EMPLOYEE 테이블에서 사원명, 이메일, 이메일 중 "@" 위치 조회
SELECT EMP_NAME, EMAIL, INSTR(EMAIL, '@')
FROM EMPLOYEE;

✏️ SUBSTR('문자열' | 컬럼명, 잘라내기 시작할 위치 [, 잘라낼 길이])

컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라내서 반환
-> 잘라낼 길이 생략 시 끝까지 잘라냄

-- EMPLOYEE 테이블에서 사원명, 이메일 중 아이디만 조회 sun_di@or.kr
SELECT EMP_NAME, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') -1 ) 아이디
FROM EMPLOYEE
ORDER BY 아이디;

✏️ TRIM([옵션] '문자열' | 컬럼명 [FROM '문자열' | 컬럼명] )

주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거
-> (보통 양쪽 공백 제거에 많이 사용)

  • 옵션 : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)
SELECT '     K  H     ', TRIM('     K  H     ') FROM DUAL; -- 양쪽 공백 제거( 중간 미포함)

SELECT '---KH---', TRIM(TRAILING '-' FROM '---KH---') FROM DUAL;
-- BOTH 또는 생략 시 : 양쪽 '-' 기호 제거
-- LEADING : 앞쪽만 제거
-- TRAILING : 뒤쪽만 제거

💡 숫자 관련 함수


✏️ ABS(숫자 | 컬럼명)

절댓값 반환

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

✏️ MOD(숫자 | 컬럼명, 숫자 | 컬럼명)

나머지 값 반환

-- EMPLOYEE 테이블에서 사원의 월급을 100만으로 나눴을 때 나머지 조회
SELECT EMP_NAME, SALARY, MOD(SALARY, 1000000)
FROM EMPLOYEE;

✏️ ROUND(숫자 | 컬럼명 [, 소수점 위치])

반올림

SELECT 123.456, ROUND(123.456) FROM DUAL; -- 소수점 첫째 자리에서 반올림
SELECT 123.456, ROUND(123.456, 1) FROM DUAL; -- 소수점 첫째 자리까지 출력 == 소수점 둘째 자리 반올림
SELECT 123.456, ROUND(123.456, 2) FROM DUAL;

SELECT 123.456, ROUND(123.456, 0) FROM DUAL; -- 소수점 첫째 자리에서 반올림
SELECT 123.456, ROUND(123.456, -1) FROM DUAL; -- 소수점 0번째 자리에서 반올림
SELECT 123.456, ROUND(123.456, -2) FROM DUAL; -- 소수점 -1번째 자리에서 반올림

✏️ CEIL(숫자 | 컬럼명)

올림

✏️ FLOOR(숫자 | 컬럼명)

내림
-> 둘 다 소수점 첫째 자리에서 올림/내림 처리

SELECT 123.5, CEIL(123.5), FLOOR(123.5) FROM DUAL;

✏️ TRUNC(숫자 | 컬럼명 [, 위치])

특정 위치 아래를 버림(절삭)

SELECT TRUNC(123.456, 1), TRUNC(123.456, -1)
FROM DUAL;
  • 버림, 내림의 차이점
SELECT TRUNC(-123.5), FLOOR(-123.5)
FROM DUAL;


💡 날짜(DATE) 관련 함수


✏️ SYSDATE

시스템의 현재 시간(년, 월, 일, 시, 분, 초)을 반환

SELECT SYSDATE FROM DUAL;

✏️ SYSTIMESTAMP

SYSDATE + MS 단위 추가

SELECT SYSTIMESTAMP FROM DUAL;

✏️ MONTHS_BETWEEN(날짜, 날짜)

두 날짜의 개월 수 차이 반환

SELECT ROUND( MONTHS_BETWEEN(SYSDATE, '2022/02/21') ) || '개월' AS 수강기간 FROM DUAL;

-- EMPLOYEE 테이블에서
-- 사원의 이름, 입사일, 근무 개월 수, 근무 햇수 조회
SELECT EMP_NAME, HIRE_DATE,
    '근무 ' || CEIL ( MONTHS_BETWEEN(SYSDATE, HIRE_DATE) ) || '개월 차' "근무 개월 수",
    '근무 ' || CEIL ( MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 ) || '년 차' "근무 햇수"
FROM EMPLOYEE;

✏️ ADD_MONTHS(날짜, 숫자)

날짜에 숫자만큼의 개월 수를 더함

SELECT ADD_MONTHS(SYSDATE, 4) +7 FROM DUAL;

✏️ LAST_DAY(날짜)

해당 달의 마지막 날짜를 구함

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY('2023/04/01') FROM DUAL;

✏️ EXTRACT

년, 월, 일 정보를 추출하여 리턴

EXTRACT(YEAR FROM 날짜)

년도만 추출

EXTRACT(MONTH FROM 날짜)

월만 추출

EXTRACT(DAY FROM 날짜)

일만 추출

-- EMPLOYEE 테이블에서 각 사원의 이름, 입사 년도, 입사 월, 입사 일 조회
SELECT EMP_NAME 이름,
    EXTRACT(YEAR FROM HIRE_DATE) "입사 년도",
    EXTRACT(MONTH FROM HIRE_DATE) "입사 월",
    EXTRACT(DAY FROM HIRE_DATE) "입사 일"
FROM EMPLOYEE
WHERE EXTRACT(MONTH FROM HIRE_DATE) = 1;

💡 형변환 함수

문자열(CHAR), 숫자(NUMBER), 날짜(DATE)끼리 형변환 가능


✏️ TO_CHAR (문자열로 변환)

TO_CHAR(날짜, [포맷])

날짜형 데이터를 문자형 데이터로 변경

TO_CHAR(숫자, [포맷])

숫자형 데이터를 문자형 데이터로 변경

[패턴]

  • 9 : 숫자 한 칸을 의미, 여러 개 작성 시 오른쪽 정렬
  • 0 : 숫자 한 칸을 의미, 여러 개 작성 시 오른쪽 정렬 + 빈칸 0 추가
  • L : 현재 DB에 설정된 나라의 화폐 기호
SELECT TO_CHAR(1234, '9999999') FROM DUAL;
SELECT TO_CHAR(1234, '00000') FROM DUAL;

SELECT TO_CHAR(1000000, '9,999,999') FROM DUAL; -- 자릿수 구분
SELECT TO_CHAR(1000000, 'L9,999,999') FROM DUAL; -- 화폐 기호
SELECT TO_CHAR(1000000, '$9,999,999') FROM DUAL; -- 화폐 기호

-- 직원들의 급여를 '\999,999,999' 형식으로 조회
SELECT TO_CHAR(SALARY, 'L999,999,999')
FROM EMPLOYEE;

[날짜에 TO_CHAR 적용]

  • YYYY : 년도 / YY : 년도 (짧게)
  • RRRR : 년도 / RR : 년도 (짧게)
    - Y : 현재 세기 (21세기 == 20XX년 == 2000년대)
    - R : 1세기 기준으로 절반(50년) 이상이면 이전 세기(1900년대) / 절반(50년) 미만이면 현재 세기(2000년대)
  • MM : 월 / DD : 일
  • AM 또는 PM : 오전/오후 표시
  • HH : 시간 / HH24 : 24시간 표기법
  • MM : 분 / SS : 초
  • DAY : 요일(전체) / DY : 요일(요일명만 표시)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DY AM HH24:MI:SS') FROM DUAL;

-- 직원들의 입사일을 '2017년 12월 06일 (수)' 형식으로 출력
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일" "("DY")"') 입사일
FROM EMPLOYEE;
--> 년, 월, 일은 오라클에 등록된 날짜 표기 패턴이 아니라서 오류
--> 기존에 없던 패턴 추가 시 ""(쌍따옴표)로 감싸줘서 문자열 그대로를 출력하게 함

✏️ TO_DATE (날짜로 변환)

TO_DATE(문자형 데이터, [포맷])

문자형 데이터를 날짜로 변경

TO_DATE(숫자형 데이터, [포맷])

숫자형 데이터를 날짜로 변경

-> 지정된 포맷으로 날짜를 인식함

SELECT '2023-05-17', TO_DATE('2023-05-17') FROM DUAL;

SELECT TO_DATE('20100103') FROM DUAL;
SELECT TO_DATE(20100103) FROM DUAL; -- 숫자도 가능

SELECT TO_DATE('041030 143000', 'YYMMDD HH24MISS') FROM DUAL;

SELECT TO_CHAR( TO_DATE('041030 143000', 'YYMMDD HH24MISS'), 'YYYY/MM/DD HH24"시 "MI"분"' ) FROM DUAL;

-- EMPLOYEE 테이블에서 각 직원이 태어난 생년월일 조회
SELECT EMP_NAME, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD') 생년월일 FROM EMPLOYEE;

SELECT TO_DATE('19490115', 'RRRRMMDD') FROM DUAL;

✏️ TO_NUMBER (숫자 형변환)

TO_NUMBER(문자데이터, [포맷])

문자형 데이터를 숫자 데이터로 변경

SELECT '1,000,000' + 10 FROM DUAL;

SELECT TO_NUMBER('1,000,000', '9,999,999') + 10 FROM DUAL;

💡 NULL 처리 함수


✏️ NVL(컬럼명, 컬럼값이 NULL일 때 바꿀 값)

NULL인 컬럼값을 다른 값으로 변경

-- EMPLOYEE테이블에서 이름, 급여, 보너스, 급여*보너스 조회
SELECT EMP_NAME, SALARY, NVL(BONUS, 0), SALARY * NVL(BONUS, 0)
FROM EMPLOYEE;

✏️ NVL2(컬럼명, 바꿀값1, 바꿀값2)

해당 컬럼의 값이 있으면 바꿀값1로 변경, 해당 컬럼이 NULL이면 바꿀값2로 변경

-- EMPLOYEE 테이블에서
-- 기존 보너스를 받던 사원의 보너스를 0.8로
-- 보너스를 받지 못했던 사원의 보너스를 0.3으로 변경하여
-- 이름, 기존 보너스, 변경된 보너스 조회

SELECT EMP_NAME, BONUS, NVL2(BONUS, 0.8, 0.3)
FROM EMPLOYEE;

[연습 문제]

-- EMPLOYEE 테이블에서 사원명, 입사일-오늘, 오늘-입사일 조회
-- 단, 입사일-오늘의 별칭은 "근무일수1",
-- 오늘-입사일의 별칭은 "근무일수2"로 하고
-- 모두 정수(내림)처리, 양수가 되도록 처리
SELECT EMP_NAME 사원명,
    FLOOR( ABS(HIRE_DATE - SYSDATE) ) 근무일수1,
    FLOOR( ABS(SYSDATE - HIRE_DATE) ) 근무일수2
FROM EMPLOYEE;

-- EMPLOYEE 테이블에서 사번이 홀수인 직원들의 정보 모두 조회
SELECT * FROM EMPLOYEE
--WHERE SUBSTR(EMP_ID, -1, 1) IN ('1','3','5','7','9');
WHERE MOD(EMP_ID, 2) = 1;

-- EMPLOYEE 테이블에서 근무 년수가 20년 이상인 직원 정보 조회
SELECT * FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) >= 20;

-- EMPLOYEE 테이블에서
-- 직원명과 주민번호를 조회
-- 단, 주민번호 9번째 자리부터 끝까지는 '*'문자로 채움
-- 예 : 홍길동 771120-1******
SELECT EMP_NAME, SUBSTR(EMP_NO, 1, 8) || '******' 주민번호
FROM EMPLOYEE;

-- EMPLOYEE 테이블에서
-- 직원명, 직급코드, 연봉(원) 조회
-- 단, 연봉은 보너스가 적용된 1년치 급여 + ₩57,000,000 으로 표시 
SELECT EMP_NAME, JOB_CODE,
    -- (급여 + (급여 * 보너스) * 12
    TO_CHAR((SALARY + (SALARY * NVL(BONUS, 0)))*12, 'L999,999,999') "연봉(원)"
FROM EMPLOYEE;

SELECT EMP_NAME, JOB_CODE,
    -- 급여 * (1 + 보너스) * 12
    TO_CHAR(SALARY * (1 + (NVL(BONUS, 0))) * 12, 'L999,999,999') AS "연봉(원)"
FROM EMPLOYEE;

💡 선택 함수

여러 가지 경우에 따라 알맞은 결과를 선택할 수 있음


✏️ DECODE

DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2, ... , 아무것도 일치하지 않을 때)

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환
일치하는 값을 확인(자바의 SWITCH와 비슷함)

-- 직원들의 성별 구분하기
SELECT EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여')
FROM EMPLOYEE;

-- 직원의 급여를 인상하고자 한다
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고
-- 직급코드가 J6인 직원은 급여의 15%를 인상하고
-- 직급코드가 J5인 직원은 급여의 20%를 인상하며
-- 그 외 직급의 직원은 급여의 5%만 인상한다.
-- 직원 테이블에서 직원명, 직급코드, 급여, 인상급여(위 조건)을 조회하세요
SELECT EMP_NAME, JOB_CODE, SALARY,
    DECODE(JOB_CODE, 'J7', SALARY * 1.1,
                    'J6', SALARY * 1.15,
                    'J5', SALARY * 1.2,
                    SALARY * 1.05) 인상급여
FROM EMPLOYEE;

✏️ CASE

CASE WHEN 조건식 THEN 결과값
     WHEN 조건식 THEN 결과값
     ELSE 결과값
END

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환
조건은 범위 값 가능

-- 성별 구분
SELECT EMP_NAME,
    CASE
        WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남자'
        WHEN SUBSTR(EMP_NO, 8, 1) = '2' THEN '여자'
    END 성별
FROM EMPLOYEE;

-- EMPLOYEE 테이블에서 사번, 사원명, 급여를 조회
-- 급여가 500만원 이상이면 '고급'
-- 급여가 300~500만원이면 '중급'
-- 그 미만은 '초급'으로 출력처리하고 별칭은 '구분'으로 한다.
-- 부서코드가 'D6'인 직원만 조회
-- 직급코드 오름차순 정렬
SELECT EMP_ID, EMP_NAME, SALARY,
    CASE
        WHEN SALARY >= 5000000 THEN '고급'
        WHEN SALARY >= 3000000 THEN '중급'
        ELSE '초급'
    END 구분
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6'
ORDER BY JOB_CODE;
profile
풀스택 개발자 기록집 📁

0개의 댓글