습관처럼

SQLD 시험을 본지 시간이 좀 지났지만 늦게나마 많은 사람들에게 도움이 되고자 공부법 및 시험 후기를 알려드리고자 합니다. 먼저 저는 전공자이지만 데이터베이스를 공부한지 오래되어.... Anyway~  저는 37화 시험을 봤고 운좋게도 80점으로 합격을 할수 있었습니다.

이글을 통해 시험을 준비하는 모든사람들이 노랭이에 대해서와 시험공부자료를 얻어갈수 있으면 좋겠습니다.

저는 참고서를 이용하지 않고 데이터진흥원에서 제공하는 자료를 다운받아 공부했습니다.

(생각보다 양이 좀 많았지만 자세하게 공부할 수 있어서 저에게는 큰 도움을 받았습니다 하지만 비전공자의 경우 참고서를 이용하는 방법도 좋은거 같아요!!!

다음으로 문제집입니다. 일명 노@랭@이@책을 5회독 정도했습니다.

저는 기억력이 나빠서 빠르게 여러번을 보는 타입이라.. 횟수는 중요치 않습니다~

각자 공부 방법에 맞춰 진행하시면 될거 같아요!!!  꼭 기 억 하 셔 야 됩 니 다 노 랭 이!!!!(홍보아니에요...ㅠㅠ;;)

솔직하게 말씀드리면 개념을 빠르게 훑으시고 위의 노랭이 문제를 편하게 풀수 있을정도로 실력을 갖춘다면 시험에서 무리없이 좋은 성적을 얻을거라 확신하고 확신합니다. ^^

후기를 쓰는 것이 처음이라 뭘 써야할지 몰라서 뒤죽박죽이지만 이 글을 보고 나갈때는 노랭이 책을 통해 공부하자는 것만 기억한다면 ㅎㅎ 성공입니다 >_0. 

제가 공부했던 개념과 문제 자료는 아래에 올려드렸으니 함께 병행하시면 더 좋은 성적을 얻을 수 있을겁니다.

다들 1회 1성공 하세요! 화이팅!!

Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.

예를 들면,  아래와 같이 직원 테이블이 있다고 생각 하자.

 기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴과 connect by 를 사용한다면 표현이 가능하다.

재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야 한다는 점도 무시 할 수 없다.

이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급

  from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원

* start with

select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.

 정의 : start with <조건>

where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.

 ex) start with 직원='개똥' and 직원='순희'

start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.

예를 들면 start with 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.

(한번은 개똥의 하위에서, 그리고 한 번은 루트로서) 

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급

  from 직원
start with 직원 = '개똥' or 직원 ='순희'
connect by 직속상사 = prior 직원    직원      직급

 같은 결과셋이 여러 번 만들어지는 것을 방지하기 위해서는 이러한 조건을 사용해서는 안 된다.

처음 쿼리의 예제에서 직원 ='개똥'이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한 목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여 직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.

그러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의 값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직속상사 is null

connect by 직속상사 = prior 직원

* connect by prior

connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 

즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.

현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.

connect by 직속상사 = prior 직원

 이는 "방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라"라는 의미이다.

쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.

다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데,

결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급

  from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원

 이 쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 

그 이유는 " 방금 전 행의 직속상사 값이 현재 행의 직원 값인 모든 행을 찾아라"라고 선언했기 때문이다. 

이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지 않고, 전혀 다른 결과가 얻어짐을 알 수 있다.  

prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.

select lpad(' ',(level-1)*2,' ')||직원 직원, prior 직원 상사, 직급

  from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원

* level

level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.

계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.

select 직원,level

  from 직원

한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.

select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level

  from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사

 트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다. 

level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.

select lpad(' ',(level-1)*2,' ')||직원 직원

또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.

select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level

  from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사 and level <=3

철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다. 

level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다.  

어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.

출처: https://vaert.tistory.com/m/166

제 3절 조인 수행 원리

조인이란?

두 개 이상의 테이블을 하나의 집합으로 만드는 연산. SQL문에서 FROM 절에 두 개 이상의 테이블이 나열될 경우 조인이 수행. 조인 연산은 두 테이블 사이에서 수행된다.

조인의 종류

NL Join, Sort Merge Join, Hash Join

1. NL Join

NL Join 정의

두개의 테이블을 중첩된 반복문처럼 조인을 수행한다.

반복문 외부(처음 테이블)에 있는 테이블을 선행테이블 또는 외부 테이블

반복문 내부(두번째 테이블)에 있는 테이블을 후행테이블 또는 내부 테이블 이라고 부른다.

# 조인을 반복문으로 표현하면 아래와 같다.

for 선행 테이블 읽음            --- 다른말로---> 외부 테이블(Outer Table)

       for 후행 테이블 읽음     ---다른말로---> 내부 테이블(Inner Table)

              (선행 테이블과 후행 테이블 조인)

NL Join 중요사항

결과 행의 수가 적은 테이블을 선행 테이블로 선택한다.(왜냐하면 선행 결과값이 많을 경우 일량이 늘어날 수 있기 때문)

NL Join 수행 방식

1. 선행 테이블에서 조건에 맞는 값을 찾음

2. 선행 테이블의 조인 키를 가지고 후행 테이블 조인 키 확인

3. 후행 테이블의 인덱스에 선행 테이블의 조인 키 존재 확인

4. 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블 액세스

5 ~ 11 앞의 작업을 반복 수행함

2. Sort Merge Join

Sort Merge Join 정의

Sort Merge Join은 조인 칼럼을 기준으로 데이터를 정렬하여 조인한다.

Sort Merge Join 특징

넓은 범위의 데이터를 처리할 때 주로 이용.

정렬 데이터가 많을 경우 성능이 떨어질 수 있다.(디스크 I/O로 인한 부하 발생)

비동등 조인에 대해서도 조인이 가능

인덱스를 사용하지 않아 인덱스가 존재하지 않을 경우에 사용할 수 있다.

Sort Merge Join 수행 방식

 1. 선행 테이블에서 조건에 맞는 행을 찾음.

2. 선행 테이블의 조인 키를 기준으로 정렬 작업 수행.

1 ~ 2번 작업을 반복 수행

3. 후행 테이블에서 조건에 맞는 행을 찾음.

4. 후행 테이블의 조인 키를 기준으로 정렬 작업 수행.

3 ~ 4번 작업을 반복 수행

5. 정렬된 결과를 이용하여 조인 수행 -> 결과값은 버퍼에 저장

3. Hash Join

Hash Join 정의

해싱 기법을 이용하여 조인 수행

Hash Join 특징

NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 정렬 작업의 부담을 해결 위한 대안으로 사용, 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있다. "="로 수행하는 조인 즉, 동등 조인만 가능(왜냐하면 해쉬함수 이용으로..)

해쉬 테이블의 크기가 메모리에 적재할 수 있는 크기보다 커지면 디스트 사용(I/O에 따른 부하 가중) 즉, 해쉬 조인을 할때 결과 행의 수가 적은 테이블을 선행 테이블로 사용한다.

Hash Join 수행 방식

1. 선행 테이블에서 조건에 만족하는 행을 찾음.

2. 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블 생성

1 ~ 2번 작업을 선행 테이블의 조건 만큼 반복

3. 후행 테이블에서 조건에 만족하는 행을 찾음

4. 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음

5. 조인에 성공하면 추출버퍼에 저장

3 ~ 5번 작업을 후행 테이블의 조건 만큼 수행

출처 : http://bysql.net/

1. 인덱스 특징과 종류

인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념이다. 

Insert, Update, Delete 등과 같은 DML 작업은 테이블과 인덱스를 함께 변경해야 하기 때문에 오히려 느려질 수 있다는 단점이 존재한다.

인덱스에 대한 설명으로 옳은 것

• 인덱스는 데이터 조회 목적에는 효과적이지만, insert, update, delete 작업에는 오히려 많은 부하를 줄 수 있다.
• sql server의 클러스터형 인덱스는 oracle의 IOT와 매우 유사하다.
• 인덱스는 insert 와 delete 작업과는 다르게 update 작업에는 부하가 없을 수도 있다.
  • >>인덱스를 구성하는 컬럼 이외의 데이터가 update될때에는 인덱스로 인한 부하가 발생하지 않는다.
• 대량의 데이터를 조회하는 경우 인덱스를 이용한 조회보다는 테이블 전체 스캔 방식으로 조회하는 것이 더 빠를 수도 있으며 , 인덱스를 구성하는 컬럼들의 순서는 데이터 조회시 성능적인 관점에서 매우 중요한 역할을 한다.

가. 트리 기반 인덱스

DBMS에서 가장 일반적인 인덱스는 B-트리 인덱스이다.

B-트리 인덱스 = 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)

• 브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록(Root Block)
• 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.
• 리프 블록은 트리의 가장 아래 단계에 존재한다.
• 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID, Record    Identifier/Rowid)로 구성(****)
• 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.
• 만약 인덱스 데이터의 값이 동일하면 레코드 식별자의 순서로 저장된다.
• 리프 블록은 양방향 링크(Double Link)를 가지고 있다.(****)
• 이것을 통해서 오름 차순(Ascending Order)과 내림 차순(Descending Order) 검색을 쉽게 할 수 있다.
• B-트리 인덱스는 ‘=’로 검색하는 일치(Exact Match) 검색과 ‘BETWEEN’, ‘>’ 등과 같은 연산자로 검색하는 범위(Range) 검색 모두에 적합한 구조

