DECODE

• 값에 따라 반환값이 결정되는 구문이다.
• decode(칼럼명, 값1, 반환값1,

     칼럼명, 값2, 반환값2,
            값3, 반환값3)
  

ex)

-- 각 사원의 부서이름을 가져온다.
-- 10 : 인사과, 20 : 개발부, 30 : 경영 지원팀
-- 40 : 생산부
select empno, ename,
    decode(deptno, 10, '인사과',
                   20, '개발부',
                   30, '경영지원팀',
                   40, '생산부')
from emp;

ex)

-- 직급에 따라 인상된 급여액을 가져온다.
-- CLEAK : 10%
-- SALESMAN : 15%
-- PRESIDENT : 20%
-- MANAGER : 5%
-- ANALYST : 20%
select empno, ename, job,
    decode(job, 'CLEAK', sal *1.1,
                'SALESMAN', sal *1.15,
                'PRESIDENT', sal *1.2,
                'MANAGER', sal * 1.05,
                'ANALYST', sal * 1.2)
from emp;

CASE

• 조건에 따라 반환값이 결정되는 구문이다.
• case when 조건식1 then 반환값1

   when 조건식2 then 반환값2
  

  end

ex)

-- 급여액별 등급을 가져온다.
-- 1000미만 : C등급
-- 1000이상 2000미만 : B등급
-- 2000이상 : A등급
select empno, ename,
    case when sal < 1000 then 'C등급'
            when sal >= 1000 and sal < 2000 then 'B등급'
            when sal >= 2000 then 'A등급'
    end
from emp;

ex)

-- 직원들의 급여를 다음과 같이 인상한다.
-- 1000이하 : 100%
-- 1000초과 2000미만 : 50%
-- 2000이상 : 200%
select empno, ename,
    case when sal<=1000 then sal*2
            when sal>1000 and sal<2000 then sal *1.5
            when sal>=2000 then sal*3
            end
from emp;

DECODE를 사용하면 값에 따라 반환값을 결정할 수 있다.
CASE를 사용하면 조건에 따라 반환 값을 결정할 수 있다.

Comment and share

날짜 함수

• 오라클을 날짜데이터를 제어할 수 있는 함수들을 제공하고 있다.
• sysdate : 현재 날짜와 시간을 반환한다.
• months_between : 두 날짜간의 개월 수를 구한다.
• add_months : 주어진 개월 수 만큼 더한다.
• next_day : 돌아오는 지정된 요일의 날짜를 반환한다.
• last_day : 지정된 달에 마지막 날을 반환한다.
• round : 지정된 기준으로 반올림한다.
• trunc : 지정된 기준으로 버린다.

ex)

-- 현재 날짜 구하기
select sysdate
from dual;

ex)

-- 날짜 데이터 연산(10000일빼기, 10000일더하기)
select sysdate -10000, sysdate+10000
from dual;

ex)

-- 각 사원이 입사한 날짜로 부터 1000일 후가 되는 날짜를 가져온다.
select hiredate+1000
from emp;

ex)

-- 직무가 SALESMAN인 사원의 입사일 100일전 날짜를 가져온다.
select hiredate-100
from emp
where job = 'SALESMAN';

ex)

-- 전 사원의 근무 일을 가져온다.
select trunc(sysdate-hiredate)
from emp;

ex)

-- 반올림(버림은 trunc를 사용하면된다.)
select round(sysdate, 'CC') as "년도두자리"
, round(sysdate, 'YYYY') as "월기준"
, round(sysdate, 'ddd') as "시기준"
, round(sysdate, 'HH') as "분기준"
, round(sysdate, 'MM') as "일기준"
, round(sysdate, 'DAY') as "주기준"
, round(sysdate, 'MI') as "초기준"
from dual;

결과값 : 01/01/01, 21/01/01, 21/03/23, 21/03/22, 21/04/01, 21/03/21, 21/03/22

ex)

-- 각 사원의 입사일을 월 기준으로 반올림한다.
select round(hiredate, 'yyyy')
from emp;

ex)

-- 1981년에 입사한 사원들의 사원번호, 사원이름, 급여, 입사일을가져온다.
select empno, ename, sal, hiredate
from emp
where hiredate >='1981/01/01' and hiredate <= '1981/12/31';

select empno, ename, sal, hiredate
from emp
where hiredate between '1981/01/01' and '1981/12/31';

select empno, ename, sal, hiredate
from emp
where trunc(hiredate, 'yyyy') = '1981/01/01';

ex)

