[Oracle] 오라클 함수

*** 함수(function)

- 질의문과 함께 사용하여 강력하게 처리할 수 있는 기능

- 함수의 종류

1) 단일행 함수 : 각각의 레코드에 기능을 적용시키는 함수

2) 그룹행 함수 : 특정 조건에 맞게 레코드를 묶어서 기능을 적용시키는 함수

 

 

*** 단일행 함수

1) 문자열을 처리하는 함수

- lower(문자열) : 영문자를 소문자로 변경

- upeer(문자열) : 영문자를 대문자로 변경

- initcap(문자열) : 영문자를 첫글자만 대문자, 나머지 소문자로 변경

- concat(문자열1, 문자열2) : 문자열 결합

- substr(문자열, 위치) : 문자열에서 지정된 위치 위의 모든 문자열을 반환

- substr(문자열, 위치1, 개수) : 문자열에서 지정된 위치1 ~ 개수 사이의 문자열을 반환 (인덱스 1부터 시작)

- length(문자열) : 문자열의 길이 반환

- instr(문자열, 부분문자열) : 문자열 내에서 부분 문자열의 위치

- replace(문자열, 찾는 문자열, 바꿀 문자열) : 문자열 치환

 

 

 

-- 이름을 소문자와 대문자로 조회

select lower(first_name), upper(first_name)

from employees;

 

 

-- 더미 테이블 (dual) 사용하기

select lower('ASIAN CUP 2024'), initcap('ASIAN CUP 2024')

from dual;

 

 

-- sunstr(문자열, 위치) : 인덱스가 1부터 시작

select upper(substr('The Soap', 5))

from dual;

 

 

-- sunstr(문자열, 위치, 개수)

select upper(substr('The Soap', 5, 2)) as result

from dual;

 

 

-- length() : 길이 반환

select length('The Soap')

from dual;

 

 

-- instr() : 위치값 찾기

select instr('The Soap', 'o')

from dual;

 

 

-- instr() : 위치값 찾기

select instr('오라클 마스터', '터')

from dual;

 

 

-- instr() : 위치값 찾기 (찾지 못할 경우 0 반환)

select instr('오라클 마스터', '우')

from dual;

 

 

** 참고용

-- 다른 문장과 함께 사용

select employee_id, first_name

from employees;

 

 

-- 함수를 where절에서 사용하기

-- 이름이 n으로 끝나는 직원의 사원번호와 이름 조회

select employee_id, first_name

from employees

where substr(first_name, -1, 1) = 'n';

 

 

-- 이름의 끝에서 2번째 2개가 'en'으로 끝나는 직원의 사원번호와 이름 조회

select employee_id, first_name

from employees

where substr(first_name, -2, 2) = 'en';

 

 

-- concat() : 2개의 문자열을 합치는 함수(||와 동일)

select concat('I have a ', 'dream')

from dual;

 

 

-- concat()을 테이블에 적용

select concat(employee_id, concat(' ', first_name)),

concat(employee_id, concat(':', first_name))

from employees;

 

 

-- replace(문자열, 찾을 문자열, 바꿀 문자열)

select first_name, hire_date

from employees;

 

 

-- 입사일 날짜의 '/'를 '-'로 변환

select first_name, replace(hire_date, '/', '-')

from employees;

 

 

-- 이름, 전화번호를 조회하되, 전화번호의 '.'을 ' '으로 치환하시오

select first_name, replace(phone_number, '.', ' ')

from employees;

 

 

 

2) 숫자와 관련된 함수

- abs(숫자) : 절대값

- ceil(숫자) : 올림, 가장 가까운 양의 방향의 정수를 반환

- floor(숫자) : 버림, 가장 가까운 으므이 방향의 정수를 반환

- round(숫자, 자릿수) : 지정한 위치에서 반올림

- trunc(숫자, 자릿수) : 지정한 위치에서 절삭

- mod(숫자1, 숫자2) : 1번째 숫자를 2번째 숫자로 나눴을 때 나머지

- power(숫자1, 숫자2) : 1번째 숫자를 2번째 숫자만큼 곱한 값 (n승)

- sign(숫자) : 숫자가 음수면 -1, 0이면 0, 양수이면 1을 반환

 

 

-- abs()

select abs(-15.3), abs(15.3) from dual;

 

-- ceil()

select ceil(15.3), ceil(-15.3) from dual; /* 16, -15 */

 

-- floor()

select floor(15.3), floor(-15.3) from dual; /* 15, -16 */

 

 

-- round(m, n) : 방향이랑 상관없음

select round(15.195, 1), round(15.195, 2),

round(15.195, 0), round(15.195, -1)

from dual; /* 15.2, 15.2, 15, 20 */

 

 

select round(-15.195, 1), round(-15.195, 2),

round(-15.195, 0), round(-15.195, -1)