예 : 브랜치 브록이 3개의 포인터로 구성된 B-트리 인덱스의 예이다. 

1단계. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동

2단계. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동

3단계. 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동, 이 과정을 리프 블록을 찾을 때까지 반복한다.

예 :

-  1개의 값을 찾을 경우 : 37을 찾을 경우

-  구간값을 찾을 경우 : 37과 50사이의 모든 값을 찾고자 한다면(BETWEEN 37 AND 50)

Oracle에서 트리 기반 인덱스에는 B-트리 인덱스 외에도 비트맵 인덱스(Bitmap Index),리버스 키 인덱스(Reverse Key Index), 함수기반 인덱스(FBI, Function-Based Index) 등이 존재

Bitmap 인덱스

• Null 저장
• 부정형 조건 사용 시에도 Scan 가능
• Random Access 의 Cost 는 B-Tree 와 큰 차이 없음(Index block scan 만 줄어듬)
• 여러 Bitmap Index 를 동시에 사용하기 쉬움(Bitmap Index Merge)
• 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

활용방안

• Distinct value 의 개수가 적은 컬럼일 때 저장 효율 높음
• 대용량 데이터 검색 성능 향상
• 비 정형(ad-hoc) 쿼리 많을 때 적합(***)
• 변경(DML) 작업 시 동일 Bitmap 범위의 모든 레코드에 Lock
• 일반적으로 DW 환경에 적합(***)

Reverse Key 인덱스

• B-Tree 와 유사하나 Leaf 의 Key 값이 글자 역순
• reverse(column) 함수 적용하여 인덱스 생성 한 것과 같음

활용방안

• Right Growing 현상으로 Insert 시 동일 인덱스 블록에 대한 경합 심할 때 사용 시 효율적
• = 조건 입력시만 사용 가능함 - 데이터의 '의미' 적인 부분이 없어지므로 (hash 함수 적용시와 유사)

나. SQL Server의 클러스터형 인덱스

SQL Server의 인덱스 종류 : 저장 구조에 따라 클러스터형(clustered) 인덱스와 비클러스터형(nonclustered) 인덱스로 나뉜다.

클러스터형 인덱스

(첫째)

인덱스의 리프 페이지가 곧 데이터 페이지(***)다. 따라서 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다

(인덱스 키 칼럼과 나머지 칼럼을 리프 페이지에 같이 저장하기 때문에 테이블을 랜덤 액세스할 필요가 없다).

클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.(***)

(둘째) 

리프 페이지의 모든 로우(=데이터)는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장(***)된다.

테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다.

그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

예 : Employee ID, Last Name, First Name, Hire Date로 구성된 Employees 테이블에 대해 Employee ID에 기반한 클러스터형 인덱스 B-트리 구조를 편의상, 삼각형 모양을 왼쪽으로 90도 돌려서 나타냈다.

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

전체 테이블 스캔 방식으로 데이터를 검색한다는 것은 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식으로 검색한다.

Oracle

그림 Ⅱ-3-9와 같이 검색 조건에 맞는 데이터를 찾기 위해서 테이블의 고수위 마크(HWM, High Water Mark) 아래의 모든 블록을 읽는다. 고수위 마크는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치(현재는 지워져서 데이터가 존재하지 않을 수도 있음)를 의미한다.

- 참고 HWM : http://jinh2004.blog.me/80110801303

전체 테이블 스캔 방식으로 데이터를 검색할 때 고수위 마크까지의 블록 내 모든 데이터를 읽어야 하기 때문에 모든 결과를 찾을 때까지 시간이 오래 걸릴 수 있다. 이와 같이 전체 테이블 스캔 방식은 테이블에 존재하는 모든 블록의 데이터를 읽는다. 그러나 이것은 결과를 찾기 위해 꼭 필요해서 모든 블록을을 읽은 것이다. 따라서 이렇게 읽은 블록들은 재사용성이 떨어진다. 그래서 전체 테이블 스캔 방식으로 읽은 블록들은 메모리에서 곧 제거될 수 있도록 관리된다.

옵티마이저가 연산으로서 전체 테이블 스캔 방식을 선택하는 이유는 일반적으로 다음과 같다.

1. SQL문에 조건이 존재하지 않는 경우 : SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든 데이터가 답이 된다는 것이다. 
2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하는 않는 경우 : 또한 주어진 조건에 사용 가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변형한 경우에도 인덱스를 사용할 수 없다.
3. 옵티마이저의 취사 선택 : 조건을 만족하는 데이터가 많은 경우, 결과를 추출하기 위해서 테이블의 대부분의 블록을 액세스해야 한다고 옵티마이저가 판단하면, 조건에 사용 가능한 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.
4. 그 밖의 경우 : 병렬처리 방식으로 처리하는 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우

나. 인덱스 스캔

• 인덱스의 리프 블록은 인덱스 구성하는 칼럼과 레코드 식별자로 구성되어 있다.
• 따라서 검색을 위해 인덱스의 리프 블록을 읽으면 인덱스 구성 칼럼의 값과 테이블의 레코드 식별자를 알 수 있다.
• 인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있다. 인덱스의 구성 칼럼이 A+B라면 먼저 칼럼 A로 정렬되고 칼럼 A의 값이 동일할 경우에는 칼럼 B로 정렬된다. 그리고 칼럼 B까지 모두 동일하면 레코드 식별자로 정렬된다.
• 인덱스가 구성 칼럼으로 정렬되어 있기 때문에 인덱스를 경유하여 데이터를 읽으면 그 결과 또한 정렬되어 반환된다. 따라서 인덱스의 순서와 동일한 정렬 순서를 사용자가 원하는 경우에는 정렬 작업을 하지 않을 수 있다. 

1. 인덱스 유일 스캔은 유일 인덱스(Unique Index)를 사용하여 단 하나의 데이터를 추출하는 방식이다.
   1. 유일 인덱스는 중복을 허락하지 않는 인덱스이다.
   2. 유일 인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.
2. 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식이다.
   1. 유일 인덱스의 구성 칼럼 모두에 대해 ‘=’로 값이 주어지지 않은 경우와
   2. 비유일 인덱스(Non-Unique Index)를 이용하는 모든 액세스 방식은 인덱스 범위 스캔 방식으로 데이터를 액세스하는 것이다.
3. 인덱스 역순 범위 스캔은 인덱스의 리프 블록의 양방향 링크를 이용하여 내림 차순으로 데이터를 읽는 방식이다.
   1. 이 방식을 이용하여 최대값(Max Value)을 쉽게 찾을 수 있다. 이 또한 인덱스 범위 스캔의 일종이다.
4. 기타방식들 - 인덱스 전체 스캔(Index Full Scan), 인덱스 고속 전체 스캔(Fast Full Index Scan), 인덱스 스킵 스캔(Index Skip Scan)

다. 전체 테이블 스캔과 인덱스 스캔 방식의 비교

• 데이터를 액세스하는 방법은 크게 두 가지로 나눠 볼 수 있다. 인덱스를 경유해서 읽는 인덱스 스캔 방식과 테이블의 전체 데이터를 모두 읽으면서 데이터를 추출하는 전체 테이블 스캔 방식이다.
• 인덱스 스캔 방식은 사용 가능한 적절한 인덱스가 존재할 때만 이용할 수 있는 스캔 방식이지만 전체 테이블 스캔 방식은 인덱스의 존재 유무와 상관없이 항상 이용 가능한 스캔 방식이다.
• 옵티마이저는 인덱스가 존재하더라도 전체 테이블 스캔 방식을 취사 선택할 수 있다.

인덱스 스캔은 인덱스에 존재하는 레코드 식별자를 이용해서 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다. 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

전체 테이블 스캔은 테이블의 모든 데이터를 읽으면서 원하는 데이터를 찾아야 하기 때문에 비효율적인 검색을 하게 된다. 반대로 테이블의 대부분의 데이터를 찾을 때는 한 블록씩 읽는 인덱스 스캔 방식 보다는 어차피 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리할 수 있다.

출처 : http://bysql.net/

1.옵티마이저

옵티마이저(Optimizer)

사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행. 이러한 최적의 실행 방법을 실행계획(Execution Plan)이라 함 

- 최적의 실행 방법 결정 : 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것

옵티마이저가 최적의 실행 방법을 결정하는 방식

• 규칙기반 옵티마이저(RBO, Rule Based Optimizer) 
• 비용기반 옵티마이저(CBO, Cost Based Optimizer)

 가. 규칙기반 옵티마이저(RBO, rule based Optimizer)****정의암기!!

규칙기반 옵티마이저는 규칙(우선 순위)을 가지고 실행계획을 생성, 실행계획을 생성하는 규칙을 이해하면 누구나 실행계획을 비교적 쉽게 예측 가능

• 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선 순위가 높음
• 이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행계획 생성
• 조인 순서를 결정 시 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준
• 조인 칼럼에 대한 인덱스가 양쪽에 존재  >>  우선 순위가 높은 테이블이 선행 테이블(Driving Table)
• 한쪽에만 인덱스 존재  >>  인덱스가 없는 테이블이 선행 테이블 (NL Join 사용)
• 모두 인덱스가 존재하지 않을 경우  >>  FROM 절의 뒤에 나열된 테이블이 선행 테이블로 선택 (Sort Merge Join 사용)
• 우선 순위가 동일  :  FROM 절에 나열된 테이블의 역순으로 선행 테이블 선택

규칙기반 옵티마이저의 최적화 과정 예시

