Do it! 오라클로 배우는 DB 입문 : 06 데이터 처리와 가공을 위한 오라클 함수 + 실습 문제

 

 

06-1 오라클 함수

함수란?

수학에서의 x, y 변수가 존재하고 연산에 따라 변할 때 y를 함수라 한다.

오라클 함수의 종류

오라클에서 제공하고 있는 내장 함수와 사용자가 필요에 의해 정의한 사용자 정의 함수로 나뉜다.

 

내장 함수의 종류

입력 방식에 따라 데이터 처리에 사용하는 행이 나뉜다

데이터가 한 행씩 입력되고, 한 행당 결과가 나오는 함수를 단일행 함수

여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수라고 한다.

 

06-2 문자 데이터를 가공하는 문자 함수

대소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수

INITCAP(문자열) : 초기화한다의 initialize.  첫글자만 대문자, 나머지 소문자로 변환

SELECT ename, UPPER(ename), LOWER(ename), INITCAP(ename) FROM emp;

-- 사원 이름이 scott
SELECT * FROM emp WHERE UPPER(ename) = UPPER('scott');
-- 사원 이름에 scott 단어 포함한 데이터
SELECT * FROM emp WHERE UPPER(ename) LIKE UPPER('%scott%');

 

 

문자열 길이를 구하는 LENGTH 함수

특정 문자열의 길이를 구할 때 LENGTH()

-- WHERE절에서 LENGTH()를 사용하여 행 선별 가능.
select ename, length(ename) from emp where length(ename) >= 5;

-- lengthb는 바이트 수를 반환
select length('한글'), lengthb('한글') from dual;
select * from dual;
select sysdate from dual;

 

 

문자열 일부를 추출하는 SUBSTR 함수

문자열의 일부만 추출할 때 사용한다. (주민등록번호, 전화번호 등)

select job, substr(job, 0, 2), substr(job,3,2), substr(job,5) from emp;

 

SUBSTR 함수와 다른 함수 함께 사용하기

LENGTH와 함께 사용

select job, substr(job, -length(job),2), substr(job,-3) from emp;

 

문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수

select instr('hello, oracle!', 'l') as instr_1,
        instr('hello, oracle!', 'l', 5) as instr_2,
        instr('hello, oracle!', 'l' ,2, 2) as instr_3
from dual;

select instr('abc','d') from dual;

 

 

특정 문자를 다른 문자로 바꾸는 REPLACE 함수

전화번호에서 -를 빈 문자열로 . . .

지정하지 않으면 삭제된다.

SELECT '010-1234-5678' AS REPLACE_BEFORE,
    REPLACE('010-1234-5678','-',' ') AS R_1,
    REPLACE('010-1234-5678','-') AS R_2,
    REPLACE(REPLACE('010-1234-5678','010',''),'-','') AS R_3
FROM DUAL;

 

데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

Padding은 빈 공간을 채우는... 쓸 일 별로 없음

특정 문자로 자릿수 채워서 출력하기. 지정 안하면 공백(' ')

SELECT 'Oracle', 
    LPAD('Oracle', 10, '#') AS LPAD_l,
    RPAD('Oracle', 10, '*') AS RPAD_1,
    LPAD('Oracle', 10) AS LPAD_2, 
    RPAD('Oracle', 10) AS RPAD_2 
FROM DUAL;

 

 

두 문자열 데이터를 합치는 CONCAT 함수

두 데이터를 연결해준다. 

SELECT CONCAT(empno, ename) AS C1,
        CONCAT(empno, concat(' : ',ename)) AS C2,
        empno||' : '||ename AS C3 --오라클만 가능
from emp where ename='SCOTT';

 

특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수

TRIM은 가운데 공백을 제거하지 않는다.

TRIM（［삭제 옵션（선택）］ ［삭제할 문자（선택）］ FROM ［원본 문자열 데이터（필수）］）

select '          te st      ',
    ltrim('          te st      '),
    rtrim('          te st      '),
    trim('          te st      '),
    replace('          te st      ', ' ', '')
from dual;

 

 

06-3 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

특정 위치에서 반올림하는 ROUND 함수

특정 숫자를 반올림하되 반올림할 위치를 지정할 수 있다.

반올림할 위치를 지정하지 않으면 소수점 첫째 자리 에서 반올림한 결과가 반환됨.

음수값은 자연수쪽으로 반올림하게 된다.

select
    round(1234.5678) as r0,
    round(1234.5678,0) as r1,
    round(1234.5678,1) as r2,
    round(1234.5678,2) as r3,
    round(1234.5678,-1) as r4,
    round(1234.5678,-2) as r5
from dual;

 

특정 위치에서 버리는 TRUNC 함수

지정된 자리에서 숫자를 버림처리하는 함수이다.

지정하지 안흥면 소수점 첫째 자리에서 버림 처리 된다.

select
    TRUNC(1234.12346, 4) as r0,
    TRUNC(1234.78965, 2) as r1,
    TRUNC(1234.487777,-1) as r2
from dual;

 

지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

CEIL 함수와 FLOOR 함수는 각각 입 력 된 숫자와 가까운 큰 정수, 작은 정수를 반환하는 함수

select ceil(3.14), floor(3.14), ceil(-3.14), floor(-3.14) from dual;

 

숫자를 나눈 나머지 값을 구하는 MOD 함수

 

06-4 날짜 데이터를 다루는 날짜 함수

 

몇 개월 이후 날짜를 구하는 ADD.MONTHS 함수

-- 오늘 일자, 내일, 엊그제, 3개월 후
select sysdate, sysdate+1, sysdate-2, add_months(sysdate,3) from dual;

-- 입사 10주년
SELECT EMPNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 120) AS WORK10YEAR FROM EMP;

 

두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수

