뷰(View)

실제 물리적으로는 존재하지 않는 가상의 논리적인 테이블을 말한다.

• 자주 사용하는 복잡한 sql을 view를 만들어 둠으로써 편리하게 사용할 수 있다.
• 사용자 별로 접근 권한을 두고자 할 때에 view를 이용한다.

사용법

create view 뷰이름 as select ~

• 코드

  create view vw_book 
  as select * from book where bookName like '%축구%';
  select * from vw_book;
  
  create view vw_customer
  as select * from customer where address like '%대한민국%';
  
  select * from vw_customer;
  
  create or replace view vw_worker
  as 
  select w.workerId, w.name, d.id, w.email 
  from worker w
  join department d on d.id = w.id;
  
  // 권한 부여
  grant select on c##madang.vw_worker to c##kim;
  // 권한 삭제
  revoke select on c##madang.vw_worker from c##kim;

뷰 생성시에 사용된 조건에 맞지 않는 레코드도 추가 된다. 모테이블에 추가되고 뷰에는 조회가 되지 않는다.

조건에 맞지 않는 레코드를 추가할 수 없게 하기

with check option

읽기만 가능한 뷰 생성

with read only

---

인덱스

조건식에 자주 사용되는 컬럼에 대하여 미리 색인표를 만들어 두는 것을 말한다. 인덱스를 사용하면 검색시에 빠른 성능을 기대할 수 있다.

💡 어떠한 책이 있다고 가정하면 그책이 10페이지가 안되면 굳이 색인표(인덱스)를 만들 필요가 없다 하지만 페이지가 너무 많다면 색인표를 만들면 빠르게 찾을 수 있다.

레코드 수가 많을 때 검색(조건식)에 빈번히 사용하는 컬럼에 대하여 인덱스를 만들면 검색시 성능향상을 기대할 수 있지만 레코드 적거나 수정이 빈번한 컬럼에 추가하면 오히려 성능 저하될 수 있다.

인덱스를 만드는 방법

create index 인덱스이름 on 테이블이름(컬럼이름[들]);

인덱스 재구성

인덱스를 담아준 컬럼을 수정했다면 인덱스를 재구성해야 한다.

alter index 인덱스이름 rebuild;

인덱스 삭제

drop index 인덱스이름;

주의

인덱스가 효과를 발휘하려면 데이터 수정이 빈번하지 않아야 하고 설정한 컬럼의 값이 같은 종류가 많지 않아야 의미가 있다. 인덱스에 담을 컬럼 값은 유니크해야 한다.

---

PL/SQL

오라클 안에서도 프로그램을 만들 수 있다. 오라클 전용 프로그램이 PL/SQL이다.

• procedure : 자바의 메서드와 유사
• function : select절에 사용할 수 있다.
• trigger : 이벤트(insert, update, delete)가 발생할 때 연쇄하여 동작

프로시저

생성

create or replace procedure 프로시저이름(변수명 모드 자료형, …)
as
	지역변수(들)
begin
	프로시저가 해야할 sql명령어(들)
end;
/

모드의 종류

• in : 입력용, 프로시저가 일을 하기 위해 값을 전달 받기 위한 모드, default 값
• out : 출력용, 프로시저가 일을 한 결과를 돌려주기 위한 모드

프로시저 호출

exec 프로시저명(값1, 값2, …);

코드

// 프로시저
// 생성
create or replace procedure insertBook(
    myBookId in book.bookId%TYPE,
    myBookName book.bookName%TYPE,
    myPrice book.price%TYPE,
    myPublisher book.publisher%TYPE
)
as
begin
    insert into book values(myBookId, myBookName, myPrice, myPublisher);
end;
/

exec insertBook(14, '한입거리 리액트', 30000, '별빛출판사');

// 수정
create or replace procedure updateBook(
    myBookId in book.bookId%TYPE,
    myPrice book.price%TYPE
)
as
begin
    update book set price = (myPrice) where bookId = (myBookId);
end;
/

exec updatebook(14, 29000);
exec updatebook(13, 13000);

// 삭제
create or replace procedure deleteBook(
    myBookId in book.bookId%TYPE
)
as
begin
    delete from book where bookId = (myBookId);
end;
/

exec deleteBook(14);

PL/SQL에서 선택문 사용

조건에 따라 sql문을 만들 수 있다.

create or replace procedure BookInsertOrUpdate(
    myBookId book.bookId%TYPE,
    myBookName book.bookName%TYPE,
    myPublisher book.publisher%TYPE,
    myPrice book.price%TYPE
)
as
    myCount number;
begin
    select count(*) into myCount from book where bookId = myBookId;
    
    if(myCount = 0) then
        insert into book values(myBookId, myBookName, myPrice, myPublisher);
    else
        update book set price = (myPrice) where bookId = (myBookId);
    end if;    
end;
/

trigger

어떠한 테이블에 insert, update, delete에 어떠한 일이 일어났을 때 연쇄하여 자동으로 동작시키고자 할 sql문장이 있다면 트리거를 이용합니다.

create or replace trigger 트리거이름
시점 이벤트종류
on 테이블명 for each row

declare

begin

end;
/

• 시점 : before, after
• 이벤트 : insert, update, delete
• 트리거에서 사용하는 키워드
  • :new ⇒ insert, update가 된 새로운 행을 의미하는 변수
  • :old ⇒ delete, update가 되기전 과정의 행을 의미하는 변수