* EMP_JOB : JOB        * EMP_SAL : SAL * PK_EMP : EMPNO (UNIQUE) 

나. 비용기반 옵티마이저(CBO, Cost Based Optimizer)*****정의암기!!!

비용(예상되는 소요시간, 자원 사용량)이 가장 적은 실행계획을 선택하는 방식, 규칙기반 옵티마이저의 단점을 극복하기 위해서 출현

다양한 객체 통계정보와 시스템 통계정보 등 이용

통계정보  없을 경우  >>  정확한 비용 예측이 불가능해 비효율적인 실행계획 생성

(정확한 통계정보 유지하는 것은 비용기반 최적화에 중요한 요소)

그림 Ⅱ-3-3 비용기반 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성

• 질의 변환기   :   사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
• 대안 계획 생성기   :   동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

- 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성

- 대안 계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린다. 

대부분의 상용 옵티마이저들은 대안 계획의 수를 제약하는 다양한 방법 사용

대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있다. 

* 비용 예측기   :   생성된 대안 계획의 비용을 예측하는 모듈

- 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다. 

- 정확한 통계정보, 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확 

규칙기반 옵티마이저

- 항상 인덱스를 사용할 수 있다면 전체 테이블 스캔 보다는 인덱스를 사용하는 실행계획을 생성한다고 했다. 

비용기반 옵티마이저 

- 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다. 

- 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다. 

- 또한 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다.

2. 실행계획

- SQL에서 요구한 사항을 처리하기 위한 절차와 방법

- 다양한 처리 방법(실행계획)마다 실행 시간(성능)은 서로 다를 수 있다 (옵티마이저는 최적의 실행계획 생성)

- 실행계획 구성 요소*** (구성요소 암기!!!)

1) 조인 순서(Join Order)

2) 조인 기법(Join Method)

3) 액세스 기법(Access Method)

4) 최적화 정보(Optimization Information)

* Cost : 상대적인 비용 정보

* Card : Cardinality의 약자. 결과집합의 건수

* Bytes : 결과집합이 차지하는 메모리 양의 바이트

* 비용정보 : 실제 SQL 실행하고 얻은 결과가 아니라 통계 정보 바탕으로 계산한 예상치 

5) 연산(Operation): 여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업

3. SQL 처리 흐름도***(처리 흐름도에서 무엇을 알수 있는지 알것!!!)

- SQL의 내부적인 처리 절차를 시각적으로 표현한 도표(실행계획을 시각화)

- 조인 순서, 액세스 기법과 조인 기법 등을 표현 가능

l  SELECT ...

    FROM TAB1 A, TAB2 B

    WHERE A.KEY = B.KEY

    AND A.COL1 = :condition1

    AND B.COL2 = :condition2 

    ;

* 액세스 건수 :  TAB1을 액세스한 건수

A.COL1 관련 인덱스가 없어서 전체 테이블 스캔 수행을 의미(액세스 건수 = TAB1테이블 총 건수)

* 조인 시도 건수 : TAB1에서 읽은 건에 대해 A.COL1 = :condition1 조건을 만족한 건수

* 테이블 액세스 건수 : B.KEY 로 구성된 인덱스인 I01_TAB2 에서 즉, 조인 시도한 건들 중 B.KEY = A.KEY 조건을

만족한 건수                                                         

* 성공 건수 : 결과 건수로 TAB2  에서 B.COL2 = :condition2 조건에 만족하는 건수

예상문제 

다음중 실행계획에 대한 설명으로 가장 부적절한 것은?

1) 실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.

2) 실행계획은 조인 방법, 조인 순서, 액세스 기법 등이 표현된 것이다.

3) 동일 SQL 문에 대해 실행계획이 다르면 실행 결과도 달라질 수 있다. >>오답 (실행계획 즉, 실행방법이 달라진다고 해서 결과가 달라지지는 않는다.)

4) CBO(cost based optimizer)의 실행계획에는 단계별 예상 비용 및 건수 등이 표현된다.

SQL 처리 흐름도에 대한 설명으로 가장 절절한 것을 2개 고르시오

위의 SQL 처리 흐름도 그림 참조~

1) 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법이 표현된다. SQL의 내부적인 처리 절차를 시각적으로 표현해준다.

2) SQL의 내부적인 처리 절차를 시각적으로 표현해준다.

3) SQL 처리 흐름도는 실행 계획을 시각화하여 표현한 것이다 따라서 무관하지 않다.

4) SQL 처리 흐름도만을 보고 실행 시간을 알수없다.

다음중 옵티마이저와 실행계획에 대한 설명으로 부적절한 것을 2개 고르시오

1) SQL 처리 흐름도는 성능적인 측면의 표현은 고려하지 않는다. >> 성능적인 측면도 표현할 수 있다.

2) 규칙기반 옵티마이저에서 제일 높은 우선 순위는 행에 대한 고유 주소를 사용하는 방법이다.

>>규칙기반 옵티마이저에서 제일 낮은 우선순위는 전체 테이블 스캔이고, 제일 높은 우선순위는 rowid를 활용하여 테이블을 액세스하는 방법이다.

3) SQL 처리 흐름도는 인덱스 스켄 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있다.

>>SQL 처리 흐름도는 처리 절차를 시각적으로 표현한 것으로, 인덱스 스캔 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있으며, 성능적인 측면도 표현할 수 있다.

4) 인덱스 범위 스캔은 항상 여러 건의 결과가 반환된다. >>인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다.

아래의 옵티마이저와 실행계획에 대한 설명 중에서 옳은 것을 모두 묶은 것은?

ㄱ) oracle의 규칙기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 single row by rowid 액세스 기법이다.

ㄴ) 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통걔정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 sql의 실행 계획이 달라질수 있다.

ㄷ) oracle의 실행계획에 나타나는 기본적인 join 기법으로는 nl join, hash join, sort merge join 등이 있다.

ㄹ) 다양한 join 기법중 nl join은 DW 등에서 데이터를 집계하는 업무에 많이 사용된다. >>nl join은 데이터를 집걔하는 업무보다는 OLTP의 목록 처리 업무에 많이 사용된다. DW 등의 데이터 집계 업무에서 많이 사용되는 join 기법은 hash join 또는 sort merge join 이다.

출처 : http://bysql.net/

1. 절차형 SQL 개요
일반적인 개발 언어처럼 SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 절차형 SQL 제공

Oracle : PL(Procedural Language)/SQL
DB2 : SQL/PL
SQL Server  : T-SQL

절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성가능
절차형 SQL을 이용하여 만들 수 있는 저장 모듈인 Procedure, User Defined Function, Trigger 에 대해서 간단하게 살펴본다. (상세한 내역은 각 DBMS 벤더의 매뉴얼 참조)

2. PL/SQL 개요
가. PL/SQL 특징
Oracle의 PL/SQL은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다. 이런 PL/SQL을 이용하여 다양한 저장 모듈(Stored Module)을 개발할 수 있다. 저장 모듈이란 PL/SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.

PL/SQL의 특징

• PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다. 
  
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다. 
  
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다. 
  
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다. 
  
• PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다. 
  
• PL/SQL은 응용 프로그램의 성능을 향상시킨다. 
  
• PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

그림 Ⅱ-2-18은 PL/SQL Architecture이다. PL/SQL Block 프로그램을 입력받으면 SQL 문장과 프로그램 문장을 구분하여 처리한다. 즉 프로그램 문장은 PL/SQL 엔진이 처리하고 SQL 문장은 Oracle 서버의 SQL Statement Executor가 실행하도록 작업을 분리하여 처리한다.

​

나. PL/SQL 구조
다음은 PL/SQL의 블록 구조를 표현한 내용이다.

​

- DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다. 
- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. 
- EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.

​

다. PL/SQL 기본 문법(Syntax)
앞으로 살펴볼 User Defined Function이나 Trigger의 생성 방법이나 사용 목적은 다르지만 기본적인 문법은 비슷하기 때문에 여기에서는 Stored Procedure를 통해서 PL/SQL에 대한 기본적인 문법을 정리한다.

CREATE OR REPLACE Procedure Procedure_name ( argument1 mode data_type1, argument2 mode date_type2, ... ... ) IS AS ... ... BEGIN ... ... EXCEPTION ... ... END; /

다음은 생성된 프로시저를 삭제하는 명령어이다.
DROP Procedure Procedure_name;

CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성할 수 있다. 이렇게 생성한 프로시저는 데이터베이스 내에 저장된다. 프로시저는 개발자가 자주 실행해야 하는 로직을 절차적인 언어를 이용하여 작성한 프로그램 모듈이기 때문에 필요할 때 호출하여 실행할 수 있다. 

OR REPLACE 절은 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰기 하겠다는 의미이다. Argument는 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과값을 운영 체제로 리턴시킬 매개 변수를 지정할 때 사용한다. 
mode 부분에 지정할 수 있는 매개 변수의 유형은 3가지가 있다. 
먼저 IN은 운영 체제에서 프로시저로 전달될 변수의 MODE이고, 
OUT은 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE이다. 
마지막으로 잘 쓰지는 않지만 INOUT MODE가 있는데 이 MODE는 IN과 OUT 두 가지의 기능을 동시에 수행하는 MODE이다. 
마지막에 있는 슬래쉬(“/”)는 데이터베이스에게 프로시저를 컴파일하라는 명령어이다. 