-- 두 날짜 사이의 일수를 구한다.
select sysdate - hiredate
from emp;

ex)

-- 모든 사원이 근무한 개월 수를 구한다.
select trunc(months_between(sysdate, hiredate))
from emp;

ex)

-- 개월수를 더한다.(오늘날짜로부터 100개월 후)
select add_months(sysdate, 100)
from dual;

ex)

-- 각 사원들의 입사일 후 100개월 되는 날짜를 구한다.
select add_months(hiredate, 100)
from emp;

ex)

-- 지정된 날짜를 기준으로 지정된 다음 요일이 몇일인지 구한다.
select next_day(sysdate, '월요일')
from dual;

ex)

-- 지정된 날짜의 월 마지막 날짜를 구한다.
select sysdate, last_day(sysdate)
from dual;

ex)

-- to_char : 날자데이터를 문자형으로 반환한다.
주로 오라클에서 다른 프로그램으로 출력할 때 사용한다.
select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS AM')
from dual;

날자 형식에는 한글이 들어갈 수 없다.

ex)

-- to_date : 프로그램 -> 오라클
select to_date('2018-03-20 01:58:20 오후', 'YYYY-MM-DD HH:MI:SS AM')
from dual;

각자리에 해당하는 데이터를 날자형식으로 반환하여 출력한다.

ex)

-- 사원들의 입사일을 다음과 같은 양식으로 가져온다.
-- 1900-10-10
select to_char(hiredate, 'YYYY-MM-DD')
from emp;

Comment and share

문자열 함수

• 컬럼에 저장되 있는 문자열에 대해 처리를 하여 값을 가져올 수 있는 함수들을 의미한다.
• http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm

ex)

-- 대문자--> 소문자
select lower('ABcdef')
from dual;

결과값 : abcdef

ex)

-- 사원들의 이름을 소문자로 가져온다.
select lower(ename)
from emp;

ex)

-- 소문자 --> 대문자
select upper('abCDEF')
from dual;

결과값 : ABCDEF

ex)

-- 첫 글자만 대문자로, 나머지는 소문자로
select initcap ('aBCDEF')
from dual;

결과값 : Abcdef

ex)

-- 사원이름을 첫 글자는 대문자로 나머지는 소문자로 가져온다.
select initcap(ename)
from emp;

ex)

-- 문자열 연결
select concat(concat('kkk', concat('abc', 'def')), 'zzz')
from dual;

결과값 : kkkabcdefzzz

ex)

-- 사원들의 이름과 직무를 다음과 같이 가져온다.
-- 사원의 이름은 ㅇㅇㅇ이고, 직무는 ㅇㅇㅇ입니다.
select concat(concat(concat(concat('사원의 이름은',ename), '이고, 직무는 '), job),'입니다.')
from emp;

select '사원들의 이름은'||ename||'이고, 직무는'|| job||'입니다.'
from emp;

ex)

-- 문자열의 길이 lenth는 글자수, lengthb는 바이트 수를 가져온다. 영어의 경우 한글자의 수가 1바이트로 동일하므로 동일한 결과값을 가져온다. 그러나 한글은 한글자당 2바이트 이르모 lengthb를 사용할 경우 length값의 2배 값을 가져온다.
그러므로 한글을 사용할경우 length를 사용하는 경우가 많다.
select length('abcd'), lengthb('안녕하세요')
from dual;

결과값 : 4, 10

ex)

-- 문자열 잘라내기(문자열의 길이와 마찬가지고 바이트 단위로 잘린다.)
select substr('안녕하세요', 3), substrb('안녕하세요', 3)
from dual;

결과값 : 하세요, 녕하세요
왼쪽부터 3번째 글자수 부터 출력, 왼쪽 부터 3바이트 이후부터

ex)

select substr('abcdefghi', 3, 4), substr('동해물과 백두산이', 3, 4)
from dual;

결과값 : cdef, 물과 백
왼쪽부터 3번째 글자수 부터 출력 하여 4글자 출력

ex)

-- 문자열 찾기
select instr('abcdabcdabcd', 'bc'), instr('abcdabcdabcd', 'bc', 3), instr('abcdabcdabcd', 'bc', 3, 2)
from dual;

결과값 : 2, 6, 10
bc의 첫번째 위치, 3번째 글자 이후 부터의 bc, 3번째 글자 이후 부터의 2번째 bc
없는 것은 0이 나온다.

ex)

-- 사원의 이름 중에 A라는 글자가 두번째 이후에 나타나는 사원의 이름을 가져온다.
select ename
from emp
where instr(ename, 'A') >1;

