Dreamer - I'm wendy~♥♥

<%

    'On Error Resume Next 구문은 에러가 발생해도 일단 에러부분을 무시하고 넘기게 됩니다.
    On Error Resume next
 
    Dim strConnect, dbCon, rs
    strConnect="DSN=test;uid=sa;pwd=1234;"

  
    SET dbCon=server.CreateObject("ADODB.Connection")
    dbCon.Open strConnect
 
    SET rs = dbCon.execute("select uid, name, addr from TestTable where uid ='"& uid &"'")

    'Error 발생했을때 처리하게 되는 루틴입니다.
    if Err.Number <> 0 then
    response.Write "<h2>DB연결에 실패하였습니다.</h2>"
    Err.Clear
    response.end
    else
    rs.Close
    Set rs = nothing
    dbCon.Close
    Set dbCon = nothing
    response.Write "<h2>DB연결에 성공하였습니다.</h2>"
    end if

%>

<%
Function sendMail(strTo, strFrom, strSubject, strBody)
 On Error Resume Next
 
 Set iConf = Server.CreateObject("CDO.Configuration")
 Set Flds = iConf.Fields
 
  Flds("http://schemas.microsoft.com/cdo/configuration/smtpserver")               = "mail.xxxx.com"          ' 메일서버 IP
  Flds("http://schemas.microsoft.com/cdo/configuration/smtpserverport")          = 25                               ' 포트번호
  Flds("http://schemas.microsoft.com/cdo/configuration/sendusing")                = 2                                 ' cdoSendUsingPort
  Flds("http://schemas.microsoft.com/cdo/configuration/smtpaccountname")     = "master@xxxx.com"                      ' 계정이름
  Flds("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")     = """관리자"" <master@xxxx.com>"
  Flds("http://schemas.microsoft.com/cdo/configuration/smtpuserreplyemailaddress")= """관리자"" <master@xxxx.com>"
  Flds("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")       = 1 'cdoBasic
  Flds("http://schemas.microsoft.com/cdo/configuration/sendusername")          = "master@xxxx.com"            ' 계정ID
  Flds("http://schemas.microsoft.com/cdo/configuration/sendpassword")          = "xxxx"                         ' 비밀번호
  Flds.Update
 
 Set Flds = Nothing
 Set iMsg =  Server.CreateObject("CDO.Message")
 With iMsg
  .Configuration = iConf
  .To       = strTo           ' 받는넘
  .From     = strFrom           ' 보내는넘
  .Subject  = strSubject                             ' 제목     
  .HTMLBody = strBody                            ' 내용
  .Send
 End With
 
 Set iMsg = Nothing
 
 Set iConf = Nothing
 If Err Then
     Response.Clear
     Response.write "Error Number : " & Err.number & "<br>" & _
                "Error Source : " & Err.Source & "<br>" & _
                "Error Descryption : " & Err.Description
     Response.End
 Else
     'Response.Write "메일이 정상적으로 발송되었습니다"
 End If

End Function
%>

저장 프로시저

저장 프로시저 (Stored Procedure : 이하 SP)는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다. SP를 사용하면 일반적인 쿼리 문장을 사용하는 것보다 나은 성능상의 이점을 제공 받을 수 있습니다.

일례로 여러분이 쿼리 문장을 웹 페이지에서 실행하는 경우 이 쿼리 문장은 네트워크를 통해 데이터베이스로 전송되며 이 과정에서 데이터베이스 서버와의 연결이 필요합니다. 데이터베이스 서버와의 연결은 매우 비용이 많이 드는 작업이며 또한 쿼리 문장이 길면 길수록 네트워크를 통한 전송은 더욱 시간이 오래 걸릴 것입니다.

그러나 SP의 경우 저장 프로시저 이름과 필요한 몇몇 변수만을 데이터베이스 서버에 전송하게 되면 모든 처리는 데이터베이스 서버에서 발생하므로 추가적인 리소스의 소모가 없습니다.

뿐만 아니라 SP를 사용하면 여러분은 많은 쿼리를 한 번의 SP 호출로 실행할 수 있으며 트랜잭션을 적용하기에도 매우 용이한 환경을 제공 받게 됩니다.

저장 프로시저 만들기

SP는 다음과 같은 형식의 CREATE PROCEDURE 구문을 이용하여 생성할 수 있습니다.

CREATE PROC[EDURE] 저장 프로시저 이름
[저장 프로시저의 매개 변수 목록]
AS
 저장 프로시저 내에서 실행될 쿼리들

SP의 이름은 여러분이 임의로 지정해도 관계없지만 통상적으로 SQL Server가 기본적으로 제공하는 시스템 SP인 경우에는 sp_라는 접두어를 붙이며 사용자가 임의로 구현한 SP에 대해서는 통상적으로 up_라는 접두어를 붙입니다.

매개 변수가 없는 저장 프로시저 만들기

먼저 매개 변수가 없는 간단한 SP를 생성해 보겠습니다. 다음 쿼리를 쿼리 분석기에 입력해 보세요.

CREATE PROC up_loadPublishers
AS
 SELECT *
 FROM publishers

이 쿼리를 쿼리 분석기에서 실행하면 pubs 데이터베이스의 [저장 프로시저] 노드에 up_loadPublishers라는 새로운 SP가 생성됩니다. 이제 이 SP를 실행해 볼까요?

SP를 실행하기 위해서는 다음과 같은 구문을 사용합니다.

EXEC 실행할 저장 프로시저 이름

아래 그림은 방금 생성한 up_loadPublishers SP를 실행한 모습입니다.
 

매개 변수가 필요한 저장 프로시저 생성하기

앞서 작성했던 up_loadPublishers SP는 단순히 publishers 테이블의 모든 레코드를 리턴 하는 것이었습니다. 이번에는 매개 변수로 출판사 ID를 전달 받아 해당 ID를 갖는 출판사의 정보만을 출력하는 새로운 SP를 생성해 보겠습니다.

CREATE PROC up_findPublisher
 @pub_id varchar(4)
AS
 SELECT *
 FROM publishers
 WHERE pub_id = @pub_id

이 쿼리를 쿼리 분석기에서 실행하면 up_findPublisher라는 SP가 추가됩니다. @pub_id 변수는 매개 변수의 이름이며 이 변수는 varchar 타입의 4바이트 문자열을 저장할 수 있음을 의미합니다. 변수의 데이터 형은 SQL Server가 제공하는 어떠한 데이터 형도 지정될 수 있습니다.

이 SP는 다음과 같이 실행할 수 있습니다.

EXEC up_findPublisher 출판사 ID

아래 그림에 이 SP의 실행 결과가 나타나 있습니다.

리턴 값을 가지는 저장 프로시저 만들기

SP는 실행 후 그 결과를 정수 데이터를 이용하여 리턴 할 수 있습니다. 결과 값을 리턴 하기 위해서는 RETURN 키워드를 사용합니다. 다음 예제를 보겠습니다.

CREATE PROC up_getPublisherCount
AS
 DECLARE @count int
 SELECT @count = COUNT(pub_id)
 FROM publishers

 RETURN @count

이 쿼리를 쿼리 분석기에 입력하고 실행하면 up_getPublisherCount라는 SP가 추가됩니다. 이 쿼리에서 사용된 DECLARE 문장은 새로운 변수를 선언하기 위한 문장으로 DECLARE 문장이 사용되는 것 외에 변수의 이름 및 데이터 형식을 지정하는 것은 SP의 매개 변수와 동일합니다.

