Programming/Microsoft SQL Server
Store Procedure를 이용하면 여러행에 걸친 많은 Query들을 한데 모아 일괄적으로 실행시킬 수 있습니다. 또한 특정 Table에 접근 권한이 없는 사용자라 할지라도 Procedure를 이용하면 조회가 가능하게 할 수 있으므로 보안성면에서도 충분한 활용가치가 있고 뿐만 아니라 많은 Query를 Client측에서 직접 전송하기 보다는 Query를 Procedure에 저장하고 해당 Procedure를 호출하는 Query만 전송한다면 Network Traffic감소에도 상당한 도움을 줄 수 있습니다.

Store Procedure에도 여러 종류가 있으나 지금은 가장 일반적으로 사용되는 사용자 Store Procedure에 대해 알아보도록 하겠습니다.

1. Procedure 생성및 변경, 삭제

사용자 Sotre Procedure는 다음과 같은 방법을 통해 생성할 수 있습니다.

Create procedure (이름)
 @(매개변수) (형식)
As
(query)

<이름>은 생성하고자 하는 procedure명에 해당하며 @<매개변수>는 Procedure에서 Data를 처리할때 필요한 인수를 전달해 주도록 하는 변수입니다.(<형식>은 해당 매개변수의 Data type을 의미합니다.) 그리고 As하위에 실제 작동할 Query가 작성됩니다.

다음은 실제 Procedure를 구현한 예제 입니다.

Create procedure Employee
 @BusID Int
As
Select BusinessEntityID, NationalIDNumber, LoginID
From HumanResources.Employee
Where BusinessEntityID = @BusID;


Employee라는 이름으로 Procedure를 작성합니다. 해당 Procedure는 HumanResources.Employee Table을 조회하여 BusinessEntityID, NationalIDNumber, LoginID 열값을 반환하도록 하였습니다. 이때 사용자가 설정한 @BusID변수에 따라 다른 BusinessEntityID에 해당하는 내용을 보여주게 되는 것입니다.

 

참고로 프로시저 이름에 #이나 ##을 사용하는 경우 해당 저장프로시저는 임시저장프로시저가 되며 현재 DB가 아닌 tempDB에 프로시저가 저장됩니다.

프로시저가 동작시 오류사항을 처리하려면 Try Catch나 @@ERROR 함수를 사용할 수 있습니다.

 

오류 처리의 예를 확인해 보기 위해 간단한 테이블을 만들고

 

Create Table Tmp (
    idx Int Primary Key,
    product nChar(10),
    price Decimal(8, 3),
    memo VarChar(500)
);

 

관련 프로시저를 생성해 보겠습니다.

 

Create procedure InTmp
     @idx Int,
     @product nChar(10),
     @price Decimal(8, 3),
     @memo VarChar(500)
As
Declare @err Int;

Insert Into Tmp
Values(@idx, @product, @price, @memo);

Select @err = @@ERROR;

If @err > 0
Begin
Print 'Insert Error';
End

Return @err;
Go

 

프로시저는 임의로 만든 테이블에 인수로 전달된 값들을 Insert하도록 합니다. 이때 @@ERROR함수를 사용해 오류번호를 확인하고 값이 0이상이면 오류로 판단하게 됩니다.

 

Exec InTmp 1, 'car', 83.97, 'truck';

Exec InTmp 1, 'car', 83.97, 'truck';


프로시저를 위와 같이 같은 값을 인수로 해서 2번 연속으로 호출하도록 합니다. 처음에는 값이 잘 들어가겠지만 두번째에서는 Tmp테이블의 Primary Key제약조건으로 인해 오류가 발생할 것입니다.

 

프로시저 마지막에는 Return으로 오류번호를 반환하도록 했는데 이 번호를 확인하려면 프로시저호출시 값을 받을 수 있도록 지정해야 합니다.

 