앞에서 잠깐 언급했지만 PL/SQL과 관련된 내용은 상당히 다양하고 분량이 많기 때문에 본 가이드에서는 간단한 문법과 사용 목적에 초점을 맞춰 이해하기 바란다.

3. T-SQL 개요
가. T-SQL 특징
T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서, T-SQL은 엄격히 말하면, MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것이다. T-SQL을 이용하여 다양한 저장 모듈(Stored Module)을 개발할 수 있는데, T-SQL의 프로그래밍 기능은 아래와 같다.

- 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다. 
- 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며 전역변수는 이미 SQL서버에 내장된 값이다.
- 데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다. 
- 연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다. 
- 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다. 
- 주석 기능한줄 주석 : -- 뒤의 내용은 주석범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함

T-SQL과 타 DBMS가 제공하는 SQL은 약간만 다를 뿐 그 맥락은 같이 하기 때문에, 조금의 변경 사항만 적용하면 같은 기능을 수행할 수 있다. 그리고 많은 사람들이 SQL 서버에 엔터프라이즈 매니저의 UI를 통하여 접근하는 경우가 많은데, 실제로는 SQL 또는 T-SQL 구문을 사용하여 접근하는 것이  더 바람직하다.

나. T-SQL 구조
다음은 T-SQL의 구조를 표현한 내용이다. PL/SQL과 유사하다.

- DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다. 
- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니지만 블록 단위로 처리하고자 할 때는 반드시 작성해야 한다. 
- ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.

다. T-SQL 기본 문법(Syntax)
앞으로 살펴볼 User Defined Function이나 Trigger의 생성 방법과 사용 목적은 Stored Procedure와 다르지만 기본적인 문법은 비슷하기 때문에 여기에서는 Stored Procedure를 통해서 T-SQL에 대한 기본적인 문법을 정리한다.

CREATE Procedure schema_name.Procedure_name @parameter1 data_type1 mode, @parameter2 date_type2 mode, ... ... WITH AS ... ... BEGIN ... ... ERROR 처리 ... ... END;

다음은 생성된 프로시저를 삭제하는 명령어이다.

DROP Procedure schema_name.Procedure_name;

CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성할 수 있다. 이렇게 생성한 프로시저는 데이터베이스 내에 저장된다. 프로시저는 개발자가 자주 실행해야 하는 로직을 절차적인 언어를 이용하여 작성한 프로그램 모듈이기 때문에 필요할 때 호출하여 실행할 수 있다. 

프로시저의 변경이 필요할 경우 Oracle은 CREATE OR REPLACE와 같이 하나의 구문으로 처리하지만 SQL Server는 CREATE 구문을 ALTER 구문으로 변경하여야 한다. 

@parameter는 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과 값을 리턴 시킬 매개 변수를 지정할 때 사용한다. 

mode 부분에 지정할 수 있는 매개 변수(@parameter)의 유형은 4가지가 있다.
① VARYING결과 집합이 출력 매개 변수로 사용되도록 지정합니다. CURSOR 매개변수에만 적용된다. 
② DEFAULT지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리한다. 즉, 기본 값이 지정되어 있으면 해당 매개 변수를 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행이 된다. 
③ OUT, OUTPUT프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환한다. 
④ READ ONLY자주 사용되지는 않는다. 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타낸다. 매개 변수 유형이 사용자 정의 테이블 형식인 경우 READ ONLY를 지정해야 한다.

WITH 부분에 지정할 수 있는 옵션은 3가지가 있다.
① RECOMPILE데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 된다. 데이터베이스 엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용한다. 
② ENCRYPTIONCREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다. 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않는다. 원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업을 해두어야 한다. 
③ EXECUTE AS 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다.

앞에서 잠깐 언급했지만 T-SQL과 관련된 내용은 상당히 다양하고 분량이 많기 때문에 본 가이드에서는 간단한 문법과 사용 목적에 초점을 맞춰 이해하기 바란다.

​

4. Procedure의 생성과 활용
그림 Ⅱ-2-21은 앞으로 생성할 Procedure의 기능을 Flow Chart로 나타낸 그림이다.

예제 SCOTT 유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 Procedure를 작성한다. SCOTT 유저가 기본적으로 소유한 DEPT 테이블의 구조는 표 Ⅱ-2-14와 같다.

예제 Oracle 

CREATE OR REPLACE Procedure p_DEPT_insert -------------① ( v_DEPTNO in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2) IS cnt number := 0; 
BEGIN 
SELECT COUNT(*) INTO CNT -------------② FROM DEPT 
WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1; 
if cnt >0 then -------------③ v_result := '이미 등록된 부서번호이다'; 
else 
INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------④ VALUES (v_DEPTNO, v_dname, v_loc);
COMMIT; -------------⑤ v_result := '입력 완료!!'; 
end if; 
EXCEPTION -------------⑥ WHEN OTHERS THEN 
ROLLBACK; 
v_result := 'ERROR 발생'; 
END; /

예제 SQL Server 

CREATE Procedure dbo.p_DEPT_insert -------------① @v_DEPTNO int, @v_dname varchar(30), @v_loc varchar(30), @v_result varchar(100) OUTPUT AS 
DECLARE @cnt int SET @cnt = 0 
BEGIN 
SELECT @cnt=COUNT(*) -------------② FROM DEPT 
WHERE DEPTNO = @v_DEPTNO
IF @cnt >0 -------------③ BEGIN 
SET @v_result = '이미 등록된 부서번호이다' 
RETURN 
END 
ELSE 
BEGIN 
BEGIN TRAN 
INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------④ 
VALUES (@v_DEPTNO, @v_dname, @v_loc) 
IF @@ERROR<>0 
BEGIN
ROLLBACK -------------⑥ SET @v_result = 'ERROR 발생' 
RETURN 
END 
ELSE 
BEGIN 
COMMIT -------------⑤ SET @v_result = '입력 완료!!' 
RETURN 
END 
END 
END

DEPT 테이블은 DEPTNO 칼럼이 PRIMARY KEY로 설정되어 있으므로, DEPTNO 칼럼에는 유일한 값을 넣어야만 한다. 
예제에 대한 설명은 다음과 같다.

① DEPT 테이블에 들어갈 칼럼 값(부서코드, 부서명, 위치)을 입력 받는다.
② 입력 받은 부서코드가 존재하는지 확인한다.
③ 부서코드가 존재하면 '이미 등록된 부서번호입니다'라는 메시지를 출력 값에 넣는다.
④ 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.
⑤ 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션을 종료한다.
⑥ 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생'라는 메시지를 출력값에 넣는다.

앞에 있는 프로시저를 작성하면서 주의해야 할 몇 가지 문법적 요소가 있다.

• 첫째, PL/SQL 및 T-SQL에서는 다양한 변수가 있다. 예제에서 나온 cnt라는 변수를 SCALAR 변수라고 한다. SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정할 수 있다. 
• 둘째, PL/SQL에서 사용하는 SQL 구문은 대부분 지금까지 살펴본 것과 동일하게 사용할 수 있지만 SELECT 문장은 다르다. PL/SQL에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러를 발생시킨다. T-SQL에서는 결과 값이 없어도 에러가 발생하지 않는다. 
  
• 셋째, T-SQL을 비롯하여 일반적으로 대입 연산자는 “=”을 사용하지만 PL/SQL에서는 “:=”를 사용한다. 
  
• 넷째, 에러 처리를 담당하는 EXCEPTION에는 WHEN ~ THEN 절을 사용하여 에러의 종류별로 적절히 처리한다. OTHERS를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 처리하는 것이 좋다. T-SQL에서는 에러 처리를 다양하게 처리할 수 있으며 위의 예제는 그 한 예이다. 

다음은 지금까지 작성한 프로시저를 실행하여 기능을 테스트한 과정이다.

실행 결과 Oracle
SQL> SELECT * FROM DEPT; -----------------①
DEPTNO DNAME  LOC 
------- -------------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON
SQL> variable rslt varchar2(30); -----------------② SQL> EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt); -----------------③ PL/SQL 처리가 정상적으로 완료되었다. 
SQL> print rslt; -----------------④RSLT -------------------------------- 이미 등록된 부서번호이다 
SQL> EXECUTE p_DEPT_insert(50,'NewDev','seoul',:rslt); ----------------⑤ PL/SQL 처리가 정상적으로 완료되었다. 
SQL> print rslt; ----------------⑥ RSLT -------------------------------- 입력 완료!! 
SQL> SELECT * FROM DEPT; ----------------⑦
DEPTNO DNAME  LOC 
------- -------------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON
50 NewDev SEOUL 
5개의 행이 선택되었다.

① DEPT 테이블을 조회하면 총 4개 행의 결과가 출력된다. 
② Procedure를 실행한 결과 값을 받을 변수를 선언한다. (BIND 변수) 
③ 존재하는 DEPTNO(10)를 가지고 Procedure를 실행한다. 
④ DEPTNO가 10인 부서는 이미 존재하기 때문에 변수 rslt를 print해 보면 '이미 등록된 부서번호이다' 라고 출력된다. 
⑤ 이번에는 새로운 DEPTNO(50)를 가지고 입력한다. 
⑥ rslt를 출력해 보면 '입력 완료!!' 라고 출력된다. 
⑦ DEPT 테이블을 조회하여 보면 DEPTNO가 50인 데이터가 정확하게 저장되었음을 확인할 수 있다.

T-SQL로 작성한 프로시저를 실행하기 위해서는 일반적으로 SQL Server에서 제공하는 기본 클라이언트 프로그램인 SQL Server MANAGEMENT STUDIO를 사용한다.

