02. 함수(1)

게시 2023/01/10

By Kim Jung Wook 13 분읽는 시간

함수

1) 정의

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

2) 종류

단일 행 함수 : N개의 값을 읽어서 N개의 결과를 반환
그룹 함수 : N개의 값을 읽어서 1개의 결과를 반환(Ex. 합계, 평균, 최대, 최소 ..)

단일행 함수

1) 문자 관련 함수

< LENGTH(컬럼명 | 문자열) : 길이반환 >
SELECT EMAIL, LENGTH(EMAIL) FROM EMPLOYEE;

  
-- < INSTR(컬럼명 | 문자열, '찾을 문자열' [, 찾기 시작할 위치 [, 순번]])
-- 지정한 위치부터 지정한 순번째로 검색되는 문자의 위치를 반환

SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;
-- 3반환

SELECT INSTR('AABAACAABBAA', 'B', 5) FROM DUAL;
-- 9반환, 반환값은 전체 길이에서의 위치

SELECT INSTR('AABAACAABBAA', 'B', 5, 2) FROM DUAL;
-- 10반환

  
-- < SUBSTR(컬럼명 | 문자열, 잘라내기 시작할 위치 [, 잘라낼 길이])
-- 컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라서 반환
--> 잘라낼 길이 생략 시 끝까지 잘라냄

SELECT EMP_NAME, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@')-1) FROM EMPLOYEE;

  
-- < TRIM ([[옵션] '문자열' | 컬럼명 FROM] '문자열' | 컬럼명) >
-- 주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거
--> 양쪽 공백 제거에 많이 사용됨
-- 옵션 : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)

SELECT TRIM('        안녕       ') FROM DUAL;
-- 안녕만 반환

SELECT TRIM(BOTH # FROM '#####안녕######') FROM DUAL;
-- 안녕만 반환

SELECT TRIM(LEADING # FROM '#####안녕######') FROM DUAL;
-- 안녕###### 반환

SELECT TRIM(TRAILING # FROM '#####안녕######') FROM DUAL;
-- #####안녕 반환

  
-- < UPPER(컬럼명) : 조회한 컬럼이 영문자일 경우 대문자로 바꿔주는 함수 >
-- 반대는 LOWER

SELECT EMP_NO, UPPER(EMAIL) FROM EMPLOYEE;

2) 숫자 관련 함수

  
-- < ABS(숫자 | 컬럼명) : 절대 값을 리턴 >

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

  
-- < MOD(숫자 | 컬럼명, 숫자 | 컬럼명) : 나머지 값 반환 >

SELECT EMP_NAME, SALARY, MOD(SALARY, 1000000) FROM EMPLOYEE;
-- 월급에서 100만원을 나눈 나머지 반환

SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
WHERE MOD(EMP_ID, 2) = 0;
-- 사원 사번이 짝수인 사람들 조회

SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
WHERE MOD(EMP_ID, 2) <> 0; -- 같지 않다, !=와 같은 쓰임
-- 사원 사번이 홀수인 사람들 조회

  
-- < ROUND(숫자 | 컬럼명[, 소수점 위치]) : 반올림 >

SELECT ROUND(123.456) FROM DUAL; 
-- 소수점 위치를 지정하지 않으면 소수점 첫째자리에서 반올림. 기본값

SELECT ROUND(123.456, 1) FROM DUAL; 
-- 소수점 둘째자리에서 반올림하여 첫째자리까지 표현

SELECT ROUND(123.456, -1) FROM DUAL; 
-- 소수점 0번째자리에서 반올림하여 -1번째자리까지 표현. 
-- 1의 자리에서 반올림하여 10의 자리부터 표현

  
-- < CEIL(숫자 | 컬럼명) : 올림 & FLOOR(숫자 | 컬럼명) : 내림 >
--> 둘다 소수점 첫째 자리에서 올림 / 내림 처리

SELECT CEIL(123.1) , FLOOR(123.9) FROM DUAL;
-- 124, 123 반환

  
-- < TRUNC(숫자 | 컬럼명 [, 위치]) : 특정 위치 아래를 버림(절삭) >

SELECT TRUNC(123.456) FROM DUAL; -- 소수점 아래를 버림(기본)
SELECT TRUNC(123.456, 1) FROM DUAL; -- 소수점 첫째자리 아래를 버림
SELECT TRUNC(123.456, -1) FROM DUAL; -- 10의 자리 아래를 버림

  
-- < 버림과 내림 차이점 >

SELECT FLOOR(-123.5), TRUNC(-123.5) FROM DUAL;
-- -124와 123 반환, 내림은 해당 숫자보다 적은 수로 반환, 버림은 말그대로 지정숫자가 버려짐

3) 날짜 관련 함수

  
-- < SYSDATE : 시스템에 현재 시간(년, 월, 일, 시, 분, 초)을 반환 >

SELECT SYSDATE FROM DUAL;

  
-- < SYSDATESTAMP : SYSDATE + MS 단위 추가 >

SELECT SYSTIMESTAMP FROM DUAL;
-- TIMESTAMP : 특정 시간을 나타내거나 기록하기 위한 문자열

  
-- < MONTH_BETWEEN(날짜, 날짜) : 두 날짜의 개월 수 차이를 반환 >

SELECT ROUND(MONTH_BETWEEN(SYSDATE, '2023-07-10'), 3) "수강 기간(개월)"
FROM DUAL;

  
-- < ADD_MONTHS(날짜, 숫자) : 날짜에 숫자만큼의 개월 수를 더함(음수도 가능) >

SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;

  
-- < LAST_DAY(날짜) : 해당 달의 마지막 날짜를 구함 >

SELECT LAST DAY('2024-02-01') FROM DUAL;

4) 형변환 함수

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

