5. 단일함수/그룹함수/CASE문

단일함수 

 

숫자함수

함수	설명	예시
ABS	숫자의 절댓값	SELECT ABS(-100) FROM dual; -> 결과: 100
ROUND(a, b)	숫자를 반올림 (a: 변환할 숫자 / b: 반올림할 자리) * 반올림할 자리가 소수점: 양수 / 정수: 음수	SELECT ROUND(12.345, 2) FROM dual; -> 결과: 12.35
TRUNC(a, b)	숫자 버림	SELECT TRUNC(99.9, -1) FROM dual; -> 결과: 90
MOD(a, b)	a를 b로 나누고 나머지 값 조회	SELECT MOD(21, 5) FROM dual; -> 결과: 1
CEIL	주어진 숫자에서 가장 근접한 큰 정수 조회	SELECT CEIL(123.1) FROM dual; -> 결과: 124
FLOOR	주어진 숫자에서 가장 근접한 작은 정수 조회	SELECT FLOOR(123.9) FROM dual; -> 결과: 123
POWER(a, b)	a의 b제곱 조회	SELECT POWER(2,3) FROM dual; -> 결과: 8

 

 

문자함수

함수	설명	예시
UPPER	값 or 컬럼명 대문자 변환	SELECT UPPER('abcd') FROM dual; -> 결과: ABCD
LOWER	값 or 컬럼명 소문자 변환	SELECT LOWER('ABCD') FROM dual; -> 결과: abcd
INITCAP	단어의 앞 글자만 대문자로 변환	SELECT INITCAP('new york') FROM dual; -> 결과: New York
LENGTH	문자 길이 조회	SELECT LENGTH('안녕?') FROM dual; -> 결과: 3
LENGTHB	문자의 바이트 조회	SELECT LENGTHB('안녕?') FROM dual; -> 결과: 7
INSTR	특정 문자의 위치(순서) 반환	SELECT INSTR('가나다라', '다') FROM dual; -> 결과: 3
INSTRB	특정 문자의 위치(바이트) 반환	SELECT INSTRB('가나다라','다') FROM dual; -> 결과: 7
SUBSTR(a,b,c)	대상의 시작 위치부터 선택한 개수만큼 조회 * a문자열의 b번째부터 c개만큼 출력	SELECT SUBSTR('가나다',2,1) FROM dual; ->결과: 나
SUBSTRB	대상의 시작 위치부터 선택한 바이트만큼 조회	SELECT SUBSTRB('가나다',2) FROM dual; -> 결과: 나다
L/RPAD(a,b,c)	빈칸만큼 특정문자로 채움(왼쪽/오른쪽) * a문자열이 b개수만큼 채워지지 않으면 c로 채움	SELECT LPAD('123', 5, 0) FROM dual; -> 00123
L/RTRIM	왼쪽/오른쪽의 공백문자 삭제	SELECT RTRIM(' 안녕 ') FROM dual; -> 결과: ' 안녕' (6글자 -> 4글자로 줄음)
TRIM(a FROM b)	양 끝의 특정 문자 삭제 * a문자를 b문자열에서 지움 * 특정 문자를 지정하지 않으면 공백이 삭제됨	SELECT TRIM('a' FROM 'a12a') FROM dual -> 결과: '12'
	* 옵션 지정 가능 LEADING: 왼쪽에서 제거 TRAILING: 오른쪽에서 제거 BOTH: 양쪽(아무 옵션 없을 때 실행)	SELECT TRIM(LEADING 'a' FROM 'a123a') FROM dual; -> 결과: '123a;

 

 

날짜함수

 * 날짜 포맷: YYYY/YY(년) MM(월) DD(일) HH/HH24(시) MI(분) SS(초) DAY(요일)

함수	설명	예시
SYSDATE	현재 시스템의 날짜와 시간 나타냄	SELECT SYSDATE FROM dual;
SYSTIMESTAMP	초의 소수점까지(밀리세컨드) 제어 * FF 뒤에 숫자를 붙여 자릿수 지정 가능	SELECT TO_CHAR(SYSTIMESTAMP, 'HH:MI:SS.FF2') FROM dual;
MONTH_BETWEEN	두 날짜 사이 개월 수	SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE-500) FROM dual;
ADD_MONTH	주어진 날짜에 개월 수를 더함	SELECT TO_CHAR(ADD_MONTHS(SYSDATE, '6')) FROM dual;
NEXT_DAY	주어진 날짜에서 가까운 요일의 날짜	SELECT NEXT_DAY(SYSDATE, '금요일') FROM dual;
LAST_DAY	주어진 날짜가 속한 달의 마지막 날짜	SELECT LAST_DAY(SYSDATE) FROM dual;

 

 