또 하나 처음 보는 구문은 SELECT 절에서 나타납니다. @count라는 변수에 SELECT 문장의 실행 결과 인 COUNT함수의 결과 값을 대입하기 위해 SELECT @count = COUNT(pub_id) 구문이 사용되었습니다.

이렇게 하면 사용자가 임의로 선언한 변수에 쿼리의 결과를 받아올 수 있습니다. 단, SELECT 문의 결과로 전달되는 레코드가 오직 한 개일 경우에만 가능합니다.

이제 이 SP를 실행하기 위해서 다음 쿼리를 작성합니다.

DECLARE @count int
EXEC @count = up_getPublisherCount
SELECT @count

이 쿼리의 실행 결과가 아래 그림에 나타나 있습니다.
 

이 예제는 publishers 테이블의 전체 레코드 개수를 리턴 하는 SP입니다.

OUTPUT 매개 변수를 사용하는 저장 프로시저 만들기

SP의 매개 변수는 앞서 살펴본 것과 같이 SP 내에 값을 전달하기 위한 것 외에 SP로부터 값을 전달 받기 위한 매개 변수가 존재하는데 이것을 OUTPUT 매개 변수라고 합니다. OUTPUT 매개 변수를 사용하여 publisher 테이블의 전체 레코드 개수를 구하는 SP를 다음과 같이 구현할 수 있습니다.

CREATE PROC up_getPublisherCountOutput
 @count int OUTPUT
AS

 SELECT @count = COUNT(pub_id)
 FROM publishers

이 SP는 다음과 같이 실행할 수 있습니다.

DECLARE @count int
EXEC up_getPublisherCountOutput @count OUTPUT
SELECT @count

이와 같이 OUTPUT 매개 변수를 전달할 때도 OUTPUT 키워드를 사용합니다. 이 SP의 실행 결과가 아래 그림에 나타나 있습니다.
 

저장 프로시저 수정하기

SP는 ALTER PROC 구문을 이용하여 손쉽게 수정할 수 있습니다. ALTER PROC 구문의 형식은 다음과 같습니다.

ALTER PROC 수정할 SP 이름
[프로시저 매개 변수 목록]
AS
실행할 쿼리 문장들

CREATE PROC 구문과 비교하면 CREATE가 ALTER로 변경된 것 외에는 별다른 차이점이 없습니다.

저장 프로시저 삭제하기

이미 생성된 SP를 삭제하려면 DROP PROC 구문을 사용합니다. 이 구문의 형식은 다음과 같습니다.

DROP PROC 삭제할 SP 이름

이상으로 SP에 대해 알아보았습니다. 더욱 유용한 SP의 예제는 나중에 예제를 통해 제공하도록 하겠습니다. 지금까지 SQL Server에서 사용할 수 있는 기본적인 SQL 문장들에 대해 알아보았습니다. 어떻게 보면 많은 분량을 할애한 것 같지만 또 어떻게 보면 여러 가지로 부족한 면이 보일 것입니다. 보다 자세한 내용은 관련 서적이나 SQL Server 온라인 설명서를 참고하시기 바랍니다.

출처 : bullog.net

========================================================================================

들어가며

: 자 이번시간에는 저장 프로시져(Stored Procedure)에 대해서 살펴 보도록 하겠습니다. 저장 프로시져에 대하여서는 SQL 에 대해서 조금만 관심을 가지고 살펴 보셨다면 한번쯤은 들어 보셨을 만한 용어입니다. 또한 지금까지는 들어 보시지 못했더라도 앞으로 자주 듣게 될 까다로운 녀석중에 하나입니다.

일단 영어 원문을 살펴 보도록 하죠 ^^; 영어 사전을 찾아 보시면 Stored 란 [저장, 저장소]라는 단어로 해석됩니다. 물론 다 아시는 단어이겠지만 김바람에게는 이런 단어 하나, 하나가 새롭군요 ^^; 그리고 Procedure 란 영어 사전에 [진행, 절차]라는 뜻을 가지고 있습니다. 이러한 단어를 곰곰히 새겨 보시면 그 뜻이나 저장 프로시져의 역할을 쉽게 이해 할 수 있습니다. 무언가 저장된 절차나 진행이라는 뜻이겠습니다.

실제로 저장 프로시져는 매우 복잡한 쿼리문을 서버에 저장해 두고 그 실행된 嘯解じ맛?돌려 줍니다. 그렇다면 언뜻 생각해도 복잡하면서도 매우 편리한 기능을 제공해 줄 것만 같은 저장 프로시져(이하 프로시져로 표기!) 에 대해서 살펴 보도록 하겠습니다.

1. 개요

(1) 저장 프로시져 정의

: 저장 프로시져는 서버에 저장되어 매우 복잡한 쿼리문을 처리하고 결과값을 돌려주는 일종의 프로그래밍이나 프로그래밍 인터페이스입니다. 이를 이용하면 매우 빠른 처리 결과와 보안등의 많은 장점을 얻을 수 있습니다. 저장 프로시져에 대한 온라인 도움말의 정의를 참고 해 보겠습니다.

즉 저장 프로시져란 서버에 프로그램을 저장하고 처리하게끔 한다는 것입니다. 이를 통해서 매우 복잡한 쿼리문에 대한 효과적인 사용과 모듈화하여 중복 사용이 가능하게한다는 점에서는 앞서 살펴 보았던 [뷰]와 상당히 비슷합니다.

이미 뷰에서도 여러 테이블들에 대한 쿼리문을 간단하게 가상 테이블로 만들어서 쿼리문을 획기적으로 줄이면서도 부가적으로 재 사용이 가능하다는 등의 많은 장점이 있던것을 확인할 수 있었습니다. (만약 뷰에 대한 이해가 되지 않거나 살펴 보지 않으신 분들은 절대적으로 뷰를 먼저 학습하시길 바랍니다.) 그런데 프로시져는 이러한 [뷰]를 사용함으로써 얻을 수 있던 모든 장점과 함께 매우 빠른 실행 속도와 각종 매개 변수를 사용할 수 있음으로 인해서 개발자들에게 보다 많은 프로그래밍의 자유도를 제공함니다.

살짝 들어보니 상당히 많은 장점이 있는 듯 하며 반드시 사용해야만 할것 같습니다. 그러나 프로시져를 사용한다는 것이 그리 쉽지만은 않습니다. 이것이 바로 장점에 이어지는 단점이겠죠. 더욱 빠른 결과값과 보다 자유롭고 복잡한 프로그래밍을 할 수 있지만 이로 인해서 더욱 복잡하고 어려워 진다는 것입니다. 더욱이 초보 관리자나 개발자들에게는 분명 부담이 되는 부분입니다.

그렇다고 꼭 사용해야만 하느냐 ? 면 그것은 아닙니다. 지금까지 배우신 T-SQL 문을 이용해서 쿼리를 하시면 원하시는 모든 처리가 가능합니다. 그러나 이제 원하는 작동만을 하게하는 수준에서 벗어나 조금더 빠르게 조금더 안전하게 ... 를 원하신다면 프로시져를 사용해야만 합니다. 프로시져를 사용함으로써 얻어지는 장점들이 너무 많기 때문에 반드시 사용해야만 하고 투자한 만큼의 효과도 보여 줍니다. 자 그럼 프로시져를 사용함으로써 얻어 지는 장, 단점을 살펴 보도록 하겠습니다.

(2) 저장 프로시져의 장단점

1) 장점