ex)

-- 특정 문자열로 채우기
select '문자열', lpad('문자열', 20), rpad('문자열', 20),
lpad('문자열', 20, '_')
from dual;

결과값 : 문자열, 문자열, 문자열 , ______________문자열

ex)

-- 공백제거
select ltrim('     문자열     '),  rtrim('     문자열     '),trim('     문자열     ')
from dual;

결과값 : 문자열 , 문자열, 문자열

ex)

-- 문자열 변경
select replace('abcdefg','abc','kkkkkk')
from dual;

결과값 : kkkkkkdefg

Comment and share

숫자함수

• 컬럼에 저장되어 있는 숫자 값에 대해 처리를 하여 값을 가져올 수 있는 함수들을 의미한다.
• http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm

ex)

-- 절대값 구하기
select abs(-10)
from dual;

결과 값 : 10

ex)

-- 전직원의 급여를 2000삭감하고 삭감한 급여액의 절대값을 구한다.
select abs(sal-2000)
from emp;

ex)

-- 소수점 이하 버림
select floor(12.3456)
from dual;

결과 값 : 12

ex)

-- 급여가 1500 이상인 사원의 급여를 15%삭감한다. 단 소수점 이하는 버린다.
select floor(sal*0.85)
from emp
where sal>=1500;

ex)

-- 반올림
select round(12.3456)
from dual;

결과 값 : 12

ex)

-- 소수점 3번째 자리에서 반올림(소수점이 0)
select round(12.3456, 2)
from dual;

결과 값 : 12.35

ex)

-- 1의 자리에서 반올림(소수점이 0)
select round(12.3456, -1)
from dual;

결과값 : 10

ex)

-- 급여가 2천 이하인 사원들의 급여를 20%씩 인상한다. 단 10의 자리를 기준으로 반올림한다.
select round(sal*1.2, -2)
from emp
where sal<=2000;

ex)

-- 버림, 자리수를 정할 수 있다.
select trunc(12.3456, 2)
from dual;

결과값 : 12.34

ex)

-- 전 직원의 급여를 10자리 이하를 삭감한다.
select trunc(sal, -2)
from emp;

ex)

-- 나머지 구하기(10을 3으로 나누기 했을 때 나머지)
select mod(10, 3)
from dual;

결과값 : 1

Comment and share

정렬

• select문을 통해 얻어온 결과를 특정 컬럼을 기준으로 오름차순 혹은 내림차순으로 정렬할 수 있다.
• 숫자, 문자열, 날짜 등 모든 타입의 데이터를 정렬할 수 있다.
• select 컬럼명 from 테이블명
  where 조건
  order by 컬럼명 [ASC|DESC]
• ASC : 오름차순, 생략가능
• DESC : 내림차순

ex)

-- 사원의 사원번호, 이름, 급여를 가져온다. 급여를 기준으로 오름차순 정렬을 한다.
select empno, ename, sal
from emp
order by sal asc;

select empno, ename, sal
from emp
order by sal;

ASC는 생략이 가능하다.

ex)

-- 사원의 사원번호, 이름, 급여를 가져온다. 사원번호를 기준으로 내림차순 정렬을 한다.
select empno, ename, sal
from emp
order by empno desc;

ex)

-- 사원의 사원번호, 이름을 가져온다. 사원의 이름을 기준으로 오름차순 정렬을 한다.
select empno, ename
from emp
order by ename asc;

ex)

-- 사원의 사원번호, 이름, 입사일을 가져온다. 입사일을 기준으로 내림차순 정렬을 한다.
select empno, ename, hiredate
from emp
order by hiredate desc;

ex)

-- 직무가 SALESMAN인 사원의 사원이름, 사원번호, 급여를 가져온다. 급여를 기준으로 오름차순 정렬을 한다.
select ename, empno, sal
from emp
where job = 'SALESMAN'
order by sal asc;

ex)

-- 1981년에 입사한 사원들의 사원번호, 사원 이름, 입사일을 가져온다, 사원 번호를 기준으로 내림차순 정렬을 한다.
select empno, ename, hiredate
from emp
where hiredate between '1981/01/01' and '1981/12/31'
order by empno desc;

ex)

-- 사원의 이름, 급여, 커미션을 가져온다. 커미션을 기준으로 오름차순 정렬을 한다.
select ename, sal, comm
from emp
order by comm asc;

ex)

