VIEW / SEQUENCE / INDEX + PL/SQL < 2021-09-28 >

목차

1. VIEW

2. SEQUENCE

3. INDEX

4. PL / SQL

---

VIEW

- SELECT문의 실행 결과(RESULT SET)을 저장하는 객체

- 논리적 가상 테이블 => 테이블의 모양을 하고 있지만 실제로 값을 저장하고 있지 않음

 

* VIEW의 사용 목적 *1) 복잡한 SELECT문을 반복하지 않고 쉽게 재사용 하기 위해2) 테이블의 진짜 모습을 감출 수 있어 보안상 유리함

 

* VIEW 사용 시 주의사항 *

1) ALTER 구문 사용 불가 ( RESULT SET을 보여주는 가상 테이블이므로 )

2) VIEW에  DML( INSERT , UPDATE , DELETE )을 이용 가능하지만 제약이 많이 따름

=> 보통 조회( SELECT ) 용도로 많이 사용한다.

 

* VIEW 생성하기 *

VIEW 생성 방법

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [(alias)]
	AS SUBQUERY
    [WITH CHECK OPTION]
    [WITH READ ONLY];
    
1) OR REPLACE : 동일한 이름의 VIEW가 존재하는 경우 덮어쓰고, 존재하지 않으면 새롭게 생성

2) FORCE / NOFORCE 옵션: 테이블과 뷰 생성 순서로 인해 발생하는 문제를 처리하기 위한 방법?
						(데이터를 다른 컴퓨터에 복사하는 경우에 사용?)
	- FORCE : SUBQUERY에 사용된 테이블이 존재하지 않아도 VIEW 생성
    - NOFORCCE : SUBQUERY에 사용된 테이블이 존재해야만 VIEW 생성(DEFAULT 값)

3) WITH CHECK OPTION : 옵션을 설정한 컬럼의 값을 수정 못하도록 함

4) WITH READ ONLY : VIEW에 대해 조회만 가능하도록 설정 (DML 수행 불가)


** VIEW 생성 예시 **
 - NO, NAME , AGE 를 가진 TEST 테이블이 있다고 가정
 
 CREATE VIEW V_TEST AS
 SELECT NO , NAME , AGE
   FROM TEST;
   
 * 만일 VIEW 생성 권한이 없는 경우 
 ORA-01031: insufficient privileges 발생
 
GRANT CREATE VIEW TO 사용자명 --> 권한부여로 해결

** OR REPLACE 사용
기존 VIEW인 V_TEST와 같은 이름의 VIEW를 생성할 경우

CREATE OR REPLACE VIEW V_TEST AS
 SELECT NO , NAME , AGE
   FROM TEST;
   
** READ ONLY 
VIEW에서 DML을 사용하면 원본 테이블 데이터가 변경되어 예상치 못한 오류 발생 가능

CREATE OR REPLACE VIEW V_TEST AS
 SELECT NO , NAME , AGE
   FROM TEST
WITH READ ONLY;

데이터 무결성 보존

 

* VIEW 삭제 *

DROP VIEW 뷰이름;

---

SEQUENCE

- 순차적으로 번호를 발생시키는 객체

- 쉽게 생각하면 은행 창구 번호표 같은 느낌

 

* SEQUENCE 사용 이유 *

- PRIMARY KEY 컬럼 값으로 주로 사용 (중복 없이 순서 부여가 가능하기 때문)

 

* SEQUENCE 생성 / 삭제 *

 ** SEQUENCE 표현식 **
