C#,Delphi,Oracle,MSSQL 개발자블로그
[Oracle/기본]3. 단일 행 함수 본문
● SQL 함수의 분류
- 단일 행 함수 : 오직 단일 행에서만 적용
행별로 하나의 결과를 리턴
데이터 값을 조작하는데 사용
중첩가능, SELECT, WHERE, ORDER BY 절에 사용 가능하다.
- 다중 행 함수 : 복수의 행을 조각하여 행의 그룹당 하나의 결과를 리턴
● 함수의 종류
☞ 문자함수
- LOWER('SQL Course') → sql course
- UPPER('SQL Course') → SQL COURSE
- INITCAP('SQL Course') → Sql Course
알파벳 문자 값을 각 단어의 첫 번째 문자는 대문자로, 나머지 문자는 소문자로 변환한다.
- CONCAT('A','B') → AB 또는 CONCAT(컬럼명,컬럼명)
|| 와 같은 기능이다. 단 2개밖에 연결하지 못한다.
- SUBSTR('String',1,3) → Str
지정된 길이만큼 스트링을 추출한다.
- LENGTH('String') → 6
- INSTR('String', 'r') → 3
명명된 문자의 위치를 숫자 값으로 리턴한다.
- LPAD('sal',10,'*') → *******sal
☞ 숫자함수
- ROUND(45.923,2) → 45.92
두 번째 argument값이 양수이면, 해당 소수점 자리까지 반올림한다.
- ROUND(45.923,0) → 46
두 번째 argument값이 0이거나 없으면, 1의자리 까지 반올림한다.
- ROUND(45.923,-1) → 50
두 번째 argument값이 음수이면, 좌측 자리수에서 반올림 한다.
- TRUNC(45.923,2) → 45.92
- TRUNC(45.923,0) → 45
- TRUNC(45.923,-1) → 40
TRUNC는 ROUND함수와 같지만 반올림이 아닌, 절삭(내림)한다.
- MOD(1600, 300) → 100
나누기를 한 후의 나머지 값
☞ 날짜함수
- MONTHS_BETWEEN(to_DATE('2008-11-25','YYYY-MM-DD'), to_date('2001-08-12','YYYY-MM-DD')) → 87.4193..
MONTHS_BETWEEN(Date1, Date2) 는 월 단위로 날짜의 차 연산을 한다. (약 87개월)
Ex. 각각의 종업원에 대해서 종업원 이름을 디스플레이하고, 오늘 날짜와 종업원이 고용된 날짜 사이의 월 수를
계산하십시오.
열 레이블은 MONTHS_WORKED 입니다. 결과는 입사한 월 수로 정렬하십시오. 가장 근접하는 월 수로
반올림 하십시오.
SELECT ROUND (MONTHS_BETWEEN (SYSDATE, hiredate), 0)
FROM scott.emp
ORDER BY MONTHS_BETWEEN (SYSDATE, hiredate);
- ADD_MONTHS(Date1, 5) → Date1날짜를 기준으로 5개월을 더한 값.
- NEXT_DAY(Date1, 'sunday') → Date1 날짜가 해당된 주의 '요일'의 날짜 출력.
단, 예를들어 , 금요일에 금요일 조회하면 다음 주의 금요일 날짜 출력한다.
- LAST_DAY(Date1) → 해당 월의 마지막 날 출력
Ex. 종업원의 이름, 입사일 그리고 근무한지 6개워 이후의 첫 번째 금요일인 급여 재검토 일자를 디스플레이 하십시오. 열 레이플은
REVIEW입니다. "Sunday, the seventh of September, 1981"과 유사한 형식으로 나타나게 날짜를 구성하십시오.
SELECT ename,
hiredate,
TO_CHAR (NEXT_DAY (ADD_MONTHS (hiredate, 6), 'MONDAY'),
'fmDay, "the" Ddspth "of" Month, YYYY')
AS review
FROM scott.emp
-결과
--------------------------------------------------------------------------------------------------------------------------------------------------------
- SYSDTE 예제 (출처 : http://www.gurubee.net/lecture/1026)SELECT TO_CHAR(SYSDATE,'RRRR-MM-DD HH24:MI:SS') "지금시간" FROM DUAL ; SELECT TO_CHAR(SYSDATE-1,'RRRR-MM-DD HH24:MI:SS') "하루전지금시간" FROM DUAL ;SELECT TO_CHAR(SYSDATE-1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간" FROM DUAL ;SELECT TO_CHAR(SYSDATE-1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간" FROM DUAL ;SELECT TO_CHAR(SYSDATE-1/24/60/10,'RRRR-MM-DD HH24:MI:SS') "6초전시간" FROM DUAL ;SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'RRRR-MM-DD HH24:MI:SS') "5시간 30분 10초전" FROM DUAL ;--------------------------------------------------------------------------------------------------------------------------------------------------------
- ROUND예제 (출처 : http://www.gurubee.net/lecture/1026)SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 'RRRR-MM-DD HH24:MI:SS'), 'YEAR'), 'RRRR-MM-DD HH24:MI:SS') FROM DUAL;☞ 년도 까지 반올림 (월 단위에서 반올림)
SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 'RRRR-MM-DD HH24:MI:SS'), 'MONTH'), 'RRRR-MM-DD HH24:MI:SS') FROM DUAL;☞ 월 단위 까지 반올림 (일자에서 반올림)
SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 'RRRR-MM-DD HH24:MI:SS'), 'DD'), 'RRRR-MM-DD HH24:MI:SS') FROM DUAL;
☞ 일자 까지 반올림 (시간에서 반올림)
SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 'RRRR-MM-DD HH24:MI:SS')), 'RRRR-MM-DD HH24:MI:SS') FROM DUAL;-- 실행 결과 --2012-01-01 00:00:002011-09-01 00:00:002011-09-12 00:00:002011-09-12 00:00:00
--------------------------------------------------------------------------------------------------------
- TRUNC예제 (출처 : http://www.gurubee.net/lecture/1026)
SELECT TO_CHAR(TRUNC(TO_DATE('2011-09-11 21:00:01',
'RRRR-MM-DD HH24:MI:SS'), 'YEAR'),
'RRRR-MM-DD HH24:MI:SS')
FROM DUAL;
☞ 년도까지 내림(월 단위 에서 내림)
SELECT TO_CHAR(TRUNC(TO_DATE('2011-09-11 21:00:01',
'RRRR-MM-DD HH24:MI:SS'), 'MONTH'),
'RRRR-MM-DD HH24:MI:SS')
FROM DUAL;
☞ 월 단위 까지 내림 (일자에서 내림)
SELECT TO_CHAR(TRUNC(TO_DATE('2011-09-11 21:00:01',
'RRRR-MM-DD HH24:MI:SS'), 'DD'),
'RRRR-MM-DD HH24:MI:SS')
FROM DUAL;
☞ 일 단위까지 내림(시간에서 내림)
SELECT TO_CHAR(TRUNC(TO_DATE('2011-09-11 21:00:01',
'RRRR-MM-DD HH24:MI:SS')),
'RRRR-MM-DD HH24:MI:SS')
FROM DUAL;
-- 실행 결과 --
2011-01-01 00:00:00
2011-09-01 00:00:00
2011-09-11 00:00:00
2011-09-11 00:00:00
--------------------------------------------------------------------------------------------------------
- ROUND(TO_DATE('1998/04/11','YYYY/MM/DD'), 'YEAR') : 1998/01/01
날짜로 반올림, (월 단위에서 반올림)
- ROUND(TO_DATE('1998/04/11'),'MONTH') : 1998/04/01
월 단위로 반올림, (날짜 단위에서 반올림)
- ROUN(TO_DATE('1998.04.11'), 'DAY') : 1998/04/11
ROUND(Date, A) : A로 반올림 한다. (A아랫자리에서 반올림.)
- TRUNC
SELECT SYSDATE
, TRUNC(SYSDATE)
, TRUNC(SYSDATE, 'YEAR')
, TRUNC(SYSDATE, 'MONTH')
, TRUNC(SYSDATE, 'DAY')
, TRUNC(123.4567,2)
, TRUNC(123.4567)
FROM DUAL
결과
2013/01/21 21:13:18
2013/01/21 00:00:00
2013/01/01 00:00:00
2013/01/01 00:00:00
2013/01/20 00:00:00
123.45
123
● 데이터의 변환
- 암시적변환 : 오라클 자체변환
Varchar2, Char -> Number, Date
- 명시적변환 : 함수로 변환
TO_CHAR,
Ex. 숫자를 달러 단위로 변환.
TO_CHAR('5000', 'fm$99,999.00')
Ex. 이름, 입사일, 입사한 요일을 디스플레이 하십시오. 열 레이블은 DAY 입니다.
결과는 월요일부터 시작하는 요일 순으로 정렬하십시오.
SELECT ename, hiredate, TO_CHAR (hiredate, 'DAY') DAY
FROM scott.emp
ORDER BY TO_CHAR (hiredate - 1, 'D')
TO_NUMBER,
TO_DATE
● NVL 함수
- NVL(value, 1) : value가 null일 경우 1을 반환,
null이 아닐 경우 value값을 반환.
- NVL2(value, A, B) : value가 null일 경우 A를 반환,
null이 아닐 경우 B를 반환.
★★★ NVL의 내부 argument는 서로 데이터형이 맞아야 한다.
SELECT ename, NVL(TO_CHAR(comm), 'NO Commission') comm
FROM scott.emp
또는
SELECT ename, NVL(comm,0) comm
FROM scott.emp
'Programming > Oracle 공부' 카테고리의 다른 글
| [Oracle/기본]5. 그룹 함수를 사용한 데이터 집계 (2) | 2017.05.22 |
|---|---|
| [Oracle/기본]4. 다중 테이블로부터 데이터 디스플레이 (0) | 2017.05.22 |
| [Oracle/기본]2. 데이터 제한과 정렬 (0) | 2017.05.15 |
| [Oracle/기본]1. 기본적인 SQL문장 작성. (0) | 2017.05.15 |
| [Oracle/기본]Oracle 개론(소개) (1) | 2017.05.15 |