: 프로시져를 사용하여 얻어지는 장점은 거의 뷰를 사용함으로써 얻어지는 장점과 어찌 보면 같습니다. 그러나 뷰와 비교하여 좀더 다른 장점들을 찾는 다면 비교할 수 없이 빠른 실행속도와 네트워크 소통량의 감소, 보안등을 들 수 있습니다. 정리한 내용을 참고 하면 다음과 같습니다.

• 모듈별 프로그래밍을 허용합니다.

  프로시저를 한 번만 만들어 데이터베이스에 저장하면 프로그램이 여러 번 이 프로시저를 호출할 수 있습니다. 저장 프로시저는 데이터베이스 프로그래머가 만들며 프로그램 원본 코드와 상관 없이 수정이 가능합니다.

• 작업을 더 빨리 실행할 수 있습니다.

  Transact-SQL 코드가 많거나 작업이 반복되는 경우는, 저장 프로시저가 Transact-SQL 코드로 만든 일괄 처리보다 더 빨리 실행됩니다. 저장 프로시저는 만들어질 때 구문 분석되고 최적화됩니다. 프로시저가 실행된 다음 인-메모리 버전의 프로시저를 사용할 수 있습니다. 클라이언트가 실행할 때마다 반복해서 보내는 Transact-SQL 문은 SQL Server에서 실행될 때마다 컴파일되고 최적화됩니다.

• 네트워크 소통량을 줄일 수 있습니다.

  수백 줄의 Transact-SQL 코드가 필요한 작업을 네트워크로 보내지 않고 프로시저에서 코드를 실행하는 하나의 명령문을 통해 수행할 수 있습니다.

• 보안 메커니즘으로 사용할 수 있습니다.

  프로시저 문을 직접 실행할 수 있는 권한이 없는 사용자도 저장 프로시저를 실행할 수 있는 권한을 가질 수 있습니다.

이중에서 다른 것들은 모두 이해가 되지만 매우 빠른 작업 속도는 약간의 부연 설명을 필요로 합니다. 그렇다면 왜? 작업 속도에 차이를 보이게 되는가? 이것에 대한 해답은 일반적인 SQL 문과 Procedure 문이 서로 실행과정에 약간의 차이가 있기 때문입니다. 각각의 실행과정을 살펴 보면 다음과 같습니다.

위의 과정을 보면 두 과정이 비슷한듯 하지만 프로시져의 경우 반복 실행시 같은 실행 계획이 있는 경우는 컴파일을 하지않는다는 점이 상당히 다른점입니다. 즉 미리 컴파일된 소스라는 점에서 프로시져는 일반 쿼리문 보다 더 빠른 결과값을 보여줄 수 있는 한 이유입니다. 또한 미리 최적화된 소스를 기반으로 하기 때문에 최적화의 과정을 거치지 않기 때문에도 처음 실행시도 일반 쿼리문 보다 빠르게 수행됩니다.

2) 단점

: 항상 그렇듯이 장점과 상대적인 것이 바로 단점입니다. 단점으로는 프로그래밍이 필요하기 때문에 이에 대한 거부감이나 부담감, 그리고 복잡한 쿼리문에 대한 디버깅등이 어렵다는 점, 서버의 자원을 소모한다는 점등이 되겠습니다. 그러나 단점 보다는 장점의 효과가 크기 때문에 그리 영향을 주지는 않습니다.

(3) 저장 프로시져의 종류

1) 개요

: 프로시져에 대한 내용을 다루는 것은 이번이 처음인듯 하지만 사실 여러분들은 이마 앞단원들에게 계속적으로 프로시져를 사용해 왔습니다. 그것이 바로 시스템 저장 프로시져로 [sp_help] 나 [sp_who] 등의 시스템 저장 프로시져가 바로 그것들입니다. 이미 여러번 사용했던 프로시져들을 만나 보니 프로시져들의 종류가 궁금해 지는군요. 저장 프로시져의 종류에 대하여 살펴 보면 다음과 같습니다.

2) 시스템 저장 프로시져

: SQL 서버에 미리 저장되어져 사용되는 프로시져들이 주로 이에 해당하는 것으로 거의 대부분이 master 데이터베이스에 저장되어 있습니다. sp_help 등과 같이 sp_로 시작하는 거의 대분의 프로시져들이 이에 속합니다. 워낙에 많은 시스템 프로시져들이 있기 때문에 각각의 설명은 생략합니다. 자세한 내용은 온라인 도움말을 참고 하시기 바랍니다.

☞ 예제(시스템 저장 프로시져)

sp_help -- 실행결과 Name Owner Object_type ---------------------------- ------------------------ ----------- CHECK_CONSTRAINTS INFORMATION_SCHEMA view COLUMN_DOMAIN_USAGE INFORMATION_SCHEMA view COLUMN_PRIVILEGES INFORMATION_SCHEMA view COLUMNS INFORMATION_SCHEMA view CONSTRAINT_COLUMN_USAGE INFORMATION_SCHEMA view CONSTRAINT_TABLE_USAGE INFORMATION_SCHEMA view DOMAIN_CONSTRAINTS INFORMATION_SCHEMA view DOMAINS INFORMATION_SCHEMA view sp_who -- 결과 생략 sp_helptext sp_who -- 결과 생략 sp_depends -- 결과 생략

위의 예제는 가장 기본적인 시스템 저장 프로시져들의 예입니다. 직접 실행하여 보시기 바랍니다.

3) 사용자 저장 프로시져

: 말 그대로 사용자가 임의로 생성하여 사용하는 프로시져들을 지칭합니다.

4) 확장 저장 프로시져

: xp_ 로 시작되는 프로시져들로 DLL 로 구현되어 있는 외부 함수들이 이에 속합니다. SQL 에서 구현할 수 없는 것들을 구현하기 위해 사용합니다.

5) 원격 저장 프로시져

: 원격지의 SQL 서버에 저장되어 있는 프로시져를 불러서 사용할 수 도 있는데 이때의 프로시져를 원격 저장 프로시져라 합니다. 상대적인 입장에서의 개념이라 생각하시면 되겠습니다.

2. 저장 프로시져의 생성과 실행

(1) 개요

: 다른 SQL 의 개체들과 마찬가지로 프로시져도 간단하게 생성할 수 있습니다. 그러나 프로시져의 성격상 생성 구문이 상당히 복잡해 질 수 있습니다. 또한 일반적으로 QA 를 많이 이용하지만 EM을 통한 마법사 도구를 이용하여서도 생성할 수 있습니다. 그러면 간단한 생성의 예를 살펴 보도록 하겠습니다.

(2) QA 를 통한 생성

: 가장 일반적으로 QA를 이용한 프로시져의 생성이 일반적이라 할 수 있겠습니다. CREATE PROCEDUER 의 구문을 이용해서 생성가능하며 간단히 CREATE PROC 으로 생략해서 표기해도 되겠습니다. 뷰에서와 마찬가지로 프로시져를 생성함에 있어서 몇가지 규칙이 존재 하는데 이는 다음과 같습니다.

