📌Today I Learned

오라클 수업

ex32

근태 상황을 확인할 수 있는 코드의 예시들

-- 근태 조회 > 9월 근태 기록 열람 > 결석한 날짜도 포함 + 공휴일 포함 > 빠진 날짜 메꾸기
-- 1. ANSI-SQL
-- 2. PL/SQL
-- 3. Java

set serveroutput on;

declare
    vdate date;
    vstate varchar2(30);
    vcnt number;
begin
    vdate := to_date('2023-09-01', 'yyyy-mm-dd');

    for i in 1..30 loop
        dbms_output.put_line(vdate);

        select count(*) into vcnt from tblDate where to_char(regdate, 'yyyy-mm-dd') = to_char(vdate, 'yyyy-mm-dd');

        if vcnt > 0 then
            select state into vstate from tblDate where to_char(regdate, 'yyyy-mm-dd') = to_char(vdate, 'yyyy-mm-dd');
            dbms_output.put_line(vstate);
        end if;

        vdate := vdate + 1;
    end loop;
end;


-- ANSI-SQL
-- 계층형 쿼리 사용

select * from tblComputer;

select 
    lpad(' ', level * 3) || name
from tblComputer
    start with seq = 1
        connect by pseq = prior seq;

-- 계층형 쿼리 = for문 효과 > 일련 번호 생성
select * from dual;

-- 공식같은 쿼리
select level -- level이 5보다 작으면 부모자식을 묶어라
from dual
    connect by level <= 5;


select sysdate + level
from dual
    connect by level <= 5;


-- *** 기억!! > date 자료형으로 원하는 기간의 데이터 생성하는 방법    
create or replace view vwDate
as
select 
    to_date('2023-09-01', 'yyyy-mm-dd') + level - 1 as regdate
from dual
    connect by level <= (to_date('2023-09-30', 'yyyy-mm-dd') - to_date('2023-09-01', 'yyyy-mm-dd') + 1);


select * from vwDate;

-- 매개변수 -> 뷰는 탈락임

select * from vwDate; -- 9월 한달 날짜
select * from tblDate; -- 9월 근태 기록

-- 빠진 날짜 있어서 안됨
select 
    * 
from vwDate v
    inner join tblDate t
        on v.regdate = t.regdate;

select 
    * 
from vwDate v
    left outer join tblDate t
        on v.regdate = t.regdate
            order by v.regdate;


-- 휴일 처리(토, 일)
select 
    v.regdate,
    case
        when to_char(v.regdate, 'd') in ('1') then '일요일'
        when to_char(v.regdate, 'd') in ('7') then '토요일'
        else t.state
    end as state
from vwDate v
    left outer join tblDate t
        on v.regdate = t.regdate
            order by v.regdate;

-- 공휴일 처리 (추가 테이블 필요)
create table tblHoliday(
    seq number primary key,
    regdate date not null,
    name varchar2(30) not null
);

insert into tblHoliday values(1, '2023-09-28', '추석');
insert into tblHoliday values(2, '2023-09-29', '추석');

select * from tblHoliday;


-- 평일 + 휴일 처리(토, 일) + 공휴일 + 결석
select 
    v.regdate,
    case
        when to_char(v.regdate, 'd') in ('1') then '일요일'
        when to_char(v.regdate, 'd') in ('7') then '토요일'
        when t.state is null and h.name is not null then h.name
        when t.state is null and h.name is null then '결석'
        else t.state
    end as state
from vwDate v
    left outer join tblDate t
        on v.regdate = t.regdate
                left outer join tblHoliday h
                    on v.regdate = h.regdate
                        order by v.regdate;

DB 프로젝트

각자 부분을 나누어 DDL을 작성하였고 이를 검토하여 문서로 작성하였다. 시퀀스 생성 구문이 빠져있어서 함께 추가하여 작성하였다.

개념 erd를 피터 첸 표기법으로 작성하다가 테이블 사이의 관계가 복잡해져 IE 표기법으로 변경하여 다시 작성하였다.

DDL과 더미데이터를 이용해 먼저 완성된 DML 구문들을 문서화하였다.