Transact-SQL(T-SQL) #4 - 저장 프로시저

1. 저장 프로시저란?

저장 프로시저란 하나 이상의 T-SQL 문을 실행할 수 있는 단위입니다.

저장 프로시저는 사용자 정의 저장 프로시저, 임시 저장 프로시저, 시스템 저장 프로시저까지 3가지의 유형이 있습니다.

 

사용자 정의 프로시저는 사용자가 만들어 사용할 수 있는 저장 프로시저이고

임시 프로시저는 사용자 프로시저와 같으나, tempdb에 저장되어 프로시저의 사용이 끝나면 삭제되는 프로시저입니다.

시스템 프로시저는 SQL Server에서 기본적으로 저장된 프로시저로 사용자는 시스템 프로시저를 호출하여 데이터베이스를 관리 할 수 있습니다.

 

 

데이터베이스의 '프로그래밍기능> 저장 프로시저 > 시스템 저장 프로시저'에는 그림과 같이 이미 정의되어있는 프로시저의 목록을 확인할 수 있습니다.

더 자세한 시스템 저장 프로시저의 목록 및 기능은 다음 링크를 참조하시기 바랍니다.

http://msdn.microsoft.com/ko-kr/library/ms187961(v=sql.110).aspx

 

2. DBMS에 질의를 하는 방법?

시스템에서 DBMS에 질의하는 방법은 크게 3가지 정도 있습니다.

1. Server(혹은 Application)에서 SQL 문을 완성하고 SQL Server로 질의하여 응답을 받는다.

2. MyBatis같은 프레임워크를 사용하여 SQL Server와 Communication을 한다.

3. 저장 프로시저를 사용한다.

 

 

대부분 "1. Server(혹은 Application)에서 SQL 문을 완성하고 SQL Server로 질의하여 응답을 받는다." 방법을 사용하여 DBMS에 SQL로 질의할 것으로 생각합니다.

 

다음은 Android에서 DBMS로 질의를 예로든 소스입니다.

 

 

create_table 변수에 문자열들을 더해서 최종적으로 질의할 SQL 구문을 만들고 onCreate메소드에 있는 db.execSQL 메소드를 통해 완성한 SQL을 질의하고 응답받습니다.

 

이런 식으로 Application 단에서 String을 조합하여 질의하거나 다음과 같이 라이브러리에서 제공하는 객체를 통하여 질의할 수 있습니다.

 

 

위 소스는 Android에서 제공하는 ContentValues를 이용하여 DBMS에 INSERT 질의를 하는 방법입니다.

사용자는 전체 SQL 문을 알 필요가 없지만, 해당 함수가 실행된다면 Android 단에서 최종적으로 적절한 질의문을 만들어 DBMS에 질의할 것입니다. 

 

 

"1. Server(혹은 Application)에서 SQL 문을 완성하고 SQL Server로 질의하여 응답을 받는다." 방법을 사용할 경우 문제점이 많습니다.

쿼리가 복잡하다면 소스코드의 가독성이 떨어지며 create_table 변수를 만드는 방식을 사용할 경우 SQL 인젝션등 보안에도 취약하게 됩니다.

또한, Application 단에서 질의를 최종적으로 만들어야 하므로 DBMS에 대한 질의가 많으면 부하가 걸릴 수 있습니다.

 

 

"2. MyBatis같은 프레임워크를 사용하여 SQL Server와 Communication을 한다." 방식을 사용할 경우 해당 문제점을 어느 정도 해결할 수 있습니다.

 

위 화면은 MyBatis에서 사용하는 XML 일부분입니다. MyBatis는 SQL문을 소스코드와는 별개인 XML 파일로 관리해서 전체적인 가독성 높아집니다. 또한, SQL 문이 소스코드에 종속되지 않기 때문에 재사용이 쉬우며 프레임워크 자체에서 제공하는 보안과 기타 편리한 기능을 제공 받을 수 있습니다.

 