CREATE SEQUENCE 시퀀스이름
  [STRAT WITH 숫자] -- 처음 발생시킬 시작값 지정, 생략하면 자동 1이 기본
  [INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
  [MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승, -1)
  [MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
  [CYCLE | NOCYCLE] -- 값 순환 여부 지정 (기본값 : NOCYCLE)
  [CACHE 바이트크기 | NOCACHE] -- 캐시메모리 기본값은 20바이트, 최소값은 2바이트
  
* 보통 START WITH / INCREMENT BY 옵션만 사용해도 충분
* SEQUENCE에서 캐시메모리를 설정하면 할당된 크기만큼 다음 값을 미리 생성하고 저장
  미리 값을 준비하고 있으므로 속도 향상
  
  
** SEQUENCE 생성 및 예제 **
CREATE SEQUENCE TEST
START WITH 1
INCREMENT BY 1;

-> 1부터 시작하고 1씩 증가하는 TEST 시퀀스 생성


** SEQUENCE 사용 방법 **
1) 시퀀스명.NEXTVAL : 다음 번호를 얻어옴 / 첫 호출이면 START WITH 번호
2) 시퀀스명.CURRVAL : 현재 번호를 얻어옴

ex) SELECT TEST.NEXTVAL FROM DUAL; -- 계속 실행하면 숫자가 늘어남
    SELECT TEST.CURRVAL FROM DUAL;
    
    INSERT INTO 테이블 VALUES ( TEST.NEXTVAL , '값', ...);
    --> 테이블에 삽입할 때 사용


** SEQUENCE 주의 사항 **
- SEQUENCE는 오류 또는 롤백 등과 관계없이 
  NEXTVAL 구문이 수행되면 숫자가 무조건 증가함
  ==> 중간 숫자가 누락될 수 있다는 뜻
- SEQUENCE가 없어져도 테이블에 저장된 숫자 값은 유지
  ==> 이미 컬럼에 저장된 숫자는 리터럴이므로 시퀀스랑 상관 X


** SEQUENCE 변경 **
ALTER SEQUENCE 시퀀스이름
    [INCREMENT BY 숫자]
    [MAXVALUE 숫자 | NOMAXVALUE]
    [MINVALUE 숫자 | NOMINVALUE] 
    [CYCLE | NOCYCLE]
    [CACHE 바이트크기 | NOCACHE];
- START WITH는 변경 불가능 => 재설정 필요하면 DROP후 재생성

ALTER SEQUENCE TEST
INCREMENT BY 2;
- 증가값이 1에서 2로 변경


** SEQUENCE 삭제 **
DROP SEQUENCE TEST;

 

---

INDEX

- SQL 명령문 중 SELECT의 처리 속도를 향상시키기 위해 컬럼에 대해 생성하는 객체

- 인덱스 내부 구조는 B* 트리

 

* INDEX 장점 *

- 이진트리 형식으로 구성되어 자동 정렬 및 검색 속도 향상

- 시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상

 

* INDEX 단점 *

- 인덱스 추가를 위한 별도의 저장공간 필요

- 인덱스 생성에 시간 소요

- 데이터 변경(DML)이 빈번한 경우 성능 저하 => 트리의 전체적인 구조가 바뀌므로

- 조회 기능으로 활용할 때 유리함

 

* 인덱스 생성 / 사용 방법 *

[표현식]
    CREATE [UNIQUE] INDEX 인덱스명
    ON 테이블명 (컬럼명, 컬럼명, ... | 함수명, 함수계산식);
      
- PK 또는 UNIQUE 제약조건이 설정되는 경우에 INDEX 자동 생성

ex) INDEX 생성
CREATE INDEX TEST_INDEX
ON TEST_TABLE (ID_NO);

** 인덱스 사용 방법 **
-> WHERE절에 INDEX가 설정된 컬럼을 작성
ex)
SELECT *
  FROM TEST_TABLE
 WHERE ID_NO = 10;
 ==> 데이터의 양이 많을 수록 인덱스의 효과를 느낄 수 있음.
 
 - 자주 SELECT 조건으로 사용되는 컬럼이 있다면 INDEX를 생성해 지정하는 것이 좋다.

 

---

PL / SQL (Procedural Language extension to SQL)

- 오라클 자체에 내장되어있는 절차적 언어

- SQL 문장 내에서 변수의 정의 , 조건 처리 , 반복 처리 등 지원

 

