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에 해당하는 내용을 보여주게 되는 것입니다.
작성한 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문에 의한 값을 받을 수 있도록 변수를 선언한 뒤 = 문자를 통해 값을 받을 수 있도록 하였습니다.
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. 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에 연결중인 사용자 목록을 표시합니다.
댓글 없음:
댓글 쓰기