저장된 일괄작업(Stored Procedure)

     

     View는 단순히 읽기 작업이 주를 이루는 데 반해서, Stored Procedure는 수정 및 갱신, 삭제 작업 등의 연산 작업이 주를 이루는 개체입니다.

    이렇게 한 번에 실행이 되는 T-SQL구문의 그룹을 일괄작업이라고 합니다.


    그럼 이러한 일괄작업이 어디에 저장이 될까요? 물론 MSSQL Server에 저장되어지겠죠.

    이렇게 일련의 T-SQL 구문이 SQL Server에 저장되어지는 것이 바로 Stored Procedure입니다.

     

    - 성능향상

     Stored Procedure내의 T-SQL들에 대해서 빠른 시간에 T-SQL을 처리를 할 수 있도록 T-SQL의 실행계획서를 작성하여 놓게 됩니다.

    일반적으로 T-SQL문장을 실행을 하게 되면, SQL Server는 Batch처리 단위로 T-SQL의 문법을 검사하고, 최적화하고, Compile을 하여 수행을 하게 됩니다. 반면에 Stored Procedure는 이러한 작업을 Stored Procedure가 만들어 질 때, 미리 하기 때문에, 다시 검사하고, 최적화하고, Compile하는 작업을 거치지 않는 다는 것입니다. 또한 실행을 하는 경우, 실행에 대한 계획도 작성을 하여 놓았기 때문에, 성능이 향상되는 효과도 얻게 되는 것입니다.

    - Network Traffic의 감소

     

    2. Defining Stored Procedures

     

    이러한 Stored Procedure 5개의 종류로 분류를 할 수 있습니다. 하나하나 설명을 드리도록 하겠습니다.

    - System Stored Procedure(sp_)

    먼저 System Stored Procedure입니다. 그림에서 보시듯이 System Stored Procedure mater Database에 저장이 되어져 있습니다. 또한 한가지 특징은 모두 sp_라는 접두어로 시작을 한다는 것입니다. 이는 SQL Server 내에서 system과 관련된 정보를 가지고 있는 Table로부터 정보를 반환하는 기능을 제공합니다. 또한 System에 대한 Option이나 다른 정보를 변경을 하는 기능 등, SQL Server의 전반적인 System과 관련된 기능을 제공을 하는 Stored Procedure System Stored Procedure라고 합니다.

    앞선 강좌들에서 실행을 하였던 sp_help sp_lock .. 모든 sp_로 시작을 하는 Stored Procedure System Stored Procedure라고 생각을 하시면 됩니다.

    - Local Stored Procedure

    Local Stored Procedure는 사용자 정의 Stored Procedure라고도 합니다. 말 그대로 사용자에 의해서 만들어진 Stored Procedure를 말합니다.

    - Temporary Stored Procedure

    Temporary Stored Procedure Temp Table과 같은 개념으로 생각을 하시면 됩니다. 이 역시 Temp Table과 같이 지역 Stored Procedure와 전역 Stored Procedure가 있으며, 각각 #(Pound) 하나와 두 개를 사용하여 지역, 전역을 분류하도록 하고 있습니다

    임시 Table과 마찬가지로 사용자의 연결이 끊어지게 되면, temp Database에서 삭제가 되어지고, 지역 Stored Procedure의 경우에는 해당 연결에서만 사용이 가능하고, 전역의 경우에는 다른 사용자도 사용을 할 수 있는 것을 말합니다.

    - Remote Stored Procedure

    이는 다른 Computer에 있는 SQL Server Stored Procedure를 호출하기 위해서 사용되는 Stored Procedure입니다. 뒤의 강좌에서 소개해드릴 Distribute 질의가 등장함으로써 사용되는 경우가 극히 드문 Stored Procedure입니다.

    - Extended Stored Procedure

    마지막으로 확장 Stored Procedure입니다. 확장 Stored Procedure SQL Server내에 저장되지 않고, DLL형식으로 저장되어 있으며, 그림에서 보시는 것과 같이 xp_ 접두어로 시작하는 Stored Procedure를 말합니다.

    이 역시 master Database에 저장이 되어 있습니다

     

    3. Creating Stored Procedures

     

    CREATE PROC[EDURE ] procedure_name [; number] 

       [ 

          {@parameter data_type} [VARYING] [=default] [OUTPUT ] 

       ] 

       [,….n] 

    [WITH 

       { 

          RECOMPILE 

          | ENCRYPTION 

          | RECOMPILEENCRYPTION 

       } 

    [FOR REPLICATION ] 

    AS 

       Sql_statement [… n]

     

     

    1. Guidelines for Creating Stored Procedures

     

    가급적이면 하나의 Stored Procedures에 하나의 업무를 수행하는 T-SQL문장을 작성하는 것이 좋습니다.

     

    Stored Procedure Stored Procedure 내에서 참조되는 모든 개체들은 dbo의 권한으로 하는 것이 좋습니다. 

    • 가급적이면 sp_, xp_ 등의 접두어는 사용하지 않는 것이 좋습니다. 이는 System Stored Procedure Extended Stored Procedure의 접두어이기 때문에 가급적이면 피하는 것이 좋습니다.

     

    2. Executing Stored Procedures

     

    이렇게 생성된 Stored Procedures EXEC 명령 또는 EXECUTE 명령을 통해서 수행이 가능합니다. 물론 앞선 강좌에서와 같이, Stored Procedure명 만을 명시하여도 수행이 가능합니다.

    Stored Procedure를 실행하는 기본적인 구문은 화면에서 보시는 바와 같습니다.

     

    -Syntax 

    > [EXEC[UTE ]] [database_name.][owner.]{procedure_name}

    - 예제

    EXEC UP_테이프

     

    3. Altering and Dropping Stored Procedures

     

    Stored Procedure 역시 SQL Server의 개체이기 때문에, Stored Procedure에 대한 수정이나 삭제 작업은 마찬가지로 ALTER 구문과 DROP 구문을 이용하게 됩니다. 수정 작업은 Stored Procedure를 정의하는 구문과 CREATE 구문을 제외하고는 모두 같습니다. 

     

    4. Using Input Parameters

    View의 정의 내역을 보기 위해서 sp_helptext라는 System Stored Procedure를 사용하였습니다.

    sp_helptext '임대품목_테이프_vw' 

    GO

     

    sp_helptext @objname = '임대품목_테이프_vw' 

    GO

     

     

    5. Executing Stored Procedures with Input Parameters

     

     

    6. Returning Values with Output Parameters

     

    7. Explicitly Recompiling Stored Procedures

     

    8. Initial Processing of Stored Procedures

     

     

    예제)

    1. 예제 1

    우선 간단한 Stored Procedure문을 보도록 하겠습니다. 테이프 Table로부터 모든 Data를 반환하는 문장을 먼저 작성해 보도록 하겠습니다.

     

    SELECT * 

    FROM 테이프

    , 굳이 이 문장을 실행하여, 제대로 수행이 되는지 살펴볼 필요는 없지만, 이 문장을 실행하여 결과를 반환 받아 보도록 하겠습니다. .

    문장이 제대로 수행되기 때문에, 여기에 Stored Procedure를 정의하는 CREATE PROC구문을 더하면, Stored Procedure를 생성하는 구문을 작성할 수 있습니다.

     

    CREATE PROC UP_테이프 

    AS 

    SELECT * 

    FROM 테이프

    여기서 UP_ User Procedure란 뜻의 약자입니다. 이 역시 개체에 대한 명명 규칙에 따라서 작성을 하시면 됩니다.

    이 문장을 실행하여 Stored Procedure를 생성하고, 실행하여 결과를 반환해 보도록 하겠습니다.

     

    EXEC UP_테이프

     

     

     

     

     

     

     

     

     

     

     

     


+ Recent posts