뷰와 저장 프로시저 뷰의 개념을 이해한다. 뷰의 정의와 관리 방법을 이해한다. 뷰를 사용함으로써 생기는 장점을 알아본다.

Presentation on theme: "뷰와 저장 프로시저 뷰의 개념을 이해한다. 뷰의 정의와 관리 방법을 이해한다. 뷰를 사용함으로써 생기는 장점을 알아본다."— Presentation transcript:

1 뷰와 저장 프로시저 뷰의 개념을 이해한다. 뷰의 정의와 관리 방법을 이해한다. 뷰를 사용함으로써 생기는 장점을 알아본다.
뷰를 만들고 기본적인 방법으로 이용할 수 있다. 저장 프로시저의 개념과 처리 메커니즘을 이해한다. 저장 프로시저의 작성 및 호출 방법을 익힌다.

2 뷰 저장 프로시저

3 1. 뷰 뷰의 개념 및 장점 뷰(view): 미리 정의된 단일 질의를 나중에 사용하기 위해 데이터
베이스에 개체로 저장해 놓은 것 단일 질의의 예 기반 테이블의 행이나 열의 부분 집합 기반 테이블 두 개 이상의 유니온 기반 테이블 두 개 이상의 조인 기반 테이블의 통계적 요약 다른 뷰의 부분 집합 뷰와 기반 테이블의 결합

4 1. 뷰 뷰의 개념 및 장점 기반 테이블(base table) : 뷰를 정의하는 데 사용되는, 데이터를 가진 진짜 테이블
단일 질의로 구성된 뷰를 정의해 놓고(①), 이 뷰를 테이블처럼 사용 하여 SELECT 문을 실행하면(③) 뷰의 단일 질의가 실행되어 결과 집합을 만들고(②), 이 결과 집합을 이용하여 최종적인 결과 집합을 얻는다(④).

5 1. 뷰 뷰의 개념 및 장점 뷰의 개념도

6 1. 뷰 뷰의 개념 및 장점 구문 schema_name에서는 뷰 개체의 스키마를 지정한다.
column은 뷰의 열 이름을 지정한다. select_statement에서 열이 산술 식, 함수 또는 상수에서 파생된 경우, 조인으로 인해 둘 이상의 열이 같은 이름을 갖는 경우, 그리고 뷰의 열이 파생된 열과 다른 이름을 갖는 경우에는 열 이름을 지정해주어야 한다. select_statement는 뷰를 정의하는 SELECT 문이다. select_statement 에는 ORDER BY 절을 포함할 수 없는 등의 제약 사항이 있다.

7 1. 뷰 뷰의 개념 및 장점 [그림 1]의 개념도를 구현해보자. 1 USE AdventureWorksLT; 2 GO 3 CREATE VIEW vw_ProductCategory 4 AS 5 SELECT ProductCategoryID, NAME 6 FROM SalesLT.ProductCategory; 7 GO 8 SELECT * 9 FROM vw_ProductCategory 10 WHERE ProductCategoryID <= 4; 예제 1

8 1. 뷰 뷰의 개념 및 장점

9 1. 뷰 뷰의 개념 및 장점 뷰를 사용할 때의 장점 사용자를 위한 데이터에 초점을 맞춘다.
- 사용자에게 꼭 필요한 데이터만 보여주고, 그 밖의 데이터는 감춘다. 사용자는 뷰가 돌려주는 데이터를 기반 테이블의 데이터처럼 조작할 수 있다. 데이터베이스의 복잡도를 줄여준다. - 데이터베이스 구조를 바꾸지 않고도 사용자에게 필요한 맞춤식 데이터를 제공할 수 있다. 또한 복잡한 질의 대신 단순화된 뷰를 쓸 수 있다. 사용자 액세스 관리를 단순화시켜준다. - 기반 테이블의 특정 열에 대해 사용자의 액세스권한 등을 설정할 필요 없이, 사용자들이 뷰를 통해서만 데이터를 액세스하도록 설정하면 되므로 관리가 쉬워진다.

