[데이터베이스] 7.4 뷰(View)

뷰(view)

기본 테이블(물리적 테이블)을 기반으로 만들어진 가상 테이블

가상 테이블인 이유는 일반 테이블과 달리 데이터를 실제로 저장하고 있지 않기 때문

but 일반 테이블과 동일한 방법으로 사용

뷰를 기반으로 새로운 뷰 만들기도 가능

기본 테이블 내용 쉽게 검색 가능 but 기본 테이블 내용 변화 작업은 제한적(수정 및 삭제연산 거부)

 

뷰 생성

CREATE VIEW 뷰이름(속성_리스트) //()생략시 SELECT절의 속성 이름 그대로 사용
AS SELECT 문 //기본 테이블에 대한 SELECT문. 생성하려는 뷰의 정의. ORDER BY는 사용불가
[WITH CHECK OPTION]; //생성한 뷰 대상으로 삽입, 수정 연산시 SELECT문의 조건 만족해야만 적용되는 제약조건을 지정

 

Q1. 고객 테이블에서 등급이 VIP인 고객의 고객아이디, 고객이름, 나이로 구성된 뷰를 우수고객이라는 이름으로 생성하고 우수고객 뷰의 모든 내용을 검색하라.

CREATE VIEW 우수고객(고객아이디, 고객이름, 나이)
AS SELECT 고객아이디, 고객이름, 나이
FROM 고객
WHERE 등급="vip"
WITH CHECK OPTION;

SELECT * FROM 우수고객;

//위아래 동일 코드

CREATE VIEW 우수고객
AS SELECT 고객아이디, 고객이름, 나이
FROM 고객
WHERE 등급="vip"
WITH CHECK OPTION;

SELECT * FROM 우수고객;

 

Q2. 제품 테이블에서 제조업체별 제품 수로 구성된 뷰를 업체별 제품수라는 이름으로 생성해보자. 그다음 업체별 제품수 뷰의 모든 내용을 검색해보자

CREATE VIEW 업체별제품수(제조업체, 제품수)
AS SELECT 제조업체, COUNT(*) //제품수는 원래 테이블에 있던 속성이 아닌 집계함수 통해 계산된 새로운 속성 
FROM 제품
GROUP BY 제조업체
WITH CHECK OPTION;

SELECT * FROM 업체별제품수;

 

뷰의 활용

뷰는 일반 테이블과 마찬가지의 방법으로 원하는 데이터 검색 가능

Q3. 우수고객 뷰에서 나이가 25세 이상인 고객에 대한 모든 내용을 검색하라

SELECT *
FROM 우수고객
WHERE 나이>=25;

 

INSERT, UPDATE, DELETE 문

뷰에 대한 삽입, 수정, 삭제 연산은 기본 테이블에 수행되므로 결과적으로 기본 테이블이 변경됨

뷰에 대한 삽입, 수정, 삭제 연산은 제한적: 변경 가능 뷰 VS 변경 불가능 뷰

 

변경 불가능 뷰의 특징

• 기본 테이블의 기본키 구성 속성이 미포함된 뷰
• 기본 테이블 내용이 아닌 집계함수로 새로 계산된 내용이 포함된 뷰
• DISTICNT 키워드 포함하여 정의한 뷰
• GROUP BY 사용하여 정의한 뷰
• 여러개의 테이블을 조인하여 정의한 뷰

문제용 기본 뷰

제품1: 제품번호, 재고량, 제조업체

제품2: 제품명, 제고량, 제조업체

CREATE VIEW 제품1
AS SELECT 제품번호, 재고량, 제조업체
FROM 제품
WITH CHECK OPTION;

SELECT * FROM 제품1;

Q1. 제품번호가 P08, 재고량이 1000, 제조업체가 신선식품인 새로운 제품의 정보를 제품1 뷰에 삽입하고 검색하라.

INSERT INTO 제품1
VALUES('P08',1000,'신선식품');

SELECT * FROM 제품1;

위의 코드로 삽입된 제품 데이터는 뷰가 아니라 제품 테이블에 삽입된다.

제품2 뷰에 대한 삽입 연산은 실패: 제품2뷰는 제품 테이블의 기본키인 제품번호 속성을 미포함했기 때문에 새로운 투플 삽입시 제품번호 속성이 NULL이 되어 삽입 연산 실패

 

뷰의 장점

• 질의문 쉽게 작성 가능
  • GROUP BY, 집계함수, 조인 등을 이용해 뷰를 미리 만들면 복잡한 SQL문 작성 안해도 SELECT와 FROM만으로 원하는 데이터 검색 가능
• 데이터 보안 높아짐
  • 자신에게 제공된 뷰에 대해서만 데이터 접근하도록 권한 설정 가능
• 데이터 더 편하게 관리
  • 제공된 뷰와 관련없는 내용은 사용자가 신경쓸 필요가 없음

 

뷰 삭제: DROP VIEW 뷰 이름;

기본 테이블에는 영향 없음

삭제할 뷰를 참조하는 제약 조건 존재할 경우 뷰 삭제 불가능. 관련 제약조건을 먼저 삭제해야함.

 

삽입 SQL (ESQL; Embedded SQL)

프로그래밍 언어로 작성된 응용 프로그램 안에 삽입하여 사용하는 SQL문

프로그램 내 일반 명령문 위치할 수 있는 곳이면 어디든 삽입 가능

일반 명령문과 구별 위해 삽입 SQL문 앞에 EXEC SQL 붙임

프로그램에서 선언된 일반 변수를 삽입 SQL문에서 사용할 경우 이름 앞에 :(콜론) 붙여서 구분

int main(){

	EXEC SQL BEGIN DECLARE SECTION;
    	char p_no[4], p_name[21];
    	int price;
    EXEC SQL END DECLARE SECTION;
    
    printf("제품번호 입력:");
    
    EXEC SQL SELECT 제품명, 단가 INFO :p_name, :price
    	FROM 제품
        WHERE 제품번호=:p_no;
        
    printf(p_name);

}

 

커서(cursor) 

수행 결과로 반환된 여러 행을 한번에 하나씩 가리키는 포인터

여러 행을 결과로 반환하는 SELECT문을 프로그램에서 사용할 때 필요

 

삽입SQL문에서 사용할 변수 선언하기

BEGIN DECLARE SECTION과 END DECLARE SECTION 사이 선언

 

커서가 필요없는 삽입 SQL

결과로 행 하나만을 반환하는 SELECT문

CREATE TABLE문, INSERT문, DELETE문, UPDATE문

 

커서가 필요한 삽입 SQL

커서 사용전 커서 이름과 커서가 필요한 SELECT문 선언: DECLARE

EXEC SQL DECLARE 커서이름 CURSOR FOR SELECT문;

 

커서에 연결된 SELECT문을 실행하는 삽입 SQL문: OPEN

EXEC SQL OPEN 커서이름;

 

커서를 이동시키는 삽입SQL문: FETCH

커서 이동하여 처리할 다음 행을 가리키게 하고 커서가 가리키는 행에서 속성 값을 가져와서 변수에 저장함

결과 테이블에는 여러 행이 존재하므로 FETCH문을 여러번 수행해야 함

EXEC SQL FETCH 커서이름 INTO :p_name, :price;

 

커서 사용 종료 삽입 SQL문

EXEC SQL CLOSE 커서이름;