데이터베이스(5) - SQL(VIEW, 내장SQL, 스토어드프로시저)

공부/정보처리기사(실기)

데이터베이스(5) - SQL(VIEW, 내장SQL, 스토어드프로시저)

오피스엑소더스 2019. 5. 30. 12:28

8. 스토어드 프로시저(Stored Procedure)

6. 뷰(VIEW)

1. 뷰(VIEW)의 개념

> 하나 이상의 기본 테이블에서 유도되는 가상(Virtual) 테이블

- 저장 장치 내에 물리적으로 존재하지 않음

- 사용자에게는 실재하는 것 처럼 보임

> 여러 개의 테이블에 있는 데이터를 뷰에 통합하여 정보를 열람할 수 있다.

2. 뷰의 특징

> 기본 테이블에서 유도된 테이블이므로 기본테이블과 같은 형태의 구조를 가지며 조작도 유사함

> 가상테이블이므로 물리적으로 구현되어있지 않음

> 필요한 데이터만을 뷰로 정의해서 처리할 수 있으므로 관리가 용이하고 명령문 간단

> 뷰에 나타내지 않은 나머지 데이터를 안전하게 보호할 수 있음

> 삽입, 삭제, 갱신 연산을 위해서는 기본 테이블의 기본키를 포함한 속성 집합으로 뷰를 구성해야함

> 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있음

3. 뷰 연산 시 제약성

> 뷰를 변경할 수 없는 경우

- 뷰의 열(속성)이 테이블의 열이 아닌 상수, 계산식, 그룹 함수를 사용해 만들어진 경우

- DISTINCT, GROUP BY, HAVING을 사용해 만들어진 경우

- 둘 이상에서 유도된 뷰

- 변경할 수 없는 뷰를 토대로 생성된 뷰

4. CREATE VIEW

> 뷰를 정의하는 명령문

1
2
3
CREATE VIEW 뷰명 [ (속성명[, 속성명, ...] ) ]
AS SELECT 문
[WITH CHECK OPTION]
cs

- AS SELECT 문 : 일반 SELECT문과 동일하지만 UNION과 ORDER BY 사용 불가

- WITH CHECK OPTION : 뷰에 대한 갱신이나 삽입 연산이 실행될 때 뷰의 정의조건을 위배하면 실행을 거부

- 속성명을 기술하지 않으면, SELECT문의 속성명이 자동으로 뷰의 속성명으로 결정됨

> 예제

1
2
3
4
5
6
--<학생>테이블에서 컴공과 학생의 학번, 이름, 학과 속성을 갖는 뷰 <DB_학생>을 정의
CREATE VIEW DB_학생
AS SELECT 학번, 이름, 학과
FROM 학생
WHERE 학과='컴퓨터공학과'
WITH CHECK OPTION;
Colored by Color Scripter
cs

5. DROP VIEW

> 뷰를 제거하는 명령문

> 표기 형식

1
DROP VIEW 뷰명 [RESTRICT | CASCADE];
cs
      - RESTRICT : 뷰를 다른 곳에서 참조하고 있으면 제거 취소
- CASCADE : 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 제거
  > 예제
1
2
--<DB_학생> 뷰를 제거하는 명령문을 정의. 단, 다른곳 에서 참조하고 있으면 제거되지 않도록
DROP VIEW DB_학생 RESTRICT
cs

7. 내장 SQL

1. 내장 SQL의 개념(Embedded SQL)

> DB내의 데이터를 정의하거나 접근하는 SQL문을 응용프로그램에 내포하여 프로그램이 실행될 때 함께 실행되도록 호스트 프로그램언어에 삽입한 SQL이다.

2. 내장 SQL의 특징

> 호스트 프로그램 언어에서 실행문이 나타날 수 있는 곳이면 프로그램의 어느 곳에서나 사용 가능

> 실행 결과가 여러 개의 튜플이어도 맨 처음 튜플 하나만을 반환

> 반환된 튜플은 같은 타입의 일반 변수에 저장할 수 있다.

> 호스트 프로그램을 컴파일 하는 시점에 선행처리기에 의해 분리되어 컴파일된다.

> 호스트 프로그램 변수와 DB필드의 이름은 같아도 괜찮다

> 내장 SQL이 실행되면 SQL 실행 상태가 SQL 상태 변수에 저장된다.

3. 내장SQL과 호스트 언어의 실행문 구별

> 명령문의 구분

- C/C++에서 내장 SQL문은 "EXEC SQL"과 세미콜론(;)문자 사이에 기술

- 자바에서는 #SQL { <내장SQL문> } ;

> 변수의 구분

- 내장 SQL문에서 사용하는 호스트 변수는 변수 앞에 콜론(:) 문자를 붙임

