2021. 04. 28(수)

Dylan·2021년 4월 28일
0

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)
    • 텍스트 2개를 연결한다.
  • 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)
    • m을 n으로 나눈 나머지값을 반환한다.

날짜함수

  • sysdate
    • 시스템의 현재날짜와 시간을 반환한다.
  • 날짜연산
    • 날짜 + 숫자
      • 지정된 날짜에서 숫자만큼 경과된 날짜를 반환한다.
    • 날짜 - 숫자
      • 지정된 날짜에서 숫자만큼 이전 날짜를를 반환한다.
    • 날짜 - 날짜
      • 두 날짜사이의 날짜수를 반환한다.
  • round(날짜)
    • 날짜를 반올림한다. 정오가 지나면 하루가 증가된다.
  • trunc(날짜)
    • 지정된 날짜에서 시간정보를 전부 0으로 바꾼 값을 반환한다.
  • months_between(날짜, 날짜)
    • 두 날짜사이의 월수를 반환한다.
  • add_months(날짜, 숫자)
    • 날짜에서 숫자만큼 개월수를 증감시킨 값을 반환한다.

데이터 타입 변환

  • 묵시적 타입 변환 : 쿼리 실행과정에서 자동으로 데이터타입이 변환됨
    • 문자를 숫자로 (문자가 숫자로만 구성되어 있을 때)
    • 문자를 날짜러(문자가 날짜표기 형식의 문자일 때)
  • 명시적 타입 변환
    • to_char(숫자, '포맷형식')
      • 숫자를 지정된 포맷형식의 문자로 변환한다.
    • to_number('특정패턴으로 구성된 숫자형식의 문자', '패턴')
      • ,가 포함된 문자열을 숫자로 변환한다.
      • 패턴 문자
        • 9 숫자를 나타낸다
        • 0 숫자를 나타낸다.
        • $ 달러 기호를 나타낸다.
        • . 소숫점을 나타낸다.
        • , 자릿수를 나타낸다.
        -- 가격에 3자리마다 ,를 추가한 문자열을 반환한다.
        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('특정 패턴으로 작성된 날짜형식의 문자', '패턴')
      • 문자열을 날짜로 변환한다.
      -- '2020'년에 출간된 도서된 도서를 검색한다.
      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값을 포함하고 있는 컬럼이 연산식에 포함되어 있을 때 사용한다.
    • 사용예
      -- 커미션이 null인 경우 0값을 반환한다.
      select no, name, sal, com, sal + nvl(com, 0) real_salary
      from employee
  • case ~ when ~ then ~ end
    • 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.
    • java의 switch나 if ~ else if ~ else과 유사한다.
    • 사용예제
      -- javaa의 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;
    • 사용예제
      -- java의 switch문처럼 사용하기
      -- position 컬럼값에 따라서 보너스를 다르게 계산해서 조회하기
      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)
      -- 컬럼의 값이 값1과 일치하면 반환값1이 최종값이 된다.
      -- 컬럼의 값이 값2와 일치하면 반환값2가 최종값이 된다.
      -- 컬럼의 값이 값1, 값2, 값3 어느값과도 일치하지 않으면 반환값4가 최종값이 된다.
    • 사용예
      -- 소속부서가 100번인 사원들은 A팀, 101번인 사원은 B팀, 102번인 사원은 C팀, 나머지는 D팀으로 조회되게 한다.
      select no, name, deptno,
      decode(deptno, 100, 'A팀',
                     101, 'B팀',
                     102, 'C팀',
                     'D팀')
      from employees

-- 숫자함수
select  round(1.4), -- 1
        trunc(1.4), -- 1
        ceil(1.4),  -- 2, 지정된 숫자보다 같거나 큰 정수 중에서 가장 작은 정수 반환
        floor(1.4)  -- 1, 지정된 숫자보다 같거나 작은 정수 중에서 가장 큰 정수 반환
from dual;

select  round(1.7), -- 2
        trunc(1.7), -- 1
        ceil(1.7),  -- 2
        floor(1.7)  -- 1
from dual;

select 
        round(1234.56789, 4),   -- 1234.5679    소수점이하 5번째 자리에서 반올림
        round(1234.56789, 3),   -- 1234.568     소수점이하 4번째 자리에서 반올림
        round(1234.56789, 2),   -- 1234.57      소수점이하 3번째 자리에서 반올림
        round(1234.56789, 1),   -- 1234.6       소수점이하 2번째 자리에서 반올림
        round(1234.56789, 0),   -- 1235         소수점이하 1번째 자리에서 반올림
        round(1234.56789, -1),  -- 1230         일의 자리에서 반올림
        round(1234.56789, -2),  -- 1200         십의 자리에서 반올림
        round(1234.56789, -3)   -- 1000         백의 자리에서 반올림
