DAY-22. Oracle 형변환,그룹함수,조건식

5 minute read

🔐 2022-03-31 - 2

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

🔔 형변환

1) 날짜나 숫자를 문자로 → to_char()

to_char(date/number, format)

  • 날짜 혹은 숫자를 특정한 형식의 문자형으로 반환해주는 함수
  • format → 커스터마이징 이라고 생각

1) 🚀 날짜형 → 문자형

🔎
select sysdate from dual; -- 22/03/31 출력
select to_char(sysdate,'YYYY--MM--DD') from dual; -- 2022-03-31 출력
select to_char(sysdate,'YYYY/MM/DD') from dual; -- 2022/03/31 출력

표기

년도 : YY / YYYY 로 표기

월 : MM ,

월 한글로 출력 : MONTH

일 : DD

요일 한글로 출력: DAY, DY

시 분 초 : HH:MI:SS

오전 오후 : AM, PM

🔎입사년원일을 to_char를 활용해 년월일 형식으로 출력
select emp_name, hire_date, to_char(hire_date,'YYYY"년" MM"월" DD"일"') from employee;

💡 한글을 형식에 추가하고 싶다면 한글의 양쪽을 ““로 묶기

// DAY : 요일 , 요일 까지 출력
select to_char(sysdate,'YY/MM/DD/DAY') from dual; // 22/03/31/목요일

// DY : x(요일)
select to_char(sysdate,'YY/MM/DD/DY') from dual; // 22/03/31/목

// MONTH : x월
select to_char(sysdate,'YY/MONTH/DD') from dual; // 22/3월 /31

// HH MI SS 시분초
select to_char(sysdate,'HH:MI:SS') from dual; // 12:09:08

// PM/AM : 오후 / 오전
select to_char(sysdate,'PMHH:MI:SS') from dual; // 오후12:10:16

// HH24 : 24시간을 기준으로 표기
select to_char(sysdate,'HH24:MI:SS') from dual; // 13:33:09

🔎 employee 테이블 사원명, 입사일 -> 1990/02/05(화)
select emp_name, to_char(hire_date, 'YYYY/MM/DD/(DY)')from employee;

1) 🚀숫자형 → 문자형

  • to_char(대상 숫자,’999,999,999’)
  • to_char(대상 숫자,’000,000,000’)
select to_char(123456789,'999,999,999') from dual; // 123,456,789
select to_char(123457789,'000,000,000') from dual; // 123,457,789

// 9를 사용했을 때 범위('999,999,999') 보다 적으면 결과값은 잘라서 나옴
select to_char(12345,'999,999,999')from dual; //      12,345

// 0을 사용했을 때 범위('000,000,000') 보다 적으면 결과값 + 빈칸을 0으로 채운다
select to_char(12345,'000,000,000')from dual; // 000,012,345

통화표시

L(local)에 맞는 원화 표시를 해줌 한국 -> ￦

select to_char(12345,'L999,999') from dual; --          ￦12,345

nvl(숫자/컬럼, 치환값)

만약 해당 숫자/컬럼이 null값이라면 치환값으로 변환해주는 함수

select bonus, nvl(bonus,0) from employee; //bonus가 null 이면 bonus는 0으로 치환해서 계산

🔎
employee -> 직원명, 직급코드, 연봉(원) 조회
단, 연봉은 ￦ 로 해주기

select * from employee;
select emp_name "직원명",
	   job_code "직급코드",
	   to_char((salary + salary*0.3) * 12,'L999,999,999') "연봉(원)"
	   //to_char(((salary + (salary * nvl(bonus,0)))*12),'L999,999,999') -- 위의 문장과 같은 표현
	from employee;

// L000,000,000 으로 하면 억단위가 아닌사람은 0으로 채워지게 되버림

1.png

2) 숫자나 문자를 데이트 타입으로의 변경 → to_date()

to_date(숫자/문자,형식) : 숫자형 혹은 문자형 데이터를 데이트(날짜) 타입으로 바꿔주는 함수

select to_date(20120505) from dual; // 12/05/05
select to_date(20120505,'YYYYMMDD') from dual; // 12/05/05
select to_date(20120505,'YYMMDD') from dual; // 12/05/05
select to_date('20120505','YYMMDD') from dual; // 12/05/05

// 시간을 입력 할때 시간 앞에 년월일을 입력하지 않으면 그 달의 첫째날을 반환 한다
select to_date('110808','HH:MI:SS') from dual; // 22/03/01
select to_char(to_date('110808','HH:MI:SS'),'YYYY/MM/DD HH:MI:SS') from dual;

// 년도값을 반환할때 2000/1900년대에 대한 값을 명확히 제시하지 않으면 2000년대를 default 값으로 인식
select to_char(to_date('890909','YY/MM/DD'),'YYYY/MM/DD') from dual; // 2089/09/09

❗ 년도값을 반환할때 2000/1900년대에 대한 값을 명확히 제시하지 않으면 2000년대를 default 값으로 인식

🔎 employee 테이블에서 2000년도 이후에 입사한 사원명, 사번, 입사일 조회

select emp_name, emp_id, hire_date from employee
    where hire_date > to_date('20000101','YY/MM/DD');

3) 문자열을 숫자형으로 변환 → to_number

  • 문자열을 숫자형으로 변환해주는 함수
  • 넘겨주는 문자형의 형식에 맞게 숫자형식도 동일하게 해야한다
select to_number('123,456,789','999,999,999') from dual; // 123456789
select to_number('123,456,789','999,999') from dual; // error
select to_number('123,123','9999,999') from dual; // error 숫자로 변환불가한 문자열을 눔겨줄 수 x

4) 묵시적 형변환

오라클에서는 어느 정도 자동적으로 자료형을 유추해서 형변환