하지만 MyBatis와 같은 프레임워크를 사용해도 Application 단에서 최종 질의를 생성하는 것은 같으므로 Application에 부하가 걸리는 것은 해결할 수 없습니다. 

 

 

저장 프로시저를 사용 할 경우 이런 문제점들을 해결할 수 있습니다.

 

 

3. 저장 프로시저의 이점

저장 프로시저를 사용할 경우 다음과 같은 이점이 있습니다.

 

1. 클라이언트의 부하가 줄어든다.

 - SQL Server에서 저장되어있는 프로시저를 호출하는 방식으로 질의를 수행하므로 클라이언트에서 SQL 문을 만들 필요가 없습니다.

(SQL Server 내부에서는 이미 컴파일된 저장 프로시저를 호출합니다)

 

2. 서버/클라이언트 네트워크 트래픽 감소

 - 저장 프로시저는 단일 코드로 호출 가능합니다. 전체 SQL 문을 DBMS로 전송하는 것에 비해 전체 트래픽이 많이 감소합니다.

 

3. 보안 강화

 - SQL Server에서는 저장 프로시저의 실행 권한을 사용자에게 개별 설정 할 수 있습니다. 

 - 전체 SQL 문을 전송할 필요가 없어 데이터베이스의 구조가 노출될 위험이 적습니다.

 - 프로시저에서 사용되는 매개변수의 변조를 통한 공격에도 대응할 수 있습니다.

 

4. 코드의 재사용

 - 한번 생성한 저장 프로시저를 필요할 때마다 호출하는 식으로 재사용이 편리합니다. 또한, 새로운 플랫폼이나 Application을 구축하더라도 DBMS에 저장되어있는 저장 프로시저를 재사용하면 되므로 질의문을 만들 필요가 없습니다.

 

4. 저장 프로시저 만들어 보기

 

1. 새 저장 프로시저 생성

데이터베이스의 '프로그래밍 기능 > 저장 프로시저'에서 우클릭 후 '새 저장 프로시저'메뉴를 클릭합니다.

 

2. 스크립트를 수정

 

자신이 원하는 기능을 수행하도록 스크립트를 수정합니다.

위 소스는 이전 장에 만들었던 FILE TABLE의 전체 내용을 출력하는 'SELECT * FROM SSM_FILETABLE' 쿼리를 질의하는 저장 프로시저를 SSMProcedure 이름으로 생성하는 스크립트입니다.

 

3. 저장 프로시저 실행

 

 

exec '저장 프로시저 이름'

방식으로 저장 프로시저를 간단하게 호출할 수 있습니다.

프로시저 호출 결과 저장했던 SQL이 실행됨을 알 수 있습니다.

 

5. 매개 변수가 있는 저장 프로시저 만들어 보기

1. 스크립트 생성

위 소스코드는 @p1, @p2 두 개의 매개 변수를 입력받은 후, 매개 변수의 값을 출력하는 저장 프로시저의 생성 코드입니다.

 

2. 저장 프로시저 실행

 

실행 화면입니다. 저장 프로시저 명 뒤에 매개 변수의 이름과 변수의 값을 명시합니다.

실행 결과 매개변수의 값이 제대로 입력된 후 출력되었음을 알 수 있습니다.

 

이런 식으로 저장 프로시저에 값을 전달할 수 있습니다.

 

6. 마치며

SQL Server에서 제공하는 강력한 기능 중의 하나인 저장 프로시저의 특징에 대해 알아보았습니다. 여러 종류의 플랫폼으로 서비스를 제공하거나 플랫폼을 변경할 때 저장 프로시저를 사용하면 각각의 플랫폼마다 쿼리를 만들 필요없이 SQL Server의 저장 프로시저를 재사용할 수 있다는 점은 아주 큰 장점이라 생각합니다.

 

다음장에서 소개하고 있는 'T-SQL 프로그래밍' 기능 사용해 저장 프로시저로 만든다면 저장 프로시저의 사용 범위는 더 커질것 입니다.