10 1. 뷰 뷰의 개념 및 장점 테이블 구조가 바뀌었을 경우, 이전 버전과 호환되는 뷰를 만들어서
기존의 응용 프로그램이 사용하도록 하면 이전 버전과의 호환성을 제공할 수 있다. 다른 응용 프로그램에서 사용할 때‘데이터 내보내기’를 할 수 있다. - 복잡한 질의에 기반을 둔 뷰를 만들고, 이를 통해 데이터를 내보내 다른 응용 프로그램이 사용하도록 할 수 있다.

11 1. 뷰 뷰의 수정 및 삭제 뷰를 수정하거나 삭제할 때 사용하는 기본 구문
뷰를 삭제한 후에 다시 만들면 액세스 허가를 모두 새로 설정해야 하지 만, ALTER VIEW 문으로 뷰를 수정하면 액세스 권한이 그대로 유지된다. DROP VIEW 문으로 삭제된 뷰를 참조하는 개체(예: 또 다른 뷰)를 액세스하면 오류가 발생한다. 또, 뷰를 참조하는 개체가 삭제되더라도 해당되는 뷰는 자동으로 삭제되지 않으므로 수작업으로 삭제해주어야 한다.

12 1. 뷰 뷰의 수정 및 삭제 뷰에 열을 하나 더 추가하도록 수정해보자. 1 USE AdventureWorksLT; 2 GO 3 ALTER VIEW vw_ProductCategory 4 AS 5 SELECT ProductCategoryID, ParentProductCategoryID, NAME 6 FROM SalesLT.ProductCategory; 7 GO 8 SELECT * 9 FROM vw_ProductCategory 10 WHERE ProductCategoryID BETWEEN 5 AND 8; 예제 2

13 1. 뷰 뷰의 수정 및 삭제

14 1. 뷰 뷰의 수정 및 삭제 뷰를 삭제해보자. 예제 3 DROP VIEW vw_ProductCategory;

15 1. 뷰 뷰의 수정 및 삭제 [예제 3]과 같이 뷰를 삭제할 때 발생하는 오류를 살펴보자. - 만약 삭제하려는 뷰 vw_ProductCategory가 없다면 오류가 발생 한다. SQL에서는 이를 방지하기 위해 IF 문으로 뷰가 있는지 테스트 한 후, 해당 뷰가 있을 때만 삭제할 수 있다. 1 IF object_id('vw_ProductCategory', 'V') IS NOT NULL 2 DROP VIEW vw_ProductCategory; 예제 4

16 2. 저장 프로시저 저장 프로시저의 개념 저장 프로시저(stored procedure): 선택적으로 매개변수를 받아
일련의 SQL 문을 통해 처리하고, 선택적으로 상태 값을 돌려주는 프로시저 데이터베이스에서 범용 프로그래밍 언어의 하부 프로시저(sub- procedure)에 해당하는 개념이다. 복잡한 데이터 처리나 주기적인 업무 처리를 위해서는 저장 프로시저 의 작성이 반드시 필요하다.

17 2. 저장 프로시저 저장 프로시저의 개념 저장 프로시저의 개념도

18 2. 저장 프로시저 저장 프로시저의 개념 저장 프로시저를 이용하지 않고 SQL 문을 직접 사용할 경우, 네트
워크를 통해 일련의 복잡한 SQL 문을 직접 SQL 서버로 보내고, 이 문을 일일이 컴파일하고 실행해야 한다. 그러나 이 SQL 문을 저장 프로시저로 작성해 놓으면 저장 프로시저의 이름만 SQL 서버로 보내면 된다(필요 시 매개변수를 포함한다). 대부분의 DBMS에서 저장 프로시저는 첫 번째 실행 시 컴파일된 실행 계획을 저장해 놓고, 두 번째부터는 이 실행 계획을 직접 실행하므로 처리 속도도 빠르다.

