Oracle Chapter4. SQL주요함수 (오라클 입문자용)
현 블로그는 모바일 환경이 아닌 PC환경에 최적화 되어있습니다.
|
|
||||
오라클에서는 데이터를 처리하기위한 다양한 함수가 제공된다.
기능에 따라 숫자함수, 문자함수, 날짜함수 등등 여러종류의 함수가 있다.
그중 대체적으로 많이 사용되는 함수에 대해 알아보자.
우선 함수를 들어가기전에 한가지 알아두고 갈것이 있다.
DUAL 테이블
DUAL 테이블이란 SYS사용자가 소유하는 테이블이며 모든 사용자가
사용할수 있도록 설정되어 있다.
DUAL 테이블의 구조를 보면 DUMMY란 단 하나의 컬럼으로 구성되어있다.
왜 갑자기 DUAL테이블을 설명하냐면
데이터베이스 작업을 할때 가끔 여러가지 계산이 필요할때가 있다.
예를 들어 134*254를 계산한다고 할때 암산으로 해도 시간이 걸리고
계산기를 꺼낼려니 귀찮다.
그래서 SQL문에의 연산기능을 이용하여 계산을 할려고 하는데
SELECT 123 * 245
를 그냥 치면 오류가 난다. FROM절이 없기 때문이다.
그래서 아무 테이블이나 사용하여 123 * 245를 계산해보았다.
SELECT 123*245 FROM emp;
계산은 되었는데 14개의 행이 다
계산결과로 나왔다.
14개의 행만 가진 테이블이여서 다행이지
10000개의 행을 가진 테이블이였으면...
그래서 만들어 놓은 테이블이 바로 DUAL 테이블이다.
SELECT 123*245 FROM DUAL
이렇게 보기 쉽고 깔끔하게 나오기 때문에 간단한 연산은 DUAL테이블을 이용하면 편하다.
물론 이용을 안해도 상관은 없다. 그냥 이런 테이블이 있으니 이용을 하고 싶은 해보라는것이다.
자 그럼 이제 함수에 대해서 알아보자 .
숫자, 문자, 날짜, 형변환, NULL변환 등등 많은 함수가 존재하는데
솔직히 이건 이해보다는 어디에 사용되며 명령어가 무엇인지 외워야 되는 것들이다.
간단한 설명뒤에 문제로서 익혀보도록 하자.
숫자함수
ROUND : 반올림해주는 함수이다. ROUND( 값, 반올림 지점) 형식으로 쓰인다.
ex) ROUND(98.765, 2) = 98.77 // ROUND(98.765 , -1) = 100
TRUNC : 버림함수이다. 지정한 자리수 이하를 버림한다.
TRUNC(값 , 버림지점)형식으로 쓰인다.
ex) TRUNC(98.765, 2) = 98.77 // TRUNC (98.765 , -1) = 90
MOD : 나머지를 구하는 함수이다. MOD(값 , 나눌수)형식으로 쓰인다.
ex) MOD(12, 5) = 2 // MOD(10, 5) = 0
1. 45.564을 소수 세번째 자리에서 반올림하여라.
2. 45.564를 반올림하여 정수로 만들어라.
3. 45.564를 1의 자리에서 반올림 하여라.
4. 45.564를 소수 첫번째 자리까지만 남기고 버림하여라.
5. 1200을 14로 나눈 나머지를 구하여라.
6. emp의 월급컬럼(sal)을 100으로 나눈뒤 그 나머지를 출력하여라.
7. emp에서 사번이 홀수인 사람의 정보를 출력하여라.
1. 45.564을 소수 세번째 자리에서 반올림하여라.
select round(45.564, 2) from dual;
round(x,0)이 1의 자리를 가르키며 오른쪽으로(소수점으로)갈수록 1,2,3 으로 늘어나며
왼쪽으로(정수쪽으로)갈수록 -1,-2,-3 으로 줄어든다.
. . . 100 10 1 0.1 0.01 0.001 . . .
. . . ------l------------l-----------l-----------l------------l-----------l----- . . .
. . . round(x.-2) round(x,-1) round(x,0) round(x,1) round(x,2) round(x,3) . . .
2. 45.564를 반올림하여 정수로 만들어라.select round(45.564, 0) from dual;
3. 45.564를 1의 자리에서 반올림 하여라.
select round(45.564, -1) from dual;
4. 45.564를 소수 첫번째 자리까지만 남기고 버림하여라.
select trunc(45.564, 1) from dual;
5. 1200을 14로 나눈 나머지를 구하여라.
select mod(1200, 14) from dual;
6. emp의 월급컬럼(sal)을 100으로 나눈뒤 그 나머지를 출력하여라.
select sal, mod(sal,100) from emp;
7. emp에서 사번이 홀수인 사람의 정보를 출력하여라.
select * from emp where mod(empno,2)=1;
문자함수
INITCAP - 이니셜만 대문자로 변환시킨다. ex) INITCAP(mir) ------ 출력시 Mir
INSTR - 특정문자가 출현하는 위치를 알려준다.
INSTR( [검색할곳], [특정문자], [검색시작위치], [결과선택] ) 형식으로 쓰인다.
ex) select instr('Welcome to Oracle 10g' , 'o', 3, 2) -> 출력시 10
위 Welcome to Oracle 10g에서 o를 검색할때 3번째 자릿수부터 검색을 시작해서
두번째 발견된 'o'의 위치를 출력하라는 뜻이다.
공백값(스페이스)도 자릿수에 포함된다는거 까먹지 말자!
SUBSTR - 문자의 일부분을 추출한다.
LPAD - 오른쪽 정렬 후 왼쪽에 생긴 빈 공백에 특정 문자를 채운다.
RPAD - 왼쪽 정렬 후 오른쪽에 생긴 빈 공백에 특정 문자를 채운다.
LPAD,RPAD ( [정렬할곳], [정렬자릿수], [채워넣을문자] ) 형식으로 쓰인다.
ex) select rpad(ename, 10, 'is win') from emp;
ex) select lpad(ename, 15, '#')from emp;
15자릿수로 맞춰지며 남는 공간에 #을 모두 집어넣게 된다.
LTRIM - 왼쪽에서 특정 문자를 삭제한다.
RTRIM - 오른쪽에서 특정 문자를 삭제한다.
LTRIM,RTRIM( [삭제될값], [삭제할특정문자] ) 형식으로 쓰인다.
ex) 'aaaOracle 10gaaa' 라는 글에서 앞뒤로 'a'를 지워보자.
select RTRIM('aaaOracle 10gaaa', 'a') from dual;
TRIM - 앞,뒤에서 특정 문자를 삭제한다.
위 RTRIM과 LTRIM의 특성을 합친것이라고 보면된다.
하지만 형식이 약간 다른데
TRIM( [삭제할특정문자] from [삭제될값] ) 형식으로 쓰인다.
select trim('a' from 'aaaOracle 10gaaa') from dual;
1. emp 테이블에서 사원 이름(ename)을 소문자로 출력하세요.
2. emp 테이블에서 사원 이름의 첫글자만 대문자로 출력하세요.
3. emp 테이블에서 사원 이름의 길이를 출력하세요.
4. emp 테이블에서 사원이름과 이름에 A가 몇번째 있는지 출력하세요.
5. emp 테이블에서 세번째 자리가 R인 사원의 정보를 출력하세요.
6. emp 테이블에서 이름의 끝자리가 N으로 끝나는 사원의 정보를 출력하세요.
7. AND나 BETWEEN을 사용하지 않고 87년도에 입사한 직원의 이름과 월급을 출력하세요.
8. emp 테이블에서 사원의 이름이 5글자인 사원의 이름을 첫글자만 대문자로 출력하세요.
select lower(ename) from emp;
2. emp 테이블에서 사원 이름의 첫글자만 대문자로 출력하세요.
select initcap(ename) from emp;
3. emp 테이블에서 사원 이름의 길이를 출력하세요.
select length(ename) from emp;
4. emp 테이블에서 사원이름과 이름에 A가 몇번째 있는지 출력하세요.
select ename, instr(ename, 'A') from emp;
5. emp 테이블에서 세번째 자리가 R인 사원의 정보를 출력하세요.
select ename from emp where instr(ename,'R') = 3;
select ename from emp where ename like '__R%';
6. emp 테이블에서 이름의 끝자리가 N으로 끝나는 사원의 정보를 출력하세요.
select ename from emp where substr(ename,-1,1) = 'N';
select ename from emp where ename like '%N';
7. AND나 BETWEEN을 사용하지 않고 87년도에 입사한 직원의 이름과 월급을 출력하세요.
select * from emp where substr(hiredate, 1, 2)=87;
select * from emp where hiredate like '87%';
8. emp 테이블에서 사원의 이름이 5글자인 사원의 이름을 첫글자만 대문자로 출력하세요.
select initcap(ename) from emp where length(ename) = 5;
날짜함수
SYSDATE - 시스템에 저장된 현재 DATE를 출력한다.
MONTHS_BETWEEN - DATE와 DATE사이의 개월수를 계산한다.
date - date = '몇일'
MONTHS_BETWEEN(date, date) = '몇개월'
ex) 현재날짜부터 100일이 지난 날짜 구하기
ex) 사원들이 입사한 날부터(hiredate) 현재 몇개월이 지났는지 구하기
결과를 보면 소숫점까지 나오게 되는데 다들 소숫점이 안나오게 출력을 해보길 바란다.
ADD_MONTHS - DATE에 개월을 더한 DATE를 계산한다.
ex) select ADD_MONTHS(sysdate, 6) from dual; -> 출력시 12/2/25
LAST_DAY - 월의 마지막 DATE를 계산한다.
ex) select LAST_DAY(sysdate) from dual; -> 출력시 11/8/31
NEXT_DAY - 해당 date를 기준으로 명시된 요일에 해당하는 날짜를 반환한다.
ex) select NEXT_DAY(sysdate, '금') from dual; -> 출력시 11/8/26
현재 포스트 쓰고 있는 날짜가 11년 8월 25일이며 목요일이다.
그래서 last_day는 8월 31일이 나오고 next_day '금' 을 출력하면 다음 금요일인 8월 27일이 나온다.
출력값이 다를것이다. 출력값을 같게 만들고 싶으면 11년 8월 25일에 출력해보기 바란다.
그럼 문제를 풀어보자
------------------------------------------------------------------------------
1. emp테이블에서 이름과 입사일자 그리고 현재날까지의 경과일을 산출하세요.
(소숫점을 빼버리고 해딩이름을 경과일로 바꾸세요.)
2. 1번문제를 경과일을 개월수로 바꿔서 산출하세요.
(소숫점을 빼버리고 해딩이름을 경과개월수로 바꾸세요.)
3. emp테이블에서 입사후 첫 휴일(일요일)은 언제일지 구하세요.
4. emp테이블에서 입사후 6개월이 지난날짜 바로 다음 일요일을 구하세요.
------------------------------------------------------------------------------
1. emp테이블에서 이름과 입사일자 그리고 현재날까지의 경과일을 산출하세요.
(소숫점을 빼버리고 해딩이름을 경과일로 바꾸세요.)
select ename, hiredate, trunc(sysdate-hiredate) as "경과일" from emp;
2. 1번문제를 경과일을 개월수로 바꿔서 산출하세요.
(소숫점을 빼버리고 해딩이름을 경과개월수로 바꾸세요.)
select ename, hiredate, trunc(months_between(sysdate, hiredate)) as "경과개월수" from emp;
3. emp테이블에서 입사후 첫 휴일(일요일)은 언제일지 구하세요.
(이름과 입사날짜, 첫휴일을 출력하세요)
select ename,hiredate,next_day(hiredate,'일') as "첫휴일" from emp;
4. emp테이블에서 입사후 6개월이 지난날짜 바로 다음 일요일을 구하세요.
select next_day(add_months(hiredate,6),'일') as "6개월후일요일" from emp;
형변환함수
TO_DATE - 문자열을 날짜 형으로 변환한다. TO_DATE('문자', 'format형식')
-> 문자를 변환하는것이기 때문에 '(홑따옴표)를 꼭 써줘야 한다.
-> 홑따옴표를 안쓰면 1986나누기 12나누기 12(숫자형태)로 인식한다.
-> 포멧형식에 맞는 문자를 집어 넣어야 한다.
날짜 format형식
| YYYY | 년도 표현(4자리) |
| YY | 년도 표현(2자리) |
| MM | 월을 숫자로 표현 |
| MON | 월을 알파벳으로 표현 |
| DAY | 요일 표현 |
| DY | 요일을 약어로 표현 |
시간 format형식
| AM/PM | 오전,오후 표시 |
| HH/HH12 | 시간(1~12) |
| HH24 | 시간(0~23) |
| MI | 분을 표현 |
| SS | 초를 표현 |
| 9 | 한자리의 숫자 표현 | ex) (1111, '99999') -> 1111 |
| 0 | 앞부분을 0으로 표현 | ex) (1111, 099999') -> 001111 |
| $ | 달러 기호를 앞에 표현 | ex) (1111, '$99999') -> $1111 |
| . | 소수점을 표시 | ex) (1111, '99999,99) -> 1111.00 |
| , | 특정 위치에 , 표시 | ex) (1111, '99,999) -> 1,111 |
| MI | 오른쪽에 -기호 표시 | ex) (1111, '99999MI) -> 1111- |
| PR | 음수값을 <>으로 표현 | ex) (1111, '99990PR) -> <1111> |
| EEEE | 과학적 표기법으로 표현 | ex) (11, '9.9EEEE') -> 1.1E +01 |
| V | 10ⁿ을 곱한값으로 표현 | ex) (1111, '999V99') -> 111100 |
| B | 공백을 0으로 표현 | ex) (1111, 'B9999.99') -> 1111.00 |
| L | 지역 통화를 표현 | ex) (1111, 'L9999) -> ₩1111 |
select TO_CHAR(sysdate, 'yyyy"년" mm"월" dd"일", hh"시" mi"분" ss"초" day') from dual;
ex)급여를 3자리마다 ,를 찍어주고 지역 국가 통화기호를 붙혀 출력하기
select ename, TO_CHAR(sal, 'L999,999') from emp;
문제로 조금더 알아보자.
------------------------------------------------------------------------------
1. 오늘날짜를 "xx년 xx월 xx일" 형식으로 출력하세요.
2. 지금현재 몇시 몇분인지 출력하세요.
3.이번년 12월 31일까지 몇일이 남았는지 출력하세요.
select TO_CHAR(sysdate, 'hh12"시 " mi"분" ss"초"') from dual;
select trunc (to_date ('2011/12/31', 'yy/mm/dd') - sysdate) as "이번년 남은일수" from dual;
select ename as "이름" , to_char(sal,'$999,999') as "급여" from emp;
NULL변환 함수
NVL - null값을 0또는 다른 값으로 변환하기 위해 사용하는 함수이다.
이전에 comm을 더하는식에서 comm이 null값이여서 출력값도 null값이 였던 것을 보며
null의 특성을 배웠다.
NVL을 사용하면 null값을 0 또는 다른 값으로 변환할수 있기때문에 출력값이 null이 아닌
정상적인 출력값으로 바꿀수 있다.
ex) 일반적인 null값 연산
ex) NVL을 사용한 null값 연산
NVL(comm, 0) : comm이 null값을 가지고 있으면 0으로 대입하라.
select ename,sal,comm,sal*12+nvl(comm,0) from emp;
그럼 아래 문제를 한번 풀어보자.
emp에서 이름과 매니져번호(mgr)를 출력하는데 매니져번호가 null이면 ceo로 출력하세요.
- emp에서 이름과 매니져번호(mgr)를 출력하는데 매니져번호가 null이면 ceo로 출력하세요.
*mgr은 정수이기 때문에 CEO를 대입할려면 문자형으로 바꿔 줘야 한다.
select ename, NVL(to_char(mgr,'9999'), 'CEO') from emp;
조건 함수
DECODE
- C언어의 else if 구문과 같은 역활을 한다.
조건을 주고 난뒤 그 조건과 일치하는 값을 출력한다.
ex)부서번호에 따른 부서명 출력하기
위 예제에서 부서번호가 10,20,30,40이 되었을때 danme을 각각의 값에 따라 서로 다르게 출력하도록
설정하였다. 예제를 따라 출력해보자
- DECODE문과 같이 조건을 주고 난뒤 그 조건과 일치하는 값을 출력한다.
하지만 다른점이 있다면 DECODE문은 조건을 줄때 조건과 같은(=)만 할수 있으며
CASE문은 연산자를 사용하여 조건을 범위로 지정할수도 있다. 아래 예제를 보자
ex)부서번호에 따른 부서명 출력하기
select ename, deptno,
CASE when deptno = 10 then 'ACCOUNTING'
when deptno = 20 then 'RESEARCH'
when deptno = 30 then 'SALES'
when deptno = 40 then 'OPERATIONS'
END AS dame
from emp;
CASE문은 연산자를 바꿔주면 deptno >=10, deptno<20 등등 여러 범위를 지정할수 있지만
DECODE문은 연산자를 사용하지 않기때문에 각각의 조건을 다 적어줘야 한다.
예제를 따라 출력해보자.
개인적으로는 CASE가 훨씬 더 편할듯 하다. (자바에서도 CASE가 쓰기는 편했엇다.)
각자 자기가 마음에 드는것을 자주 사용하면 될듯하다.
자 마지막문제를 풀어보자!
-------------------------------------------------------------------------------------------------------------------------------------
2. emp테이블에서 이름,직책,직책수당(Up Sal)을 출력하는데
-------------------------------------------------------------------------------------------------------------------------------------
2. emp테이블에서 이름,직책,직책수당(Up Sal)을 출력하는데
(필자가 위에 설명을 깜빡 안하고 넘어갔었는데 나머지 모두를 선택할때에는 else를 사용한다.)
Chapter5에서는 데이터를 보다 효율적으로 처리하기 위해 제공되는 함수들을 간단히 알아보았다.
문자함수, 숫자함수, 날짜함수, 형변환함수, null값 함수 등등 개인적으론 생각보다 분량이 많아서 너무 싫었다.;;;
아무튼 문제같은경우는 꼭 한번씩 풀어보기 바란다. (한번풀어야지 이런것이 있었다라는것이 머리에 심어진다.)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
출처 꼭 기재해주시고요. 상업적인 용도의 이용은 절대 금합니다.
⬇⬇⬇⬇ 도움이 되셨다면 다음뷰 추천(아래 손가락 모양) 한번씩만 클릭해주세요 ^^ 추천은 저에게 힘이 됩니다~⬇⬇⬇⬇
'DBMS > Oracle' 카테고리의 다른 글
| Oracle Chapter6.조인(JOIN) (오라클 입문자용) (5) | 2011.08.27 |
|---|---|
| Oracle Chapter5. 그룹함수 (오라클 입문자용) (7) | 2011.08.26 |
| Oracle Chapter3. WHERE절과 연산자 (오라클 입문자용) (3) | 2011.08.24 |
| Oracle Chapter2. SQL*Plus 명령어 (오라클 입문자용) (4) | 2011.08.24 |
| Oracle Chapter1. SQL의 기본 (오라클 입문자용) (3) | 2011.08.23 |