형 변환 함수

함수	설명	예시
TO_CAHR	날짜형/숫자형 -> 문자형	SELECT TO_CHAR(SYSDATE, 'MM/DD') FROM dual;
TO_DATE	문자형 -> 날짜형	SELECT SYSDATE - TO_DATE('2006/01/01', 'YYYY/MM/DD') FROM dual;
TO_NUMBER	문자형 -> 숫자형	SELECT TO_NUMBER('3') FROM dual;

 

* TO_CHAR 에서 사용하는 형식

형식	설명	예시
9	일반적인 숫자 표현	SELECT TO_CHAR(1234,'9999') FROM dual; -> 결과: 123
0	앞의 빈자리를 0으로 채움	SELECT TO_CHAR(1234,'09999') FROM dual; -> 결과: 01234
$	$ 표시	SELECT TO_CHAR(1234,'$9999') FROM dual; -> 결과: $1234
L	지역 통화 표시	SELECT TO_CHAR(1234,'L9999') FROM dual; -> 결과: \1234
.	소수점 표시	SELECT TO_CHAR(1234,'9999.9') FROM dual; -> 결과: 1234.0
,	천단위 구분	SELECT TO_CHAR(1234,'9,999') FROM dual; -> 결과: 1,234

 

 

기타함수

형식	설명	예시
NVL(a,원하는 값)	a가 NULL이면 '원하는 값'으로 변환	* 급여와 보너스를 합한 연봉을 조회 SELECT ename, sal*12 + NVL(bonus,0) "연봉" FROM emp_table; --> NULL값은 연산을 할 수가 없으니 0으로 변환
NVL2(a,b,c)	a가 NULL이 아니면 b로, NULL이면 c로	* 지급급여 조회(보너스가 있으면 보너스를 더한 값) SELECT ename, NVL2(bonus, bonus+sal, sal) FROM emp_table;
NULLIF(a,b)	a와 b의 값이 같다면 NULL, 아니면 a 반환	SELECT NULLIF(1,1), NULLIF(1,2) FROM dual; -> 결과: NULL , 1
DECODE(a,b,c)	a가 b와 같다면 c반환	* 부서번호가 10이면 '인사팀', 20이면 '회계팀' SELECT deptno, DECODE(deptno, 10, '인사팀', 20, '회계팀') 부서 FROM emp_table;

 

 

---

그룹함수

단일행 함수는 행마다 결과를 출력하나, 그룹함수는 행들을 그룹화하여 하나의 결과를 출력한다.

형식	설명	예시
COUNT	행 개수를 출력 컬럼 이름을 명시하면 NULL값을 제외하고 출력하고, *을 사용하면 NULL값도 포함하여 출력한다.	SELECT COUNT(*) FROM emp_table; -> 결과: 14 SELECT COUNT(bonus) FROM emp_table; -> 결과: 8
SUM	NULL값을 제외한 모든 행의 합 출력	SELECT SUM(bonus) FROM emp_table;
AVG	NULL값을 제외한 모든 행의 평균 출력	SELECT AVG(bonus) FROM emp_table;
MAX	NULL값을 제외한 모든 행의 최댓값 출력	SELECT MAX(bonus) FROM emp_table;
MIN	NULL값을 제외한 모든 행의 최솟값 출력	SELECT MIN(bonus) FROM emp_table;

* 그룹함수는 일반컬럼과 함께 쓸 수 없다.

SELECT ename, SUM(sal) FROM emp_table;
-- 오류: ORA-00937: not a single-group group function

 

---

CASE문

일반 함수 DECODE와 비슷한 조건문 함수

-- 형식
SELECT 컬럼이름1, 컬럼이름2,  * 쉼표 조심
 CASE WHEN 컬럼이름 THEN 결과1 
      WHEN 컬럼이름 THEN 결과2 
      WHEN 컬럼이름 THEN 결과3  * 컬럼 이름이 모두 같아야 한다
 ELSE when절의 모든 조건이 거짓인 경우의 결과
END [AS 결과를 저장할 가상의 컬럼이름(화면출력이름)]

-- 예제: 직무가 'MANAGER'인 사람들 중 sal이 2000 이상이면 3등급,
--       2700 이상이면 2등급, 2900이상이면 1등급으로 하여 조회 
SELECT ename, deptno, sal,
CASE WHEN sal >= 2900 THEN '1등급'
     WHEN sal >= 2700 THEN '2등급'
     WHEN sal >= 2000 THEN '3등급'
ELSE 'Unknown'
END AS 등급
FROM emp_table
WHERE lower(job) = 'manager';

예제 결과