실행 결과  SQL Server 
SELECT * FROM DEPT; -----------------① 
DEPTNO DNAME  LOC 
------- -------------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON
DECALRE @v_result VARCHAR(100) -----------------② 
EXECUTE dbo.p_DEPT_insert 10, 'dev', 'seoul', @v_result=@v_result OUTPUT -----------------③ 
SELECT @v_result AS RSLT -----------------④ 
RSLT -------------------------------- 이미 등록된 부서번호이다 DECALRE @v_result VARCHAR(100) -----------------⑤
 EXECUTE dbo.p_DEPT_insert 50, 'dev', 'seoul', @v_result=@v_result OUTPUT -----------------⑥ 
SELECT @v_result AS RSLT -----------------⑦ 
RSLT -------------------------------- 입력 완료! 
SELECT * FROM DEPT; ----------------⑧ 
DEPTNO DNAME  LOC 
------- -------------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON
 50 NewDev SEOUL 
5개의 행에서 선택되었다.

① DEPT 테이블을 조회하면 총 4개 행의 결과가 출력된다. 
② Procedure를 실행한 결과 값을 받을 변수를 선언한다. 
③ 존재하는 DEPTNO(10)를 가지고 Procedure를 실행한다. 
④ DEPTNO가 10인 부서는 이미 존재하기 때문에 변수 rslt를 print해 보면 ‘이미 등록된 부서번호이다’라고 출력된다. 
⑤ Procedure를 실행한 결과 값을 받을 변수를 선언한다. 
⑥ 이번에는 새로운 DEPTNO(50)를 가지고 입력한다. 
⑦ rslt를 출력해 보면 ‘입력 완료!’라고 출력된다. 
⑧ DEPT 테이블을 조회하여 보면 DEPTNO가 50인 데이터가 정확하게 저장되었음을 확인할 수 있다.

5. User Defined Function의 생성과 활용
User Defined Function은 Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다. 앞에서 학습한 SUM, SUBSTR, NVL 등의 함수는 벤더에서 미리 만들어둔 내장 함수이고, 사용자가 별도의 함수를 만들 수도 있다. Function이 Procedure와 다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것이다. 즉 Function은 Procedure와는 달리 SQL 문장에서 특정 작업을 수행하고 반드시 수행 결과 값을 리턴한다. 

예제 K-리그 8월 경기결과와 두 팀간의 점수차를 ABS 함수를 사용하여 절대값으로 출력한다.

예제 Oracle 

SELECT SCHE_DATE 경기일자, HOMETEAM_ID || ' - ' || AWAYTEAM_ID 팀들, 
       HOME_SCORE || ' - ' || AWAY_SCORE SCORE, ABS(HOME_SCORE - AWAY_SCORE) 점수차 
FROM  SCHEDULE 
WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
ORDER BY SCHE_DATE;
예제 SQL Server 
SELECT SCHE_DATE 경기일자, HOMETEAM_ID + ' - ' + AWAYTEAM_ID AS 팀들, 
       HOME_SCORE + ' - ' + AWAY_SCORE AS SCORE, ABS(HOME_SCORE - AWAY_SCORE) AS 점수차 
FROM  SCHEDULE 
WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
ORDER BY SCHE_DATE;

실행 결과 
경기일자 팀들 SCORE 점수차 
------- -------- ----- ----- 
20120803 K01 - K03 3 - 0 3 
20120803 K06 - K09 2 - 1 1 
20120803 K08 - K07 1 - 0 1 
20120804 K05 - K04 2 - 1 1 
20120804 K10 - K02 0 - 3 3 
20120811 K07 - K10 1 - 1 0 
20120811 K03 - K08 2 - 0 2 
20120811 K09 - K05 0 - 1 1 
20120811 K04 - K02 0 - 2 2 
20120811 K01 - K06 0 - 0 0 
20120818 K05 - K01 0 - 2 2 
20120818 K02 - K09 1 - 2 1 
20120818 K08 - K10 3 - 1 2 
20120818 K04 - K07 1 - 0 1 
20120818 K06 - K03 3 - 1 2 
20120824 K02 - K01 1 - 1 0 
20120824 K05 - K03 3 - 3 0 
20120824 K08 - K06 4 - 3 1 
20120825 K10 - K04 1 - 1 0 
20120825 K09 - K07 1 - 1 0 
20120828 K04 - K08 2 - 3 1 
20120828 K09 - K10 2 - 0 2 
20120828 K03 - K02 0 - 0 0 
20120828 K01 - K07 0 - 1 1 
20120828 K06 - K05 1 - 1 0 
25개의 행이 선택되었다.
예제에서 사용한 ABS 함수를 만드는데, INPUT 값으로 숫자만 들어온다고 가정한다.  

예제 Oracle 
CREATE OR REPLACE Function UTIL_ABS (v_input in number) ---------------- ① 
return NUMBER IS v_return number := 0; ---------------- ② 
BEGIN 
if v_input <0 then ---------------- ③ 
v_return := v_input * -1; 
else 
v_return := v_input; 
end if; 
RETURN v_return; ---------------- ④ 
END; 

예제 SQL Server 
CREATE Function dbo.UTIL_ABS (@v_input int) ---------------- ① 
RETURNS int AS BEGIN DECLARE @v_return int ---------------- ② 
SET @v_return=0 
IF @v_input <0 ---------------- ③ 
SET @v_return = @v_input * -1 
ELSE 
SET @v_return = @v_input 
RETURN @v_return; ---------------- ④ 
END

예제에서 생성한 UTIL_ABS Function의 처리 과정은 다음과 같다.
① 숫자 값을 입력 받는다. 예제에서는 숫자 값만 입력된다고 가정한다. 
② 리턴 값을 받아 줄 변수인 v_return를 선언한다. 
③ 입력 값이 음수이면 -1을 곱하여 v_return 변수에 대입한다. 
④ v_return 변수를 리턴한다.

예제 함수를 이용하여 앞의 SQL을 수정하여 실행한다.

예제 Oracle 
SELECT SCHE_DATE 경기일자, HOMETEAM_ID || ' - ' || AWAYTEAM_ID 팀들, 
       HOME_SCORE || ' - ' || AWAY_SCORE SCORE, UTIL_ABS(HOME_SCORE - AWAY_SCORE) 점수차 
FROM  SCHEDULE 
WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
ORDER BY SCHE_DATE;
예제 SQL Server 
SELECT SCHE_DATE 경기일자, HOMETEAM_ID + ' - ' + AWAYTEAM_ID AS 팀들, 
       HOME_SCORE + ' - ' + AWAY_SCORE AS SCORE, dbo.UTIL_ABS(HOME_SCORE - AWAY_SCORE) AS 점수차 
FROM  SCHEDULE 
 WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
ORDER BY SCHE_DATE;

실행 결과 
경기일자 팀들 SCORE 점수차 
------- -------- ------ ------ 
20120803 K01 - K03 3 - 0 3 
20120803 K06 - K09 2 - 1 1 
20120803 K08 - K07 1 - 0 1 
20120804 K05 - K04 2 - 1 1 
20120804 K10 - K02 0 - 3 3 
25개의 행이 선택되었다.

실행 결과는 앞의 ABS 내장함수를 사용한 SQL 문장과 같은 결과를 확인할 수 있다.

6. Trigger의 생성과 활용
Trigger란 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. 즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다. 
Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다. 요구 사항은 다음과 같다고 가정한다.

어떤 쇼핑몰에 하루에 수만 건의 주문이 들어온다. 주문 데이터는 주문일자, 주문상품, 수량, 가격이 있으며, 사장을 비롯한 모든 임직원이 일자별, 상품별 총 판매수량과 총 판매가격으로 구성된 주문 실적을 온라인상으로 실시간 조회한다고 했을 때, 한 사람의 임직원이 조회할 때마다 수만 건의 데이터를 읽어 계산해야 한다. 가끔 한 번씩 조회한다면 문제가 없을 수도 있으나 빈번하게 조회작업이 일어난다면 조회작업에 많은 시간을 허비할 수 있다.

예제 트리거(Trigger)를 사용하여 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량과 판매금액을 집계하여 집계자료를 보관하도록 한다. 먼저 관련 테이블을 생성한다.

예제 Oracle 
CREATE TABLE ORDER_LIST ( 
ORDER_DATE CHAR(8) NOT NULL, 
PRODUCT VARCHAR2(10) NOT NULL, 
QTY NUMBER NOT NULL, 
AMOUNT NUMBER NOT NULL ); 
CREATE TABLE SALES_PER_DATE ( 
SALE_DATE CHAR(8) NOT NULL, 
PRODUCT VARCHAR2(10) NOT NULL, 
QTY NUMBER NOT NULL, 
AMOUNT NUMBER NOT NULL );

예제 SQL Server 
CREATE TABLE ORDER_LIST ( 
ORDER_DATE CHAR(8) NOT NULL, 
PRODUCT VARCHAR(10) NOT NULL, 
QTY INT NOT NULL, 
AMOUNT INT NOT NULL); 
CREATE TABLE SALES_PER_DATE ( 
SALE_DATE CHAR(8) NOT NULL, 
PRODUCTVARCHAR(10) NOT NULL, 
QTY INT NOT NULL, 
AMOUNT INT NOT NULL );

예제 이제 Trigger를 작성한다.