* PL / SQL의 구조 *

- 선언부 : DECLARE로 시작, 변수나 상수 선언

- 실행부 : BEGIN으로 시작, 제어문, 반복문, 함수 정의등 로직 기술

- 예외처리부 : EXCEPTION으로 시작 , 예외 발생 시 해결하기 위한 문장 기술

 

* PL / SQL의 장점 *

- PL/SQL문은 BLOCK 구조로 다수의 SQL을 한번에 DB로 보내 처리 => 수행속도 향상

- PL/SQL의 모든 요소는 하나 또는 두 개 이상의 블록으로 구성하여 모듈화가 가능함

- 단순, 복잡한 데이터 형태의 변수 및 테이블의 데이터 구조와 컬럼명에 준해 동적으로 변수 선언 가능

- EXCEPTION 루틴을 이용하여 ORACLE SERVER ERROR도 처리 가능 + 사용자 정의 에러 선언 및 처리 가능

 

* PL / SQL 작성 *

 

** 프로시저 사용 시 출력하는 내용을 화면에 보여주도록 환경 변경
SET SERVEROUTPUT ON;

DECLARE		-- 선언부
  ID NUMBER;	  -- 변수명 자료형;
  NAME VARCHAR2(30);			  
  TITLE VARCHAR2(30) := '제목';	-- ' := ' -> 대입연산자

BEGIN		-- 선언부 종료, 실행부 시작
  ID := 999; -- 변수에 값 대입
  NAME := '홍길동';
  
  DBMS_OUTPUT.PUT_LINE('번호 : ' || ID); -- 출력
  DBMS_OUTPUT.PUT_LINE('이름 : ' || NAME);
  DBMS_OUTPUT.PUT_LINE('제목 : ' || TITLE);
END;
/

END; 이후에 '/' 작성 => PL/SQL이 끝나는 것을 알리는 기호


** 레퍼런스 변수 : 변수의 데이터 타입을 테이블 또는 뷰의 컬럼을 참조하여 지정하는 변수
- %TYPE , %ROWTYPE

** %TYPE : 해당 컬럼의 데이터 타입을 얻음

<예시>
DECLARE
  ID TEST.TEST_ID%TYPE; --> TEST테이블의 TEST_ID 컬럼과 같은 데이터 타입을 가짐

BEGIN
  SELECT TEST_ID
  INTO ID			--> SELECT 조회 결과를 ID 변수에 대입
  FROM TEST
  WHERE TEST_ID = '&번호';
  	--> 입력기호(&), 번호 == 입력화면에 나타나는 글자
  
  DBMS_OUTPUT.PUT_LINE(ID);
END;
/

** %ROWTYPE : 한 행에 있는 모든 컬럼의 데이터 타입을 얻음

<예시>
DECLARE
  TEST TEST_TABLE%ROWTYPE; --> TEST_TABLE의 한 행을 모두 담는 TEST 변수 선언

BEGIN
  SELECT * 
    INTO TEST
    FROM TEST_TABLE
   WHERE TEST_ID = '&번호';
   
   DBMS_OUTPUT.PUT_LINE(ID);
END;
/

 

** 조건문 / 반복문 **

 

** 선택문 (조건문)
- IF ~ THEN ~ END IF
- IF ~ THEN ~ ELSE ~ END IF
- IF ~ THEN ~ ELSIF ~ ELSE ~ END IF

* (주의) ELSE IF가 아니라 ELSIF임

- CASE ~ 
      WHEN ~ THEN ~ 
   END;
(SWITCH ~ CASE문과 비슷)


** 반복문 
- BASIC LOOP : 내부에 처리문을 작성하고 마지막에 LOOP 벗어날 조건 명시

[표현식]
    LOOP 
        처리문
        조건문
    END LOOP;

- FOR LOOP

[표현식]
 FOR 인덱스 IN [REVERSE] 초기값..최종값
    LOOP
        처리문
    END LOOP;