2021.06.04

[for each row]

1. for each row 생략

   • 문장 단위 트리거
   • 트리거 실행 횟수 1회
   • DML에 의해서 적용된 행의 갯수와 무관하게 단 1회만 실행
   • 목적: 행동 자체가 중요한 트리거(적용된 레코드(데이터)는 그다지 중요하지 않다.)

2. for each row 사용

   • 행 단위 트리거
   • 트리거 실행 횟수 N회
   • DML에 의해서 적용된 행의 갯수와 동일한 횟수만큼 실행
   • 목적: 적용되는 레코드의 특정 정보가 중요한 트리거
     • 가상 컬럼(Pseudo Column)
       a. :old -> 트리거가 일어나기 전 상태의 레코드
       b. :new -> 트리거가 일어난 후의 상태의 레코드

-- 트리거 발생 확인
select * from tblTodo;

create sequence seqTodo start with 21;  -- 시퀀스 21번부터 시작하도록 생성

insert into tblTodo values (seqTodo.nextVal, '프로시저 만들기', sysdate, null);
insert into tblTodo values (seqTodo.nextVal, '강아지 목욕시키기', sysdate, null);
insert into tblTodo values (seqTodo.nextVal, '장보기', sysdate, null);
insert into tblTodo values (seqTodo.nextVal, '프로젝트 정리하기', sysdate, null);
insert into tblTodo values (seqTodo.nextVal, '오라클 복습하기', sysdate, null);

commit;

create or replace trigger trgAddTodo
    after
    insert on tblTodo
    for each row
begin
    dbms_output.put_line('트리거 발생:' || to_char(sysdate, 'hh24:mi:ss'));
    dbms_output.put_line('새로 추가된 행의 번호: ' || :new.seq); 
    dbms_output.put_line('새로 추가된 행의 내용: ' || :new.title);
end;

set serveroutput on;

create or replace trigger trgEditTodo
    after
    update on tblTodo -- 하나의 테이블에 여러 트리거를 걸 수 있다. -> 무거워짐.
    for each row
begin
    dbms_output.put_line('수정 트리거 발생: ' || to_char(sysdate, 'hh24:mi:ss'));
    dbms_output.put_line('수정되기 전 제목: ' || :old.title);
    dbms_output.put_line('수정된 후 제목: ' || :new.title);
    dbms_output.put_line(' ');
end;

update tblTodo set title = title || '.' where seq = 1;
update tblTodo set title = title || '@'; -- 문장단위 트리거, 적용된 행의 갯수만큼 반복 호출

select * from tblTodo;

-- 프로시저 vs 트리거 -> 둘 다 만들어보면 어느 한쪽으로 치우쳐진 느낌이 있음..

-- 회원제 + 게시판 운영 + 글작성 + 포인트 누적
create table tblUser (
    id varchar2(50) primary key,        -- 아이디(PK)
    name varchar2(20) not null,         -- 이름
    point number default 1000 not null  -- 포인트
);

create table tblBoard (
    seq number primary key,                         -- 글번호(PK)
    subject varchar2(100) not null,                 -- 제목
    regdate date default sysdate not null,          -- 날짜
    id varchar2(50) not null references tblUser(id) -- 작성자(FK)
);

insert into tblUser values ('hong', '홍길동', default); -- 새로 추가된 레코드의 아이디
select * from tblUser;

insert into tblBoard values (1, '안녕하세요', default, 'hong'); -- 글작성
update tblUser set point = point + 100 where id = 'hong';

-- 글쓰기 감시 -> 포인트 증가
create or replace trigger trgAddBoard
    after -- 글쓰고 난 후 
    insert on tblBoard
    for each row -- 글쓴이의 id가 필요해서
begin
    
    update tblUser set point = point + 100 where id = :new.id;
    
end;

insert into tblBoard values (2, '안녕하세요', default, 'hong'); -- 포인트 자동으로 증가
select * from tblBoard;


-- 글삭제 -> 포인트 감소
create or replace trigger trgDeleteBoard
    before -- 지워지기 전에 확인
    delete on tblBoard
    for each row -- 글쓴이의 id가 필요해서
begin
    
    update tblUser set point = point - 50 where id = :old.id; -- 예전 레코드의 아이디
    
end;

delete from tblBoard where seq = 1;

-- 게시판
select * from tblBoard;

-- 댓글
create table tblComment (
    seq number primary key,                         -- 댓글번호(PK)
    subject varchar2(100) not null,                 -- 댓글제목
    regdate date default sysdate not null,          -- 날짜
    pseq number not null references tblBoard(seq)   -- 게시판 글번호(FK)
);

create sequence seqComment;

select * from tblBoard;

insert into tblComment values (seqComment.nextVal, '댓글입니다.', default, 2);

select * from tblComment;


--글 삭제: 먼저 댓글을 삭제하고~ 본 글을 삭제
-- 1. 딸린 댓글의 글 번호 select
-- 2. 1번 댓글들을 delete
-- 3. 게시판 글을 delete
delete from tblBoard where seq = 2;

create or replace trigger trgDeleteBoard
    before -- 글 삭제하기 전
    delete on tblBoard
    for each row
begin
    
    -- 댓글 삭제
    delete from tblComment where pseq = :old.seq; --tblBoard.seq 컬럼값, 삭제될 글번호
    
end;

---

인덱스, Index

• 색인(Index): 키워드 나열 -> 페이지 연결