from dual; /* -15.2, -15.2, -15, -20 */

 

 

-- trunc(m, n)

select trunc(-15.195, 1), trunc(-15.195, 2),

trunc(-15.195, 0), trunc(-15.195, -1)

from dual; /* -15.1, -15.19, -15, -10 */

 

 

-- mod(m, n)

select mod(10, 3), power(10, 3),

sign(10), sign(-10)

from dual; /* 1, 0, 1, -1 */

 

 

 

*** 날짜 함수

- 날짜 관련 시스템 값: sysdate(시스템 현재 날짜),

                                    systimestamp(시스템 현재 날짜와 시간)

- 날짜 타입은 기본 산술 연산자를 이용한 연산이 가능: + - (일 단위)

 

- add_months(날짜, 정수값) : 날짜에 정수달을 더했을 때 변경되는 날짜를 반환

- months_between(날짜1, 날짜2) : 두 날짜 사이에 몇 개월의 차이가 있는지 반환

- last_day(날짜) : 특정 달의 마지막 날이 몇일인지 반환

- next_day(날짜, '요일') : 날짜를 가준으로 그 다음 요일이 몇일인지 반환

 

 

-- 일 단위

select sysdate, systimestamp from dual;

select sysdate + 1 as "내일", sysdate - 1 as "어제" from dual;

 

 

-- 월 단위

-- 10달 후 오늘

select add_months(sysdate, 10) from dual;

 

-- 10달 전 오늘

select add_months(sysdate, -10) from dual;

 

 

-- to_date는 연산 가능, 뒤에는 일반 문자열

select to_date('23/01/01'), '23/01/01' from dual;

 

 

-- 시스템 현재 날짜에서 특정 날짜 빼기 (몇일이 지났는지)

select ceil(sysdate - to_date('23/01/01')) from dual;

 

 

-- 이번달의 마지막 날

select last_day(sysdate) from dual;

 

 

-- 태어난 해 그 달 마지막 날

select last_day(to_date('00/12/05')) from dual;

 

 

 

-- [문제-1] 입사한 달의 근무일수를 계산하고, 이름, 부서번호, 근무일수를 각각 출력

select first_name, department_id, hire_date,

last_day(hire_date) - hire_date as "working_day"

from employees;

 

 

-- [문제-2] 80번 부서에 근무하는 직원들의 시간당 임금을 계산하시오. (이름, 급여, 시간당 임금)

-- 소수점 이하 2자리 절삭하고 시간당 임금의 내림차순 정렬

-- 주 5일 근무, 하루 8시간 x 4주 = 160시간

select first_name, salary, trunc(salary / 160, 2) as "시간당 임금"

from employees

where department_id = 80

order by 3 desc;

 

 

-- [문제-3] job_history 테이블의 정보를 이용해 102번 직원이 특정 부서에서 몇개월을 근무했는지 계산하시오

select employee_id, ceil((end_date - start_date) / 30) as "근무_개월수", department_id

from job_history

where employee_id = 102;

 

 

-- [문제-4] 오늘까지 몇 일 생존해 있는지

select ceil(sysdate - to_date('00/12/05')) from dual;

 

 

-- [문제-5] 오늘부터 수료일(24/05/08)까지 몇 일 남았는지

select ceil(to_date('24/05/08') - sysdate) from dual;

 

 

 

*** 변환 함수

to_char(날짜, 포맷) : 날짜를 특정 포맷에 맞춰 문자열로 변환

to_date(문자열) : 날짜 형태로 된 문자열을 날짜 타입으로 변환

to_number(문자열) : 숫자 형태로 된 문자열을 숫자 타입으로 변환

 

 

-- 문자열은 왼쪽 정렬, 숫자는 오른쪽 정렬

select '123.5', to_number('123.5') from dual;

 

 

** to_char()를 이용한 날짜 포맷

select sysdate,

to_char(sysdate, 'YYYY-MM-DD'),

to_char(sysdate, 'YYYY-MM-DD HH:mm') as "현재 시간 1",

to_char(sysdate, 'YYYY-Mon-DD HH:mm') as "현재 시간 2", -- Mon은 2월

to_char(sysdate, 'YYYY-Mon-DDD HH:mm') as "현재 시간 3" --1월1일부터 날짜 수

from dual;

 

 

** to_char()를 이용한 숫자 포맷

select

to_char(1234560, '999,999,999'),

to_char(1234560.34, '999,999,999.00'), -- 소수점 표시

to_char(1234560.34, '$999,999,999.00'), -- 달러 표시

to_char(1234560.34, 'L999,999,999.00') -- 원 표시

from dual;

 

 

-- [문제] 이름, 급여(통화 기호, 천자리 콤마, 소수점 3자리 표현), 입사일(4-2-2 (요일))을 출력