-- 사원의 이름, 급여, 커미션을 가져온다. 커미션을 기준으로 내림차순 정렬을 한다.
select ename, sal, comm
from emp
order by comm desc;

ex)

-- 사원의 이름, 사원번호, 급여를 가져온다. 급여를 기준으로 내림차순 정렬, 이름을 기준으로 오름차순 정렬
select ename, empno, sal
from emp
order by sal desc, ename asc;

Comment and share

null비교

• null은 정해져 있지 않은 값 혹은 무한대의 의미를 갖는 값이다.
• 이 때문에 =이나 <>를 통해 컬럼의 값이 null이면 연산을 할 수가 없다.
• 이 때, is null 이나 is not null을 통해 null 비교가 가능하다.

ex)

-- 사원중에 커미션을 받지 않는 사원의 사원번호, 이름, 커미션을 가져온다.
select empno, ename, comm
from emp
where comm is null;

ex)

-- 사원중에 커미션을 받는 사원의 사원번호, 이름, 커미션을 가져온다.
select empno, ename, comm
from emp
where comm is not null;

ex)

-- 회사대표(직속상관이 없는 사람)의 이름과 사원 번호를 가져온다.
select ename, empno
from emp
where mgr is null;

Comment and share

Like

• 조건문에서 문자열 컬럼도 = 과 <>로 비교가 가능하다.
• 만약 문자열 컬럼에 저장되어 있는 값이 특정 문자열을 포함하고 있는지 파악하고 싶을 떄 like 연산자를 사용한다.
• select 컬럼명 from 테이블명
  where 컬럼명 like ‘와일드카드’
• _ : 글자 하나를 의미한다.
• % : 글자 0개 이상을 의미한다.

ex)

-- 이름이 F로 시작하는 사원의 이름과 사원번호를 가져온다.
select ename, empno
from emp
where ename like 'F%';

ex)

-- 이름이 S로 끝나는 사원의 이름과 사원번호를 가져온다.
select ename, empno
from emp
where ename like '%S';

ex)

-- 이름에 A가 포함되어 있는 사원이름과 사원 번호를 가져온다.
select ename, empno
from emp
where ename like '%A%';

ex)

-- 이름의 두번째 글자가 A인 사원의 사원 이름, 사원 번호를 가져온다.
select ename, empno
from emp
where ename like '_A%';

ex)

-- 이름이 4글자인 사원의 사원이름, 사원 번호를 가져온다.
select ename, empno
from emp
where ename like '____';

Comment and share

논리 연산자

• 논리 연산자를 사용하면 여러 조건식을 묶어 하나의 조건식으로 만들 수 있다.
• and : 좌우 조건식이 모두 참일 경우 참
• or : 좌우 조건식이 모두 거짓일 경우 거짓
• not : 조건식의 결과를 부정
• between and : 범위 조건
• in : 항목 조건

ex)

-- 10번 부서에서 근무하고 있는 직무가 MANAGER인 사원의 사원번호, 이름, 근무부서, 직무를 가져온다.
select empno, ename, deptno, job
from emp
where deptno = 10 and job = 'MANAGER';

ex)

-- 1982년 1월 1일 이후에 입사한 사원의 사원번호, 이름, 입사일을가져온다.
select empno, ename, hiredate
from emp
where hiredate >= '1982/01/01';

ex)

-- 10번 부서에서 근무하고 있는 직무가 MANAGER인 사원의 사원번호, 이름, 근무부서, 직무를 가져온다.
select empno, ename, deptno, job
from emp
where deptno = 10 and job = 'MANAGER';

ex)

-- 입사년도가 1981년인 사원중에 급여가 1500이상인 사원의 사원번호, 이름, 급여, 입사일을 가져온다.
select empno, ename, sal, hiredate
from emp
where hiredate >= '1981/01/01' and hiredate <= '1981/12/31' and sal >=1500;

select empno, ename, sal, hiredate
from emp
where hiredate between '1981/01/01' and '1981/12/31' and sal >=1500;

ex)

-- 20번 부서에 근무하고 있는 사원 중에 급여가 1500 이상인 사원의 사원번호, 이름, 부서번호, 급여를 가져온다.
select empno, ename, deptno, sal
from emp
where deptno = 20 and sal >= 1500;

ex)

-- 직속상관 사원 번호가 7698번인 사원중에 직무가 CLERK인 사원의 사원번호, 이름, 직속상관번호, 직무를 가져온다.
select empno, ename, mgr, job
from emp
where mgr = 7698 and job = 'CLERK';

ex)

