Database
오라클 내장함수
- SQL 작성이 사용할 수 있는 유용한 기능이 제공되는 함수
- DBMS 제품마다 조금씩 차이가 있다.
오라클 내장함수의 종류
- 단일행 함수
- 조회된 행마다 하나의 결과를 반환한다.
- 단일행 함수는 중첩해서 사용할 수 있다.
- 종류
- 문자함수 : 문자를 입력값으로 받아서 계산한 결과를 반환
- 숫자함수 : 숫자를 입력값으로 받아서 계산한 결과를 반화
- 날짜함수 : Date 타입의 값에 대한 처리를 수행
- 변환함수 : 데이터의 타입을 변환하는 처리를 수행
- 기타함수 : nvl, case, decode 등의 함수가 있다.
- 다중행 함수(그룹함수)
- 조회된 행을 그룹으로 묶고 행 그룹당 하나의 결과를 반환한다.
- group by 절을 사용해서 조회된 행을 그룹으로 묶고 다중행 함수로 각 그룹당 하나의 결과(합계, 평균, 분산, 표준편차, 최고값, 최저값) 등을 계산해 낸다.
문자 함수
- lower(column or exp)
- upper(column or exp)
- substr(column or exp, beginIndex)
- substr(column or exp, beginIndex, length)
- 텍스트의 값을 시작위치부터 지정된 길이만큼 잘라낸다.
- concat(column or exp, column or exp)
- length(column or exp)
- instr(column or exp, 'string')
- 텍스트에서 지정된 문자열이 처음으로 등장하는 위치를 반환한다.
- lpad(column or exp, length, 'string')
- 텍스트의 길이가 지정된 길이보다 부족하면 지정된 문자를 왼쪽에 채운다.
- rpad(column or exp, length, 'string')
- 텍스트의 길이가 지정된 길이보다 부족하면 지정된 문자를 오를쪽에 채운다.
- trim(column or exp)
- replace(column or exp, 'search_string', 'replacement_string')
- 텍스트에서 검색된 문자를 대체할 문자로 바꾼다.
숫자함수
- round(column or exp)
- round(column or exp, n)
- trunc(column or exp)
- trunc(column or exp, n)
- ceil(column or exp)
- 숫자보다 크거가 같은 정수중에서 가장 작은 정수를 반환한다.
- floor(column or exp)
- 숫자보다 작거나 같은 정수중에서 가장 큰 정수를 반환한다.
- mod(m, n)
날짜함수
- sysdate
- 날짜연산
- 날짜 + 숫자
- 지정된 날짜에서 숫자만큼 경과된 날짜를 반환한다.
- 날짜 - 숫자
- 지정된 날짜에서 숫자만큼 이전 날짜를를 반환한다.
- 날짜 - 날짜
- round(날짜)
- 날짜를 반올림한다. 정오가 지나면 하루가 증가된다.
- trunc(날짜)
- 지정된 날짜에서 시간정보를 전부 0으로 바꾼 값을 반환한다.
- months_between(날짜, 날짜)
- add_months(날짜, 숫자)
- 날짜에서 숫자만큼 개월수를 증감시킨 값을 반환한다.
데이터 타입 변환
- 묵시적 타입 변환 : 쿼리 실행과정에서 자동으로 데이터타입이 변환됨
- 문자를 숫자로 (문자가 숫자로만 구성되어 있을 때)
- 문자를 날짜러(문자가 날짜표기 형식의 문자일 때)
- 명시적 타입 변환
- to_char(숫자, '포맷형식')
- to_number('특정패턴으로 구성된 숫자형식의 문자', '패턴')
- ,가 포함된 문자열을 숫자로 변환한다.
- 패턴 문자
- 9 숫자를 나타낸다
- 0 숫자를 나타낸다.
- $ 달러 기호를 나타낸다.
- . 소숫점을 나타낸다.
- , 자릿수를 나타낸다.
select book_no, book_title, to_number(book_price, '9,999,999')
from sample_books;
- to_char(날짜, '포맷형식')
select book_no, book_title, to_char(book_create_date, 'yyyy-mm-dd')
from sample_books
- to_date('특정 패턴으로 작성된 날짜형식의 문자', '패턴')
select book_no, book_title, book_price, book_create_date
from sample_books
where book_create_date >= to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss')
and book_create_date < to_date('2021-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss')
- 패턴 문자
- YYYY 년도를 나타낸다.
- MM 월을 나타낸다.
- DD 일을 나타낸다.
- AM 오전 오후를 나타낸다.
- HH, HH12, HH24 시간을 나타낸다.
- MI 분을 나타낸다.
- SS 초를 나타낸다.
기타함수
- nvl(null값이 예상되는 컬럼, 대체할 값)
- nvl은 null값을 지정된 대체값으로 변환한다.
- 컬럼의 값이 null이 아닌 경우에는 그 컬럼의 원래값이 반환된다.
- nvl에서는 첫번째 항목과 두번째 항목의 데이터 타입이 동일해야 한다.
- 주로 null값을 포함하고 있는 컬럼이 연산식에 포함되어 있을 때 사용한다.
- 사용예
select no, name, sal, com, sal + nvl(com, 0) real_salary
from employee
- case ~ when ~ then ~ end
- 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.
- java의 switch나 if ~ else if ~ else과 유사한다.
- 사용예제
select no, name, salary,
case
when salary >= 1000 then salary * 0.01
when salary >= 750 then salary * 0.015
when salary >= 500 then salary * 0.02
else salary * 0.03
end as increase_salary
from employee;
select no, name, position, salary
case position
when '임원' then salary/5
when '부장' then salary/3
when '과장' then salary/2
else salary
end as bonus
from employees
- decode(컬럼, 값, 반환값, 값 반환갑, ...)
- 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.
decode(컬럼, 값1, 반환값1,
값2, 반환값2,
값3, 반환값3,
반환값4)
select no, name, deptno,
decode(deptno, 100, 'A팀',
101, 'B팀',
102, 'C팀',
'D팀')
from employees
select round(1.4),
trunc(1.4),
ceil(1.4),
floor(1.4)
from dual;
select round(1.7),
trunc(1.7),
ceil(1.7),
floor(1.7)
from dual;
select
round(1234.56789, 4),
round(1234.56789, 3),
round(1234.56789, 2),
round(1234.56789, 1),
round(1234.56789, 0),
round(1234.56789, -1),
round(1234.56789, -2),
round(1234.56789, -3)
from dual;
select
trunc(1234.56789, 4),
trunc(1234.56789, 3),
trunc(1234.56789, 2),
trunc(1234.56789, 1),
trunc(1234.56789, 0),
trunc(1234.56789, -1),
trunc(1234.56789, -2),
trunc(1234.56789, -3)
from dual;
select sysdate from dual;
select employee_id, first_name, hire_date, trunc(MONTHS_BETWEEN(SYSDATE,hire_date), 0)
from employees;
select date('21/08/26') - date('21/03/22')
from dual;
select sysdate + 5 , sysdate - 5
from dual;
select employee_id, first_name, hire_date, trunc(sysdate - hire_date)
from employees
where department_id = 60;
select sysdate, next_day(sysdate, '월')
from dual;
select sysdate, last_day(sysdate), last_day('2021/07/26')
from dual;
select employee_id, salary
from employees
where salary >= 15000;
select employee_id, salary
from employees
where salary >= '15000';
select employee_id, first_name, hire_date
from employees
where hire_Date > '2007/01/01';
select employee_id, first_name, hire_date
from employees
where hire_Date > '2007-01-01';
select employee_id, first_name, hire_date
from employees
where hire_Date > '2007.01.01';
select employee_id, first_name,
salary, to_char(salary, '999,999'),
commission_pct, to_char(commission_pct, '0.999')
from employees
where commission_pct is not null;
select employee_id, first_name, hire_date,
to_char(hire_date, 'yyyy'),
to_char(hire_date, 'mm'),
to_char(hire_date, 'dd'),
to_char(hire_date, 'yyyy/mm/dd'),
to_char(hire_date, 'yyyy-mm-dd'),
to_char(hire_date, 'YYYY"년"MM"월"DD"일" day'),
to_char(hire_date, 'hh:mi:ss'),
to_char(hire_date, 'hh"시"mi"분"ss"초"')
from employees;
select '100' + '100' from dual;
select to_number('1,000', '9,999') + to_number('1,000', '9,999') from dual;
select sysdate - to_date('2020-12-25', 'yyyy-mm-dd') from dual;
select to_date('2020.01.01') + 1000 from dual;
select nvl(1000, 0),
nvl(null, 0)
from dual;
select 100 + null, 100 - null, 100 * null, 100 / null
from dual;
select employee_id, first_name, salary, salary + salary * nvl(commission_pct, 0)
from employees;
select employee_id, first_name, salary,
nvl2(commission_pct, salary * commission_pct, 0) commission_1,
salary*nvl(commission_pct, 0) commission_2
from employees;
select employee_id, first_name, department_id, salary,
decode(department_id, 30, salary*0.2) bonus
from employees
where department_id in (10, 20, 30);
select employee_id, first_name, department_id, salary,
decode(department_id, 10, salary*0.1,
20, salary*0.2,
30, salary*0.3) bonus
from employees
where department_id in (10, 20, 30);
select employee_id, first_name, job_id,
decode(substr(job_id, 4), 'CLERK', (select trunc(avg(salary)/2) from employees),
(select trunc(avg(salary)*0.3) from employees)) bonus
from employees;
select employee_id, first_name, salary,
round(
case
when salary >= 20000 then salary*0.1
when salary >= 10000 then salary*0.3
when salary >= 5000 then salary*0.5
else salary*0.75
end, -2) as bonus
from employees;
select employee_id, first_name, salary,
case
when salary >= 20000 then 'A'
when salary >= 10000 then 'B'
when salary >= 5000 then 'C'
when salary >= 2500 then 'D'
else 'E'
end as salary_grade
from employees;
select DISTINCT manager_id
from employees;
select distinct department_id
from employees
where department_id is not null;
select employee_id, first_name, lower(email)
from employees;
select employee_id, first_name
from employees
where first_name like 'A%';
select employee_id, first_name, salary, (salary*12 + (salary*12*nvl(commission_pct, 0))) as 연간총수입
from employees;
select employee_id, first_name, salary,
(case
when salary >= 14000 then salary*0.45
when salary >= 10000 then salary*0.42
when salary >= 7500 then salary*0.35
when salary >= 5000 then salary*0.25
when salary >= 2000 then salary*0.09
else 0
end) as 세금
from employees
where department_id = 80;
select employee_id, first_name, salary,
decode(department_id, 50, salary + salary*0.15) as 인상된급여, decode(department_id, 50, salary + salary*0.15) - salary as 차이
from employees
where department_id = 50;
select employee_id, first_name
from employees
where first_name like 'A%' or first_name like 'J%' or first_name like 'M%';
select employee_id, first_name, hire_date, round(MONTHS_BETWEEN(SYSDATE,hire_date), 0)
from employees;
select employee_id, first_name, salary,
lpad('*', trunc(salary/1000) , '*') as 별
from employees;