Trigger의 역할은 ORDER_LIST에 주문 정보가 입력되면 주문 정보의 주문 일 (ORDER_LIST.ORDER_DATE)와 주문 상품(ORDER_LIST.PRODUCT)을 기준으로 판매 집계 테이블(SALES_PER_DATE)에 해당 주문 일자의 주문 상품 레코드가 존재하면 판매 수량과 판매 금액을 더하고 존재하지 않으면 새로운 레코드를 입력한다.

예제 Oracle 
CREATE OR REPLACE Trigger SUMMARY_SALES ---------------- ① 
AFTER INSERT ON ORDER_LIST FOR EACH ROW  
DECLARE ---------------- ② 
o_date ORDER_LIST.order_date%TYPE; 
o_prod ORDER_LIST.product%TYPE; 
BEGIN 
o_date := :NEW.order_date; 
o_prod := :NEW.product; 
UPDATE SALES_PER_DATE ---------------- ③ 
SET    qty = qty + :NEW.qty, amount = amount + :NEW.amount 
WHERE  sale_date = o_date AND product = o_prod; 
if SQL%NOTFOUND then ---------------- ④
INSERT INTO SALES_PER_DATE VALUES(o_date, o_prod, :NEW.qty, :NEW.amount); 
end if; 
 END; 

SUMMARY_SALES Trigger의 처리절차를 설명하면 다음과 같다.

① Trigger를 선언한다.

CREATE OR REPLACE Trigger SUMMARY_SALES : Trigger 선언문

AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생

ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정

FOR EACH ROW : 각 ROW마다 Trigger 적용 

② o_date(주문일자), o_prod(주문상품) 값을 저장할 변수를 선언하고, 신규로 입력된 데이터를 저장한다. : NEW는 신규로 입력된 레코드의 정보를 가지고 있는 구조체 : OLD는 수정, 삭제되기 전의 레코드를 가지고 있는 구조체 표 Ⅱ-2-17 참조 
③ 먼저 입력된 주문 내역의 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이블에 업데이트한다. 
④처리 결과가 SQL%NOTFOUND이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며, SALES_ PER_DATE 테이블에 새로운 집계 데이터를 입력한다.

예제 SQL Server 
CREATE Trigger dbo.SUMMARY_SALES ---------------- ① 
ON ORDER_LIST AFTER INSERT AS 
 DECLARE 
@o_date DATETIME,@o_prod INT,@qty int, @amount int 
BEGIN 
SELECT @o_date=order_date, @o_prod=product, @qty=qty, @amount=amount 
FROM   inserted ---------------- ② 
UPDATE SALES_PER_DATE ---------------- ③ 
SET    qty = qty + @qty, amount = amount + @amount 
WHERE  sale_date = @o_date AND product = @o_prod;
IF @@ROWCOUNT=0 ---------------- ④ 
INSERT INTO SALES_PER_DATE VALUES(@o_date, @o_prod, @qty, @amount) 
END

SUMMARY_SALES Trigger의 처리절차를 설명하면 다음과 같다.
① Trigger를 선언한다.

CREATE Trigger SUMMARY_SALES : Trigger 선언문

ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정

AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생 

② o_date(주문일자), o_prod(주문상품), qty(수량), amount(금액) 값을 저장할 변수를 선언하고, 신규로 입력된 데이터를 저장한다.inserted는 신규로 입력된 레코드의 정보를 가지고 있는 구조체deleted는 수정, 삭제되기 전의 레코드를 가지고 있는 구조체. 표 Ⅱ-2-18 참조 
③ 먼저 입력된 주문 내역의 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이블에 업데이트한다. 
④ 처리 결과가 0건이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며, SALES_PER_DATE 테이블에 새로운 집계 데이터를 입력한다.

예제 ORDER_LIST 테이블에 주문 정보를 입력한다.

예제 Oracle 
SQL> SELECT * FROM ORDER_LIST; 선택된 레코드가 없다. 
SQL> SELECT * FROM SALES_PER_DATE; 선택된 레코드가 없다. 
SQL> INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000); 1개의 행이 만들어졌다. 
SQL> COMMIT; 커밋이 완료되었다.

예제 SQL Server 
SELECT * FROM ORDER_LIST; 선택된 레코드가 없다. 
SELECT * FROM SALES_PER_DATE; 선택된 레코드가 없다.
INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000); 1개의 행이 만들어졌다.

예제 주문 정보와 판매 집계 테이블에 같은 데이터가 들어왔는지 확인한다.

실행 결과 
SQL> SELECT * FROM ORDER_LIST; 
 ORDER_DATG PRODUCT QTY      AMOUNT 
--------- -------- -------- ------- 
 20120901  MONOPACK 10     300000 
SQL> SELECT * FROM SALES_PER_DATE; 
 SALE_DATG PRODUCT QTY       AMOUNT 
-------- -------- -------- -------- 
 20120901 MONOPACK 10        300000

예제 다시 한 번 같은 데이터를 입력해보고, 두 테이블의 데이터를 확인한다.

실행 결과 Oracle 
SQL> INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000); 1개의 행이 만들어졌다. 
SQL> COMMIT; 커밋이 완료되었다. 
SQL> SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT    QTY   AMOUNT 
--------- ---------- ------ ------- 
20120901   MONOPACK   10      300000 
 20120901   MONOPACK   20    600000 
SQL> SELECT * FROM SALES_PER_DATE; 
 SALE_DATG PRODUCT   QTY   AMOUNT 
-------- --------- ----- ------- 
20120901 MONOPACK   30    900000

실행 결과 SQL Server
INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000); 1개의 행이 만들어졌다. 
SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT   QTY   AMOUNT 
--------- --------- ----- -------- 
20120901   MONOPACK   10   300000 
20120901   MONOPACK   20   600000 
SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT   QTY   AMOUNT
 -------- --------- ---- -------- 
20120901 MONOPACK   30    900000

예제

이번에는 다른 상품으로 주문 데이터를 입력한 후 두 테이블의 결과를 조회해 보고 트랜잭션을 ROLLBACK 수행한다. 판매 데이터의 입력 취소가 일어나면, 주문 정보 테이블과 판매 집계 테이블에 동시에 입력(수정) 취소가 일어나는지 확인해본다.

실행 결과 Oracle 
SQL> INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000); 1개의 행이 만들어졌다. 
SQL> SELECT * FROM ORDER_LIST; 
ORDER_DA PRODUCT   QTY    AMOUNT 
-------- -------- ------ ------- 
20120901 MONOPACK  10    300000 
20120901 MONOPACK  20    600000 
20120901 MULTIPACK 10    300000 
SQL> SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT  QTY    AMOUNT 
-------- -------- ------ ------- 
20120901 MONOPACK  30    900000 
20120901 MULTIPACK 10    300000 
SQL> ROLLBACK; 롤백이 완료되었다.
SQL> SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY    AMOUNT 
-------- -------- ------ ------- 
20120901 MONOPACK  10    300000 
20120901 MONOPACK  20    600000 
SQL> SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT   QTY    AMOUNT 
-------- --------- ------ ------- 
20120901 MONOPACK   30    900000

실행 결과 SQL Server 
BEGIN TRAN 
INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000); 1개의 행이 만들어졌다. 
SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT    QTY   AMOUNT 
--------- --------- ------ ------- 
20120901 MONOPACK     10   300000 
20120901 MONOPACK     20   600000 
20120901 MULTIPACK    10   300000 
SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT   QTY    AMOUNT 
-------- --------- ------ ------- 
20120901 MONOPACK   30     900000 
20120901 MULTIPACK  10     300000 
ROLLBACK; 롤백이 완료되었다. 
SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT   QTY   AMOUNT 
--------- -------- ------ ------- 
20120901 MONOPACK    10    300000 
20120901 MONOPACK    20    600000 
SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT   QTY   AMOUNT 
 -------- -------- ------ ------- 
20120901 MONOPACK   30    900000

ROLLBACK을 하면 하나의 트랜잭션이 취소가 되어 Trigger로 입력된 정보까지 하나의 트랜잭션으로 인식하여 두 테이블 모두 입력 취소가 되는 것을 보여 주고 있다. 
Trigger는 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들이라 할 수 있다. 
Trigger는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.

7. 프로시저와 트리거의 차이점
프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, 데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다.  

출처 : http://bysql.net/

1. DCL 개요

유저를 생성하고 권한을 제어할 수 있는 명령어

2. 유저와 권한

Oracle과 SQL Server의 사용자에 대한 차이점

Oracle은 유저를 통해 데이터베이스에 접속을 하는 형태이다.

즉, 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다.

SQL Server는 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다.

더 나아가 특정 유저는 특정 데이터베이스 내의 특정 스키마에 대해 권한을 부여받을 수 있다.

3.SQL Server 로그인 방식

첫 번째) 

Windows 인증 방식으로 Windows에 로그인한 정보를 가지고 SQL Server에 접속하는 방식이다.

Microsoft Windows 사용자 계정을 통해 연결되면 SQL Server는 운영 체제의 Windows 보안 주체 토큰을 사용하여 계정 이름과 암호가 유효한지 확인한다.

즉, Windows에서 사용자 ID를 확인한다. SQL Server는 암호를 요청하지 않으며 ID의 유효성 검사를 수행하지 않는다. Windows 인증은 기본 인증 모드이며 SQL Server 인증보다 훨씬 더 안전하다. Windows 인증은 Kerberos 보안 프로토콜을 사용하고, 암호 정책을 적용하여 강력한 암호에 대해 적합한 복잡성 수준을 유지하도록 하며, 계정 잠금 및 암호 만료를 지원한다.