• CREATE PROCEDURE 문을 한 일괄 처리에서 다른 SQL 문과 결합할 수 없습니다.
  
  
• 저장 프로시저를 만들 수 있는 기본 권한은 데이터베이스 소유자에게 있습니다. 데이터베이스 소유자는 이 권한을 다른 사용자에게 부여할 수 있습니다.
  
  
• 저장 프로시저는 데이터베이스 개체이며, 저장 프로시저 이름은 식별자 규칙을 따라야 합니다.
  
  
• 현재 데이터베이스에서만 저장 프로시저를 만들 수 있습니다.
  
  
• 저장 프로시저 안에서 다른 데이터베이스 개체를 만들 수 있습니다. 저장 프로시저에서 만든 개체를 참조할 수 있습니다.
  
  
• 저장 프로시저 안에서 임시 테이블을 참조할 수 있습니다.
  
  
• 저장 프로시저 안에서 개인 임시 테이블을 만들면 저장 프로시저가 실행될 때만 임시 테이블이 존재합니다. 저장 프로시저를 끝내면 임시 테이블이 사라집니다.
  
  
• 다른 저장 프로시저를 호출하는 저장 프로시저를 실행하면 호출된 저장 프로시저가 첫 번째 저장 프로시저가 만든 임시 테이블을 포함한 모든 개체에 액세스할 수 있습니다.
  
  
• 원격 Microsoft® SQL Server™ 2000에서 변경 작업을 하는 원격 저장 프로시저를 실행하면 변경된 내용을 롤백할 수 없습니다. 원격 저장 프로시저는 트랜잭션에 포함되지 않습니다.
  
  
• 저장 프로시저에서 최대 2100개의 매개 변수를 사용할 수 있습니다

또한 일반적으로 sp_ 나 xp_ 로 시작되는 이름으로 프로시져를 생성하지 않는것이 좋습니다. 이와 같은 몇가지 규칙을 준수한다면 별 문제 없이 생성할 수 있습니다.

1) 기본 구문

: 프로시져를 생성하는 기본 구문은 역시 CREATE 로 시작되는 구문으로 기타 개체를 생성하는 구문과 별 다른 차이점이 없습니다. 기본 구문은 다음과 같습니다.

☞ 프로시져 기본 생성 구문

CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]

프로시져를 만들때의 최대 크기는 128MB입니다. 사용자 정의 저장 프로시저는 현재 데이터베이스에서만 만들 수 있습니다(항상 tempdb에 만들어지는 임시 프로시저는 제외). 또한 프로시져는 최대 32 수준까지 중첩되어 생성할 수 있습니다.

매개 변수에는 기본적으로 Null 이 허용됩니다. 그리고 매개변수는 최고 2100개를 사용할 수 있습니다. 그러나 이러한 규칙들을 벗어나서 생성하는 경우는 그다지 없을 것입니다. 예를 들어서 32 수준 이상의 프로시져를 중첩해 가면서 프로시져를 생성하는 경우는 거의 없겠죠.

2) 실습

: 자 ! 그럼 천리길도 한걸음 부터라고 아주 간단한 프로시져부터 천천히 실습을 해 보도록 하겠습니다. 결국은 프로시져도 T-SQL 구문이 사용되기 때문에 기본은 같습니다. 따라서 차근히 접근하신다면 그리 어렵지 않습니다.

먼저 아래와 같이 간단한 쿼리문을 생각해 볼 수 있습니다. pubs DB 상의 authors 테이블의 모든 정보를 가져오는 쿼리문을 생각한다면 다음과 같습니다.

☞ 예제(간단한 쿼리문)

USE pubs SELECT * FROM authors

위와 같이 간단히 쿼리문을 프로시져로 변환한다면 다음 예제와 같이 간단히 CREATE PROCEDURE 를 통해서 이름등만 지정해주면 간단하게 프로시져를 생성 할 수 있습니다.

☞ 예제(프로시져로 전환)

CREATE PROCEDURE myproc_test AS SELECT * FROM authors

프로시져를 실행할때는 아래와 같이 간단히 [EXEC 프로시져이름]으로 실행가능합니다.

☞ 예제(프로시져 실행)

EXEC myproc_test

자 이제는 약간 복잡한 쿼리문을 생각해봅시다. 역시나 그리 복잡하진 않지만 앞서의 예제가 너무나도 간단하기 때문에 ^^; 역시 pubs DB 상의 authors 테이블상에서 저자들의 이름와 지역 정보등을 목록화하여 지역구분으로 출력하는 쿼리문은 다음과 같습니다.

☞ 예제(약간 복잡한 쿼리문 예제)

USE pubs SELECT au_id, au_lname, au_fname, city, state FROM authors WHERE au_id is NOT NULL ORDER BY state DESC COMPUTE COUNT(au_id) BY state

이 역시 프로시져로 변환하여 생성한다면 다음과 같습니다. 쿼리문의 변화는 하나도 없습니다. 다만 CREATE PROCEDURE 구문정도만이 추가 됨을 알 수 있습니다. 이렇듯 자유롭게 T-SQL 쿼리문을 사용할 수 있음을 보여 드리기 위한 것이였는데... 아마도 지금쯤은 어떤 감? 이 오시지 않았을까 ?

☞ 예제(저장 프로시져로 생성)

CREATE PROCEDURE myproc_test1 AS SELECT au_id, au_lname, au_fname, city, state FROM authors WHERE au_id is NOT NULL ORDER BY state DESC COMPUTE COUNT(au_id) BY state

역시나 실행은 무척 간단합니다.

☞ 예제(myproc_test1 실행)

EXEC myproc_test1 -- 실행결과 au_id au_lname au_fname city state ----------- ---------- ---------- ---------------- ----- 899-46-2035 Ringer Anne Salt Lake City UT 998-72-3567 Ringer Albert Salt Lake City UT cnt =========== 2 -- 생략

다음으로 실습을 할 것은 온라인 도움말에 수록된 예제입니다. 온라인 도움말의 예제들은 무척이나 중요하기 때문에 필히 실습해 보시기 바랍니다. 아래의 프로시져는 4개의 테이블을 조인하여 모든 저자와 책 제목, 출판사등을 검색하는 예제입니다.

☞ 예제(단순 프로시저와 복합 SELECT 사용)

USE pubs 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

역시나 실행은 쉽습니다. 그러나 각종 실행 방법들이 있으니 필요에 따라서 실행 하시면 되겠습니다.(이 부분에 대해서는 조금뒤에서도 다시금 살펴 보도록 하겠습니다.)

☞ 예제(au_info_all 각종 실행방법)

EXECUTE au_info_all -- Or EXEC au_info_all -- 일괄 처리에서 첫 번째 명령문일 경우 다음과 같이 실행 au_info_all

(3) EM 을 통한 생성

: SQL 의 다른 부분들과 마찬가지로 프로시져 역시 EM 의 강력한 GUI 환경을 통한 작업을 하실 수 있습니다. 또한 마법사 도구를 이용하여 쉽게 생성할 수 도 있습니다. 그러나 김바람 개인적으로는 그다지 추천하지 않는 방법입니다. 적어도 프로시져에서만은 더욱이 그렇습니다. 그래도 간단히 EM 을 통한 프로시져 생성 방법을 기재 합니다. 참고 하세요.

