1. 저장 프로시져(Stored Procedure)란

앞에서 배운 뷰(View)는 검색에 사용되는 퀴리문을 미리 저장해두어 이를 쉽게 호출하여 사용함으로써 사용자의 편의를 준다고 했습니다. 저장 프로시져도 이와 비슷합니다. 단 저장 프로시져는 검색뿐만이 아니라 여타의 처리, 즉 Update와 Insert를 포함한 처리를 할 수 있습니다. 여러가지 퀴리문을 포함하여 일괄 처리하는 배치(Batch)를 아실겁니다.(사실 강좌에서는 언급한적이 없네요..) 이러한 배치가 하는 작업을 저장 프로시져는 하게 됩니다. 하지만 배치보다 많은 장점을 지니고 있습니다. 저장 프로시져의 장점을 나열한다면 다음과 같습니다.

o 저장 프로시져에 사용된 모든 구문이 미리 분석되어 최적화된 후 처음 수행시 메모리에 올려져 이후에 사용 될때는 메모리에 올려진 내용이 수행되므로 속도가 월등히 빠릅니다.
o 복잡한 퀴리문을 네트워크를 통하여 서버로 보낼 필요가 없이, 단지 저장 프로시져를 호출하는 간단한 내용만 서버로 전달되므로 네트워크 트래픽이 감소됩니다.
o 특정 테이블에 대한 권한이 없는 사용자 계정에 저장 프로시져를 수행 할 수 있는 권한을 주어 필요한 작업을 할 수있게 할 수 있으므로 보안성을 높일 수 있습니다.
o 특정 기능을 수행하는 저장 프로시져를 만들어 두면 여러 응용프로그램에서 이를 활용 할 수 있습니다. 즉, 특정처리를 위한 모듈화작업이 가능합니다. 모듈화가 되어 있으므로 응용프로그램 전체의 수정없이 해당 저장 프로시져만을 수정하여 원하는 기능 구현을 할수 있습니다.

저장 프로시져의 사용 예를 간단히 들어보도록 하겠습니다. 예를 들어 다음과 같이 급여의 13%를 보너스로 지급하기위한 내역을 조회하는 저장 프로시져 Calc_Bonus 가 있다고 하겠습니다.(저장 프로시져 만드는 방법은 다시 언급하겠습니다.)

CREATE PROC Calc_Bonus AS SELECT emp_code, emp_name, salary, CONVERT(int, salary * 0.13) AS bonus FROM Employee

많은 회계 프로그램에서는 Calc_Bonus를 호출하여 급여 작업을 할것입니다. 보너스를 20%로 조정한다면 위와 같이 Calc_Bonus의 계산 부분만을 '* 0.20'으로 고치면 되고, Calc_Bonus를 호출하는 실제 응용 프로그램은 전혀 수정할 필요가 없습니다. 예가 좀 그런가요?

2. 저장 프로시져의 종류

o 시스템 저장 프로시져 : sp_help, sp_who 처럼 'sp_'로 시작되는 프로시져로 SQL Server에의해 기본적으로 제공되는 프로시져입니다. 대부분 master 데이터베이스에 있으며, msdb 또는 distribution 데이터베이스에 있습니다. 사용자 정의 프로시져도 'sp_'를 붙여 만들 수 있으나 시스템 저장 프로시져와 구별이 힘들어지므로 'sp_' 사용은 피하는 것이 좋습니다.
o 사용자 저장 프로시져 : 사용자가 추가로 만든 일반적인 저장 프로시져입니다. 바로 우리가 만들 저장 프로시져입니다.
o 확장 저장 프로시져 : 'xp_'로 시작되며 DLL로 만들어진 외부 함수들이라 생각하시면 됩니다. SQL Server의 기능만을 가지고는 만들수 없는 기능을 구현하기 위해 C++ 과 같은 개발 툴로 만들어진 DLL들 입니다.
o 원격 저장프로시져 : 현재의 SQL Server가 아닌 다른 SQL Server의 저장 프로시져를 호출 할 수 있습니다. 이렇듯 원격으로 호출한다 하여 원격 저장 프로시져라 합니다.

3. 저장 프로시져 만들기

저장프로시져는 수행하려는 배치의 앞부분에 CREATE PROCEDURE를 붙이는 과정으로 만들 수 있습니다. 물론 CREATE PROCEDURE를 CREATE PROC로 줄여 사용 할 수도 있습니다.

앞에서 사용된 Calc_Bonus를 보면 다음과 같이 일반적인 SELECT문 앞에 CREATE PROC를 사용(진하게 표시한 부분)하여 저장 프로시져를 만들었음을 알 수 있습니다.

CREATE PROC Calc_Bonus AS SELECT emp_code, emp_name, salary, CONVERT(int, salary * 0.13) AS bonus FROM Employee

이와 같이 우선 배치가 원하는 기능을 수행하는지 충분히 확인하고 이상이 없으면 CREATE PROC를 앞에 붙여 저장 프로시져를 만드는 것리 오류를 줄이면서 효과적으로 저장 프로시져를 만들 수 있는 방법입니다.

4. 저장 프로시져의 형태

1) 매개변수 없는 저장 프로시져

