뷰의 개념과 장점
삽입 SQL의 역할
<뷰>
기본 테이블을 기반으로 만들어진 가상 테이블
기본 테이블 : 뷰를 만드는 기반이 되는 물리적 테이블
논리적으로만 존재(데이터를 실제로 저장하지 않음)
일반 테이블과 동일한 방법으로 원하는 데이터 검색 가능
다른 뷰를 기반으로 새로운 뷰 만들기 가능
뷰를 통해 기본 테이블의 내용을 쉽게 검색 가능
기본 테이블 내용을 변화시키는 작업은 제한적
CREATE VIEW 뷰 이름[(뷰를 구성하는 속성의 리스트)]
AS SELECT (기존 테이블에서 SELECT 해올 것들)
FROM (기존 테이블명)
WITH CHECK OPTION;
(SELECT 만족하는 데이터에 대해 적용할 조건)AS SELECT : 생성하려는 뷰의 정의, 기본 테이블에 대한 SELECT 문 (ORDER BY 는 사용 불가능)
WITH CHECK OPTION : SELECT 문의 조건을 만족하는 데이터에게 적용되는 제약조건. 생성한 뷰를 대상으로 삽입, 수정 연산할 때 적용
EX01
CREATE VIEW 우수고객(고객아이디, 고객이름, 나이)
AS SELECT 고객아이디, 고객이름, 나이
FROM 고객
WHERE 등급 = 'VIP'
WHERE CHECK OPTION :
SELECT * FROM 우수고객;CREATE VIEW 우수고객
AS SELECT 고객아이디, 고객이름, 나이
FROM 고객
WHERE 등급 = 'VIP'
WHERE CHECK OPTION :
SELECT * FROM 우수고객;CREATE VIEW 에서 속성 리스트를 생략하면 SELECT 절에 나열된 속성 이름을 그대로 사용
뷰 생성 후 우수고객 뷰에 VIP 등급이 아닌 고객 데이터를 삽입하거나 뷰의 정의 조건을 위반하는 수정, 삭제 연산 시도시 실행 거부
EX02
CREATE VIEW 업체별제품수(제조업체, 제품수)
AS SELECT 제조업체, COUNT(*)
FROM 제품
GROUP BY 제조업체
WITH CHECK OPTION;
SELECT * FROM 업체별제품수;
속성 중 제품수 는 집계함수를 통해 새로 계산된거니까 VIEW 에서 새로운 이름 정의해야 함
<뷰의 활용>
1. 일반 테이블과 동일한 방법으로 원하는 데이터 검색 가능
-> 뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행
EX03
SELECT * FROM 우수고객 WHERE 나이 >= 25 ;
2. INSERT, UPDATE, DELETE
뷰에 대한 삽입, 수정, 삭제 연산은 실제로는 기본 테이블에 수행 -> 기본 테이블이 변경됨
뷰에 대한 삽입, 수정, 삭제 연산은 제한적으로 수행
-> 변경 가능한 뷰, 변경 불가능한 뷰 존재
변경 불가능한 뷰 :
- 기본 테이블의 기본키를 구성하는 속성이 포함되어있지 않음
- 기본 테이블에 있던 내용이 아닌 집계 함수로 새로 계산된 내용을 포함
- DISTINCT 키워드를 포함하여 정의
- GROUP BY 절을 포함하여 정의
- 여러개의 테이블을 조인하여 정의
EX04
CREATE VIEW 제품1
AS SELECT 제품번호, 재고량, 제조업체
FROM 제품
WITH CHECK OPTION;
SELECT * FROM 제품1기본 테이블 제품에서 제품번호, 재고량, 제조업체를 가져와서 제품1 이라는 VIEW 만듦
제품1 이라는 VIEW 내용 전체를 출력
EX05
CREATE VIEW 제품2
AS SELECT 제품명, 재고량, 제조업체
FROM 제품
WITH CHECK OPTION;
SELECT * FROM 제품2;
EX06
INSERT INTO 제품1 VALUES('P08', 1000, '신선식품')
SELECT * FROM 제품1;제품 1 뷰에 대한 삽입 연산은 실제로 기본 테이블인 제품 테이블에 수행
cf) 제품 2 뷰는 제품 테이블의 기본키인 제품번호 속성을 포함하고 있지 않기 때문에 삽입 연산은 실패
( 새로운 속성을 삽입하려고 하는데 기본키값이 NULL 이니까 )
<뷰의 장점>
- 질의문을 더 쉽게 작성 가능
GROUP BY, 집계함수, 조인 등을 이용해 뷰를 미리 만들어두면 복잡한 SQL 문을 작성하지 않아도 SELECT 절과 FROM 절을 이용해서 데이터 검색 가능
- 데이터의 보안이 높아짐
자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한 설정 가능
- 데이터를 조금 더 편리하게 관리 가능
제공된 뷰와 관련없는 다른 내용에 대해 사용자가 신경 쓸 필요 없음
<뷰의 삭제>
뷰를 삭제해도 기본 테이블은 영향을 받지 않음
DROP VIEW 뷰_이름;삭제할 뷰를 참조하는 제약조건이 존재하는 경우 뷰 삭제가 수행되지 않고, 관련 제약조건을 먼저 삭제해야 함
<<삽입 SQL>>
프로그래밍 언어로 작성된 응용프로그램 안에 삽입하여 사용하는 SQL 문
## 다른 언어
EXEC SQL BEGIN DECLARE SECTION
## SQL 내용
EXEC SQL END DECLARE SECTION
## 다른 언어
프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입 가능
일반 명령문과의 구별 위해 삽입 SQL 문 앞에 EXEC SQL 붙임
프로그램에서 선언된 일반 변수를 삽입 SQL 문에서 사용할때는 이름 앞에 : 을 붙여서 구분
커서
수행 결과로 반환된 여러 행을 한번에 하나씩 가리키는 포인터
여러 행을 결과로 반환하는 SELECT 문을 프로그램에서 사용할 때 필요
- 커서가 필요한 삽입 SQL
1) 커서를 선언하는 삽입 SQL 문
커서를 사용하기 전에 커서 이름, 필요한 SELECT 문 선언
EXEC SQL DECLARE 커서 이름 CURSOR FOR
SELECT문 ;EX
EXEC SQL DECLARE product_Cursor CURSOR FOR
SELECT 제품명, 단가 FROM 제품;product_Cursor : 제품 테이블에서 제품명과 단가를 모두 검색해오는 SELECT 문을 위한 커서
2) 커서에 연결된 SELECT 문을 실행하는 삽입 SQL 문
EXEC SQL OPEN 커서_이름;EX
EXEC SQL OPEN prouct_cursor;product_cursor 라는 커서에 연결된 SELECT 문 실행
3) 커서를 이동시키는 삽입 SQL 문
커서를 이동하여 처리할 다음 행을 가리키도록 하고, 커서가 가리키는 행으로부터 속성값을 가져와 변수에 저장시킴
결과 테이블에는 여러 행이 존재하므로 FETCH 문을 여러번 수행해야함 -> 반복문과 함께 사용
EXEC SQL FETCH 커서_이름 INTO 변수_리스트;EX
EXEC SQL FETCH product_cursor INTO :p_name, :price;product_cursor 커서를 이용해 결과 테이블의 다음 행의 접근 -> 제품의 속성명 값은 p_name 변수에, 단가 속성의 값은 price 변수에 저장
4) 커서의 사용을 종료하는 삽입 SQL 문
EXEC SQL CLOSE 커서_이름;해당 커서를 더이상 사용하지 않음
- 커서가 필요없는 삽입 SQL
CREATE TABLE, INSERT, DELETE, UPDATE, 행 하나만 반환하는 SELECT
EX
1) 삽입 SQL 문에서 사용할 변수 선언
테이블 내에서 대응되는 속성과 같은 타입으로 변수 데이터타입 선언
2) 검색하고자하는 제품의 제품번호를 사용자로부터 입력받음
3) 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품명과 단가를 검색 -> 대응하는 각각의 변수에 저장
4) 검색된 제품명과 단가를 화면에 출력