- 호스트 언어 내에서 호스트 변수는 그대로 사용

4. 커서(Cursor)

> 내장 SQL문의 실행 결과로 반환된 튜플들에 접근할 수 있도록 해주는 개념

> 질의 실행 결과로 반환된 테이블의 튜플들을 순서대로 가리키는 튜플에 대한 포인터

> 커서를 사용하면 질의 결과로 반환된 튜플들을 한 번에 하나씩 차례로 처리할 수 있다.

> 명령어 :

- DECLARE : 커서 관련 선언(커서 정의 등)

- OPEN : 커서가 질의 결과의 첫 번째 튜플을 가리키도록 설정하는 명령어

- FETCH : 질의 결과에 대한 튜플들 중 현재의 다음 튜플로 커서를 이동시키는 명령어

- CLOSE : 커서를 닫기 위해 사용하는 명령어

8. 스토어드 프로시저(Stored Procedure)

1. 스토어드 프로시저의 개념

> 프로시저란?

- 특정한 작업을 수행하는 SQL문을 논리적으로 그룹화한 것을 지칭

- 그 자체만으로도 완전한 프로그램의 축소판으로 볼 수 있음

> 스토어드 프로시저는?

- 연속된 SQL문을 하나로 모아 SQL 서버에 미리 컴파일하여 저장해 놓은 것

- 클라이언트로부터 호출문을 통해 복잡한 SQL의 일괄 작업을 수행하기에 적합

> 스토어드 프로시저의 구성

- 선언부 : 타입, 커서, 상수, 변수, 내포된 서브 프로그램을 선언하는 부분

- 실행부 : 코드실행을 제어하고 데이터를 조작하는 문장들을 작성하는 부분

- 예외처리부 : 스토어드 프로시저 실행 중에 발생하는 예외를 처리하는 부분

2. 스토어드 프로시저의 장점

> 모둘 별 프로그래밍 허용

- 스토어드 프로시저를 한 번만 만들어 데이터베이스에 저장해 두면, 여러 프로그램에서 계속 스토어드 프로시저를 호출하여 사용할 수 있다.

- 스토어드 프로시저는 DB프로그래머가 작성하며, 원본 코드와 상관없이 수정할 수 있다.

> 빠른 SQL 실행 시간

- 스토어드 프로시저는 만들어질 때 구문이 분석되고 최적화된다.

- 한 번 실행된 후에는 메모리에 캐시되므로 다음 실행에서는 보다 빠르게 실행된다.

> 보안성 향상

- 사용자가 스토어드 프로시저를 통해서만 데이터에 접근할 수 있도록 DB 접근 권한을 제한할 수 있다.

> 네트워크 통신량 감소

- 수백줄의 SQL작업을 스토어드 프로시저 하나의 명령문으로 바꿈으로써 통신 효율 확보

3. 스토어드 프로시저 생성

> 표기 형식

1
2
3
4
5
6
7
8
9
10
11
CREATE [OR REPLACE] PROCEDURE 프로시져명(파라미터)
[지역변수 선언]
프로시져 BODY;
 
--스토어드 프로시저 생성 예시
CREATE OR REPLACE PROCEDURE emp_change_s(IN i_사원번호 integer)
BEGIN
    UPDATE 급여
    SET 지급방식 = 'S'
    WHERE 사원번호 = i_사원번호;
END;
Colored by Color Scripter
cs

- OR REPLACE : 선택적 예약어

ㄴ 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 스토어드 프로시저를 대체

- 프로시저명 : 생성하려는 프로시저의 이름

- 파라미터 : 스토어드 프로시저 파라미터로는 다음과 같은 것 들이 올 수 있다.

ㄴ IN : 호출되는 SP에 값을 전달하는 설정

ㄴ OUT : SP가 그 호출 프로그램에게 값을 반환한다는 설정

ㄴ INOUT : SP에 값을 전당하고 SP실행 후 호출 프로그램에 값을 반환해야 한다 설정

- 프로시저 BODY : SP 본문 코드 기록 부분

ㄴ 형식 : BEGIN SQL문[SQL문 ... ] END

ㄴ SQL문이 하나 이상이어야함

ㄴ변수에 값을 치환할 때는 예약어 SET 사용

4. 스토어드 프로시저 제거

> 스토어드 프로시저를 제거하면 다시 되돌릴 수 없다.

> 표기 형식

1
2
3
4
DROP PROCEDURE 프로시저명;
 
--프로시저 emp_change_s 제거 예시
DROP PROCEDURE emp_change_s;
cs

5. 스토어드 프로시저 실행

> 표기 형식

1
2
3
4
5
EXECUTE 프로시저명;
EXEC 프로시저명;
 
--예시. emp_change_s 실행
EXECUTE emp_change_s;
cs

티스토리