19 2. 저장 프로시저 저장 프로시저의 개념 저장 프로시저를 사용할 때의 장점
프로그램 로직을 다른 응용 프로그램과 공유하며, 이에 따라 데이터 액세스나 변경을 일관성 있게 처리할 수 있다. 사용자가 필요로 하는 기능을 저장 프로시저로 제공할 경우, 테이블을 직접 액세스할 필요가 없으므로 보안이 강화된다. 사용자는 직접 액세스 할 권한이 없는 테이블이나 뷰를 저장 프로시저를 통하여 액세스할 수 있다. 저장 프로시저를 반복적으로 호출할 경우, 컴파일되고 저장되어 있는 실행 계획을 직접 실행하므로 성능을 향상시킬 수 있다. 네트워크를 통해 SQL 문 수십 또는 수백 개를 데이터베이스 서버로 보내지 않고 하나의 저장 프로시저 호출문을 보냄으로써 네트워크 트래픽을 줄일 수 있다.

20 2. 저장 프로시저 저장 프로시저의 정의 저장 프로시저 정의를 위한 기본적인 구문
schema_name에서는 저장 프로시저 개체의 스키마를 지정한다. (Transact-SQL에서 이것을 생략하면 기본 스키마 또는 dbo가 지정됨) procedure_name은 저장 프로시저 이름이다. parameter는 매개변수 이름, data_type은 매개변수의 데이터 형식 sql_statement는 하나 이상의 확장 SQL(예: Transact-SQL, PL/SQL 등) 문들이다.

21 2. 저장 프로시저 저장 프로시저의 개념 저장 프로시저를 사용해보자. 예제 5 1 USE AdventureWorksLT;
2 GO 3 CREATE PROCEDURE usp_GetProductCategory ( 4 @ID int 5 ) 6 AS 7 SELECT * 8 FROM SalesLT.ProductCategory 9 = ProductCategoryID; 10 GO

22 2. 저장 프로시저 저장 프로시저의 호출 저장 프로시저의 호출 방법은 DBMS마다 다르다. Oracle, MySQL
등에서는 CALL 문을 사용하는데, 여기서는Transact-SQL의 호출 방법 위주로 설명하기로 한다. EXECUTE 또는 EXEC 키워드는 생략해도 되지만, 여러 명령문을 한꺼번에 실행할 때 이것을 생략하면 오류가 발생한다. 따라서 가급적 생략하지 말기 바란다. =”를 사용하는 것을 참조 값 전달(Passing Values by Reference 또는 Named Parameter)이라고 하는데, 매개변수의 이름과 함께 매개변수 값을 넘기는 방식이다.

23 2. 저장 프로시저 저장 프로시저의 호출 반면,“ @parameter =”를 사용하지 않고 단순히 매개변수 값만 넘기는
방식을 위치 값 전달(Passing Values by Position 또는 Positioned Parameter) 방식이라고 한다(일반적인 프로그래밍 언어의 Call-by- Value 또는 Call-by-Reference와는 완전히 다른 개념이므로 혼동하지 말길 바란다). 참조 값 전달 방식이 위치 값 전달 방식보다 훨씬 더 융통성이 있다. 매개변수의 이름을 명시함으로써 임의 위치의 매개변수를 생략할 수 있으 며, 심지어 매개변수의 순서를 바꿔 호출할 수도 있다.

24 2. 저장 프로시저 저장 프로시저의 호출 저장 프로시저를 호출해보자. 예제 6
1 EXEC = 5; 2 EXEC usp_GetProductCategory 6;

25 2. 저장 프로시저 저장 프로시저의 호출 Oracle에서의 호출 방법
1행의 예를 들면, EXEC 대신 CALL을 사용하고, 매개변수 이름의 첫 글자 @는 생략하고,‘ =’대신‘=>’를 사용하며, 반드시 ( )를 사용한다. 1 CALL usp_GetProductCategory(ID => 5); 2 CALL usp_GetProductCategory(6);