1) 새 저장 프로시져 만들기

	앞서 프로시져 생성이 여러 규칙에서와 살펴 본 것과 같이 프로시져는 실행하고자 하는 DB 상에 생성해야만 합니다. 따라서 그림과 같이 생성하고자 하는 데이터베이스(pubs)를 선택하고 하위 목록중에서 [저장 프로시져]를 마우스 오른쪽 버튼으로 선택하여 매뉴에서 [새 저장 프로시져]를 선택합니다.
	[새 저장 프로시져]대화상자에서 [텍스트] 박스에 이미 기본적인 생성 구문이 기재된 상태로 출력되는 것을 확인할 수 있습니다. QA 에서와 마찬가지로 이곳에 생성하고자 하는 프로시져 구문을 직접 기재 하면 되겠습니다.
	위에서 예제로 사용했던 4개의 테이블을 조인하는 프로시져 구문을 이름만 다르게 하여 텍스트 박스에 붙여 넣기 하였습니다. 프로시져 구문을 생성하기 전에 항상 먼저 쿼리구문을 먼저 실행하여 보도록 하고, 이상이 없다면 이처럼 프로시져를 생성하도록 합니다. 아래의 [구문 검사]버튼을 클릭하여 구문의 이상 여부를 확인할 수 있습니다.
	프로시져를 생성한뒤에는 상단의 [사용 권한] 버튼이 활성화 됨으로 이를 통해서 옆의 그림과 같이 프로시져를 실행할 사용자 권한을 설정해 주어야만 하겠습니다. 이를 통해서 보안을 한층 강화할 수 도 있음은 당연하겠습니다.

2) 마법사

	[마법사]를 사용하는 방법은 이제 따로 말씀 드리지 않아도 될 정도로 많이 다루었기 때문에 쉽게 하실 수 있을 겁니다. EM 의 콘솔에서 매뉴 [도구 - 마법사 - 저장 프로시져 마법사]를 차례로 선택하시면 간단하게 그림과 같이 프로시져 만들기 마법사가 시작됩니다. 간단한 절차를 읽어 보신후 계속 진행하시면 되겠죠.
	[데이터베이스 선택] 대화상자에서는 프로시져를 만들 데이터베이스를 선택합니다. 프로시져를 실행할 DB 를 선택하야만 하겠습니다. 여기서는 예제로 pubs DB를 선택했습니다.
	[저장 프로시져 선택] 대화상자에서는 설명에서와 같이 작업을 하게될 개체를 선택하는데 여기서는 authors 테이블을 선택하고 [업데이트]에 체크를 하였습니다. 이런 식으로 간단하게 여러 테이블들도 선택가능하면 여러 작업을 수행하는 프로시져를 생성할 수 있습니다.
	마법사의 완료 화면이 출력되면 선택한 프로지져의 생성 이름이 자동으로 명명 됩니다. 보다 자세한 세부적인 프로그래밍을 위해서는 아래의 [편집] 버튼을 클릭합니다.
	마법사 완료 화면에서 [편집] 버튼을 클릭하면 다음 그림과 같이 [저장 프로시져 속성 편집] 대화 상자가 출력됩니다. 이미 앞서 선택했던 구성요소들을 기준으로 보다 자세하게 각 컬럼들까지 선택할 수 있도록 하고 있습니다. 이 대화상자에서도 보다 세부적으로 프로그래밍을 위해서는 SQL 쿼리문을 직접 편집 할 수 있도록 하단의 [SQL 편집] 버튼을 제공하고 있습니다.
	[SQL 편집] 버튼을 클릭하면 다음 그림과 같이 [저장 프로시져 SQL 편집] 대화상자가 출력되면서 실제 쿼리문을 살펴 볼 수 있습니다. 실제 사용은 기본적으로 간단한 쿼리문을 이처럼 마법사를 이용해서 만들어 두고 이 편집창을 통해서 수정하는 방법이 더욱 좋겠습니다. 역시 마법사를 이용한 작업이 때로는 더욱 버거롭게 생각 될 수 도 있겠습니다.
	생성된 프로시져를 확인 할 수 있습니다. 방금 만든 프로시져외에도 앞서 실습에서 생성한 프로시져들이 모두 등록되어 있군요.

(4) 실행

1) 실행 개요

: 프로시져의 실행은 무척이나 간편한 편입니다. EXECUTE 문을 사용하여 저장 프로시저를 실행할 수 있습니다. 저장 프로시저가 일괄 처리의 첫 번째 문이면 EXECUTE 키워드를 사용하지 않고 저장 프로시저를 실행할 수 있으며 다만 EXEC 키워드만 사용해도 무방합니다. 또한 실행 옵션으로 WITH RECOMPILE 이나 OUTPUT 매개 변수등을 함께 사용할 수 있습니다. 이는 뒤에서 자세히 다루도록 하겠습니다. 실행에 대한 기본 구문은 다음과 같습니다.

☞ 실행 기본 구문

저장 프로시저 실행 [ [ EXEC [ UTE ] ] { [ @return_status = ] { procedure_name [ ;number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [ ,...n ] [ WITH RECOMPILE ] 문자열 실행 EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )

각종 인수들은 역시 설명을 생략합니다. 자세한 내용은 온라인 도움말을 참고 하시면 되겠습니다.(필히 온라인 도움말을 참고 하시기 바랍니다. 실제로 많은 도움이되는 각종 예제와 옵션들에 대한 설명이 있습니다.)

2) 각종 실행 예제

: 실제로 프로시져를 실행하는 방법은 위에서 이미 살펴 본 것과 같이 기본적으로 3 가지 방법이 있겠습니다. 일단 간단히 프로시져를 실행하는 방법을 살펴 보면 다음과 같습니다.

☞ 프로시져 실행

-- 일반적인 프로시져 실행 EXECUTE myproc1 -- 또는 간단히 EXEC 키워드만 사용 EXEC myproc1 -- 또는 이 프로시저가 일괄 처리에서 첫 번째 명령문일 경우 프로시져 이름만 myproc1

이러한 경우외에 뒷부분에 다루겠지만 매개 변수가 있는 프로시져의 경우 다음과 같이 실행할 수 있습니다. ( 매개 변수에 대한 내용은 뒷 부분에 설명합니다.)

-- 매개변수 @test1 의 값을 test로 하여 실행 EXECUTE myproc1 test1 -- 명시적으로 매개변수와 값을 지정하여 실행 EXEC myproc1 @test1 = test1 -- 일괄 처리에서 첫 번째 명령문일 경우 myproc1 test1 -- 또는 myproc1 @test1 = test1

갑자기 변수를 사용하는 실행방법이 나와서 당황되는 면도 있지만 뒷부분에 다룰것이니 지금은 이렇게 변수를 넘길 수 있다는 것만 기억하시면 됩니다. 특히 ASP 등의 기타 프로그래밍을 해 보신분들의 경우는 쉽게 이해가 가실 겁니다. 물론 처음 접하시는 분들도 충분합니다.

3. 저장 프로시져 수정과 삭제

(1) 수정

: 프로시져의 수정 또한 상당히 간단합니다. 마치 뷰에서와 마찬가지로 ALTER 키워드를 사용하는점을 제외한다면 프로시져의 생성과 같습니다.

1) QA

: 기본적인 키워드는 ALTER PROCEDURE 입니다. 이하 다른 사항은 CREATE PROCEDURE 와 동일합니다. 먼저 위에서 생성했던 myproc_test 프로시져를 수정한다면 다음과 같습니다.

☞ 예제(프로시져 수정)

ALTER PROCEDURE myproc_test AS SELECT au_lname, au_fname, city FROM authors GO EXEC myproc_test -- 결과 au_lname au_fname city ------------------- -------------------- ---------- White Johnson Menlo Park Green Marjorie Oakland Carson Cheryl Berkeley O'Leary Michael San Jose Straight Dean Oakland Smith Meander Lawrence Bennet Abraham Berkeley -- 생략

위의 예제는 실행 결과까지 보여드리고 있지만 쉽게 이해 하실 수 있을겁니다. 중요한 부분은 ALTER PROCEDURE 이후는 CREATE PROCEDURE 와 같은 구성이라는 점입니다. 얼마든지 기존의 프로시져를 수정할 수 있을 것입니다. 프로시져를 삭제하고 새로 동일한 프로시져를 만드는 경우는 당연히 사용권한 역시 다시 설정해 주어야만 한다는 점을 기억해야만 합니다.

프로시져의 이름을 수정할때는 기존의 sp_rename 시스템 프로시져를 이용하면 간단히 수정 가능합니다. 이미 DB 생성, 관리 부분에서 다루었기 때문에 설명은 생략합니다.

2) EM

	EM 을 통한 프로시져의 수정은 보다 간편합니다. 그러나 역시 약간의 차이기는 합니다. 수정하고자 하는 프로시져를 그림과 같이 EM 에서 더블클릭하거나 또는 마우스 오른쪽 버튼으로 클릭하여 [등록정보]를 클릭하시면 되겠습니다.
	[저장 프로시져 속성]대화상자에서의 텍스트 박스에는 프로시져의 쿼리문이 직접 표기 됨으로 이를 수정하고자 하는 데로 직접 수정함으로써 간단히 프로시져를 수정할 수 있습니다.

