728x90
반응형
SMALL
변환 함수
* 암시적인 데이터형 변환
* 명시적인 데이터형 변환
1. 암시적 형 변환
- 값 할당 시 오라클은 자동으로 변환한다.
| From | To | 비고 |
| VARCHAR2 / CHAR | NUMBER | 표현식 계산의 경우 |
| VARCHAR2 / CHAR | DATE | 표현식 계산의 경우 |
| NUMBER | VARCHAR2 | |
| DATE | VARCHAR2 |
select employee_id, first_name, hire_date
from employees
where hire_date='03/06/17';
--char가 DATE형으로 변환select employee_id, first_name, department_id
from employees
where department_id='40';
--char가 NUMBER타입으로 자동 형 변환
2. 명시적 형 변환
| 변환 함수 | 설명 |
| TO_CHAR(number [, 'fmt']) | 숫자를 문자 타입으로 |
| TO_CHAR(date[, 'fmt']) | 날짜를 문자 타입으로 |
| TO_NUMBER(char[, 'fmt']) | 숫자를 포함하 문자를 숫자로 |
| TO_DATE(char[, 'fmt']) | 날짜를 나타내는 문자를 DATE타입으로 |
3. 날짜를 문자로 변환
TO_CHAR(date, 'fmt')select first_name, TO_CHAR(hire_date, 'MM/YY') as hiremonth
from employees
where first_name='Steven';
select first_name, to_char(hire_date, 'YYYY"년" MM"월" DD"일"') as hiredate
from employees;
select first_name,to_char(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM',
'NLS_DATE_LANGUAGE=english') as hiredate from employees;
4. 숫자를 문자로 변환
TO_CHAR(number, 'fmt')select first_name, last_name,to_char(salary, '$999,999') salary
from employees;
select first_name, last_name, salary*0.123456 salary1,
to_char(salary*0.123456, '$999,999.99') salary2
from employees
where first_name='David';
5. TO_NUMBER 함수
TO_NUMBER(char, 'fmt')select '$5,500'-4000 from dual; --오류
select to_number('$5,500', '$999,999')-4000 from dual;
6. TO_DATE 함수
TO_DATE(char, 'fmt')select first_name, hire_date
from employees
where hire_date=to_date('2003/06/17','YYYY/MM/DD');
select first_name, hire_date
from employees
where hire_date = to_date('2003년06월17일', 'YYYY"년"MM"월"DD"일"');
7. Null 치환 함수 NVL, NVL2, COALESCE
1) NVL1
NVL(expr1, expr2)
--expr1:널이 있을 수 있는 열
--expr2:널일경우 치환할 값select first_name, salary + salary*nvl(commission_pct,0) from employees;
2) NVL2
nvl2(expr1, expr2, expr3)select first_name,
nvl2(commission_pct, salary+salary*commission_pct, salary) as nann_sal
from employees;3) COALESCE
COALESCE(expr1, ...)select first_name,
coalesce(salary+salary*commission_pct, salary)as ann_sal
from employees;
8. 기타 변환 함수
1) LNNVL
LNNVL(expr1)select first_name, coalesce(salary*commission_pct,0) as bonus from employees
where salary*commission_pct < 650;
==>null인 것들을 모두 제외하게 됨
select first_name, coalesce(salary*commission_pct,0) as bonus from employees
where lnnvl(salary*commission_pct >= 650);
=>null값도 포함시킴
2) DECODE
select job_id, salary,
decode(job_id, 'IT_PROG', salary*1.10,
'FI_MGR', salary*1.15,
'FI_ACCOUNT', salary*1.20,
salary) as revised_sal
from employees;
3) CASE
select job_id, salary,
case job_id when 'IT_PROG' then salary*1.10
when 'FI_MGR' then salary*1.15
when 'FI_ACCOUNT' then salary*1.20
else salary
end as revise_sal
from employees;
=>위와 같은 결과
select employee_id, salary,
case when salary < 5000 then salary*1.2
when salary < 10000 then salary*1.10
when salary < 15000 then salary*1.05
else salary
end as revised_sal
from employees;
집합 연산자
1. UNION
- 합집합과 같은 의미
- 중복된 정보는 한 번만 보여줌.
select employee_id, first_name from employees where hire_date like '04%'
union
select employee_id, first_name from employees where department_id=20;
2. UNION ALL
- 중복된 정보 포함
select employee_id, first_name from employees where hire_date like '04%'
union all
select employee_id, first_name from employees where department_id=20;
3. INTERSECT
- 중복된 행만 출력
- 교집합과 같은 의미
select employee_id, first_name from employees where hire_date like '04%'
intersect
select employee_id, first_name from employees where department_id=20;
4. MINUS
- 첫번째 쿼리에만 포함되고 두번째 쿼리에는 없는 데이터를 보여준다.
- 차집합과 같은 의미
select employee_id, first_name from employees where hire_date like '04%'
minus
select employee_id, first_name from employees where department_id=20;
728x90
반응형
LIST
'IT > SQL' 카테고리의 다른 글
| [SQL] 5. 분석 함수 (0) | 2023.05.11 |
|---|---|
| [SQL] 4. 그룹 함수를 이용한 데이터 집계 (0) | 2023.05.10 |
| [SQL] 3. 함수 - 1 (0) | 2023.05.09 |
| [SQL] 2. SELECT문 (0) | 2023.05.08 |
| [SQL] 1. 데이터베이스 소개 (0) | 2023.05.08 |