SQL Server가 Windows에서 제공하는 자격 증명을 신뢰하므로 Windows 인증을 사용한 연결을 트러스트된 연결이라고도 한다.

두 번째)

혼합 모드(Windows 인증 또는 SQL 인증) 방식으로 기본적으로 Windows 인증으로도 SQL Server에 접속 가능하며, Oracle의 인증과 같은 방식으로 사용자 아이디와 비밀번호로 SQL Server에 접속하는 방식이다.

예제)아래 그림 Ⅱ-1-16을 보면 SCOTT이라는 LOGIN 이름으로 인스턴스 INST1에 접속을 하여 미리 매핑되어 있는 SCOTT이라는 유저를 통해 PRODUCT라는 스키마에 속해 있는 ITEM이라는 테이블의 데이터를 액세스하고 있다.

가. 유저 생성과 시스템 권한 부여

유저를 생성하고 데이터베이스에 접속한다. 하지만 데이터베이스에 접속했다고 해서 테이블, 뷰, 인덱스 등과 같은 오브젝트(OBJECT)를 생성할 수는 없다. 사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다.

- 시스템 권한 약 100개 이상의 종류

- 롤(ROLE)을 이용하여 간편하고 쉽게 권한을 부여

① ORACLE

예제 SCOTT 유저로 접속한 다음 PJS 유저(패스워드: KOREA7)를 생성해 본다.

Oracle CONN SCOTT/TIGER
연결되었다.
 
CREATE USER PJS IDENTIFIED BY KOREA7; CREATE USER PJS IDENTIFIED BY KOREA7;
* 1행에 오류: ERROR: 권한이 불충분하다

새로운 유저를 생성하려면 일단 유저 생성 권한(CREATE USER)이 있어야 한다. 현재 SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 권한이 불충분하다는 오류가 발생한다. 

Oracle의 DBA 권한을 가지고 있는 SYSTEM 유저로 접속하면 유저 생성 권한(CREATE USER)을 다른 유저에게 부여할 수 있다.

예제 SCOTT 

유저에게 유저생성 권한(CREATE USER)을 부여한 후 다시 PJS 유저를 생성한다.

예제 및 실행 결과

Oracle GRANT CREATE USER TO SCOTT;
권한이 부여되었다.
 
CONN SCOTT/TIGER
연결되었다.
 
CREATE USER PJS IDENTIFIED BY KOREA7;
사용자가 생성되었다.

② SQL Server

유저를 생성하기 전 먼저 로그인을 생성해야 한다.

로그인을 생성할 수 있는 권한을 가진 로그인은 기본적으로 sa이다.

예제 

sa로 로그인을 한 후 SQL 인증을 사용하는 PJS라는 로그인(패스워드: KOREA7)을 생성해 본다.

로그인 후 최초로 접속할 데이터베이스는 AdventureWorks 데이터베이스로 설정한다.

예제 및 실행 결과

SQL Server CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks

SQL Server에서의 유저는 데이터베이스마다 존재한다. 그러므로 유저를 생성하기 위해서는 생성하고자 하는 유저가 속할 데이터베이스로 이동을 한 후 처리해야 한다.

예제 및 실행 결과

SQL Server USE ADVENTUREWORKS;
GO
CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;

예제 

생성된 PJS 유저로 로그인한다.

예제 및 실행 결과

Oracle CONN PJS/KOREA7;

오류: ERROR: 사용자 PJS는 CREATE SESSION 권한을 가지고 있지 않음;

로그온이 거절되었다.

PJS 유저가 생성됐지만 아무런 권한도 부여받지 못했기 때문에 로그인을 하면 CREATE SESSION 권한이 없다는 오류가 발생한다. 유저가 로그인을 하려면 CREATE SESSION 권한을 부여받아야 한다.

예제 

PJS 유저가 로그인할 수 있도록 CREATE SESSION권한을 부여한다.

예제 및 실행 결과

Oracle CONN SCOTT/TIGER
연결되었다.
 
GRANT CREATE SESSION TO PJS;
권한이 부여되었다.
 
CONN PJS/KOREA7
연결되었다.

예제 PJS 유저로 테이블을 생성한다.

예제 및 실행 결과

Oracle
SELECT * FROM TAB;
선택된 레코드가 없다.
 
CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );
CREATE TABLE MENU (
* 1행에 오류: ERROR: 권한이 불충분하다.

예제 및 실행 결과

SQL Server

CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) );

데이터베이스 ‘AdventureWorks'에서 CREATE TABLE 사용 권한이 거부되었다.

PJS 유저는 로그인 권한만 부여되었기 때문에 테이블을 생성하려면 테이블 생성 권한(CREATE TABLE)이 불충분하다는 오류가 발생한다.(Oracle, SQL Server)

예제 

SYSTEM 유저를 통하여 PJS 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블을 생성한다.

예제 및 실행 결과

Oracle
CONN SYSTEM/MANAGER
연결되었다.
 
GRANT CREATE TABLE TO PJS;
권한이 부여되었다.
 
CONN PJS/KOREA7
연결되었다.
 
CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );
테이블이 생성되었다.

예제 및 실행 결과

SQL Server
GRANT CREATE TABLE TO PJS;
권한이 부여되었다.
 
스키마에 권한을 부여한다.
GRANT Control ON SCHEMA::dbo TO PJS
권한이 부여되었다.
 
PJS로 로그인한다.
CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) );
테이블이 생성되었다.

나. OBJECT에 대한 권한 부여

오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미한다.

표 Ⅱ-2-9는 오브젝트 권한과 오브젝트와의 관계를 보여 주고 있다.

앞에서 PJS 유저가 생성한 MENU 테이블을 SCOTT 유저를 통해서 조회하면 어떻게 될까?

SCOTT, PJS 뿐만 아니라 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.

SQL Server도 같은 방식으로 동작한다.

한 가지 다른 점은 위에서 언급했듯이 유저는 단지 스키마에 대한 권한만을 가진다. 다시 말하면 테이블과 같은 오브젝트는 유저가 소유하는 것이 아니고 스키마가 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지는 것이다.

먼저 SCOTT 유저로 접속하여 PJS.MENU 테이블을 조회한다. 다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근해야 한다.

SQL Server는 객체 앞에 소유한 유저의 이름을 붙이는 것이 아니고 객체가 속한 스키마 이름을 붙여야 한다.

예제 및 실행 결과

Oracle
CONN SCOTT/TIGER
연결되었다.
 
SELECT * FROM PJS.MENU;
SELECT * FROM PJS.MENU
* 1행에 오류:
ERROR: 테이블 또는 뷰가 존재하지 않는다.

예제 및 실행 결과

SQL Server
SCOTT로 로그인한다.
 
SELECT * FROM dbo.MENU;
개체이름 ‘dbo.MENU'이(가) 잘못되었다.

SCOTT 유저는 PJS 유저로부터 MENU 테이블을 SELECT할 수 있는 권한을 부여받지 못했기 때문에 MENU 테이블을 조회할 수 없다.

예제 PJS 유저로 접속하여 SCOTT 유저에게 MENU 테이블을 SELECT 할 수 있는 권한을 부여한다.

예제 및 실행 결과

Oracle
CONN PJS/KOREA7
연결되었다.
 
INSERT INTO MENU VALUES (1, '화이팅');
1개의 행이 만들어졌다.
 
COMMIT;
커밋이 완료되었다.
 
GRANT SELECT ON MENU TO SCOTT;
권한이 부여되었다.

예제 및 실행 결과

SQL Server
PJS로 로그인한다.
 
INSERT INTO MENU VALUES (1, '화이팅');
1개의 행이 만들어졌다.
 
GRANT SELECT ON MENU TO SCOTT;
권한이 부여되었다.

다시 한 번 SCOTT 유저로 접속하여 PJS.MENU 테이블을 조회한다. 이제 PJS.MENU 테이블을 SELECT하면 테이블 자료를 볼 수 있다.

SCOTT 유저는 PJS.MENU 테이블을 SELECT하는 권한만 부여 받았기 때문에 UPDATE, INSERT, DELETE와 같은 다른 작업을 할 수 없다.

오브젝트 권한은 SELECT, INSERT, DELETE, UPDATE 등의 권한을 따로따로 관리한다.

예제 PJS.MENU 테이블에 UPDATE를 시도한다.

예제 및 실행 결과

Oracle
CONN SCOTT/TIGER
연결되었다.
 
SELECT * FROM PJS.MENU;
MENU_SEQ TITLE
-------- --------
1 화이팅
 
UPDATE PJS.MENU SET TITLE = '코리아'
WHERE MENU_SEQ = 1;
 
UPDATE PJS.MENU * 1행에 오=text>

 예제 및 실행 결과

SQL Server
SCOTT으로 로그인한다.
 
SELECT * FROM PJS.MENU;
 MENU_SEQ TITLE
-------- ----------
1 화이팅

UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1; 개체 ‘MENU',

데이터베이스 ’AdventureWorks', 스키마 ‘dbo'에 대한 UPDATE 권한이 거부되었다. 권한이 부족하여 UPDATE를 할 수 없다는 오류가 나타난다. PJS 유저에게 UPDATE 권한을 부여한 후 다시 시도하면 업데이트가 가능하다.

3. Role을 이용한 권한 부여

유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야 한다.

