11.16.(수) OracleDB(4): 내장 함수(1)

내장 함수

SQL에 내장되어있는 함수들을 이용해서 조건들을 편리하게 정할 수 있다.

숫자 함수

ABS(n) : 절대값

SELECT ABS(-10) FROM dual;

CEIL(n) : 올림

SELECT CEIL(10.1) FROM DUAL;

FLOOR(n) : 내림

SELECT FLOOR(10.9) FROM DUAL;

MOD(m, n) : m을 n으로 나눈 나머지 반환, n이 0일 경우 m을 반환

SELECT MOD(9, 4) FROM DUAL;

POWER(m, n) : m의 n제곱 값

SELECT POWER(4, 2) FROM DUAL;

ROUND(n, [m]) : n을 소수점 m째 자리로 반올림, m이 음수일 경우 그만큼 10의 자리수에서 반올림

SELECT ROUND(124.3566, 2) FROM DUAL;
SELECT ROUND(124.3566, -2) FROM DUAL;

SQRT(n) : n의 제곱근 값

TRUNC(n, m) : n을 소수점 m째 자리로 반내림

문자열 처리 함수

CONCAT(char1, char2) : char1과 char2를 결합

INITCAP(char) : char의 첫 문자를 대문자로 변환

LOWER(char), UPPER(char) : char을 소문자, 대문자로 변환

SELECT LOWER('POBY KIM') FROM DUAL;
SELECT UPPER('poby kim') FROM DUAL;

SUBSTR(c, n, m) : 문자열에서 일부 문자 얻어오기, 시작위치 n, 개수 m

SELECT SUBSTR('hong gildong', 3, 5) FROM dual;

LPAD(char1, n [,char2]), RPAD(char1, n [,char2]) : 왼쪽/오른쪽에 문자열을 끼워넣기, char1칼럼을 n길이의 문자열로 반환하고 왼쪽/오른쪽을 char2로 채운다. 예시로 알아보자.

-- [문제] 이름을 글자길이의 50%만 출력하고 나머지는 '*'로 표시하라
select ename, RPAD(SUBSTR(ename, 1, CEIL(LENGTH(ename)/2)), LENGTH(ename), '*') name1,
RPAD(SUBSTR(ename, 1, FLOOR(LENGTH(ename)/2)), LENGTH(ename), '*') name2 from emp;

REPLACE(char, str1, str2) : 특정 문자를 다른 문자로 변경하기, char의 문자 중 str1을 str2로 변경

SELECT ename, REPLACE(ename, 'A','에이') FROM emp;

INSTR(str, char, [m, n]) : 문자열이 포함되어있는지 확인하고 해당 위치를 리턴

str의 문자 중 char의 위치(왼쪽부터), m번째 문자부터 탐색, n번째 해당위치를 리턴, 못 찾으면 0 리턴

SELECT ename, INSTR(ename, 'T', 2, 2) FROM emp;	-- 2번째 문자열부터 'A' 탐색하고 두번째 'A'에 해당하는 위치

TRIM(char FROM str), LTRIM(str, char), RTRIM(str, char) : 양쪽/좌우 사이드의 특정 문자 제거

SELECT sal, TRIM(0 from sal) FROM emp; -- 양쪽에 있는 '0' 제거
SELECT ename, LTRIM(ename, 'S'), RTRIM(ename, 'S') FROM emp;

일반 함수

DECODE(value, if1, then1, if2, value2,...) : 데이터들의 값을 다른 값으로 바꾸기

-- [문제] 10:기획부, 20:총괄, 30:인사부
SELECT ename, deptno, DECODE(deptno, 10, '기획부', 20, '총괄부', 30, '인사부') 부서명 FROM emp;

날짜 함수

날짜 -> 연도: YYYY, YY  월: MM  일: DD  요일: DY, DAY

시간 -> 시: HH, HH24  분: MI  초: SS  오전/오후: PM, AM

SYSDATE : 시스템 현재 날짜/시간

TO_CHAR(date, 'format') : 날짜를 문자로 변환

TO_DATE(char, 'format') : 문자를 날짜로 변환

LAST_DAY(date) : 그 달의 마지막 날

ADD_MONTHS(n) : 월 더하기

MONTHS_BETWEEN(d1, d2) : d1과 d2 사이의 개월 수

ROUND(d, [format]) : format 단위로 날짜를 반올림 **day는 요일로 반올림 (일요일 시작)

*날짜 산술

날짜-날짜=숫자, 날짜+-숫자=날짜 (단위: day)

-- [문제] 오늘을 기준으로 100일 후 날짜는?
SELECT SYSDATE-100 FROM dual;
-- [문제] 입사한지 몇일 되었을까?
SELECT hiredate, FLOOR(SYSDATE-hiredate) FROM emp;

그룹 함수

여러 행 또는 테이블에 적용돼서 하나의 결과값을 리턴하는 함수

COUNT() : 개수

SELECT count(ename) 사원수 FROM emp;
SELECT count(ename), sal FROM emp;	-- ERROR 그룹함수와 칼럼은 공존X

MAX(), MIN() : 최대/최소값

AVG() : 평균값

SUM() : 합

STDDEV() : 표준편차

GROUP BY : 그룹함수를 기준을 정하여 계산

-- 부서별 급여의 합계와 평균
SELECT deptno, SUM(sal) sum, ROUND(avg(sal),2) average FROM emp GROUP BY deptno ORDER BY deptno;

ROLLUP() : 그룹 함수의 처리를 결과 전체에 대해 적용한다

SELECT job, MAX(sal), SUM(sal), ROUND(avg(sal),2) FROM emp 
GROUP BY ROLLUP(job);

CUBE() : 1차 분류 또는 2차 분류에 대한 통계

SELECT deptno, job, COUNT(empno) "사원 수", SUM(sal) "급여의 합계" FROM emp
GROUP BY CUBE(deptno, job) ORDER BY deptno;

GROUPING() : GROUP BY로 계산된 경우는 0, ROLLUP이나 CUBE로 계산된 경우엔 1를 리턴

SELECT deptno, job, GROUPING(deptno) D, GROUPING(job) J, COUNT(empno) num, SUM(sal) sum, MAX(sal) max FROM emp
GROUP BY CUBE(deptno, job) ORDER BY deptno;