(2) 삭제

: 일반적으로 개체들을 생성보다 삭제는 쉽습니다. 일반적으로 다른 개체들을 삭제 할 때 사용했던 DROP 키워드를 사용해서 간단하게 삭제할 수 있습니다. 다만 프로시져를 생성할때 프로시져는 다른 프로시져를 참조하여 중첩되게 생성할 수 있음으로 이점에 주의하여 참조 하는 프로시져가 없는지 확인하여 삭제해야만 하겠습니다.

1) QA

: 삭제할 프로시져는 간단히 DROP PROC[EDURE] 프로시져이름 의 방법으로 삭제가 가능합니다.

☞ 예제(프로시져 삭제)

DROP PROCEDURE myproc_test -- 또는 DROP PROC myproc_test

2) EM

: EM 을 통한 삭제는 직접 삭제하고자 하는 프로시져를 선택한뒤 오른쪽 마우스 매뉴에서 [삭제]를 선택하면 간단하게 삭제가 가능합니다.

4. 매개 변수 사용

(1) 개요

: 자! 이제 프로시져를 사용해야만 하는 중요한 이유이면서도 프로시져만이 제공하는 멋진 기능인 매개 변수를 사용하는 방법에 대해서 살펴 보도록 하겠습니다.

매개 변수를 사용하는 방법을 설명하기 전에 먼저 변수에 대해서 다시한번 상기해 봅시다. 우리는 앞서 단원들에서 이미 변수를 이용하는 T-SQL 을 살펴 보았었습니다. 변수는 말 그대로 고정된 값이 아니기 때문에 프로그래밍에서 자주 사용되며 보다 더 다양한 처리 결과를 나타내기 위해서 사용합니다. 기억이 잘 안나시는 분을 위해서 앞서 예제로 했던 변수를 사용하는 예제를 다시 한번 살펴 보겠습니다.

☞ 예제(다시 한번 살펴 보는 변수사용의 예)

USE pubs DECLARE @totalcount int SET @totalcount = (SELECT COUNT(*) FROM employee) SELECT @totalcount AS 사원수

음... 위의 예제가 기억나시나요? 아마도 모두 기억하실것이라 생각합니다. 총 사원의 수를 구하는 쿼리문인데 변수를 사용하고 있음을 알 수 있습니다. (아~~ 맞다... 이렇게 쓰는 거였지...^^;)

자 ! 이제 그럼 조금더 복잡한 변수를 사용한 예제를 다시 한번 살펴 보죠, 이 역시 앞서 변수를 설명하면서 실습했던 예제입니다.

☞ 예제(다시 한번 살펴 보는 2개 이상의 변수사용)

USE pubs DECLARE @pub_id char(4), @hire_date datetime SET @pub_id = '0877' SET @hire_date = '93/01/10' SELECT fname, lname FROM employee WHERE pub_id = @pub_id and hire_date >= @hire_date 결과 집합은 다음과 같습니다. fname lname -------------------- ------------------------------ Anabela Domingues Paul Henriot

두 개의 변수를 사용하여 좀더 자유롭게 원하는 정보를 검색하고 있습니다. 이러한 변수를 프로시져에서 그대로 사용할 수 있을 뿐만 아니라 OUTPUT 매개변수등을 사용하여 변수값을 돌려줄 수 있습니다. 이러한 매개 변수를 이용한 프로시져의 처리는 ASP 등에서 처리하는 것 보다 훨씬 좋은 처리결과를 보여주며 코딩의 고단함을 줄여 줄 수 있습니다. 자 이제 변수를 사용한 프로시져를 살펴 보도록 하겠습니다.

(2) 변수 사용

1) 변수 사용 기본 예제

: 먼저 변수를 사용한 프로시져의 처리결과 예제를 사용하기 위한 테이블을 다시 한번 살펴 보도록 하겠습니다.

SELECT * FROM sales

결과값은 생략합니다. sales 테이블의 정보들을 잘 기억해 두시고 이제 변수를 사용해서 원하는 데이터를 뽑아오는 프로시져를 생성하여 보도록 하겠습니다.

☞ 예제(한개의 변수를 사용하는 프로시져 생성)

CREATE PROC myproc_test3 --myproc_test3이라는 이름의 프로시져 생성 @mydec_qty int --변수 mydec_qty 지정과 함께 데이터형식을 int 로 지정 AS SELECT stor_id, ord_num, ord_date, qty, title_id FROM sales WHERE qty > @mydec_qty --변수와 같은 값을 가져오도록 조건을 지정한 쿼리문

위의 프로시져를 보시면 기존의 프로시져 생성에서 생성 구문중간에 [ @mydec_qty int ]의 변수가 추가 되었음과 쿼리문의 조건에 역시 변수를 지정하고 있음등을 제외하고는 기존의 쿼리문이나 프로시져 생성문과 동일합니다. 따로 설명을 드리지 않아도 이해를 할 수 있는 쉬운 구문임으로 따로 따로 구분해서 보시면 이해를 하실 수 있을 겁니다. 전체적으로는 sales 테이블에서 수량의 변수값을 입력하여 같은 수량을 가진 판매 기록의 정보를 가져오는 프로시져입니다. 주석문을 보시면 충분히 이해를 하실 수 있으라 생각합니다. 그러면 실제로 실행은 아래와 같습니다.

EXEC myproc_test3 20 --판매 수량의 변수값(@mydec_qty)를 20으로 하여 프로시져 실행 -- 결과 생략

결과 값을 보시면 상당히 흥미롭습니다. 이렇게 변수를 지정하여 프로시져를 실행하는 것이 일반적인 작업의 형태입니다. 좀더 이해를 돕기위해서 이번에는 같은 조건에 문자형 데이터형식의 변수값을 가지는 프로시져를 생성하여 실행하여 보도록 하겠습니다.

☞ 예제(문자형 데이터형식을 가지는 변수를 포함하는 프로시져)

CREATE PROC myproc_test4 @mydec_fname varchar(20) AS SELECT au_lname, au_fname, phone, address, city FROM authors WHERE au_fname = @mydec_fname GO EXEC myproc_test4 'Johnson' -- 결과 au_lname au_fname phone address city ---------- ---------- ------------ ----------------- ----------- White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park