from dual;

select 
        trunc(1234.56789, 4),   -- 1234.5678    소수점이하 4번째 자리까지 남기고 버림
        trunc(1234.56789, 3),   -- 1234.567     소수점이하 3번째 자리까지 남기고 버림
        trunc(1234.56789, 2),   -- 1234.56      소수점이하 2번째 자리까지 남기고 버림
        trunc(1234.56789, 1),   -- 1234.5       소수점이하 1번째 자리까지 남기고 버림
        trunc(1234.56789, 0),   -- 1234         소수점부분 전부 버림
        trunc(1234.56789, -1),  -- 1230         일의 자리에서 버림
        trunc(1234.56789, -2),  -- 1200         십의 자리에서 버림
        trunc(1234.56789, -3)   -- 1000         백의 자리에서 버림
from dual;

-- 날짜 관련 함수
-- sysdate : 시스템의 현재 날짜 시간정보 반환
select sysdate from dual; -- 시스템의 현재 날짜, 시간정보를 반환한다.

-- months_betwwen(date1,date2) : 두 날짜간의 개월 수 반환
select employee_id, first_name, hire_date, trunc(MONTHS_BETWEEN(SYSDATE,hire_date), 0)
from employees;

-- 날짜 연산
-- date + 숫자 -> 날짜 : date에서 숫자만큼 경과된 날짜, 숫자는 day(일) 수
-- date - 숫자 -> 날짜 : date에서 숫자만큼 이전 날짜
-- date - date -> 숫자 : 두 날짜간의 day 수
select date('21/08/26') - date('21/03/22')
from dual;

select sysdate + 5 , sysdate - 5
from dual;

-- 60번 부서에 소속된 사원들이 해당 직종의 근무일수 계산
select employee_id, first_name, hire_date, trunc(sysdate - hire_date)
from employees
where department_id = 60;

-- next_day(요일) : 지정된 날짜를 기준으로 가장 최근 요일의 날짜를 반환
select sysdate, next_day(sysdate, '월') 
from dual;

-- last_day(요일) : 
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';    -- 문자 '15000' -> 숫자 15000으로 자동 변환

select employee_id, first_name, hire_date
from employees
where hire_Date > '2007/01/01'; -- 문자 '2007/01/01' -> 날짜로 자동 형변환

select employee_id, first_name, hire_date
from employees
where hire_Date > '2007-01-01'; -- 문자 '2007-01-01' -> 날짜로 자동 형변환

select employee_id, first_name, hire_date
from employees
where hire_Date > '2007.01.01'; -- 문자 '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;

-- nvl 함수
-- nvl(null값이 예상되는 컬럼 혹은 표현식, 대체할 값)
select  nvl(1000, 0),
        nvl(null, 0)
from dual;

-- null인 값과의 사칙연산결과는 언제나 null이다.
select 100 + null, 100 - null, 100 * null, 100 / null 
from dual;

-- employees 테이블에서 사원아이디, 이름, 급여, 커미션이 적용된 급여를 조회하기
select employee_id, first_name, salary, salary + salary * nvl(commission_pct, 0)
from employees;

-- nvl2(컬럼 혹은 값, null 아닐때 값, null 일 때 값)
-- 2번째, 3번째 값의 타입이 동일해야 한다.
-- 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;

-- DECODE(컬럼 혹은 표현식, 값1, 표현식, null)
-- 첫번째 파라미터값이 값1과 일치하면 표현식의 결과가 반환되고,
--                        일치하지 않으면 null이 반환된다(null은 생략할 수 있다.)

-- decode(컬럼 혹은 표현식, 값1, 표현식1, 표현식2)
-- 첫번째 파라미터값이 값1과 일치하면 표현식의 결과가 반환되고,
--                        일치하지 않으면 표현식2의 결과가 반환된다

-- decode(컬럼 혹은 표현식, 값1, 표현식1, 
--                        값2, 표현식2,
--                        값3, 표현식3,
--                        표현식4)
-- 첫번째 파라미터값이 값1과 일치하면 표현식1의 결과가 반환되고,
--                   값2과 일치하면 표현식2의 결과가 반환되고,
--                   값3과 일치하면 표현식3의 결과가 반환되고,
--                   값1/값2/값3 모두와 일치하지 않으면 표현식4의 결과가 반환된다.

-- employees에서 소속부서번호가 10, 20, 30에 해당하는 사원들의
-- 사원아이디, 이름, 부서아이디, 급여, 보너스를 조회하기
-- (부서번호가 30인 부서에 소속된 사원들은 급여의 20%를 보너스로 지급하도록 조회하기)
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);