select first_name, 
       to_char(salary, '$999,999,999.000') as salary, 
       to_char(hire_date, 'YYYY-MM-DD (DY)')

from employees;

 

 

 

*** null 관련 함수

- nvl(컬럼, 데이터) : 특정 컬럼의 데이터가 null이면 null 대신 데이터 값으로 출력

- nvl2(컬럼, 데이터1, 데이터2) : 특정 컬럼의 데이터가 null이면 데이터2 아니면 데이터1 출력

 

 

select first_name, nvl(commission_pct, 0)

from employees;

 

 

select first_name, salary, nvl2(commission_pct, salary * commission_pct, 0) as "커미션"

from employees;

 

 

-- [문제] 이름, 부서번호를 출력, 부서번호가 없으면 0으로 출력

select first_name, nvl(department_id, 0)

from employees

order by first_name;

 

 

-- [문제] 이름, 부서번호를 출력, 부서번호가 없으면 "임시"라고 출력

select first_name, nvl(to_char(department_id), '임시') as "department"

from employees;

 

 

-- [문제] 사원번호, 이름, 팀장번호를 출력, 팀장번호가 없으면 "회장"이라고 출력

select employee_id, first_name, nvl(to_char(manager_id), '회장') as "manager"

from employees

order by employee_id;

 

 

*** 기타 함수

1) DECODE

- 다른 언어의 if문과 유사한 개념의 함수

 

DECODE(exp1, cond1, result1,

cond2, result2,

cond3, result3,

default

)

 

2) CASE ~ WHEN

자바 언어의 switch~case와 유사한 개념의 함수

 

CASE WHEN 조건1 THEN 겂1

WHEN 조건1 THEN 겂1

WHEN 조건1 THEN 겂1

...

ELSE 값n

END

 

---------------------------------------------------------------------

 

 

select

first_name,

department_id,

decode(department_id, 10, 'Administration',

20, 'Marketing',

30, 'Purchasing',

40, 'Human Resources',

50, 'Shipping'

) as DEPARTMENT_NAME

from employees

where department_id in (10, 20, 30, 40, 50);

 

 

-- CASE문으로 변경

select

first_name,

department_id,

case when department_id = 10 then 'Administration'

when department_id = 20 then 'Marketing'

when department_id = 30 then 'Purchasing'

when department_id = 40 then 'Human Resources'

when department_id = 50 then 'Shipping'

else 'ETC'

end as DEPARTMENT_NAME

from employees

where department_id in (10, 20, 30, 40, 50);

 

 

 

*** 그룹핑 함수

- SUM(컬럼명), AVG(컬럼명), COUNT(컬럼명), MIN(컬럼명), MAX(컬럼명)

 

-- 전체 직원의 급여 합계

select sum(salary)

from employees;

 

 

-- 매니저가 있는 직원의 수 (null 제외)

select count(manager_id)

from employees;

 

 

-- 모든 레코드 개수 (null 포함)

select count(*)

from employees;

 

 

-- 커미션을 받는 직원의 수 (null겂은 연산 불가)

select count(commission_pct), sum(commission_pct)

from employees;

 

 

-- 최소 급여와 최대 급여 조회

select min(salary), max(salary)

from employees;

 

 

-- 부서별 최대, 최소 급여 조회

select department_id, min(salary), max(salary)

from employees

group by department_id;

 

 

-- 부서별 최대, 최소 급여와 부서의 MANAGER_ID를 조회

-- 그룹핑과 관련없는 컬럼이 select절에 있으면 오류

select department_id, min(salary), max(salary), manager_id

from employees

group by department_id, manager_id;

 

 

 

*** 그룹핑에 조건을 걸고 싶을 때 having절 사용

 

-- job_id별 급여의 평균과 job_id 조회

select job_id, avg(salary)

from employees

group by job_id

order by 2;

 

 

-- job_id별 급여의 평균과 job_id 조회 (단, 급여의 평균이 5000 이상만 조회)

select job_id, avg(salary)

from employees

group by job_id

having avg(salary) >= 5000

order by 2;

 

 

-- 부서별 직원의 수를 조회 (단, 부서의 아이디가 null값인 경우 제외)

select department_id, count(*)

from employees

group by department_id

having department_id is not null

order by 1;

 

 

-- [문제] 부서의 인원수가 없거나 1명인 부서를 제외하여 조회

-- 부서의 아이디가 null값인 경우도 제외

select department_id, count(*)

from employees

group by department_id

having count(*) != 1 and department_id is not null

order by 1;

 

 

-- [문제] 입사년도별 입사 인원을 조회

-- 년도 4자리, 입사인원 출력

select to_char(hire_date, 'YYYY') as "입사 년도", count(*) as "입사 인원"

from employees

group by to_char(hire_date, 'YYYY')

order by 1;