[데이터베이스] 데이터베이스 언어 SQL (3/3)

본 포스팅은 "데이터베이스 개론 [2판]" 도서로 공부한 내용을 요약하기 위한 포스팅입니다.

---

1. 뷰 (VIEW)

 

1-1. 뷰의 개념

뷰는 다른 테이블을 기반으로 만들어진 가상 테이블(Virtual tabel)이다.
뷰를 가상 테이블이라고 하는 이유는 일반 테이블과 달리 데이터를 실제로 저장하고 있지 않기 때문이다.
뷰는 논리적으로만 존재하면서도 일반 테이블과 동일한 방법으로 사용할 수 있어 사용자는 그 차이를 느끼기 어렵다.

  뷰를 알기 쉽게 설명하자면, 뷰는 기본테이블을 들여다볼 수 있는 창 역할을 한다.

창을 통해 바깥 풍경을 볼 수 있듯이, 뷰를 통해 기본 테이블을 들여다볼 수 있다.

창의 크기나 위치 등에 따라 보이는 풍경이 달라지는 것처럼 동일한 기본 테이블도 어떤 뷰로 보느냐에 따라 보이는 부분이 달라진다.

 

  그리고 창문을 통해 바깥 풍경을 들여다볼 수는 있지만 풍경을 만지거나 바꾸기 어려운 것처럼, 뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있지만 기본 테이블의 내용을 바꾸는 작업은 제한적으로 이루어진다.

 

1-2. 뷰의 생성

  뷰를 생성하기 위해 필요한 SQL 명령어는 CREATE VIEW다.

CREATE VIEW 문의 기본형식은 아래와 같다.

CREATE VIEW 뷰_이름[(속성_리스트)]
AS SELECT 문
[WITH CHECK OPTION];

  CREATE VIEW 문에서는  AS 키워드와 함께 기본 테이블에 대한 SELECT 절을 제시하는데, ORDER BY를 사용할 수 없다는 점만 제외하면 일반 SELECT 문과 동일하다.

 

  뷰를 구성하는 속성의 이름 리스트는 생략할 수 있는데, 생략하면 SELECT 절에 나열된 속성의 이름을 뷰에서도 그대로 사용한다. 하지만, 기존 테이블에 있던 속성이 아닌 새로운 속성(집계 함수 사용)에 관한 속성을 만들 시에는 뷰를 구성하는 속성의 이름을 명확히 제시해야 한다.

 

  WITH CHECK OPTION은 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT 문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 하는 제약조건을 의미한다.

 

예시 1. 제품 테이블에서 제조업체별 제품수로 구성된 뷰를 업체별 제품수라는 이름의 뷰로 생성.

 

뷰 생성 예시

 

1-3. 뷰의 활용

  뷰에서도 일반 데이블처럼 원하는 데이터를 검색할 수 있다.
뷰가 실제로 저장하고 있지 않는 가상 테이블임에도 SELECT 문을 이용해 데이터를 검색할 수 있는 이유는
뷰에 대한 SELECT 문이 내부적으로는 기본 테이블에 대한 SELECT 문으로 변환되어 수행되기 때문이다.

  VIEW를 대상으로 INSERT 문, UPDATE 문, DELETE 문 등을 수행할 수 있다.

뷰에 대한 삽입·수정·삭제 연산도 기본 테이블에 수행되기 때문에 결과적으로는 기본 테이블이 변한다.

하지만 이러한 연산이 모든 뷰에 허용되는 것은 아니다.

뷰는 테이블을 들여다 보는 창의 역할을 하기 때문에 뷰를 통한 기본 테이블의 변화는 제한적이다.

 

* 변경이 불가능한 뷰의 중요한 특징

• 기본 테이블의 기본키를 구성하는 속성이 포함되어 있지 않은 뷰는 변경할 수 없다.
• 기본 테이블에 있던 내용이 아니라 집계 함수로 새로 계산된 내용을 포함하고 있는 뷰는 변경할 수 없다.
• DISTINCT 키워드를 포함하여 정의한 뷰는 변경할 수 없다.
• 여러 개의 테이블을 조인하여 정의한 뷰는 변경할 수 없는 경우가 많다.

 

1-4. 뷰의 장점

1. 질의문을 좀 더 쉽게 작성할 수 있다.
   
   • 특정 조건을 만족하는 튜플들로 뷰를 미리 만들어놓으면, 사용자가 WHERE 절 없이 뷰를 검색해도 특정 조건을 만족하는 데이터를 검색할 수 있다.
   • 또한 GROUP BY, 집계 함수, 조인 등을 이용해 미리 뷰를 만들어놓으면, 복잡한 SQL문을 작성하지 않아도 SELECT절과 FROM 절만으로 원하는 데이터를 검색할 수 있다.
2. 데이터의 보안 유지에 도움이 된다.
   • 여러 사용자의 요구에 맞는 다양한 뷰를 미리 정의해두고 사용자가 자신에게 제공된 뷰를 통해서만 데이터에 접근하도록 권한을 설정하면, 뷰에 포함되지 않은 데이터를 사용자로부터 보호할 수 있다.
3. 데이터를 좀 더 편리하게 관리할 수 있다.
   
   

1-5. 뷰의 삭제

  뷰를 삭제하기 위해 필요한 SQL 명령어는 DROP VIEW이다.

DROP VIEW 문의 기본형식은 아래와 같다.