Declare @err Int;
Exec @err = InTmp 1, 'car', 83.97, 'truck';
Select @err;

 

이번에는 Try Catch로 오류사항을 처리해 보겠습니다. 위에서 만든 프로시저를 아래와 같이 변경하고 Tmp테이블의 데이터는 삭제하도록 합니다.

 

Alter procedure InTmp
     @idx Int,
     @product nChar(10),
     @price Decimal(8, 3),
     @memo VarChar(500)
As
Declare @err Int;

Begin Try
      Insert Into Tmp
      Values(@idx, @product, @price, @memo);
End Try
Begin Catch
      Select ERROR_NUMBER();
      Select ERROR_PROCEDURE();
      Select ERROR_MESSAGE();
End Catch
Go

 

변경한 프로시저를 위에서와 동일한 방법으로 다시 호출하면 오류번호와 오류를 발생시킨 프로시저이름 그리고 관련 메세지를 출력할 것입니다.

 

일반적으로 작성한 Procedure는 다음과 같은 방법으로 값을 받을 변수를 직접 지정해서 호출할 수도 있습니다.

Exec Employee @BusID = 2;

Employee Procedure를 호출하여 HumanResources.Employee Table에서 BusinessEntityID값이 2인 것만을 가져오도록 하였습니다.


생성된 저장 procedure를 수정하려면 Alter를 이용합니다.

Alter procedure Employee
 @BusID Int   = 1,
 @ID  Char(20) = ''
As
Select BusinessEntityID, NationalIDNumber, LoginID, JobTitle
From HumanResources.Employee
Where BusinessEntityID = @BusID
And LoginID = @ID;

Alter를 통해 Sotre Procedure를 변경하였습니다. 이번에는 @ID변수를 추가하였으며 실제 조회하는 Query에서도 @ID조건을 적용하였습니다. 또한 Procedure내에서 @BusID Int를  = 1이라고 해준것은 사용자가 이 매개변수에 아무런 값도 지정하지 않았을 경우를 대비한 것입니다. 값이 지정되지 않으면 기본적으로 1값을 사용하게 됩니다.

Procedure가 제대로 변경되었는지 확인해 보겠습니다.

Exec Employee @ID = 'adventure-works\ken0';

@BusID에 아무런 값도 지정하지 않고 기본값을 사용하도록 하였습니다.


위 에서 Procedure변경시에 사용자가 직접 값을 전달해 줄 수 있는 매개변수를 지정하였습니다. 이와는 다르게 Procedure가 실행 후 해당 Procedure로 부터 특정값을 가져오게 하는 매개변수를 설정할 수도 있습니다.

Alter procedure Employee
 @BusID  Int   = 1,
 @ID   Char(20) = '',
 @DateTime DateTime Output
As
Select BusinessEntityID, NationalIDNumber, LoginID, JobTitle
From HumanResources.Employee
Where BusinessEntityID = @BusID
And LoginID = @ID;

Select @DateTime = getdate();


Employee Procedure를 @Date라는 반환매개변수를 가지도록 수정합니다. @Date는 두번째 Select문을 통해 Database의 현재 시간이 들어갈 것입니다.

변경된 Procedure를 호출하여 출력되는 값을 확인해 보도록 하겠습니다.

Declare @NowTime Datetime;
Exec Employee 1, 'adventure-works\ken0', @NowTime Output
Select @NowTime;

값을 받을 변수를 선언한뒤 Procedure호출 부분에서 '<변수> output'을 통해 값을 받을 수 있도록 하였습니다. 또한 Procedure호출시 매개변수가 정의된 순서대로 값이 지정되는 경우라면 매개변수명의 생략도 가능함을 보여주고 있습니다.


Procedure실행에서 값을 받는 경우라면 Return문도 사용할 수 있습니다.
단, Return문은 Procedure의 Query가 종료되는 시점에만 사용할 수 있으며(Query실행중 Return문을 만나면 실행이 종료됩니다.) 반환값은 정수값만 가질 수 있다는 특징이 있습니다. 대부분의 경우 Return문은 Query가 정상적으로 종료되었는지 비정상적으로 종료되었는지 확인하기 위한 flag로 많이 쓰입니다.