-- 오늘과 해당 날짜 개월 수 차이
select trunc(months_between('2024-09-12', sysdate)) from dual;

-- 근속 년수
select months_between(sysdate, hiredate)/12 from emp;

 

돌아오는 요일，달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

NEXT_DAY 함수는 날짜 데이터와 요일 문자열을 입력한다.

next_day는 입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환한다.

last_day는 속한 달의 마지막 날짜를 반환한다.

select sysdate, next_day(sysdate,'월요일'),last_day(sysdate) from dual;

\

 

날짜의 반올림，버림을 하는 ROUND, TRUNC 함수

select sysdate, substr(sysdate, 1, 10) from dual;

 

 

06-5 자료형을 변환하는 형 변환 함수

-- 자동형변환이 일어난다.
select empno + '1' from emp;
select * from emp where empno='7369';

숫자로 인식 가능한 문자데이터가 자동으로 숫자로 바뀐 후 연산이 수행 됨

 

날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수

주로 날짜데이터 -> 문자데이터로 변환하는데 많이 사용 된다.

-- 강제형변환 (명시적)
-- TO_XXX
select to_char(111), 111 as abcdefg from dual;
select sysdate, to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
-- 한글을 나오게 하려면 "로 묶어야 함
select to_char(sysdate,'YYYY"년" MM"월" DD"일"') from dual;

 

 

문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

쉼표(,)가 들어간 숫자의 경우 강제로 인식시켜 준다.

select to_number(replace('1,000.5',',','')) from dual;
SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999') FROM DUAL;
select to_number(to_char(empno)) from emp;
select '100'-'50' from dual;

 

문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

-- 날짜 <-> 문자 변환
-- to_char() : 날짜 -> 문자열로 포매팅해서 출력
select to_char(sysdate, 'YYYY-MM-DD') from dual;
-- to_date() : 문자열 포매팅된 -> 날짜
select to_date('2024-01-01', 'YYYY-MM-DD') from dual;

 

랭킹함수

-- 랭킹 (순위) 함수
select 
        sal, 
        row_number() over(order by sal desc) rank1, --동순위없음
        rank() over (order by sal desc) rank2, --공동순위 있음 (중복 띄우고)
        dense_rank() over(order by sal desc) rank3 --공동순위 있음 (겹쳐짐)
from emp;

 

 

06-6 NULL 처리 함수

NULL이 들어있을 때 다른 값으로 대체해주어야 함.

 

NVL 함수의 기본 사용법

 

NVL2 함수의 기본 사용법

-- nvl
select sal+comm, sal+nvl(comm,0), nvl(sal+comm,0) from emp;
-- nvl2
select comm, nvl2(comm, 'O', 'X'), nvl2(comm, comm, 0) from emp;
-- 집계함수
select count(comm),count(empno),avg(comm),sum(comm)/4,avg(nvl(comm,0)) from emp;

 

 

06-7 상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문

NVL, NVL2 함수는 데이터가 NULL인 경우에 어떤 데이터를 반환할지 정하는 함수이다.

특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할지 정할 때는 DECODE 함수 또는 CASE문을 사용한다.

DECODE 함수

조건이 일치하면 일치하는 경우, 아니면 그 다음값으로 간다.

--decode
-- 직책이 president면 대표, 아니면 직원
select job, decode(job, 'PRESIDENT', '대표', '직원') from emp;
-- 직책이 president면 대표, salesman이면 영업, 그렇지 않으면 직원
select job, decode(
                job, 'PRESIDENT', '대표', 
                decode(job, 'SALESMAN', '영업', 
                    '직원')) from emp;
-- 위와 같은 코드
select job,
    decode(
        job,
            'PRESIDENT', '대표',
            'SALESMAN', '영업',
            '직원')
from emp;

 

 

CASE문

많이 안씀
CASE문은 DECODE 함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다.

기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE 함수와 달리,

CASE문은 각 조건에 사용하는 데이터가 서로 상관없어도 된다.

또 기준 데이터 값이 같은(=) 데이터 외에 다양한 조건을 사용할수 있다.

select
    case job
        when 'MANAGER' then sal*1.1
        when 'SALESMAN' then sal*1.15
        when 'ANALYST' then sal
        else sal*1.03
    end as comm_text
from emp;

select 
    case
        when comm is null then '해당사항 없음'
        when comm = 0 then '수당없음'
        when comm > 0 then '수당 : ' || comm --숫자만 있으면 에러나서 형변한 위해 문자열 붙여줌
    end as comm_text    
from emp;

 

 

 

---

확인 문제

-- Q1
select empno, substr(empno,0,2)||'**' as MASKING_EMPNO, 
        ename, substr(ename,0,1)|| RPAD('*', LENGTH(ENAME) - 1, '*') AS MASKING_ENAME
from emp
where length(ename) = 5 ;

 

 

-- Q2
select empno, ename, sal, 
        trunc(sal/21.5,2) as DAY_PAY, 
        round(sal/21.5/8, 1) as TIME_PAY
from emp;

 

 

 

-- Q3
select empno, 
    ename, 
    to_char(hiredate,'YYYY/MM/DD') as HIREDATE,
    next_day(add_months(hiredate,3),'월요일') as R_JOB,
    nvl(to_char(comm), 'N/A') as COMM
from emp;

 

 

-- Q4
select empno, 
        ename, 
        nvl(to_char(mgr), ' ') as MGR,
        case
            when mgr is null then to_char('0000')
            when substr(mgr,0,2)='75' then to_char('5555')
            when substr(mgr,0,2)='76' then to_char('6666')
            when substr(mgr,0,2)='77' then to_char('7777')
            when substr(mgr,0,2)='78' then to_char('8888')
            else to_char(mgr)
            end as CHG_MGR
from emp;