220711 TIL

Yeoni·2022년 7월 11일

국비교육TIL

목록 보기

29/41

국비교육 29일차 Oracle: Set Operator(Set 연산자, 집합연산자), Pseudo Column, DML, DDL, DCL, TCL, DQL

1. Set Operator(Set 연산자, 집합연산자)

1) 종류

(1) UNION
- UNION은 첫번째 컬럼별로 오름차순 정렬이 되어진다.
- 중복행은 1개만 나온다.
(2) UNION ALL
- UNION ALL을 하면 정렬 없이 그냥 순서대로 행을 붙여서 보여준다.
- UNION ALL은 중복 제거 없이 그대로 보여준다.
(3) INTERSECT
- INTERSECT 교집합
- 테이블과 테이블 사이에 공통되는 행을 찾아준다.
(4) MINUS
- MINUS 차집합
- 테이블과 테이블 사이에서 한쪽에는 존재하고 다른 한쪽에는 존재하지 않는 행을 찾아준다.

2) UNION과 JOIN의 차이

JOIN은 테이블(뷰)과 테이블(뷰)을 합쳐서 보여주는 것으로서, 이것은 행(row)과 행(row)을 합친 결과를 보여주는 것이다.

3) UNION

사용 예시

WITH V as 
(
    select *
    from tbl_panmae_202205
    UNION 
    select *
    from tbl_panmae_202206
    UNION
    select *
    from tbl_panmae
    -- 백분률을 구할 때 union all을 사용하여 모든 행을 다 더하는 방식으로 했었으나,
    -- union all 을 안써도 되는 이유는 판매데이터 별로 다 다른 시간을 가지고 있기에 다른 행으로 인식됨
    -- 따라서 아래의 백분률에서 union all을 사용하지 않고 V의 panmaesu로 해도 된다. 
) 
select decode( grouping(jepumname), 0, jepumname, '전체') AS 제품명
     , decode( grouping(to_char(panmaedate, 'yyyy-mm')), 0, to_char(panmaedate, 'yyyy-mm'), ' ') AS 판매년월
     , sum(panmaesu) AS 판매량의합계
     , to_char(
           ROUND( sum(panmaesu) /  -- 분자
                   (select sum(panmaesu) from V ) * 100  -- 퍼센트 
                , 1) -- round
               , '999.0')AS "백분율(%)"
from V
group by rollup(jepumname,to_char(panmaedate, 'yyyy-mm'))  
-- ◎오늘의에러 grouping sets로 여러번 그룹지어 준 다음, grouping 한 컬럼명을 그대로 써주어야한다.

2. Pseudo(의사,유사,모조) Column

Pseudo(의사,유사,모조) Column 은 rowid와 rownum 이 있다.

1) ROWID

rowid는 오라클이 내부적으로 사용하기 위해 만든 id 값으로서 행에 대한 id 값인데 오라클 전체내에서 공유한 값을 가진다.
rowid는 'AAASLXAAHAAAAHeAAA', '
AAASLXAAHAAAAHeAAB', 'AAASLXAAHAAAAHeAAC' 이러한 식으로 알파벳 순으로 흘러가기 때문에 등록된 순서에 따라서 데이터 삭제도 가능
rowid만 들고와서 특정 행 삭제 가능

2) ROWNUM

게시판 등 웹에서 자주 사용
ROWNUM은 insert되어진 순서대로 나온다
따라서 게시물을 최신순으로 보여주고, ROWNUM은 오름차순으로 보여주려면 VIEW를 사용하여야 한다.

select rownum -- 2. 그 다음에 rownum으로 번호 부여
     , boardno
     , subject
     , userid
     , REGISTERDAY
from
(
    select boardno 
         , subject 
         , userid
         , to_char(registerday, 'yyyy-mm-dd hh24:mi:ss') AS REGISTERDAY
    from tbl_board
    order by boardno desc -- 1. 내림차순으로 보이게 설정부터 한다.
)V; -- V가 내림차순으로 출력 받있고, 다시 밖에서 rownum을 행번호를 받아왔기 때문에 오름차순으로 rownum 출력

ROWNUM을 이용하여 페이지 구분하기

-- 1페이지에 RNO : 1 ~ 2 boardno : 5 ~ 4(최신순)만 나오게 하기
select T.boardno, T.subject, T.userid, T.REGISTERDAY
from
(
    select rownum AS RNO, boardno, subject, userid, REGISTERDAY
    from
    (
        select boardno 
             , subject 
             , userid
             , to_char(registerday, 'yyyy-mm-dd hh24:mi:ss') AS REGISTERDAY
        from tbl_board
        order by boardno desc
    )V
) T --RNO는 alias로 처리된 상태라 바로 where 절에 사용할 수 없어서 다시 인라인뷰
where RNO between 1 and 2;