-- 급여가 2000보다 크거나 1000보다 작은 사원의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal >2000 or sal <1000;

select empno, ename, sal
from emp
where not(sal >=1000 and sal <=2000);

select empno, ename, sal
from emp
where not(sal between 1000 and 2000);

ex)

-- 부서번호가 20이거나 30인 사원의 사원번호, 이름, 부서번호를 가져온다.
select empno, ename, deptno
from emp
where deptno = 20 or deptno = 30;

ex)

-- 직무가 CLERK, SALESMAN, ANALYST인 사원의 사원번호, 이름, 직무를 가져온다.
select empno, ename, job
from emp
where job = 'CLERK' or job = 'SALESMAN' or job = 'ANALYST';

select empno, ename, job
from emp
where job in ('CLERK','SALESMAN','ANALYST');

ex)

-- 사원 번호가 7499, 7566, 7839가 아닌 사원들의 사원번호, 이름을 가져온다.
select empno, ename
from emp
where empno <> 7499 and empno <> 7566 and empno <> 7839;

select empno, ename
from emp
where not (empno = 7499 and empno = 7566 and empno = 7839);

select empno, ename
from emp
where not (empno in(7499,7566,7839));

Comment and share

조건절

• SQL문은 테이블내의 모든 로우에 대해 적용을 하게 된다.
• 이 때 어떤 조건에 맞는 로우에 대해서만 작업을 하고 싶을 때 조건절을 사용한다.

select의 조건절

• select 컬럼명 from 테이블명
  where 조건절
• select ~ from 까지를 통해 모든 로우를 가져오고 각 로우를 조건절과 비교하여 참인 로우만 남겨주고 거짓인 로우는 제거한다.

비교 연산자

< : 작은가
> : 큰가
<= : 작거나 같은가
>= : 크거나 같은가
= : 같은가
<>, !=, ^= : 다른가

ex)

-- 근무 부서가 10번인 사원들의 사원번호, 이름 근무 부서를 가져온다.
select empno, ename, deptno
from emp
where deptno = 10;

ex)

-- 근무 부서 번호가 10번이 아닌 사원들의 사원번호, 이름, 근무 부서 번호를 가져온다.
select empno,ename, deptno
from emp
where deptno <> 10;

ex)

-- 급여가 1500이상인 사원들의 사원번호, 이름, 급여를 가져온다.
select empno,ename,sal
from emp
where sal >=1500;

ex)

-- 이름이 SCOTT 사원의 사원번호, 이름, 직무, 급여를 가져온다.
select empno, ename, job, sal
from emp
where ename = 'SCOTT';

ex)

-- 직무가 SALESMAN인 사원의 사원번호, 이름, 직무를 가져온다.
select empno,ename,job
from emp
where job = 'SALESMAN';

ex)

-- 직무가 CLERK이 아닌 사원의 사원번호, 이름, 직무를 가져온다.
select empno,ename,job
from emp
where job <> 'CLERK';

Comment and share

산술연산자

+ : 더하기
- : 빼기
* : 곱하기
/ : 나누기

ex)

-- 각 사원들의 급여액과 급여액에서 1000을 더한 값, 200을 뺀 값, 2를 곱한 값, 2로 나눈 값을 가져온다.
select sal,sal+1000,sal-200,sal*2,sal/2
from emp;

ex)

-- 각 사원의 급여액, 커미션, 급여 + 커미션을 가져온다.

select sal, nvl(comm,0), sal + nvl(comm,0)
from emp;

해당 컬럼의 값이 null 값인 경우 특정값으로 출력하고 싶으면 NVL 함수를 사용하고,
null 값이 아닐경우 특정값으로 출력하고 싶으면 NVL2 함수를 사용하면 된다.

Concat

• 문자열을 합치는 연산자이다.
• 문자열 ||컬럼||문자열||컬럼

ex)

-- 사원들의 이름과 직무를 다음 양식으로 가져온다.
-- 000 사원의 담당 직무는 xxx입니다.

select ename || '사원의 담당 직무는 ' || job||'입니다.'
from emp;

||는 CONCAT과 같은 기능을 보여준다.
하지만 세개이상의 문자열을 합칠때는 더 편하게 사용 할 수 있다.

Distinct

• select문을 통해 가져온 모든 로우 중에서 중복된 로우를 제거하는 키워드
• select distinct 컬럼명 from 테이블명

ex)

-- 사원들이 근무하고 있는 근무 부서의 번호를 가져온다.
select DISTINCT deptno
from emp;

Comment and share