위에서 생성한 Employee Procedure를 Return문을 통해 값을 반환하도록 수정해 보겠습니다.

Alter procedure Employee
 @BusID  Int   = 1,
 @ID   Char(20) = ''
As
Select BusinessEntityID, NationalIDNumber, LoginID, JobTitle
From HumanResources.Employee
Where BusinessEntityID = @BusID
And LoginID = @ID;

Return 1;


Query실행 마지막에 Return문을 두어 1값을 반환하도록 하였습니다.

변경된 내용을 확인하기 위해 Procedure를 호출해 보겠습니다.

Declare @RtnValue Int;
Exec @RtnValue = Employee 1, 'adventure-works\ken0';
Select @RtnValue;

Return문에 의한 값을 받을 수 있도록 변수를 선언한 뒤 = 문자를 통해 값을 받을 수 있도록 하였습니다.


일반적인 값들 이외에 테이블형을 대상으로 하는 프로시저를 생성할 수도 있습니다. 다만 우선 이러한 프로시저를 생성하려면 프로시저에서 다뤄야할 테이블형의 데이터형식을 생성해야 합니다.

 

Create Type Tmp As Table (
     idx Int Primary Key,
    product nChar(10),
    price Decimal(8, 3),
    memo VarChar(500)
);

 

그리고 해당 형식을 받을 수 있는 프로시저를 생성합니다.

 

Create procedure InTmp
     @tmp Tmp Readonly
As 
     Select *
     From @tmp;
Go

 

그런데 자세히 보면 Tmp 테이블데이터형을 받을 수 있는 인자에 Readonly가 붙어 있습니다. Readonly는 반드시 있어야 하며 따라서 인자로 받은 테이블변수는 오로지 Select만이 가능합니다.

 

Procudre가 더이상 필요없는 경우에 Drop문을 이용하시면 원하는 Procedure를 삭제하실 수 있습니다.


 

Drop Procedure Employee;

2. Store Procedure 암호화

만일 Store Procedure의 내용을 다른 사람에게 노출하지 않으시려면 다음처럼 암호화 하시면 됩니다.

Create procedure Employee
 @BusID Int   = 1,
 @ID  Char(20) = ''
 With Encryption
As
Select BusinessEntityID, NationalIDNumber, LoginID, JobTitle
From HumanResources.Employee
Where BusinessEntityID = @BusID
And LoginID = @ID;

Procedure 생성부분에 Encryption option을 지정하여 암호화 하도록 합니다.

주의:
Procedure는 한번 암호화 하면 암호를 풀어서 볼 수 있는 방법이 없습니다. 따라서 Procedure를 암호화 하기전 내용을 따로 저장해 두어야 합니다.

3. Procedure의 자동실행

Procedure는 원한다면 SQL Server가 실행될때 sp_procoption을 통하여 자동적으로 실행 될 수 있도록 할 수 있습니다. 단, 이때 자동실행되는 Procedure는 Master Database에 저장되어야 하며 매개변수는 쓸 수 없습니다.

Procdure의 자동실행은 다음과 같은 방법으로 등록할 수 있습니다.

sp_procoption @procname = 'procedure 이름', @optionname = 'startup', @optionvalue = 'on 또는 off'

@procname은 자동실행할 Procedure의 이름이며 @optionvalue는 on/off로 자동실행 여부를 나타내도록 합니다.(@optionname은 statrup만 설정가능합니다.)

그런데 만일 위와 같이 설정해도 해당 Procedure가 자동으로 시작되지 않는다면 SQL Server를 다음과 같이 설정변경해야 합니다.