페이징처리의 공식

where RNO between (조회하고자 하는 페이지 번호 * 한페이지당 보여줄 행의 개수) - (한페이지당 보여줄 행의 개수 -1) 
                  and (조회하고자 하는 페이지 번호 * 한페이지당 보여줄 행의 개수);
                  
where RNO between (1 * 2) - (2 -1) 
              and (1 * 2);

row_number()를 이용하여 페이지 구분하기

select boardno, subject, userid, registerday
from
(
    select row_number() over(order by boardno desc) AS RNO-- 글 번호의 내림차순으로 번호를 부여한다.
         , boardno 
         , subject 
         , userid
         , to_char(registerday, 'yyyy-mm-dd hh24:mi:ss') AS registerday
    from tbl_board
)V
where RNO between 1 and 2;
-- row_number() over(order by boardno desc)도 where 절에 바로 사용 불가능
-- 그러므로 이것 또한 inline view를 사용해야 한다.

3. DML, DDL, DCL, TCL, DQL

DML(Data Manuplation Language) : 데이터 조작어 ==> insert, update, delete, merge
: 수동 commit 이므로 rollback 이 가능하다.

DDL(Data Definition Language) : 데이터 정의어 ==> create, drop, alter, truncate
: 자동 commit( Auto commit) 이므로 rollback 이 불가하다.

DCL(Data Control Language) : 데이터 제어어 ==> grant, revoke
: 자동 commit( Auto commit) 이므로 rollback 이 불가하다.

TCL(Transaction Control Language) : 트랜잭션 제어어 ==> commit, rollback

DQL(Data Query Language) : 데이터 질의어 ==> select

1) 데이터 조작어(DML == Data Manuplation Language)

DML 문은 기본적으로 수동 commit 이다.
즉, DML 문을 수행한 다음에는 바로 디스크(파일)에 적용되지 않고 commit 해야만 적용된다.
그래서 DML 문을 수행한 다음에 디스크(파일)에 적용치 않고자 한다라면 rollback 하면 된다.

(1) insert --> 데이터 입력

insert into 테이블명(컬럼명1,컬럼명2,...) values(값1,값2,...);

Unconditional insert all : 조건이 없는 insert

insert all 
into 테이블명1(컬럼명1, 컬럼명2, ....)
values(값1, 값2, .....)
into 테이블명2(컬럼명3, 컬럼명4, ....)
values(값3, 값4, .....)
SUB Query문;

Conditional insert all : 조건이 있는 insert
조건(where절)에 일치하는 행들만 특정 테이블로 찾아가서 insert 하도록 하는 것이다.

insert all
when 조건절 then
into 테이블명1(컬럼명1, 컬럼명2, ....)
values(값1, 값2, .....)
when 조건절 then
into 테이블명2(컬럼명3, 컬럼명4, ....)
values(값3, 값4, .....)
SUB Query문;

(2) update --> 데이터 수정

update employees set first_name = '여진', last_name = '손'
where employee_id = 100;

(3) delete --> 데이터 삭제

delete from 테이블명
where 조건절;

(4) merge --> 데이터 병합

어떤 2개 이상의 테이블에 존재하는 데이터를 다른 테이블 한곳으로 모으는 것(병합)이다.
merge를 해주면 기존의 데이터는 업데이트가 되고, 없는 데이터는 추가가 된다.

데이터베이스 링크 생성

create database link teacherServer
connect to hr identified by a
using 'TEACHER';
-- Database link TEACHERSERVER이(가) 생성되었습니다.

이때 hr과 암호 a는 연결하고자 하는 원격지 오라클서버의계정명과 암호이다.
using 다음에 나오는 'TEACHER'는 tnsnames.ora 파일에서 추가해준 'Net Service Name(네트서비스명)' 이다.

merge into tbl_reservation_merge@teacherserver R  -- remote 원격
using tbl_reservation_shonyeojin L -- local 
on (L.rsvno = R.rsvno)
when matched then 
     update set R.memberid = L.memberid 
              , R.ticketcnt = L.ticketcnt
     -- 만일 있으면 로컬에 있는 것을 원격지에 업데이트
when not matched then 
    insert(rsvno, memberid, ticketcnt) values(L.rsvno, L.memberid, L.ticketcnt)
           -- 이 컬럼은 원격지에 있다.            -- 이 컬럼은 로컬에 있다. 
    -- 로컬에 있는 것을 원격지에 넣어준다.
    -- 로컬에 일치하는 데이터가 없어서 새로 넣어주는 경우. 
-- 1 행 이(가) 병합되었습니다.

commit;