함수
문자 처리 함수
| 구분 | 설명 |
|---|
| LOWER | 소문자로 변환한다. |
| UPPER | 대문자로 변환한다. |
| INITCAP | 첫 글자만 대문자로 나머지 글자는 소문자로 변환한다. |
| CONCAT | 문자의 값을 연결한다. |
| SUBSTR | 문자를 잘라 추출한다. (한글 1Byte) |
| SUBSTRB | 문자를 잘라 추출한다. (한글 2Byte) |
| LENGTH | 문자의 길이를 반환한다. (한글 1Byte) |
| LENGTHB | 문자의 길이를 반환한다. (한글 2Byte) |
| LPAD, RPAD | 입력 받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환한다. |
| TRIM | 잘라내고 남은 문자를 표시한다. |
| CONVERT | CHAR SET을 변환한다. |
| CHR | ASCII 코드 값으로 변환한다. |
| ASCII | ASCII 코드 값을 문자로 변환한다. |
| REPLACE | 문자열에서 특정 문자를 변경한다. |
숫자 함수
| 구분 | 설명 |
|---|
| ABS | 절대값을 구한다. |
| COS | COSINE 값을 반환한다. |
| EXP | e(2.71828183…)의 n승을 반환한다. |
| FLOOR | 소수점 아래를 잘라낸다.(버림) |
| LOG | LOG값을 반환한다. |
| POWER | POWER(m, n) m의 n승을 반환한다. |
| SIGN | SIGN (n) n<0 이면 -1, n=0이면 0, n>0 이면 1을 반환한다. |
| SIN | SINE 값을 반환한다. |
| TAN | TANGENT 값을 반환한다. |
| ROUND | 특정 자릿수에서 반올림 한다. |
| TRUNC | 특정 자릿수에서 잘라낸다.(버림) |
| MOD | 입력 받은 수를 나눈 나머지 값을 반환한다. |
날짜 함수
| 구분 | 설명 |
|---|
| SYSDATE | 시스템 저장된 현재 날짜를 반환한다. |
| MONTHS_BETWEEN | 두 날짜 사이가 몇 개월인지를 반환한다. |
| ADD_MONTHS | 특정 날짜에 개월 수를 더한다. |
| NEXT_DAY | 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환한다. |
| LAST_DAY | 해당 달의 마지막 날짜를 반환한다. |
| ROUND | 인자로 받은 날짜를 특정 기준으로 반올림 한다. |
| TRUNC | 인자로 받은 날짜를 특정 기준으로 버린다. |
날짜 연산
- 날짜 형 데이터에 숫자를 더하면(날짜+숫자) 그 날짜로부터 그 기간만큼 지난 날짜를 계산한다.
- 날짜형 데이터에 숫자를 빼면(날짜-숫자) 그 날짜로부터 그 기간만틈 이전 날짜를 구한다.
날짜 연산 오류 유형
- 날짜 데이터를 추출할 때 발생하는 에러의 대부분은 DATE 타입에 대한 잘못된 연산으로 인해 발생
- 에러가 발생하는 대표적인 DATE 연산
- DATE + DATE
- ORA-00975 : 날짜와 날짜의 계산은 할 수 없다.
- CHAR - DATE
- ORA-00932 : 일괄성 없는 데이터 유형 : CHAR이(가) 필요하지만 DATE임
- NUMBER - DATE
- ORA-00932 : 일괄성 없는 데이터 유형 : NUMBER이(가) 필요하지만 DATE임
특정 기준으로 반올림하는 ROUND 함수
- ROUND 함수는 숫자를 반올림하는 함수로 학습하였다. 하지만 이 함수에 포멧 모델을 지정하면 숫자 이외에 날짜에 대해서도 반올림을 할 수 있다.
- 형식 : ROUND(date, format)
| 포멧 모델 | 단위 |
|---|
| CC, SCC | 4자리 연도의 끝 두 글자를 기준으로 반올림 |
| SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y | 년 (7월 1일부터 반올림) |
| DDD, D, J | 일을 기준 |
| HH, HH12, HH24 | 시를 기준 |
| Q | 한 분기의 두 번째 달의 16일을 기준으로 반올림 |
| MM, MON, MONTH, RM | 월 (16일을 기준으로 반올림) |
| DAY, DY ,D | 한주가 시작되는 날짜 |
| MI | 분을 기준 |
SELECT HIREDATE, ROUND(HIREDATE, 'MONTH') FROM EMP;
- ROUND 함수의 포멧 모델로 MONTH를 지정하였기에 특정 날짜(DATE)를 달(MONTH)을 기준으로 반올림한 날짜를 구한다.
- 일을 기준으로 16일보다 적으면 이번 달 1일을 크면 다음달 1일 구한다. 6월 9일 반올림 하면 6월 1일이 되고 11월 17일을 반올림하면 12월 1일이 된다. 또한 1월 23일을 반올림 하면 2월 1일이 된다.
특정 기준으로 버리는 TRUNC 함수
- TRUNC 함수 역시 숫자를 잘라내는 것 뿐만 아니라 날짜를 잘라낼 수 있다. ROUND 함수와 마찬가지로 포맷 형식을 주어 다양한 기준으로 날짜를 잘라낼 수 있다.
SELECT HIREDATE, TRUNC(HIREDATE, 'MONTH') FROM EMP;
- 특정 날짜(DATE)를 달(MONTH)을 기준으로 버림한 날짜를 구하기 위해서는 위와 같이 표현한다.
두 날짜 사이 간격을 MONTHS_BETWEEN 함수
MONTHS_BETWEEN(date1, date2)
- MONTHS_BETWEEN 함수는 날짜와 날짜 사이의 개월 수를 구하는 함수이다.
SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
- 위는 각 직원들의 근무한 개월 수를 구하는 예제이다.
- 이 결과 역시 소수점 이하 까지 구해진다. 소수점 이하 자리는 한달이 되지 못한 일수를 나타낸다. 이를 TRUNC 함수를 사용하면 소수점 이하를 절삭 할 수 있다.
개월 수를 더하는 ADD_MONTHS 함수
- ADD_MONTHS 함수는 특정 개월 수를 더한 날짜를 구하는 함수이다.
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,6) FROM EMP;
- 위는 입사날짜에서 6개월을 추가하는 예제이다.
해당 요일의 가장 가까운 날짜를 반환하는 NEXT_DAY 함수
- NEXT_DAY 함수는 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 반환하는 함수이다.
SELECT SYSDATE, NEXT_DAY(SYSDATE,'수요일') FROM DUAL;
- 위는 오늘을 기준으로 최초로 도래한느 수요일은 언제인지 알아보는 예제이다.
해당 달의 마지막 날짜를 반환하는 LAST_DAY 함수
- LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수이다.
SELECT HIREDATE, LAST_DAY(HIREDATE) FROM DUAL;
- 위는 입사한 달의 마지막 날을 구하는 예제이다.
형 변환 함수
- 오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터 형을 다른 데이터형으로 변환해야 하는 경우가 생긴다.
- 이럴 때 사용하는 함수가 형 변환 함수이다. 형 변환 함수로는 TO_NUMBER, TO_CHAR, TO_DATE 가 있다.
| 구분 | 설명 |
|---|
| TO_CHAR | 날짜형 혹은 숫자형을 문자형으로 변환한다. |
| TO_DATE | 문자형을 날짜형으로 변환한다. |
| TO_NUMBER | 문자형을 숫자형으로 변환한다. |
문자형으로 변환하는 TO_CHAR 함수 날짜형을 문자형으로 변환하기
TO_CHAR('날짜 데이터', '출력형식')
- DATE 형태의 데이터를 지정한 양식에 의해 VARCHAR2 형의 문자로 변환한다.
| 종류 | 의미 |
|---|
| YYYY | 년도 표현(4자리) |
| YY | 년도 표현(2자리) |
| MM | 월을 숫자로 표현 |
| MON | 월을 알파벳으로 표현 |
| DAY | 요일을 표현 |
| DY | 요일을 약어로 표현 |
- 위는 날짜 출력 형식의 종류를 날열한 표이다.
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
- 위는 현재 날짜를 기본 형식과 다른 형태로 출력한다.
SELECT HIREDATE, TO_CHAR(HIREDATE,'YYYY/MM/DD DAY') FROM EMP;
- 위는 사원들의 입사일을 출력하되 요일까지 함께 출력한다.
SELECT HIREDATE, TO_CHAR(HIREDATE,'YY/MON/DD DY') FROM EMP;
- 위는 년도를 2자리(YY)로 출력하고 월은 문자(MON)로 표시하고 요일을 약어(DY)로 표시한 예이다.
| 종류 | 의미 |
|---|
| AM 또는 PM | 오전(AM), 오후(PM) 시각 표시 |
| A.M 또는 P.M | 오전(A.M), 오후(P.M) 시각 표시 |
| HH또는 HH12 | 시간(1~12) |
| HH24 | 24시간으로 표현(0~23) |
| MI | 분 표현 |
| SS | 초 표현 |
- 위는 시간 출력 형식의 종류를 날열한 표이다.
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
문자형으로 변환하는 TO_CHAR 함수 숫자형을 문자형으로 변환하기
SELECT TO_CHAR(1230000) FROM DUAL;
- 위는 숫자 1230000을 문자형으로 변환한다.
| 구분 | 설명 |
|---|
| 0 | 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다. |
| 9 | 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다. |
| L | 각 지역별 통화 기호를 앞에 표시한다. |
| . | 소수점 |
| , | 천 단위 자리 구분 |
SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999') FROM EMP;
- 각 지역별 토오하 기호를 앞에 붙이고 천 단위마다 콤마를 붙여서 출력 하려면 위와 같이 표현다.
SELECT TO_CHAR(123456, '000000000'), TO_CHAR(123456, '999,999,999') FROM DUAL;
- 9는 자릿수를 나타내며 자릿수가 맞지 않으면 채우지 않는다. 하지만 0은 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.
날짜 형으로 변화하는 TO_DATE 함수
- 날짜 형으로 변환하기에 앞서 날짜 형에 대해서 살펴보도록 합시다. 날짜 형은 세기, 년도, 월, 일 시간, 분, 초와 같이 날짜와 시간에 대한 정보를 저장한다.
- 오라클에서 기본 날짜 형식은 YY/MM/DD 형식으로 년/월/일 예를 들면 06/03/08 식으로 나타낸다.
- 만일 년도를 4자리로 출력하려면 YYYY/MM/DD 형식으로 지정한다.
- TO_DATE 함수는 문자열을 날짜 형으로 변환 한다.
SELET ENAME, HIREDATE FROM EMP WHERE HIREDATE = TO_DATE('19810220','YYYYMMDD');
- 숫자 형태인 19810220을 TO_DATE 함수를 사용해서 날짜형으로 변환 한다. 이때 두 번째 인자는 출력할 형식을 지정해 주어야 한다.
숫자형으로 변환하는 TO_NUMBER 함수
- TO_NUMBER 함수는 특정 데이터를 숫자형으로 변환해 주는 함수입니다.
SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999') FROM DUAL;
- 위와 같이 ‘20,000’dmf ‘10,000’의 차이를 알아보기 위해서 빼기를 해 보자. 산술 연산을 하려문 문자형을 숫자형으로 변환한 후에 실행해야 한다.
선택을 위한 DECODE 함수
- DECODE 함수는 프로그램 언어에서 가장 많이 사용되는 switch case 문과 같은 기능을 갖는다.
- 즉, 여러 가지 경우에 대해서 선택할 수 있도록 한다.
DECODE(표현식,조건1,결과1,조건2,결과2,조건3,결과3,기본결과)
SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10 'ACCOUNTING',
DEPTNO, 20 'RESEARCH',
DEPTNO, 30 'SALES',
DEPTNO, 40 'OPERATIONS') AS DNAME
FROM EMP;
조건에 따라 서로 다른 처리가 가능한 CASE 함수
SELECT ENAME, DEPTNO, CASE WHEN DEPTNO=10 THEN 'ACCOUNTING',
WHEN DEPTNO=20 THEN 'RESEARCH',
WHEN DEPTNO=30 THEN 'SALES',
WHEN DEPTNO=40 THEN 'OPERATIONS') AS DNAME
FROM EMP;