-- employees에서 소속부서번호가 10, 20, 30에 해당하는 사원들의
-- 사원아이디, 이름, 부서아이디, 급여, 보너스를 조회하기
-- (10번 부서는 급여의 10%, 20번 부서는 급여의 20% 30번 부서는 급여의 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);

-- employees 테이블에서 직종아이디에 CLERK가 포함되어 있는 일반 사원들인 경우
-- 그 부서의 평균급여의 50%를 보너스로 지급하고, 그외는 전체 평균급여의 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;

-- case 문
-- if문과 같은 역할을 수행한다.
-- decode는 equal 비교만 가능한데 비해서, case문은 >, >=, <, <= 등의 비교도 가능하다.
-- case
--      when 조건식1 then 표현식1
--      when 조건식2 then 표현식2
--      when 조건식3 then 표현식3
--                  else 표현식4
-- end

-- employees 테이블에서 급여를 20000달러 이상 받는 경우 급여의 10%를 보너스로 지급하고,
--                          10000달러 이상 받는 경우 급여의 30%를 보너스로 지급하고,
--                           5000달러 이상 받는 경우 급여의 50%를 보너스로 지급하고,
--                                          그 외는 급여의 75%를 보너스로 지급한다.
-- 사원아이디, 이름, 급여, 보너스를 조회하기(보너스는 10의 자리에서 반올림한다.)
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;

-- employees 테이블에 저장된 사원들의 급여를 기준으로 등급을 조회하려고 한다.
-- 급여가 20000이상인 경우 A , 10000 이상인 경우 B, 5000 이상인 경우 C,
-- 2500 이상인 경우 D, 그 외는 E 등급으로 판정한다.
-- 사원아이디, 이름, 급여, 급여등급을 조회하기
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;

-- employees 테이블에서 관리자(상사)의 사원아이디를 중복없이 조회하기
select DISTINCT manager_id
from employees;

-- employees 테이블에 등록된 사원들이 소속된 부서아이디를 중복없이 조회하기
-- (소속부서가 없는 사원은 제외시킨다.)
select distinct department_id
from employees
where department_id is not null;

-- employees 테이블에서 사원아이디, 이름, 이메일을 조회하기(이메일은 소문자로 표시)
select employee_id, first_name, lower(email)
from employees;

-- employees 테이블에서 이름이 A로 시작하는 사원의 아이디, 이름을 조회하기
select employee_id, first_name
from employees
where first_name like 'A%';

-- employees 테이블에서 모든 사원들의 아이디, 이름, 급여, 연간 총 수입을 조회하기
-- 연간 총수입은 급여*12 + (급여*12*commission_pct)다.
select employee_id, first_name, salary, (salary*12 + (salary*12*nvl(commission_pct, 0))) as 연간총수입
from employees;

-- employees 테이블에서 80번 부서에서 근무중인 사원들의 사원아이디, 이름, 급여, 세금을 조회하기
-- 14000이상 45%, 10000이상 42%, 7500이상 35%, 5000이상 25%, 2000이상 9%, 그외는 0%다.
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;

-- employees 테이블에서 50번 부서에서 근무중인 사원들의 급여를 15% 인상했을 때,
-- 사원아이디, 이름, 인상전 급여, 인상된 급여, 인상된 급여와 인상전 급여의 차이를 조회하기
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;

-- employees 테이블에서 사원의 이름이 'A', 'J' 또는 'M'으로 시작하는 사원의 아이디, 이름을 조회하기
select employee_id, first_name
from employees
where first_name like 'A%' or first_name like 'J%' or first_name like 'M%';

-- employees 테이블에서 근무를 시작한 날로부터 오늘까지 근무한 개월수를 계산해서 조회하기
-- 사원아이디, 이름, 입사일, 근무한 개월수(개월수는 정수로 반올림한다.)만 조회한다.
select employee_id, first_name, hire_date, round(MONTHS_BETWEEN(SYSDATE,hire_date), 0)
from employees;

-- employees 테이블에서 사원들의 급여를 *의 갯수로 표시하려고 한다.
-- *하나가 1000달러를 나타낸다고 했을 때, 사원의 아이디, 이름, 급여, *을 표시하기
-- 100 홍길동 3400  ***
-- 101 김유신 15000 ***************
-- 102 강감찬 7300  *******
select employee_id, first_name, salary, 
        -- lpad(replace(salary, salary, '*'), trunc(salary/1000) , '*') as 별
        lpad('*', trunc(salary/1000) , '*') asfrom employees;




0개의 댓글