1_ 문자열로 변환

  
-- < TO_CHAR(날짜 | 숫자, [포멧]) : 날짜 또는 숫자형을 문자형 데이터로 변경 >

-- ✔ 숫자 변환 시 포멧 패턴
-- 9 : 숫자 한 칸을 의미. 여러개 작성 시 오른쪽 정렬
-- 0 : 숫자 한 칸을 의미. 여러개 작성 시 오른쪽 정렬 + 빈칸에 0 추가
-- L : 현재 DB에 설정된 나라의 화폐 기호

SELECT TO_CHAR(1234,'99999') FROM DUAL;
SELECT TO_CHAR(1234,'00000') FROM DUAL;
-- ' 1234'  '01234'

SELECT TO_CHAR(1000000,'L9,999,999') FROM DUAL;
SELECT TO_CHAR(1000000,'$9,999,999') FROM DUAL;
-- ￦1,000,000  $1,000,000

-- ✔ 날짜 변환 시 포멧 패턴
-- YYYY : 년도 / YY : 년도(짧게)
-- RRRR : 년도 / RR : 년도(짧게)
-- MM : 월
-- DD : 일
-- AM / PM : 오전 / 오후 표시
-- HH : 시간 / 24HH : 24시간 표기법
-- MI : 분
-- SS : 초
-- DAY : 요일(전체) / DY : 요일(요일명만)

SELECT SYSDATE FROM DUAL;
-- 2023-01-10 10:21:26.000

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS DAY') FROM DUAL;
-- 2023-01-10 10:21:00 화요일

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)') FROM DUAL;
-- 2023년 01월 10일 (화), 중간 글씨는 ""를 이용하여 단순 문자로 인식시킨다

2_ 날짜로 변환

  
-- < TO_DATE(문자형 | 숫자형 데이터, [포멧]) : 문자 또는 숫자를 날짜형으로 변환 >

SELECT TO_DATE('2023-01-10') FROM DUAL;
SELECT TO_DATE(20230110) FROM DUAL;

SELECT TO_DATE('510505', 'YYMMDD') FROM DUAL;
-- 2051-05-05. 
-- Y패턴 : 현재 세기(21세기 = 20XX년 = 2000년대)

