Oracle 내장함수 중 유용한 몇몇에 대해 알아본다.
-- 숫자 함수 (Number Function)
ABS(n) : 절대값을 계산하는 함수
SQL> SELECT ABS(-10) FROM DUAL; // 결과는 10
CEIL(n) : 주어진 값보다 큰 최소 정수값을 구하는 함수
SQL> SELECT CEIL(5.1) FROM DUAL; // 결과는 6
SQL> SELECT CEIL(-5.1) FROM DUAL; // 결과는 -5
FLOOR(n) : 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수
SQL> SELECT FLOOR(5.1) FROM DUAL; // 결과는 5
SQL> SELECT FLOOR(-5.1) FROM DUAL; // 결과는 -6
EXP(n) : 주어진 값의 e의 승수를 구하는 함수
LN(n) : 주어진 값의 자연로그 값을 구하는 함수
MOD(m, n) : m을 n으로 나우어 남은 값을 반환한다.
SQL> SELECT MOD(5, 3) FROM DUAL; // 결과는 2
SQL> SELECT MOD(5, 0) FROM DUAL; // 결과는 5
POWER(m, n) : m의 n승 값을 구하는 함수
SQL> SELECT POWER(2, 3) FROM DUAL; // 결과는 8
ROUND(m, n) : m 값의 반올림을 구하는 함수. n은 소숫점 자릿수를 명시
SQL> SELECT ROUND(111.126, 1) FROM DUAL; // 결과는 111.1
SQL> SELECT ROUND(111.126, -1) FROM DUAL; // 결과는 110
SIGN(n) : n 값의 부호를 구하는 함수. n > 0일때는 1, n = 0일때는 0, n < 0 일때는 -1
SQRT(n) : n 값의 루트값을 구하는 함수. n은 양수이어야 한다.
TRUNC(n, m) : n 값을 m 소숫점 자리로 반내림한 값을 구하는 함수
SQL> SELECT TRUNC(10.678, 2) FROM DUAL; // 결과는 10.67
SQL> SELECT TRUNC(567.345, -2) FROM DUAL; // 결과는 500
-- 문자 함수 (String Function)
CONCAT(str1, str2) : 두 문자를 합치는 함수. "||" 연산자와 같은 역할을 합니다.
SQL> SELECT CONCAT('Oracle', ' Korea') NAME FROM DUAL; // 결과는 Oracle Korea
INITCAP(str) : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 주는 함수
SQL> SELECT INITCAP('junducki') FROM DUAL; // 결과는 Junducki
LOWER(str) : 문자열을 소문자로 변환시켜주는 함수
SQL> SELECT LOWER('JUNDUCKI') FROM DUAL; // 결과는 junducki
UPPER(str) : 문자열을 대문자로 변환시켜주는 함수
SQL> SELECT UPPER('junducki') FROM DUAL; // 결과는 JUNDUCKI
LPAD(str1 , n, str2) : str1 문자열의 왼쪽에 str2 문자열을 str1의 문자열 길이가 n이 되게 채워주는 함수. str1의 문자열이 n보다 클 경우 str1을 n개 문자열 만큼 반환합니다.
SQL> SELECT LPAD('jin', 5, '-') FROM DUAL; // 결과는 --jin
RPAD(str , n, char2) : LPAD와 반대로 오른쪽을 채워주는 함수
SQL> SELECT RPAD('jin', 5, '-') FROM DUAL; // 결과는 jin--
SUBSTR(str, m, n) : str 문자열의 m 번째 자리부터 n개의 문자열을 구하는 함수
SQL> SELECT SUBSTR('junducki', 3, 3) FROM DUAL; // 결과는 ndu
SQL> SELECT SUBSTR('junducki', -3, 3) FROM DUAL; // 결과는 cki
LENGTH(str) : str 문자열의 길이를 구하는 함수
SQL> SELECT LENGTH('junducki') FROM DUAL; // 결과는 8
REPLACE(str1, str2, str3) : str1 문자열에서 str2과 매칭되는 부분을 str3으로 변환하는 함수. 대소문자를 구분함
SQL> SELECT REPLACE('Ukzang ukzang', 'U', 'j') FROM DUAL; // 결과는 jkzang ukzang
INSTR(str1, str2, m, n) : str1 문자열에 str2가 매칭되는 위치를 구하는 함수. m은 str1 문자열의 m 위치에서 부터 검색. n은 매칭되는 횟수를 지정. 매칭되는 것이 없을 때는 0
SQL> SELECT INSTR('junducki junducki', 'u') FROM DUAL; // 결과는 2
SQL> SELECT INSTR('junducki junducki', 'u', 3) FROM DUAL; // 결과는 5
SQL> SELECT INSTR('junducki junducki', 'u', 3, 2) FROM DUAL; // 결과는 11
TRIM(str1, str2) : str1 문자열의 양끝의 str2 문자열을 제거하는 함수, str2를 주지 않으면 [공백]을 제거한다.
LTRIM(str1, str2) : TRIM을 왼쪽 끝만 적용
RTRIM(str1, str2) : TRIM을 오른쪽 끝만 적용
VSIZE(str) : str 문자열의 Byte 수를 구하는 함수. NULL이면 NULL이 반환
SQL> SELECT VSIZe('junducki') FROM DUAL; // 결과는 8
-- 날짜 함수 (Date Function)
LAST_DAY(d) : 달의 마지막 날을 구하는 함수
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL; // 결과 31-03-2008
ADD_MONTH(m, n) : m의 날짜에 n 달을 더해주는 함수
SQL> SELECT ADD_MONTH(SYSDATE, 2) FROM DUAL; // 결과 24-05-2008
MONTH_BETWEEN(m, n) : m 날짜와 n 날짜 사이의 달수를 구하는 함수
SQL> SELECT MONTHS_BETWEEN(TO_DATE('2008/06/05') , TO_DATE('2008/09/23')) FROM DUAL;
// 결과는 -3.880635
ROUND(d, [f]) : d 날짜를 f로 지정한 단위로 반올림을 구하는 함수
SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'YEAR') FROM DUAL; // 결과는 2009-01-01
SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'MONTH') FROM DUAL; // 결과는 2008-08-01
SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'DAY') FROM DUAL; // 결과는 2008-08-11
날짜에 대한 산술 연산
날짜 + 숫자 : 결과는 날짜. 날짜부터 숫자만큼의 날수가 지난 날짜
날짜 - 숫자 : 결과는 날짜. 날짜부터 숫자만큼의 날수가 전인 날짜
날짜 - 날짜 : 결과는 숫자. 두 날짜의 차이
-- 변환 함수 (Convert Function)
TO_CHAR : DATE형, NUMBER형을 VARCHAR2형으로 변환해주는 함수
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL; // 결과는 2008/03/28
TO_DATE : CHAR형, VARCHAR2형을 DATE형으로 변환해주는 함수
SQL> SELECT TO_DATE('2008/03/28', 'YYYY/MM/DD') FROM DUAL; // 결과는 2008/03/28
TO_NUMBER : CHAR형, VARCHAR2형을 숫자형으로 변환해주는 함수
SQL> SELECT TO_NUMBER('12327') FROM DUAL; // 결과는 12327
-- 기타 함수 (Etc Function)
NVL : NULL 값을 다른 값으로 변환해주는 함수. 모든 데이터 타입에 사용가능.
SQL> SELECT empno, NVL(comm, 0) FROM emp;
EMPNO NVL(COMM, 0)
----------------------------------------------
7499 300
7521 0
DECODE(value, if1, then1, if2, then2, ...) : 데이터들을 다른 값으로 변환해주는 함수. value 값이 if1일 경우 then1으로, if2일 경우 then2로 ...
SQL> SELECT deptno, DECODE(deptno, 10, 'AAA', 20, 'BBB', 30, 'CCC') FROM emp;
DEPTNO DECODE(DEPT
------------------------------------------------
10 AAA
30 CCC
20 BBB
GREATEST(n1, n2, ...) : 값 중 최대값을 구하는 함수
SQL> SELECT GREATEST(10, -5, 16, 20, -11) FROM DUAL; // 결과는 20
LEAST(n1, n2, ...) : 값 중 최소값을 구하는 함수
SQL> SELECT LEAST(10, -5, 16, 20, -11) FROM DUAL; // 결과는 -11
-- 숫자 함수 (Number Function)
ABS(n) : 절대값을 계산하는 함수
SQL> SELECT ABS(-10) FROM DUAL; // 결과는 10
CEIL(n) : 주어진 값보다 큰 최소 정수값을 구하는 함수
SQL> SELECT CEIL(5.1) FROM DUAL; // 결과는 6
SQL> SELECT CEIL(-5.1) FROM DUAL; // 결과는 -5
FLOOR(n) : 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수
SQL> SELECT FLOOR(5.1) FROM DUAL; // 결과는 5
SQL> SELECT FLOOR(-5.1) FROM DUAL; // 결과는 -6
EXP(n) : 주어진 값의 e의 승수를 구하는 함수
LN(n) : 주어진 값의 자연로그 값을 구하는 함수
MOD(m, n) : m을 n으로 나우어 남은 값을 반환한다.
SQL> SELECT MOD(5, 3) FROM DUAL; // 결과는 2
SQL> SELECT MOD(5, 0) FROM DUAL; // 결과는 5
POWER(m, n) : m의 n승 값을 구하는 함수
SQL> SELECT POWER(2, 3) FROM DUAL; // 결과는 8
ROUND(m, n) : m 값의 반올림을 구하는 함수. n은 소숫점 자릿수를 명시
SQL> SELECT ROUND(111.126, 1) FROM DUAL; // 결과는 111.1
SQL> SELECT ROUND(111.126, -1) FROM DUAL; // 결과는 110
SIGN(n) : n 값의 부호를 구하는 함수. n > 0일때는 1, n = 0일때는 0, n < 0 일때는 -1
SQRT(n) : n 값의 루트값을 구하는 함수. n은 양수이어야 한다.
TRUNC(n, m) : n 값을 m 소숫점 자리로 반내림한 값을 구하는 함수
SQL> SELECT TRUNC(10.678, 2) FROM DUAL; // 결과는 10.67
SQL> SELECT TRUNC(567.345, -2) FROM DUAL; // 결과는 500
-- 문자 함수 (String Function)
CONCAT(str1, str2) : 두 문자를 합치는 함수. "||" 연산자와 같은 역할을 합니다.
SQL> SELECT CONCAT('Oracle', ' Korea') NAME FROM DUAL; // 결과는 Oracle Korea
INITCAP(str) : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 주는 함수
SQL> SELECT INITCAP('junducki') FROM DUAL; // 결과는 Junducki
LOWER(str) : 문자열을 소문자로 변환시켜주는 함수
SQL> SELECT LOWER('JUNDUCKI') FROM DUAL; // 결과는 junducki
UPPER(str) : 문자열을 대문자로 변환시켜주는 함수
SQL> SELECT UPPER('junducki') FROM DUAL; // 결과는 JUNDUCKI
LPAD(str1 , n, str2) : str1 문자열의 왼쪽에 str2 문자열을 str1의 문자열 길이가 n이 되게 채워주는 함수. str1의 문자열이 n보다 클 경우 str1을 n개 문자열 만큼 반환합니다.
SQL> SELECT LPAD('jin', 5, '-') FROM DUAL; // 결과는 --jin
RPAD(str , n, char2) : LPAD와 반대로 오른쪽을 채워주는 함수
SQL> SELECT RPAD('jin', 5, '-') FROM DUAL; // 결과는 jin--
SUBSTR(str, m, n) : str 문자열의 m 번째 자리부터 n개의 문자열을 구하는 함수
SQL> SELECT SUBSTR('junducki', 3, 3) FROM DUAL; // 결과는 ndu
SQL> SELECT SUBSTR('junducki', -3, 3) FROM DUAL; // 결과는 cki
LENGTH(str) : str 문자열의 길이를 구하는 함수
SQL> SELECT LENGTH('junducki') FROM DUAL; // 결과는 8
REPLACE(str1, str2, str3) : str1 문자열에서 str2과 매칭되는 부분을 str3으로 변환하는 함수. 대소문자를 구분함
SQL> SELECT REPLACE('Ukzang ukzang', 'U', 'j') FROM DUAL; // 결과는 jkzang ukzang
INSTR(str1, str2, m, n) : str1 문자열에 str2가 매칭되는 위치를 구하는 함수. m은 str1 문자열의 m 위치에서 부터 검색. n은 매칭되는 횟수를 지정. 매칭되는 것이 없을 때는 0
SQL> SELECT INSTR('junducki junducki', 'u') FROM DUAL; // 결과는 2
SQL> SELECT INSTR('junducki junducki', 'u', 3) FROM DUAL; // 결과는 5
SQL> SELECT INSTR('junducki junducki', 'u', 3, 2) FROM DUAL; // 결과는 11
TRIM(str1, str2) : str1 문자열의 양끝의 str2 문자열을 제거하는 함수, str2를 주지 않으면 [공백]을 제거한다.
LTRIM(str1, str2) : TRIM을 왼쪽 끝만 적용
RTRIM(str1, str2) : TRIM을 오른쪽 끝만 적용
VSIZE(str) : str 문자열의 Byte 수를 구하는 함수. NULL이면 NULL이 반환
SQL> SELECT VSIZe('junducki') FROM DUAL; // 결과는 8
-- 날짜 함수 (Date Function)
LAST_DAY(d) : 달의 마지막 날을 구하는 함수
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL; // 결과 31-03-2008
ADD_MONTH(m, n) : m의 날짜에 n 달을 더해주는 함수
SQL> SELECT ADD_MONTH(SYSDATE, 2) FROM DUAL; // 결과 24-05-2008
MONTH_BETWEEN(m, n) : m 날짜와 n 날짜 사이의 달수를 구하는 함수
SQL> SELECT MONTHS_BETWEEN(TO_DATE('2008/06/05') , TO_DATE('2008/09/23')) FROM DUAL;
// 결과는 -3.880635
ROUND(d, [f]) : d 날짜를 f로 지정한 단위로 반올림을 구하는 함수
SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'YEAR') FROM DUAL; // 결과는 2009-01-01
SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'MONTH') FROM DUAL; // 결과는 2008-08-01
SQL> SELECT ROUND(TO_DATE('2008/08/11'), 'DAY') FROM DUAL; // 결과는 2008-08-11
날짜에 대한 산술 연산
날짜 + 숫자 : 결과는 날짜. 날짜부터 숫자만큼의 날수가 지난 날짜
날짜 - 숫자 : 결과는 날짜. 날짜부터 숫자만큼의 날수가 전인 날짜
날짜 - 날짜 : 결과는 숫자. 두 날짜의 차이
-- 변환 함수 (Convert Function)
TO_CHAR : DATE형, NUMBER형을 VARCHAR2형으로 변환해주는 함수
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL; // 결과는 2008/03/28
TO_DATE : CHAR형, VARCHAR2형을 DATE형으로 변환해주는 함수
SQL> SELECT TO_DATE('2008/03/28', 'YYYY/MM/DD') FROM DUAL; // 결과는 2008/03/28
TO_NUMBER : CHAR형, VARCHAR2형을 숫자형으로 변환해주는 함수
SQL> SELECT TO_NUMBER('12327') FROM DUAL; // 결과는 12327
-- 기타 함수 (Etc Function)
NVL : NULL 값을 다른 값으로 변환해주는 함수. 모든 데이터 타입에 사용가능.
SQL> SELECT empno, NVL(comm, 0) FROM emp;
EMPNO NVL(COMM, 0)
----------------------------------------------
7499 300
7521 0
DECODE(value, if1, then1, if2, then2, ...) : 데이터들을 다른 값으로 변환해주는 함수. value 값이 if1일 경우 then1으로, if2일 경우 then2로 ...
SQL> SELECT deptno, DECODE(deptno, 10, 'AAA', 20, 'BBB', 30, 'CCC') FROM emp;
DEPTNO DECODE(DEPT
------------------------------------------------
10 AAA
30 CCC
20 BBB
GREATEST(n1, n2, ...) : 값 중 최대값을 구하는 함수
SQL> SELECT GREATEST(10, -5, 16, 20, -11) FROM DUAL; // 결과는 20
LEAST(n1, n2, ...) : 값 중 최소값을 구하는 함수
SQL> SELECT LEAST(10, -5, 16, 20, -11) FROM DUAL; // 결과는 -11