매개변수(Input or Output)가 없이 단순한 검색만을(또는 처리를)하는 저장 프로시져를 가리킵니다. 앞에서 간단히 다루어본 Calc_Bonus 저장 프로시져는 매개변수를 받지 않고 단순한 결과만을 보여주고 있으므로 이에 속한다 할 수 있겠습니다. 다음의 예는 Books Online에서 가져온 것입니다.

USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO

o 저장 프로시져를 만들기전 이미 있는 저장 프로시져를 확인하여 지우는 부분이 앞에 있습니다. 이 부분을 참고하시면 많은 도움이 될 것입니다.
o 저장 프로시져도 하나의 개체이므로 sysobjects 테이블 안에 기록되어 있습니다. 이 정보를 이용하여 기존 존재 유무를 확인합니다.
o 저장 프로시져 삭제는 DROP PROCEDURE 를 이용합니다.
o au_info_all만 호출하면 복잡한 JOIN문이 포함된 검색을 쉽게 할 수 있게 됩니다.

아무 매개변수가 없는 au_info_all 저장 프로시져는 다음과 같이 저장 프로시져를 호출 할 수 있습니다.

EXEC au_info_all

o EXEC 대신에 EXECUTE를 사용하셔도 됩니다.

2) 입력 매개변수를 갖는 프로시져

특정 매개변수를 받아 처리하는 저장 프로시져를 만들 수 있습니다. 다음의 예 역시 Books Online에서 가져온 것임을 밝힙니다.

CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO

o lastname과 firstname을 매개변수로 받아들여 조건문(WHERE절)에 사용하고 있습니다.
o 변수앞에는 이와 같이 @를 붙여 사용합니다.
o 사용된 JOIN문이 어렵게 느껴진다면 무시하시기 바랍니다. 단지 매개변수의 사용 방법만 확인하시면 됩니다.

입력 매개변수가 있는 저장 프로시져는 다음과 같이 호출합니다.

EXEC au_info 'Dull', 'Ann'

3) 출력 매개변수를 갖는 프로시져

검색결과 또는 연산 결과를 호출한 쪽에 넘겨주는 저장 프로시져를 만들 수 있습니다. 저장 프로시져를 만들거나 이를 호출할 때 OUTPUT을 사용하게 됩니다. OUTPUT 매개변수가 사용된 아주 간단한 예를 보도록 하겠습니다.

CREATE PROC MyCalc_Sample @val01 int = 0, @val02 int = 0, @val03 int OUTPUT AS SET @val03 = @val01 + @val02

o 두개의 입력 매개변수와 하나의 출력 매개변수를 사용하였습니다.
o @val01 int = 0 과 같이 입력 매개변수를 선언할 때 매개변수가 생랙될 경우 처리할 기본 값을 정의 할 수 있습니다.
o 두 변수의 합을 갖는 @val03이 OUTPUT으로 선언되었으므로 이 값을 외부에서 호출한 곳에서 사용 할 수 있습니다.

다음은 위에서 만든 MyCalc_Sample 저장 프로시져는 사용한 예입니다.

DECLARE @sum int EXEC MyCalc_Sample 1, 2, @sum OUTPUT PRINT @sum

o @sum이 계산된 OUTPUT결과를 받게되므로 3이라는 값을 갖게 됩니다.
o 그래서 마지막 PRINT문은 3을 표시하게 됩니다.

다음의 예에서 @sum이 갖게되는 값은 얼마일까요?

DECLARE @sum int EXEC MyCalc_Sample @val01 = 100 , @val03 = @sum OUTPUT PRINT @sum

o 입력 매개변수 하나를 생략하였습니다.
o 입력 매개변수를 생략할 때는 위와 같이 변수명을 일일이 적어야 합니다.
o 임력 매개변수 @val02가 생략되었으므로 기본값 0이 @val02에 대입되며 100과 0의 합인 100이 @sum에 전달됩니다.

5. 저장 프로시져 변경(ALTER PROCEDURE)

전에 언급한적 있지요? CREATE에 의해 만들어진 것은 DROP에 의해 제거되고 ALTER에 의해 변경되어 진다고. 기억 안나시나요?(언급한 적이 없나???) 이미 만들어진 저장 프로시져는 ALTER PROCEDURE문을 이용하여 수정 할 수 있습니다. 물론 DROP을 이용하여 제거 한 후 다시 만들 수 있지만 이렇게 되면 그 저장 프로시져에 관련된 모든 권한을 다시 설정해야 합니다. 하지만 ALTER PROCEDURE문을 저장 프로시져의 내용만 바꿀 뿐 다른 것은 변화가 없습니다.

6. 저장 프로시져 암호화(WITH ENCRYPTION)

괜히 언급을 하나 싶기도 하네요. 우리가 만들었던 저장 프로시져나 시스템 저장 프로시져는 sp_helptext 저장 프로시져를 이용하여 소스를 확인 할 수 있습니다. 만일 이 소스를 확인하지 못하도록 숨기고 싶다면 이를 암호화 할 수 있습니다. 'WITH ENCRYPTION' 옵션을 이용하면 되는데, 한번 암호화된 소스는 원상복구시킬 방법이 없으므로 소스 자체(스크립트)를 잘 보관해두어야 합니다. 주의하시기 바랍니다.

----------------------------------------------------------------------------------------

출처 : http://www.sqlworld.pe.kr