SELECT TO_DATE('510505', 'RRMMDD') FROM DUAL;
-- 1951-05-05. 
-- R패턴 : 1세기를 기준으로 절반(50년) 이상인 경우 이전 세기(1900년대), 
--					  						절반(50년) 미만인 경우 현재 세기(2000년대)

  
-- < 연습문제 !! >
-- EMPLOYEE 테이블에서 각 직원이 태어난 생년월일(XXXX년 XX월 XX일) 조회

SELECT EMP_NAME , TO_CHAR(TO_DATE(SUBSTR(EMP_NO, 1, INSTR(EMP_NO, '-')-1)),
'YYYY"년" MM"월" DD"일') AS "생년월일"
FROM EMPLOYEE;

3_ 숫자로 변환

  
-- < TO_NUMBER(문자데이터, [포멧]) : 문자형 데이터를 숫자 데이터로 변환 >

SELECT '10,000,000' + 5000000 FROM DUAL;
-- SQL Error [1722] [42000]: ORA-01722: 수치가 부적합합니다

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

5) NULL 처리 함수

NULL과 산술 연산을 진행하면 결과는 무조건 NULL

  
-- < NVL(컬럼명, 컬럼값이 NULL일 때 지정값) : NULL인 컬럼값을 다른 값으로 변경 >

SELECT EMP_NAME, SALARY, BONUS, BONUS*10, NVL(BONUS, 0), SALARY * NVL(BONUS, 0)
FROM EMPLOYEE;

-- < NVL2(컬럼명, 지정값1, 지정값2) : 
-- 해당 컬럼에 값이 있으면 지정값1로 변경, NULL이면 지정값2로 변경 >

SELECT EMP_NAME, BONUS, NVL2(BONUS, 'O', 'X') FROM EMPLOYEE;

6) 선택 함수

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

  
-- < DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2.... , 
-- 아무것도 일치하지 않을 때) : 
-- 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환 >
-- 일치하는 값을 확인 (자바의 switch와 비슷함)

SELECT EMP_NAME , EMP_NO , 
	DECODE( SUBSTR(EMP_NO, 8, 1), '1', '남성', '2', '여성') "성별"
FROM EMPLOYEE;

  
< CASE WHEN "조건식" THEN "결과값"
	   WHEN "조건식" THEN "결과값"
       ELSE "결과값"
 END       
 : `비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값 반환 , 
조건은 범위 값 가능` >

  
-- < 연습문제 !! >
-- 직원의 급여를 인상하고자한다.
-- 직급 코드가 J7인 직원은 20% 인상
-- 직급 코드가 J6인 직원은 15% 인상
-- 직급 코드가 J5인 직원은 10% 인상
-- 그 외 직원은 5% 인상
-- 이름, 직급코드, 원래 급여, 인상률, 인상된 급여

SELECT EMP_NAME `이름` , JOB_CODE `직급코드` , SALARY `급여` ,
	DECODE(JOB_CODE, 'J7', '20%', 'J6', '15%', 'J5', '10%', '5%') `인상률`,
	DECODE(JOB_CODE, 'J7', SALARY * 1.2 , 'J6', SALARY*1.15, 'J5', 
					SALARY*1.1, SALARY*1.05) "인상된 급여"
FROM EMPLOYEE;


SELECT EMP_NAME `이름` , JOB_CODE `직급코드`, SALARY `급여` ,
	CASE 
		WHEN JOB_CODE ='J7' THEN '20%'
		WHEN JOB_CODE ='J6' THEN '15%'
		WHEN JOB_CODE ='J5' THEN '10%'
		ELSE '5%'
	END "인상률",
	CASE
		WHEN JOB_CODE = 'J7' THEN SALARY*1.2
		WHEN JOB_CODE = 'J6' THEN SALARY*1.15
		WHEN JOB_CODE = 'J5' THEN SALARY*1.1
		ELSE SALARY*1.05 
	END "인상된 급여"
FROM EMPLOYEE;

KH 학원 공부, DB

DB ORACLE

함수

단일행 함수

인기 태그