• 검색(select)을 빠른 속도로 하기 위해서 사용하는 도구

• 특정 키워드(컬럼)만 모아놓은 별도의 테이블

• 하나의 테이블 > 데이터 많이 저장(백만단위 이상)

  1. 상단에 있는 레코드 접근 -> 접근 속도 빠름
  2. 하단에 있는 레코드 접근 -> 접근 속도 느림

• 인덱스 사용

  • select 작업이 테이블 대상으로 실행X
  • select 작업이 인덱스 대상으로 실행O

• 오라클은 테이블 생성 시 인덱스를 명시적으로 생성하지 않아도 자동으로 생성한다.
  -> PK, Unique 제약이 붙은 컬럼은 자동으로 인덱스가 생성된다. -> PK로 검색을 자주하기 때문에

  1. PK를 where절에 조건으로 select -> 속도 빠름
  2. 일반컬럼을 where절에 조건으로 select -> 속도 느림

-- num(PK), name(일반컬럼)
select num from tblInsa;    -- 속도 동일
select name from tblInsa;   -- 속도 동일

-- where절이 붙어야 색인
select * from tblInsa where num = 1001;         -- 속도 빠름(PK)
select * from tblInsa where name = '홍길동';     -- 속도 느림

-- 제약 사항 복사X
create table tblIndex
as
    select * from tblAddressBook;

insert into tblIndex
    select * from tblIndex; -- 8만건 넣기

select count(*) from tblIndex;

set timing on; -- F5 

-- 인덱스가 없는 상태에 검색
select distinct name from tblIndex where name = '김희예'; -- 00:00:03.397

-- 인덱스 생성 
create index idx_tblIndex_name -- inx(접두어)_테이블명_컬럼명
    on tblIndex(name);

-- 인덱스가 있는 상태에 검색 -> 빨라짐
select distinct name from tblIndex where name = '김희예'; -- 00:00:00.005

-- 인덱스 안걸려있는 컬럼
select distinct job from tblIndex where job = '게임기획자'; -- 00:00:03.746

-- fk에도 인덱스를 걸어두면 조인이 빨라진다.
select * from A inner join B on A.pk = B.fk;

인덱스 장점, 단점

• 검색 처리 속도를 향상 시킨다.
• 고비용

인덱스를 사용해야 경우

1. 테이블 레코드(행)가 많은 경우
2. 인덱스를 적용한 컬럼이 where절에 많이 사용되는 경우(★★★★★★★)
3. join에 사용되는 컬럼(on 부모.PK = 자식.FK)
4. 검색 결과가 원본 테이블 레코드의 2~4%에 해당하는 경우
5. 해당 컬럼이 null을 포함하는 경우(색인 테이블은 null이 제외)

인덱스를 사용하면 안좋은 경우

1. 테이블 레코드가 적은 경우
2. 검색 결과가 많은 경우 -> 처음부터 보는 거랑 비슷함.
3. 원본 테이블(색인 컬럼)에서 삽입, 수정, 삭제가 빈번한 경우(***)

-- tblInsa 성격
-- 정적 vs 동적인지 중요 -> 데이터 변화가 많은지 적은지 > 빈번한지 명확하게 알기 어려움.
select * from tblInsa where name = '검색어';

인덱스 종류

1. 비고유 인덱스
2. 고유 인덱스
3. 단일 인덱스
4. 복합 인덱스

-- 1. 비고유 인덱스
-- : 색인의 값이 중복이 가능하다.
create index idx_tblinsa_buseo on tblInsa(buseo);
    
-- 2. 고유 인덱스
-- : 색인의 값이 중복이 불가능하다.(PK, Unique에 건다.)
create index idx_tblinsa_num on tblInsa(num);

-- 3. 단일 인덱스
-- : 1개의 컬럼을 대상으로 인덱스 생성
create index idx_tblinsa_name on tblInsa(name);

-- 4. 복합 인덱스, 결합 인덱스
select * from tblInsa where buseo = '기획부'; -- 인덱스 동작 O
select * from tblInsa where buseo = '기획부' and jikwi = '부장'; -- 인덱스 동작 X
select * from tblInsa where buseo = '기획부' and name = '홍길동'; -- 인덱스 동작 X

create index idx_tblinsa_buseo_name on tblInsa(buseo, name); -- 합쳐진 인덱스(복합인덱스)를 만들어야한다.
select * from tblInsa where buseo = '기획부' and name = '홍길동'; -- 인덱스 동작 O

select * from tblInsa where substr(name, 1, 1) = '홍'; -- 인덱스 동작 X

create index idx_tblinsa_lastname on tblInsa(substr(name, 1, 1));
select * from tblInsa where substr(name, 1, 1) = '홍'; -- 인덱스 동작 O



-- 인덱스: 전체 직원을 대상으로 한달에 받는 실제 급여(basicpay+sudang)를 조건으로 하는 질의가 다수 사용되어서 
-- 검색 속도를 향상시키고자 생성했다.
create index idx_tblinsa_pay on tblInsa(basicpay + sudang);
select * from tblInsa where basicpay + sudang > 15000000;


create index idx_test on tblIndex(name, job);

select * from tblIndex where name = '김희예';
select distinct name from tblIndex where name = '김희예';
select distinct name from tblIndex where name = '김희예' and job = '캐디';
select distinct name from tblIndex where job = '캐디' and name = '김희예';