이번에는 authors 테이블에서 저자의 fname을 검색하여 저자의 정보를 검색하는 프로시져였습니다. 역시 원하는 결과값을 얻어 낼 수 있으며, 현재는 잘 구분이 되지 않지만 일반 쿼리문과는 분명히 실행속도에서 차이가 있습니다. 실제 실무에서는 이런식의 코딩이 주로 이루어집니다.

2) 두개 이상의 변수 사용

이제는 두개 이상의 변수를 사용해서 프로시져를 생성하고 실행하여 보도록 하겠습니다. 물론 이보다 더욱 많은 변수를 지정하여 실행하는 것도 동일한 방법입니다. 그럼 예제를 보도록 하겠습니다.

☞ 예제(2개 이상의 변수 사용(온라인 도움말 예제))

다음은 4개의 테이블 조인에서 지정한 저자(성과 이름이 제공됨)와 책 제목, 출판사만 반환하는 저장 프로시저입니다. 이 저장 프로시저는 전달된 매개 변수와 정확히 일치하는 항목만 허용합니다. USE pubs GO 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

위의 예제는 상당히 복합적인 예제입니다. 지금까지 배웠던 거의 모든 내용들이 녹아있는? 멋진 예제이군요. 역시 온라인 도움말의 예제들은 이해도 쉽고 예제로써의 가치도 충분합니다. 다시 한번 온라인 도움말의 예제의 중요성을 강조합니다.

전체적으로는 테이블들을 조인하는 뷰와 같습니다. 4개의 테이블을 조인하여 그져 쿼리하는 것 보다 이처럼 프로시져로 변환하여 실행한다면 실행속도는 당연히 빨라집니다. 또한 변수를 사용해서 보다 다체롭게 쿼리를 할 수 있으며 동일한 프로시져를 변수만 바꾼다면 다시금 재사용할 수 있다는 장점등, 프로시져로의 장점을 많이 보여주고 있습니다. 그럼 실제 실행은 어떻게 해야할까요? 다음은 위의 프로시져를 실행하는 다양한 방법들입니다.

☞ 예제(프로시져 실행의 다양한 예)

EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' -- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

위의 실행 예제에서 중요한 것은 둘 이상의 변수를 사용할 때는 변수명을 정확하게 지정할 수 도 있으며, 아니면 변수의 순서대로 지정한다는 것으로 변수명을 정확히 지정하는 것이 좋겠습니다.

☞ 예제(와일드 카드가 있는 변수 사용)

USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' 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 LIKE @firstname AND au_lname LIKE @lastname

위의 예제는 좀더 색다른 예제로 와일드 카드를 사용하는 변수를 사용하고 있습니다. 당연히 보다 폭넓은 검색이 가능할 것입니다. 주로 게시판등의 검색기능에 사용한다면 훌륭하겠다는 것이 머리에 떠오르는군요. 이렇게 와일드 카드를 이용할 수 도 있음을 확인하시고 변수를 지정하지 않는다면 기본값으로 쿼리가 된다는 것도 유념해야할 사항입니다. 실제 사용의 예는 다음과 같습니다.

EXECUTE au_info2 'Hunter', 'Sheryl' -- Or EXECUTE au_info2 'H%', 'S%' -- Or EXEC au_info2

3) 데이터를 저장하는 프로시져

: 자 이번에는 프로시져를 이용한 데이터의 저장을 살펴 보도록 하겠습니다. 지금까지는 데이터를 검색하는 것만 다루어 보았는데 삽입 역시나 가능합니다. 먼저 데이터 삽입을 위해서 새로운 테이블을 하나 만들어 보도록 하겠습니다. 아래의 쿼리문을 실행하세요.

☞ 예제(예제를 위한 mytable1 생성)

USE pubs GO CREATE TABLE mytable1 ( mt_1 int , mt_2 varchar(30) )

자 이제 데이터를 삽입할 테이블도 생성되었고 실제로 삽입을 위한 쿼리문을 생성해 보도록 하겠습니다. 물론 프로시져에 변수를 사용하여 데이터를 입력하도록 하겠습니다. 실제 삽입이 예제는 아래를 참고 하세요.

☞ 예제(삽입 프로시져 myproc_insert 생성과 실행)

CREATE PROC myproc_insert @mydec_mt1 int , @mydec_mt2 varchar(30) AS INSERT INTO mytable1 (mt_1, mt_2) VALUES (@mydec_mt1, @mydec_mt2) GO EXEC myproc_insert 1, 'TEST1' EXEC myproc_insert @mydec_mt1 = 2, @mydec_mt2 = 'TEST2' SELECT * FROM mytable1 -- 결과 mt_1 mt_2 ----------- ----- 1 TEST1 2 TEST2

삽입역시 기본 쿼리에서 단지 변수를 주고 있는 것과 프로시져로 생성하는 것 외에는 그다지 다르지 않습니다. 실행에서 변수명을 지정해주는 것도 되고, 단순히 변수의 순서대로 변수를 지정하는 것도 된다는 점정도를 확인하시면 되겠습니다. 당연한 이야기지만 프로시져에서 저장하도록 지정하는 컬럼외에 테이블에 컬럼이 더 있다면 당연히 NULL 값을 허용하거나 Default 값이 지정되어 있어야만 하겠습니다.

4) 데이터를 업데이트하는 프로시져

: 이번에는 데이터를 업데이트하는 프로시져를 생성하여 보도록 하겠습니다. 역시나 기본 쿼리문은 기본적인 업데이트 쿼리문에서 크게 다르지 않습니다. 다만 변수와 프로시져 생성 명령정도가 더 있다는 것 정도...뿐 입니다. 따라서 언제나 기본이 중요하다는 것이 다시한번 실감되는 부분입니다.

☞ 예제(업데이트 프로시져 myproc_update 생성)

CREATE PROC myproc_update @mydec_mt1 int , @mydec_mt2 varchar(30) AS UPDATE mytable1 SET mt_2 = @mydec_mt2 WHERE mt_1 = @mydec_mt1

실제로 프로시져를 실행하는 방법은 다음과 같겠죠 ... 이제 더이상 설명하지 않으셔도 충분히 여러분 스스로 실행하실 수 있으리라 생각됩니다.

☞ 예제(myproc_update 실행과 결과)

EXEC myproc_update @mydec_mt1 = 2, @mydec_mt2 = 'updateTEST2' SELECT * FROM mytable1 -- 결과 mt_1 mt_2 ----------- ------------ 1 TEST1 2 updateTEST2

이와 같이 업데이트나 삽입, 검색등의 모든 쿼리가 프로시져로 가능합니다. 따라서 프로시져로 거의 모든 쿼리를 처리할 수 있습니다. 게시판이나 기타 웹 프로그램에 프로시져를 사용함으로써 얻어지는 장점이 부각될 수 있는것은 빠른 실행속도와 단순히 실행은 몇줄의 쿼리만을 처리함으로써 장문의 쿼리를 네트워크를 통해서 보내지 않아도 되는 네트워크 부하의 감소, 프로시져의 소스를 완전히 감출수 있음으로 해서 공동 작업시나 외부로 부터 보안을 한층 강화할 수 있는 듯의 많은 장점이 있겠습죠.

5. 기타

(1) 프로시져 정보 보기

: 자 이렇게 만들어둔 프로시져의 소스를 보고 싶은 때는 어떻게 해야 할 까요 ? 이미 뷰에서 보았던 sp_helptext 시스템 프로시져를 사용하면 간단하게 소스를 확인할 수 있습니다. 이하 사용법은 동일합니다.

☞ 예제(프로시져 정보 보기)