데이터베이스 관리자는 유저가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행해야 하며 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는지를 관리해야 한다.

데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.

ROLE에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있게 된다.

그림 Ⅱ-2-17에서는 유저들과 권한들 사이 간 ROLE의 역할을 보여 주고 있다. 왼쪽 그림은 권한을 직접 유저에게 할당할 때를 나타내는 것이며, 오른쪽 그림은 ROLE에 권한을 부여한 후 ROLE을 유저들에게 부여하는 것을 나타내고 있다.

ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함하여 유저에게 부여될 수도 있다.

예제 JISUNG 유저에게 CREATE SESSION과 CREATE TABLE 권한을 가진 ROLE을 생성한 후 ROLE을 이용하여 다시 권한을 할당한다. 권한을 취소할 때는 REVOKE를 사용한다.

예제 및 실행 결과

Oracle
CONN SYSTEM/MANAGER
연결되었다.
 
REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG;
권한이 취소되었다.
 
CONN JISUNG/KOREA7
ERROR: 사용자 JISUNG은 CREATE SESSION 권한을 가지고 있지 않음.
로그온이 거절되었다.

예제 및 실행 결과

SQL Server
sa로 로그인한다.
REVOKE CREATE TABLE FROM PJS;
권한이 취소되었다.
 
PJS로 로그인한다.
CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) );
데이터베이스 ‘AdventureWorks'에서 CREATE TABLE사용 권한이 거부되었다.

 예제 이제 LOGIN_TABLE이라는 ROLE을 만들고, 이 ROLE을 이용하여 JISUNG 유저에게 권한을 부여한다.

예제 및 실행 결과

Oracle
CONN SYSTEM/MANAGER
연결되었다.
 
CREATE ROLE LOGIN_TABLE;
롤이 생성되었다.
 
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
권한이 부여되었다.
 
GRANT LOGIN_TABLE TO JISUNG;
권한이 부여되었다.
 
CONN JISUNG/KOREA7
연결되었다.
CREATE TABLE MENU2( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));
테이블이 생성되었다.

이와 같이 ROLE을 만들어 사용하는 것이 권한을 직접 부여하는 것보다 빠르고 안전하게 유저를 관리할 수 있는 방법이다.

Oracle에서는 기본적으로 몇 가지 ROLE을 제공하고 있다. 그 중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE이다.

참조를 위해표 Ⅱ-2-11은 CONNECT와 RESOURCE ROLE에 부여된 권한 목록을 정리한 것이다.

CONNECT : CREATE SESSION

RESOURCE : CREATE TABLE과 같은 오브젝트의 생성 권한이 포함

일반적으로 유저를 생성할 때 CONNECT와 RESOURCE ROLE을 사용하여 기본 권한을 부여한다.

유저를 삭제하는 명령어는 DROP USER이고, CASCADE 옵션을 주면 해당 유저가 생성한 오브젝트를 먼저 삭제한 후 유저를 삭제한다.

예제 앞에서 MENU라는 테이블을 생성했기 때문에 CASCADE 옵션을 사용하여 JISUNG 유저를 삭제한 후, 유저 재생성 및 기본적인 ROLE을 부여한다.

예제 및 실행 결과

Oracle
CONN SYSTEM/MANAGER
연결되었다.
DROP USER JISUNG CASCADE;
사용자가 삭제되었다.
☞ JISUNG 유저가 만든 MENU 테이블도 같이 삭제되었다.
 
CREATE USER JISUNG IDENTIFIED BY KOREA7;
사용자가 생성되었다.
GRANT CONNECT, RESOURCE TO JISUNG;
권한이 부여되었다.
CONN JISUNG/KOREA7 연결되었다.
CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));
테이블이 생성되었다.

SQL Server에서는 위와 같이 ROLE을 생성하여 사용하기보다는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식으로 사용한다. 특정 로그인이 멤버로 참여할 수 있는 서버 수준 역할(ROLE)은표 Ⅱ-2-12와 같다.

 데이터베이스에 존재하는 유저에 대해서는 아래와 같은 데이터베이스 역할의 멤버로 참여할 수 있다.

SQL Server에서는 Oracle과 같이 Role을 자주 사용하지 않는다. 대신 위에서 언급한 서버 수준 역할 및 데이터베이스 수준 역할을 이용하여 로그인 및 사용자 권한을 제어한다.

인스턴스 수준의 작업이 필요한 경우 서버 수준 역할을 부여하고 그보다 작은 개념인 데이터베이스 수준의 권한이 필요한 경우 데이터베이스 수준의 역할을 부여하면 된다.

즉, 인스턴스 수준을 요구하는 로그인에는 서버 수준 역할을, 데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여한다.

출처 : http://bysql.net/

1. WINDOW FUNCTION 개요

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

WINDOW FUNCTION 종류

• 순위(RANK) 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER  (ANSI/ISO SQL 표준 / Oracle / SQL Server 등 대부분 DBMS 지원)
• 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT  (ANSI/ISO SQL 표준 / Oracle / SQL Server 등 대부분 DBMS지원)* SQL Server의 경우 집계 함수는 OVER 절 내의 ORDER BY 구문을 지원하지 않는다.
• 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD  (Oracle에서만 지원되는 함수)
• 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT, RATIO_TO_REPORT(CUME_DIST, PERCENT_RANK : ANSI/ISO SQL 표준/Oracle 지원 NTILE : Oracle / SQL Server 지원 RATIO_TO_REPORT: Oracle 지원)
• 선형 분석을 포함한 통계 분석 관련 함수

(참조) Oracle의 통계 관련 함수CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

 WINDOW FUNCTION SYNTAX

• WINDOW 함수에는 OVER 문구 필수
  • SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( PARTITION_BY 컬럼, ORDER BY 절, WINDOWING 절)FROM 테이블 명
•  ARGUMENTS (인수)  : 함수에 따라 0 ~ N개의 인수 
  • PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다. 
  • ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다. 
  •  WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. (다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.)

BETWEEN 사용 타입 ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

BETWEEN 미사용 타입 ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

2. 그룹 내 순위 함수

가. RANK 함수 - 특정 항목(칼럼) / 특정 범위(PARTITION) / 전체 데이터에 대한 순위를 구하는 함수- 동일한 값에 대해서는 동일한 순위 

예제 : 원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다.

SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC)  ALL_RANK, RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

- ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이 충돌 :

>> JOB 별로는 정렬 X, ORDER BY SAL DESC 조건으로 정렬(ALL_RANK과 JOB_RANK의 순서와는 상관없음)

* SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK FROM EMP;

- 업무 구분이 없는 ALL_RANK 칼럼에서 같은 값이 있을 경우 동일 순번 반환- 동일 순번일 때 다음 순위를 뛰어넘고 출력. 예) 1, 2, 2, 4

* SELECT JOB, ENAME, SAL, RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

- 업무를 PARTITION으로 구분한 JOB_RANK의 경우 같은 업무 내 범위에서만 순위를 부여

​

나. DENSE_RANK 함수

- RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점

예제 : 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력한다. 

SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) RANK, DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK FROM EMP;

​

​

다. ROW_NUMBER 함수

RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여

예제 : 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력한다. 

SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) RANK, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER FROM EMP;

- SALARY에서는 어떤 순서가 정해질지 알 수 없다. 데이터베이스 별로 틀린 결과가 나올 수 있다. (Oracle의 경우 rowid가 적은 행이 먼저 나온다)

- 동일 값에 대한 순서까지 관리하고 싶으면 ROW_NUMBER( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을 이용해 추가적인 정렬 기준을 정의

​

3. 일반 집계 함수

가. SUM 함수 – 파티션별 윈도우의 합

예제 : 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구한다. 

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP;

에제 : OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력한다. 

(SQL Server의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.) 

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM FROM EMP* RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

* 표시된 7698-WARD와 7698-MARTIN의 급여가 같으므로, 같은 ORDER로 취급하여 950+1250+1250=3450의 값이 되었다. 7698-TURNER의 경우 950+1250+1250+1500=4950의 누적합을 가진다.

​

​

나. MAX 함수 – 파티션별 윈도우의 최대값

예제 : 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값을 같이 구한다. 

SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX  FROM EMP;

예제 : 추가로, INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출할 수도 있다. SELECT MGR, ENAME, SAL FROM (         SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL         FROM EMP) WHERE SAL = IV_MAX_SAL ;

​

​

​

다. MIN 함수 – 파티션별 윈도우의 최소값

예제 : 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최소값을 같이 구한다. 

SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP;

* SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR) as MGR_MIN FROM EMP;

라. AVG 함수

예제 : EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다. 

SELECT MGR, ENAME, HIREDATE, SAL, ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG 

FROM EMP;

* ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 

>> 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다. (ROWS는 현재 행의 앞뒤 건수를 말하는 것임)

- ALLEN의 경우 파티션 내에서 첫 번째 데이터이므로 본인의 데이터와 뒤의 한 건으로 평균값을 구한다.   (1600 + 1250) / 2 = 1425 - TURNER의 경우 앞의 한 건과, 본인의 데이터와, 뒤의 한 건으로 평균값을 구한다.   (1250 + 1500 + 1250) / 3 = 1333- JAMES의 경우 파티션 내에서 마지막 데이터이므로 뒤의 한 건을 제외한, 앞의 한 건과 본인의 데이터를 가지고 평균값을 구한다.   (1250 + 950) / 2 = 1100

마. COUNT 함수

예제 : 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력하라. 

SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT FROM EMP;