DROP VIEW 뷰_이름;

  뷰를 삭제하더라도 기본 테이블은 영향을 받지 않는다.

만약 삭제할 뷰를 참조하는 제약조건이 존재한다면 삭제가 수행되지 않는다.

따라서 삭제하고자 하는 뷰를 참조하는 제약조건을 먼저 삭제해야 한다.

---

2. 삽입 SQL

2-1. 삽입 SQL의 개념과 특징

  응용 프로그램 안에 삽입하여 사용하는 SQL 문을 삽입 SQL (ESQL : Embedded SQL)이라 한다.

일반 SQL 문과 달리 응용 프로그램에 삽입 SQL 문을 사용할 때는 다음과 같은 특징을 염두에 두어야 한다.

• 삽입 SQL 문은 프로그램 안에서 일반 명령문이 위치할 수 있는 곳이면 어디든 삽입할 수 있다.
• 프로그램 안의 일반 명령문과 구별하기 위해 삽입 SQL 문 앞에 EXEC SQL을 붙인다.
• 프로그램에 선언된 일반 변수를 삽입 SQL 문에서 사용할 수 있다.
• 단, SQL 문에서 일반 변수를 사용할 때는 앞에 콜론(:)을 붙여 테이블 이름이나 속성의 이름과 구분한다.

  코드 수행 결과로 여러 개의 행을 반환하는 SELECT 문을 삽입 SQL 문으로 사용하는 경우에는 커서(Cursor)라는 도구가 필요하다. 커서는 수행 결과로 반환된 여러 행을 한 번에 하나씩 가리키는 포인터 역할을 한다.

 

2-2. 커서가 필요 없는 삽입 SQL

  SQL 문을 실행했을 때 특별히 결과 테이블을 반환하지 않는 CREATE TABLE 문, INSERT 문, DELETE 문, UPDATE 문, 결과로 행 하나만 반환하는 SELECT 문은 커서가 필요없다.

 

  삽입 SQL 문에서 사용할 변수는 미리 선언해야 하는데,

이는 BEGIN DECLARE SECTION 문장과 END DECLARE SECTION 문장 사이에서 선언하면 된다.

 

예시 .
사용자가 제품번호를 입력하면 제품 테이블에서 사용자가 입력한 제품번호에 해당하는 제품명과 단가를 검색하여 화면에 출력해주는 C 언어 프로그램

 

int main() {
	# 사용할 변수 선언
	EXEC SQL BEGIN DECLARE SECTION;
		char p_no[4], p_name[21];
		int price;
	EXEC SQL END DECLARE SECTION;
	
        # 검색하고자 하는 제품의 제품번호를 사용자로부터 입력받는 부분
	printf("제품번호를 입력하세요 : ")
	scanf("%s", p_no);

	# 사용자가 입력한 제품번호에 해당하는 제품명과 단가를 검색하여 변수에 저장
	EXEC SQL SELECT 제품명, 단가 INTO :p_name, :price
		FROM 제품
		WHERE 제품번호 = :p_no;
		
        # 검색된 제품명과 단가를 화면에 출력하는 부분
	printf("\n 제품명 = %s", p_name)
	printf("\n 단가 = %d", price)

	return 0;
}

 

2-3. 커서가 필요한 삽입 SQL

  SELECT 문의 실행 결과로 여러 행이 검색되는 경우에는 한 번에 한 행씩 차례로 접근할 수 있게 해주는 커서가 필요하다.

 

  커서를 사용하기 전에 먼저 커서의 이름과 커서가 필요한 SELECT 문을 선언해야 한다.

DECLARE 명령어를 이용해 커서를 선언하는 삽입 SQL 문의 기본 형식은 아래와 같다.

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

# 예시
# 제품 테이블에서 제품명과 단가를 모두 검색하는 SELECT 문을 위한 커서 선언
EXEC SQL DECLARE product_cursor CURSOR FOR SELECT 제품명, 단가 FROM 제품;

 

  커서를 선언했다고 SELECT문이 실행되는 것은 아니다.

커서를 선언한 후 SELECT 문을 실행하는 명령이 별도로 필요하다.

커서에 연결된 SELECT 문을 실행하는 삽입 SQL 문의 기본 형식은 아래와 같다.

EXEC SQL OPEN 커서_이름;

# 예시
EXEC SQL OPEN product_cursor;

 

  OPEN 명령어를 이용하 SELECT 문이 실행되면 검색된 행들이 반환되고, 커서는 검색된 행들 중에서 첫 번째 행의 바로 앞에 위치한다. 검색된 행들을 차례로 처리하기 위해 커서를 이동시키는 명령어는 FETCH다.

 

  커서를 이동해 처리할 다음 행을 가리키도록 하고, 커서가 가리키는 행으로부터 속성 값들을 가져와 변수에 저장하는 FETCH 문의 기본 형식은 다음과 같다.

EXEC SQL FETCH 커서_이름 INTO 변수_리스트;

# 예시
# product_cursor를 이동해 결과 테이블의 다음행에 접근하여 제품명, 단가 속성의 값을 변수에 저장
EXEC SQL FETCH product_cursor INTO :p_name, :price

  결과 테이블에는 여러 행이 존재하므로 FETCH 문은 반복해서 여러 번 수행해야 한다.

 

  커서의 사용을 종료하려면 CLOSE 명령어를 사용한다.

EXEC SQL CLOSE 커서_이름;

# 예시
EXEC SQL CLOSE product_cursor;

---