이번 장에는 오라클에서 자주 쓰이는 함수들에 대해서 정리해보겠습니다.
이 외에도 상당히 많은 함수들이 존재하겠지만,
필수로 알아야 하는 함수들을 정리해보았습니다.
들어가기 앞서, 오라클에는 '단일 행 함수' 와 '그룹 함수' 가 있습니다.
1) 단일 행 함수 : 행 단위로 적용되어지는 함수
- 문자열 함수 | 숫자 함수 | 날짜 함수 | 기타 함수(문자, 숫자, 날짜 제한 없이 모든 종료에 대한 값에 대해서 사용할 수 있는 함수) | 형 변환 함수 가 있습니다.
SELECT ename, lower(ename), job, sal
FROM emp;
위 lower 함수처럼, ename의 사원 이름은 대문자로 나오지만,
lower 함수를 써서 사원의 이름이 소문자로 나온것을 볼 수 있습니다.
이처럼 행 단위로 적용되는 함수라고 볼 수 있습니다.
2) 그룹 함수
그룹 함수는 나중에 다시 제대로 다루겠지만, 간단히 말하면,
여러 행을 하나의 집합으로 묶어서 해당 집합을 대상으로 한번에 실행되는 함수 입니다.
SELECT sum(sal)
FROM emp;
원래 sal은 각 사원의 급여를 나타냅니다.
emp 테이블에는 총 14명의 사원 정보가 있어서 14개의 정보를 볼 수 있습니다.
그런데 위와같이 SUM 함수를 쓰면, 여러 행을 하나로 묶어서 그 합에 대한 결과를 나타냅니다.
그럼 이제 본격적으로 단일 행 함수 몇가지를 정리해 보겠습니다.
1. 문자 함수
1) upper : 소문자를 대문자로 변환
- lower 함수를 썼을 때와 동일하게 사용됩니다.
2) lower : 대문자를 소문자로 변환
- 위에서 다뤘던 예제 입니다.
3) Initcap : 첫 번째 문자는 대문자, 나머지는 소문자
- 공백이 포함된 어떤 문자열이 있을 때, 각 단어의 첫 문장은 대문자로 표기 됩니다.
SELECT Initcap(loc)
FROM dept;
4) length : 바이트가 아닌 '글자수'를 출력
- 해당 문자열의 길이를 출력해줍니다.
SELECT ename, length(ename)
FROM emp;
또한 원하는 문자열을 입력해서 값을 얻을 수 있습니다.
SELECT length('abcde')
FROM emp;
abcde 문자열의 길이를 확인하려는데, emp테이블로 확인하려니 쓸때없이 값이 많이 나옵니다.
그래서 이때 더미 테이블을 사용합니다.
아무 값도 없는 더미 테이블이여서 원하는 값을 실행해 볼 때 수월합니다.
테이블 명은 dual 입니다.
SELECT length('abcde')
FROM dual;
5) lengthB : 문자의 '바이트 수'를 출력. 영어는 각 1바이트 / 한글은 각 3바이트
- length는 길이를 출력하지만, 이는 바이트 수를 출력합니다.
SELECT lengthB('abcde'), lengthB('가나다')
FROM dual;
영어는 각각 1바이트이기 때문에 총 5바이트가 나오지만
한글은 각각 3바이트이기 때문에 3(글자수) * 3(각 바이트) 해서 9가 나온것을 볼 수 있습니다.
6) substr : 부분 문자열 출력
- substr(col_list, 시작값, 끝값) 형태로 사용합니다.
- 단, sql에서는 문자열의 첫 값이 0이 아닌 1부터 시작합니다.
SELECT substr(job, 1, 3)
FROM emp;
사원 이름의 처음부터 3까지의 문자를 잘라서 출력해준 것을 볼 수 있습니다.
7) Instr : 특정 문자열 찾기
- 해당 문자가 어떤 위치에 있는지 알려줍니다.
SELECT Instr('Hello ORACLE', 'L')
FROM dual;
이것 외에 원하는 부분부터 찾거나, 몇번째에 있는 단어를 찾아라 라고 할 수 있습니다.
Instr('Hello ORACLE, 'L', 5) -> 5번째 문자부터 찾기 시작
Instr('Hello ORACLE, 'L', 2, 2) -> 2번째부터 찾아서 2번째 L을 찾기
8) replace : 특정 단어를 다른 단어로 대체
- 간혹, 특정 문자열을 다른 문자로 대체한다고 알고 있습니다.
틀린말은 아니지만 이 말이 더 옳은 것은 'translate()' 함수입니다.
우선 replace 함수를 먼저 보겠습니다.
SELECT replace('010-1234-5678', '-', ' ') as replace
FROM dual;
위 전화번호의 '-' 문자를 공백으로 바꾸는 예제입니다. 잘 변경되어 나왔네요.
그러면 translate 함수로 볼까요?
SELECT translate('010-1234-5678', '-', ' ') as translate
FROM dual;
결과는 당연히 똑같습니다.
그런데 이 translate 함수는 특정 문자를 지정된 다른 문자로 교체하는 함수가 더 적합해 보입니다.
SELECT translate('Hello World!!!?', 'He!?', '1234') as translate
FROM dual;
Hello World!!!? 라는 문구가 있습니다.
이 문구에서 H, e, !, ?를 각각 1, 2, 3, 4로 교체합니다.
그래서 위와 같은 결과가 나왔습니다.
만약, 대체 할 문자의 수가 적으면 어떻게 될까요?
SELECT translate('Hello World!!!?', 'Heo!?', '1234') as translate
FROM dual;
Hello World!!!? 문구에서 H, e, o, !, ? 다섯개를 바꾸고자 합니다.
그런데 바꿀 문자는 1, 2, 3, 4 네 개 밖에 없네요.
바뀌지 않은 문자는 공백처리가 됩니다.
마지막에 ?가 대체될 문자가 없어서 출력되지 않은 것을 볼 수 있습니다.
9) LPAD, RPAD : 각각 왼쪽 패딩, 오른쪽 패딩
패딩은 내부의 공간을 만드는 것을 뜻합니다.
LPAD는 Left Padding을 뜻하고
RPAD는 Right Padding을 뜻합니다.
쓰는 형식은 'lpad(패딩 대상, 총 칸의 수, 빈 공간은 어떤 문자로?)' 입니다.
SELECT 'oracle', lpad('oracle', 10, '#') as lpad, rpad('oracle', 10, '#') rpad
FROM dual;
oracle이란 문자열이 있습니다.
위 형식대로 보자면, 'oracle'이란 문자를, 총 10칸에다가 왼쪽 패딩으로 하고, 빈공간은 #으로 출력' 이 됩니다.
지금은 총 칸의 수를 파악하기위해 빈 공간을 #으로 출력하였지만,
보통은 깔끔하게 정렬하기 위해 사용하므로 공백을 둡니다.
2. 숫자 함수
숫자 함수는 해당 데이터를 수학적으로 연산을 해주는 함수라고 보시면 됩니다.
1) Round 함수
반올림 시켜주는 함수이며, round(숫자(필수), 반올림위치) 형태로 쓰입니다.
반올림 위치를 생략하면 첫번째 소수점 자리에서 반올림합니다.
반올림 위치를 음수로 지정하면, 양수자리에서 반올림 하라는 뜻입니다.
즉, -1은 1234라는 정수에서 4를 반올림하고 -2는 3에서 반올림하여 1200이 됩니다.
SELECT 1234.5678, round(1234.5678), round(1234.5678, 0), round(1234.5678, 1), round(1234.5678, -1)
FROM dual;
2) trunc 함수
버림 함수입니다.
round와 형태는 똑같으며, 버림 위치 또한 지정 가능합니다.
3) ceil 함수
지정된 숫자와 가장 가까운 큰 정수를 반환합니다.
예를 들어 3.14라면 3을 반환하겠죠.
4) Floor 함수
ceil 함수와는 다르게 지정된 숫자와 가장 가까운 작은 정수를 반환합니다.
5) mod 함수
숫자를 나눈 나머지 값을 구하는 함수입니다.
mod([나눗셈 될 숫자(필수)], [나눌 숫자(필수)]) 형태입니다.
15를 6으로 나눈 나머지 3이 나왔네요.
3. 날짜 함수
날짜 데이터와 숫자는 +, - 연산이 가능합니다. 여기서 숫자는 일 수를 나타내겠죠.
날짜 데이터끼리는 -연산은 되지만 +연산은 되지 않습니다. 오늘+내일은 연산이 어렵겠죠.
SELECT sysdate, sysdate + 1, sysdate - 1, sysdate - (sysdate - 100)
FROM dual;
여기서 sysdate는 현재 시간을 나타냅니다.
마지막 컬럼은 오늘 - 오늘에서 100일전 을 연산한 결과값입니다.
날짜 관련해서 함수들을 정리해 보겠습니다.
1) Add_months - 주어진 날짜에 월 수를 더한다.
2) Months_between : 개월 수를 알 수 있다. -> months_between([날짜1] , [날짜])
3) Next_day : 돌아오는 요일을 뜻한다. -> next_day(sysdate, '화') 이렇게 하면, 돌아오는 화요일을 뜻하겠죠.
4) Last_day : 달의 마지막 날짜를 구한다.
5) round, trunc
이 부분은 정리할게 좀 있네요.
날짜에 대해서도 round와 trunc를 사용할 수 있는데,
trunc는 버림이니까 정리할게 없는데,
round에 대해 알아두셔야 할게 있습니다.
우선, 일 -> 월, 월 -> 년으로 반올림이 가능합니다.
round('sysdate', {'MONTH' | 'YEAR'})
MONTH 라고 쓰면, 일 -> 월(16일 부터) 반올림
YEAR 라고 쓰면, 월 -> 년(7월 부터) 반올림
반올림에 실패하면 버림이 되겠죠.
그리고 MONTH로 반올림 되지 않는다면, 해당 달의 1일로 초기화 됩니다.
YEAR로 반올림 되지 않는다면, 해당 년의 1월 1일로 초기화 됩니다.
4. 형 변환 함수
형 변환이 필요한 이유는 간단합니다.
데이터를 보여줄 때, 항상 정해진 규격으로만 출력이 되죠.
그렇지만 상황에 따라서 사용자가 원하는 형식으로 가공해서 보여줘야 할 때가 있을겁니다.
그렇기에 숫자는 문자로, 문자는 숫자로, 날짜 데이터를 문자 데이터로 바꿔줘야 할 때가 있습니다.
여기서 하나 중요한건, 날짜 데이터 -> 문자 데이터입니다.
숫자 형태의 문자데이터 ((ex) '1234') 라면 문자로 형 변환이 가능하듯,
날짜도 날짜 형식의 문자열만 가능합니다.
또한 sql은 암시적 형 변환이 많이 일어납니다.
그런데 간혹 안되는 경우가 있는데, 아래와 같을 때 입니다.
SELECT round('23/02/09', 'MONTH')
23/02/09는 분명 날짜 데이터 형태이죠.
그렇지만 이는 날짜 데이터가 아닌, 그저 잘못된 숫자 라고 판단해버립니다. 그래서 오류가 납니다.
즉 위와 같을 땐, 명시적 형 변환이 필요합니다.
SELECT round(to_data('23/02/08'), 'MONTH')
그러면 이제 어떤 함수가 있는지 보겠습니다.
1) to_char : 숫자 또는 날짜 데이터를 문자 데이터로 변환
날짜 데이터와 숫자 데이터를 문자 데이터로 형변환 한 결과입니다.
그런데 컬럼 출력이 너무 길죠.
이럴 땐, 컬럼 출력 길이를 지정해 줄 수 있습니다.
col num_char format a10
SELECT deptno, to_char(deptno) as num_char, to_char(hiredate) as date_char
FROM emp;
col num_char format a10 라는 뜻은, num_char에 대한 출력 컬럼을 10으로 지정한다, 라는 뜻입니다.
위 결과에서 하나 알아두셔야 할 점은
deptno 컬럼은 오른쪽으로 붙어있고
deptno 컬럼을 문자열로 바꾼 num_char 컬럼은 왼쪽으로 붙어있죠.
즉, 문자는 왼쪽 정렬이 되고 숫자는 오른쪽 정렬이 된다는 것입니다.
또한 숫자 값을 문자열로 바꿀 때, 아래와 같이 다양한 형태로 표기 가능합니다.
1) SELECT ename, sal, to_char(sal, ‘99999’) FROM emp; => 자릿수 5자리로 출력한다. 부족하면 #으로 표기 됨.
2) SELECT ename, sal, to_char(sal, ‘09999’) FROM emp; => 0은, 부족한 자리수를 0으로 채우라는 의미이다.
3) SELECT ename, sal, to_char(sal, ‘09999.99’) FROM emp; => 소수점 두 자리 표기
4) SELECT ename, sal, to_char(sal, ’09,999.99$’) FROM emp; => 달러 표시
5) SELECT ename, sal, to_char(sal, ’09,999.99L’) FROM emp; => L은 지역 화폐 단위를 표기
2) to_number : 문자 데이터를 숫자 데이터로
이건 말 그대로 숫자 형태의 문자데이터를 숫자로 바꾸는 것이기 때문에
간단히만 보겠습니다.
1) SELECT to_number(sysdate) FROM dual; => 날짜 데이터이기 때문에 오류
2) SELECT to_number(‘apple’) FROM dual; => 숫자 형식의 문자열이 아니여서 오류
3) SELECT to_number(‘3.141592’) FROM dual; => 정상 출력
3) to_date : 문자 데이터를 날짜 데이터로 (이는 위의 예시와 같습니다.)
5. NULL 처리 함수
지난번 글에 사원들 연봉을 계산한 적이 있습니다.
그때, comm이 null인 사원이 있어서 연산할 때 NLV 함수를 사용했습니다.
그 외에 NLV2 라는 함수도 있습니다.
NLV - null value 함수 : null 이 아니면 그대로, null 이면 지정 값으로 변경
NLV2 : null 이 아닌 때와 null 일 때, 각각 지정한 값으로 변경
SELECT ename, job, sal, comm, sal * 12 + nvl(comm, 0) ann_sal FROM emp;
SELECT ename, job, sal, comm, sal * 12 + nvl2(comm, sal * 12 + comm, sal * 12) ann_sal FROM emp;
NVL 함수는 comm이 null이면 0으로 바꿔줍니다.
NVL2 함수는 comm이 null이면 sal * 12 + comm을 실행하고, null이 아니면 sal * 12 연산을 실행합니다.
여기까지 다양한 단일 함수에 대해 알아보았습니다.