Execute sp_configure 'show advenced option', '1'
Reconfigure With Override;
Execute sp_configure 'scan for startup procs', '1'
Reconfigure With override;

 

4. Procedure의 관리

 

저장프로시저를 생성 후 처음 실행하게 되면 우선 저장프로시저에 사용된 개체(테이블등)의 이름을 확인합니다.(프로시저를 생성할때는 개체이름을 확인하지 않습니다.) 그리고 최적화를 거쳐 컴파일하고 프로시저의 실행계획을 메모리에 담아둔뒤 프로시저를 실행합니다.

 

두번째 실행 부터는 메모리에 존재하는 해당 프로시저의 실행계획을 곧장 수행하게 되는데 이 말은 개체확인이나 최적화를 더이상 수행하지 않는다는 것을 뜻합니다. 따라서 실행속도가 이전과는 다르게 빨라질 수 있는데 여기에는 약간의 함정이 존재합니다.

 

예를 들어 프로시저 내부의 쿼리에 대한 최적화중에는 테이블에 대한 인덱스를 탈지 아니면 무시할지의 여부도 포함됩니다. 만약 최초실행시에 테이블을 검색하는 데이터의 양이 너무많다면 SQL Server는 인덱스를 타지않는것이 더 나을것이라 판단하고 실행계획을 도출할 수 있습니다. 그런데 나중에 프로시저를 재 호출할때 조건에 따라 검색하는 데이터의 양이 작아져서 이럴때는 오히려 인덱스를 타야 성능상 더 이득이 발생해버리는 경우가 생길 수 있습니다. 하지만 프로시저는 이미 인덱스를 타지 않는 계획을 세워둔 상태이고 최적화는 다시 수행하지 않기 때문에 여전히 인덱스의 고려없이 데이터 검색을 수행할 것입니다.

 

프로시저를 호출할때 이러한 상황이 발생한다고 판단되면 프로시저에 대한 재컴파일 옵션을 추가해 최적화를 다시 수행하도록 해야 합니다.

 

Exec Employee @BusID = 2 With Recompile;

 

매번 프로시저를 호출시마다 재컴파일을 다시 수행할지 말지를 판단하기 힘들다면 아예 프로시저를 만들때 Recompile옵션을 줄 수도 있습니다.

 

Create procedure Employee
     @BusID Int

With Recompile
As 
    Select BusinessEntityID, NationalIDNumber, LoginID
    From HumanResources.Employee
    Where BusinessEntityID = @BusID;

 

특정 프로시저가 아니라 앞으로 실행하는 모든 프로시저에 대해 다시 재컴파일을 수행하려면 그냥 메모리(캐시)를 비워야 합니다.

 

Dbcc FreeProcCache;


5. System Store Procedure

System Store Procedure는 사용자의 편의를 위해 SQL Server자체에 이미 준비되어 있는 Procedure입니다. 이 중에서 자주쓰이는 몇가지만 살펴보도록 하겠습니다.

(1) sp_configure

Server설정에 관한 Procedure입니다.

(2) sp_help <이름>

이름에 지정된 Table이나 Procedure의 정보를 보여줍니다.

(3) sp_helpdb <db 명>

Database에 관한 정보를 얻고자 할때 쓰입니다.

(4) sp_helptext <이름>

Procedure 혹은 View의 Code를 보여 줍니다.

(5) sp_who(who2)

현재 SQL Server에 연결중인 사용자 목록을 표시합니다.

 

5. Procedure 확인

 

저장된 프로시저는 SSMS에서 sp_helptext 프로시저를 통해 내용을 확인해 볼 수 있습니다.

 

Exec sp_helptext InTmp;

 

혹은 아래 쿼리를 통해 DB에 등록된 전체 프로시저를 모두 확인해 볼 수 있습니다.

 

Select b.[name], a.[definition]
From sys.sql_modules As a Inner Join sys.objects As b
On a.object_id = b.object_id
And b.type = 'p';

0 0