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

1. SQL - DDL

2. SQL - SELECT

3. SQL - JOIN

4. SQL - DML

5. SQL - DCL

6. 뷰(VIEW)

7. 내장 SQL

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

1. SQL - DDL

---

https://dotheright.tistory.com/90#1

2. SQL - SELECT

---

https://dotheright.tistory.com/90#2

3. SQL - JOIN

---

https://dotheright.tistory.com/101#3

4. SQL - DML

---

https://dotheright.tistory.com/101#4

5. SQL - DCL

---

https://dotheright.tistory.com/101#5

6. 뷰(VIEW)

---

1. 뷰(VIEW)의 개념

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

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

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

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

2. 뷰의 특징

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

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

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

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

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

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

3. 뷰 연산 시 제약성

  > 뷰를 변경할 수 없는 경우

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

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

- 둘 이상에서 유도된 뷰

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

4. CREATE VIEW

  > 뷰를 정의하는 명령문

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

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

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

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

  > 예제

--<학생>테이블에서 컴공과 학생의 학번, 이름, 학과 속성을 갖는 뷰 <DB_학생>을 정의
CREATE VIEW DB_학생
AS SELECT 학번, 이름, 학과
FROM 학생
WHERE 학과='컴퓨터공학과'
WITH CHECK OPTION;

5. DROP VIEW

  > 뷰를 제거하는 명령문

  > 표기 형식

DROP VIEW 뷰명 [RESTRICT | CASCADE];

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. 스토어드 프로시저 생성

  > 표기 형식

CREATE [OR REPLACE] PROCEDURE 프로시져명(파라미터)
[지역변수 선언]
프로시져 BODY;
 
--스토어드 프로시저 생성 예시
CREATE OR REPLACE PROCEDURE emp_change_s(IN i_사원번호 integer)
BEGIN
    UPDATE 급여
    SET 지급방식 = 'S'
    WHERE 사원번호 = i_사원번호;
END;

- OR REPLACE : 선택적 예약어

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

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

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

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

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

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

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

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

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

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

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

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

  > 표기 형식

DROP PROCEDURE 프로시저명;
 
--프로시저 emp_change_s 제거 예시
DROP PROCEDURE emp_change_s;

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

  > 표기 형식

EXECUTE 프로시저명;
EXEC 프로시저명;
 
--예시. emp_change_s 실행
EXECUTE emp_change_s;

맨 위로