select emp_name, salary from employee
    where salary = 8000000; // 숫자
select emp_name, salary from employee
    where salary = '8000000'; // 문자로 입력 -> 자동으로 형변환 -> 숫자로 출력

select 25 + '25' from dual; // 50

🔎
    60년 대생의 직원명과 년생, 보너스 율을 출력하시오
    그때 보너스 값이 null인 경우에는 0%로

    select * from employee;
    select  emp_name "직원명",
            substr(emp_no,1,2) "년생",
            nvl(bonus,0)*100 || '%' "보너스율" from employee
            // where emp_no like '6%';
        where to_number(substr(emp_no,1,2)) between 60 and 69;

🔔 그룹 함수

1) 합계 출력 → sum()

sum(숫자/컬럼) : 해당 컬럼/숫자 값의 총 합을 구해주는 함수

select sum(salary) from employee;

!!그룹함수를 사용할때에는 단일한 결과값이 나온다
select emp_name, sum(salary) from employee;
-- not a single-group group function 에러 발생

2) 평균 출력 → avg()

해당 컬럼 값들의 평균을 구해주는 함수

    송종기 사원과 선동일 사원의 평균 급여 구해보기

    select round(avg(salary),0)
		from employee
    where emp_name in ('송종기','선동일');


    전 직원의 보너스 평균 -> 소수점 셋째 자리수에서 반올림

    그룹함수 : 그룹함수 사용시 null 값이 존재하면 null값은 아예 제외 대상
    -> 즉 null값인 사람은 아예 제외하고 값을 나눠서 평균낸거임
    select round(avg(bonus),2) from employee; -- 0.22 null값 제외

    null 값도 포함하고 계산하려면 nvl 사용
    select round(avg(nvl(bonus,0)),2) from employee;  -- 0.08 null값 포함

❗ 그룹함수 : 그룹함수 사용시 null 값이 존재하면 null값은 아예 제외 대상

3) 컬럼 수 출력 → count()

해당 컬럼 -> 데이터의 개수를 반환해주는 함수

    select count(*) from employee; -- 23

-- bonus를 지급해야하는 사원의 수 카운트
    select count(*) from employee
        where bonus is not null; -- 9

-- 부서코드가 D5인 사원의 수 카운트
    select count(*) from employee
        where dept_code ='D5';

-- 부서코드가 D5인 사원의 수 카운트
    select count(*) from employee
        where dept_code ='D5';

-- 사원들이 속해 있는 부서의 수 카운트
    select count(distinct dept_code) from employee;
    select * from employee;

4) 최대값,최소값 반환 → max/min()

해당 그룹에서 최대값/최솟값을 반환해주는 함수

-- 최대값,최소값 반환 → max/min()
    select max(salary), min(salary) from employee;

🔔 조건식

1) decode

  • decode(대상표현식,조건1,결과1,조건2,결과2…,default)
  • 대상표현식이 조건1에 적합하면 결과1 반환, 조건2에 적업하면 결과2 반환… 모든조건에 부합x -> default
  • default의 값을 명시적으로 지정해주지 않으면 dafault = null
  • equal 비교만 가능 -> 크다 작다 이런거 안됌
-- 주민번호가 1이면 남자 2면 여자
    select emp_name "사원명", decode(substr(emp_no,8,1),'1','남','2','여') "성별"
		from employee;

-- '여'를 default 값으로
    select emp_name "사원명", decode(substr(emp_no,8,1),'1','남','여') "성별"
		from employee;

    🔎 사번 직원명과 퇴사여부를 출력하는 ENT_YN(퇴사여부)가 'Y'과 같다면
        퇴사일을 적어라 그게 아니면 재직중 출력**

    select emp_id "사번",
        emp_name "직원명",
        decode(ent_yn,'N','재직중',to_char(ent_date,'YY"년" MM"월" DD"일" "퇴사"')) "퇴사여부"
        from employee
    order by 퇴사여부, 사번 asc;

    💡 order by 앞쪽에 위치한 컬럼 1차 정리 후, 2번째 컬럼 순으로 정리

2) case

    case
        when 조건1 then 결과 1
        when 조건2 then 결과 2
        ...
        else(모든조건이 충족되지 않으면 else , decode의 default)
     end
  • 조건1이 성립하면 결과 1 출력 or 조건1이 성립 x 면 조건 2로 들어감
  • 모든 조건 성립 x → else문 실행
    select
        case
            when '가나다' = '하하하' then '같음'
        end
    from dual; -- null

    -> else (기본값)을 설정해주지 않으면 조건이 모두 맞지 않을 때 null 값 반환

❗ 오류 발생

    select
        case
            when '가나다' = '하하하' then '거짓'
            when 1 > 5 then 20
        else 10
    end
    from dual; -- 오류 발생

2.png

💡 오류 방지

    select
        case
            when '가나다' = '하하하' then 10
            when 1 > 5 then 20
        else 10
        end
    from dual;

    -- 결과값을 적어줄 때 동일한 자료형으로 사용 해야한다
    -> 숫자면 숫자만 받아야하고 문자면 문자만 받아야 한다

   🔎 사번 직원명과 퇴사여부를 출력하는 ENT_YN(퇴사여부)가 'Y'과 같다면
       퇴사일을 적어라 그게 아니면 재직중 출력**

    select emp_id "사번", emp_name "직원명",
        case
            when ent_yn = 'N' then '재직중'
            else to_char(ent_date,'YY"년" MM"월" DD"일" "퇴사"')
        end "퇴사여부"
    from employee
    order by 퇴사여부, 사번 asc;

Categories:

Updated:

Leave a comment

You may also enjoy

@RequiredArgsConstructor

less than 1 minute read

의존성주입의 종류는Constructor(생성자),Setter,Field 타입이 존재한다.