sp_helptext myproc_test1 -- 결과 Text ----------------------------------------------------------- CREATE PROCEDURE myproc_test1 AS SELECT au_id, au_lname, au_fname, city, state FROM authors WHERE au_id is NOT NULL ORDER BY state DESC COMPUTE COUNT(au_id) BY state

(2) 프로시져 암호화

: 프로시져를 만들어 두면 언제고 정보를 볼 수 있습니다. 이것은 또한 어떤 측면에서는 보안에 문제가 될 수 있음으로 인해서 다른 사람들에게는 소스를 보여주고 싶지 않는 경우에는 [뷰]에서와 마찬가지로 소스를 암호화 할 수 있습니다.

☞ 예제(WITH ENCRYPTION 옵션으로 프로시져 생성)

ALTER PROCEDURE myproc_test1 WITH ENCRYPTION AS SELECT au_id, au_lname, au_fname, city, state FROM authors WHERE au_id is NOT NULL ORDER BY state DESC COMPUTE COUNT(au_id) BY state

이렇게 수정하면서 암호화 하여도 되며, 프로시져를 처음 만드는 순간에도 WITH ENCRYPTION 옵션을 사용함으로써 암호화할 수 있습니다.

☞ 예제(암호화된 프로시져의 정보 보기)

sp_helptext myproc_test1 -- 결과 개체 주석을 암호화했습니다.

역시 정보보기를 하여도 아무런 소스를 볼 수 없습니다. 소유자도 소스를 볼 수 없기 때문에 암호화전에 원래의 소스를 필히 보관해 두어야만 하겠습니다.

(3) OUTPUT 매개 변수 사용

: OUTPUT 매개 변수에 대해서 살펴 보아야 겠습니다. OUTPUT 매개 변수는 다소 독특한 매개 변수로 OUTPUT 키워드를 사용하면 프로시져가 실행되고나서 사용된 매개 변수의 현재값을 반환해 줄 수 있습니다. 즉 테이블에 일정한 조건으로 업데이트가 실행된뒤 총 업데이트된 행수등을 알 수 있습니다.

다음의 예제는 pubs DB 상의 titles 테이블의 업데이트를 실행하고 총 업데이트된 행수를 다시금 반환하는 OUTPUT 의 예제입니다.

☞ 예제(OUTPUT 매개 변수 사용)

CREATE PROC myproc_output @r_out int OUTPUT AS SET NOCOUNT ON SELECT TOP 1 title_id, type, pub_id, price FROM titles UPDATE titles SET price = price * 2 SELECT @r_out = @@rowcount SELECT TOP 1 title_id, type, pub_id, price FROM titles SET NOCOUNT OFF DECLARE @rows int --변수 선언 EXEC myproc_output @rows OUTPUT SELECT @rows -- 결과 title_id type pub_id price -------- ------------ ------ --------------------- BU1032 business 1389 39.9800 title_id type pub_id price -------- ------------ ------ --------------------- BU1032 business 1389 79.9600 ----------- 18

결과값을 알 수 있듯이 총 18개 행이 업데이트 된것임을 다시금 반환해 주고 있습니다. 이러한 방식을 실제 웹코딩시에는 유용하게 활용할 수 있을 것입니다.

(4) RETURN 사용

: OUTPUT 매개 변수와 비슷한 이유로 사용하는 것으로 RETURN 이 있습니다. 역시나 변수의 현재값을 프로시져가 끝난뒤어 반환해 줍니다. 실제 사용의 예는 다음과 같습니다.

☞ 예제(RETURN 사용)

CREATE PROC myproc_return AS SET NOCOUNT ON SELECT TOP 1 title_id, type, pub_id, price FROM titles UPDATE titles set price = price * 2 RETURN @@rowcount SET NOCOUNT OFF DECLARE @rows int EXEC @rows = myproc_return SELECT @rows -- 결과 title_id type pub_id price -------- ------------ ------ --------------------- BU1032 business 1389 79.9600 ----------- 18

OUTPUT 과 같이 업데이트된 결과행수의 값을 출력하도록 해 보았습니다.

(5) WITH RECOMPILE

: 프로시져는 일반적으로 서버가 새로이 시작되고 처음 실행할때 최적화 됩니다. 또한 프로시져가 참조하는 원본 테이블의 데이터가 업데이트 될 때도 다시금 최적화 됩니다. 그러나 대량의 데이터가 업데이트되거나 프로시져의 성능을 향상하고자 새로이 인덱스를 추가하거나 하는 작업에서는 새로이 서버가 재 시작되기 전까지는 최적화가 이루어지지 않습니다. 따라서 이러한 경우는 새로이 컴파일을 해 줌으로써 최적화를 유도할 수 있습니다. 또한 넌 클러스터 색인을 참조하는 데이터의 검색시에 많은 데이터를 가져오는 넌 클러스터 색인의 경우 오히려 더욱 느릴 수 있습니다. 이러한 경우 넌 클러스터 색인을 참조하는 프로시져의 경우 매번 컴파일을 다시하게끔 해주는 것이 더욱 빠른 검색을 할 수 있습니다.

실제로 컴파일을 다시하게끔 하는 방법으로는 3 가지 방법이 있습니다.

☞ 예제(한번만 컴파일)

sp_recompile table명

이러한 경우는 table명에 해당하는 모든 프로시져는 다음에 실행될때 무조건 다시 한번 컴파일 됩니다. 테이블에 대량의 업데이트가 있는 경우등에 이 옵션을 사용하면 유용합니다.

☞ 예제(실행에 WHTH RECOMPILE 옵션)

EXEC myproc_test 변수값 WITH RECOMPILE

이러한 경우는 컴파일의 실행시에 WITH RECOMPILE 옵션을 사용할때만 새로이 컴파일 됩니다. 특정 프로시져만을 다시금 컴파일 하고자 할때 적합합니다.

☞ 예제(프로시져 생성시 WITH RECOMPILE 옵션)

CREATE PROC myproc_test @id int WITH RECOMPILE AS ...

이경우는 프로시져의 생성시 이미 WITH RECOMPILE 옵션을 사용함으로 인해서 항상 다시금 컴파일 됩니다. 그리 사용되지 않는 옵션입니다.

6. 실무 맛 보기

  사실 실무상의 많은 부분이 빠른 성능을 위해서 프로시져를 많이 사용합니다. 특히나 웹상에서 게시판이나 자료실등의 데이터베이스를 많이 사용하고 주로 읽는 작업이 많은 경우의 프로그램들에서는 프로시져를 사용함으로써 높은 효과를 얻을 수 있다는것은 생각만 해봐도 당연하겠습니다.

실무에서의 프로시져 사용은 이후 단원인 ASP 에서 프로시져를 사용하는 방법에서 추가적으로 설명을 하도록 하겠습니다.

 '로그 남기기기.....
 S_Date = GetCurtime24h() 'FormatDateTime(date,0)
 
 str = CTN & "|" & wflag & "|" & now
 
 'LogPath = Server.MapPath("D:\logTest")&"\"&S_Date&".txt"

 logName = replace(FormatDateTime(date,0),"-","")
 
 'S_Date = replace(S_Date,"-","")
 LogPath = "D:\sqldata\"&logName&"1111.log"
 
 Dim FileObject
 SET FileObject = Server.CreateObject("Scripting.FileSystemObject")
 Set Out= FileObject.OpenTextFile(LogPath, 8, TRUE)
 Out.WriteLine(str)
 Out.close
 SET Out = Nothing
 SET FileObject=Nothing