구조화된 Stored Procedure 사용법

이번엔 Stored Procedure 를 보자. 대부분 조금이라도 속도를 빠르게 하기 위해서, 또는 작업의 능률을 위해서 Stored Procedure 를 쓴다. (Stored Procedure 의 자세한 내용은 깜찍이님이 세세히 설명)

실무에서 프로젝트를 많이 해보신 분은 아시겠지만 ,일반적으로 어떤 규칙을 정해놓고 작업에 들어간다. 변수 작성 규칙이라던지, 앞에서 설명한 ASP코드도 그 예라고 할수있다. 그럼 이제 Stored Procedure 는 어떤 식으로 사용하는지 보자.

다음의 그림을 보자.

위 그림에서 보면 spcommon.sql 에서 customer_exec.sql, board_exec.sql, admin_exec.sql 이 분기되어 나온다.  설명하자면 개발자는 무조건 spcommon 만을 호출하면 된다.  그리고 인자에 따라 customer_exec, board_exec, admin_exec 를 호출해서 실행한다.

자..일단 다음의 예제를 보자.

// spcommon.sql /* 1    작성일 : 2001년 3월 30일 2    작성자 : 판희완 3    설 명 : 모든 스토어드 프로시져를 통합하는 스토어드 프로시져 4 5    수 정 : 6    DataBase : 7 8    설명:모든 프로시저를 통합하는 스토어드 프로시저로서 PROCID에 따라서 서버 프로시저를 부른다 9    이때, 파라미터가 이 통합프로시저와 맞지 않는 스토어드 프로시저는 여기서 부르지 않고 따로       개별적으로 존재한다. 10    PROCID와 개별적으로 존재하는 프로시저의 명명 규칙 11    앞의 세자리는 서브 시스템의 약자로서 알파벳으로 구성하고 다음 두자리는 숫자          마지막 세자리는 그 프로시저의 성격을 나타내는 약자로 구성 12 13    * 서브 시스템 코드 표준(앞의 세자리) 14    CUS : 고객과 관련 15    MSG : 메시지 관련 16    BRD : 게시판 관련 17 18    * 프로시저의 성격 표준 19    DEL : 레코드를 지우는 것 20    LST : SELECT 문을 시행하는 것 21    INS : 레코드를 삽입하는 것 22    RLE : 비지니스 로직을 표현하는 것 23    UPD : update 하는 것 24    TRA : 여러가지가 복합되어 TRANSACTION 처리가 필요한것 25 26    * ERROR 표준 27    에러는 리턴 코드로 처리한다. 28    0 : 수행 성공 29    1 : NO CASE = > 맞는 스토어드 프로시저가 없다. 30    99 : 일반적인 에러 (특수하지 않은 에러) 31 */ 33 34    set nocount on 35    go 36 37    if exists (select * from sysobjects where id = object_id(N'[dbo].[spcommon]') and < BR >             OBJECTPROPERTY(id, N'IsProcedure') = 1) 38    drop procedure [dbo].[spcommon] 39 40    go 41 42    create procedure spcommon 43 44    @procid varchar(8) , -- 프로시저 ID 45    @pwhere1 varchar(16) , -- 파라미터 1 46    @pwhere2 varchar(16) , -- 파라미터 2 47    @pwhere3 varchar(16) , -- 파라미터 3 48    @pwhere4 varchar(16) , -- 파라미터 4 49    @pwhere5 varchar(16) , -- 파라미터 5 50    @pwhere6 varchar(16) -- 파라미터 6 51 52    as 53 54    if (left(lower(ltrim(@procid)),3) = 'cus' ) -- 고객관리 관련 55    exec customer_exec @procid, @pwhere1, @pwhere2, @pwhere3, @pwhere4, @pwhere5,            @pwhere6  -- customer_exec 프로시저를 부름 : 고객관련 56 57    else if (left(lower(ltrim(@procid)),3) = 'brd' ) 58    exec board_exec @procid, @pwhere1, @pwhere2, @pwhere3, @pwhere4, @pwhere5,            @pwhere6 -- board_exec 프로시저를 부름 : 게시판관련 59 60    else -- 아무경우에도 속하지 않으면 61    begin 62        select 'ERROR', 'NOCASE COMMON' 63        return 1 64    end 65 66    GO 67 68    SET nocount off 69    GO

line 13부터 30까지는 임의로 정한 규칙이다.  line 13은 line 55와 line 58 에서 호출하는 storedprocedure 의 성격에 맞게 임의로 지정했다.

다음 예제를 보자.

'*----------------------------------------------------------------------------* sub get_Table1() set objRs = objCon.Execute("{Call SPCOMMON('cus00lst','','','','','','')}") end sub '*----------------------------------------------------------------------------*

지난 강좌에서 나온 get_Table1() 함수이다.  어떤 일이 발생할까?  storedprocedure spcommon 을 인자 'cus00lst'으로 호출한다. 그러면 line 55 에서 customer_exec를 인자 'cus00lst'으로 실행한다.

또 다음의 예제를 보자..

// customer_exec.sql 1    -- Proc-File-ID.: customer_exec.sql 2    -- Proc-Name.: 3    -- Description.: 1) 고객과 관련된 모든 수행을 가지고 있으며 SP SPCOMMON에 의해 불려진다. 4    -- 5    -- 6    -- Create.......: 7    -- Update.......: 8 9 10    if exists (select * from sysobjects where id = object_id(N'[dbo].[customer_exec]') and           OBJECTPROPERTY(id, N'IsProcedure') = 1) 11    drop procedure [dbo].[customer_exec] 12    go 13 14    set quoted_identifier off set ansi_nulls on 15    go 16 17    create procedure customer_exec @procid varchar(16), -- 프로시저 ID 18            @pwhere1 varchar(16), -- 파라미터 1 19            @pwhere2 varchar(16), -- 파라미터 2 20            @pwhere3 varchar(16), -- 파라미터 3 21            @pwhere4 varchar(16), -- 파라미터 4 22            @pwhere5 varchar(16), -- 파라미터 5 23            @pwhere6 varchar(16) -- 파라미터 6 24 25 as 26 27    if ( @procid = 'cus00lst' ) 28        begin 29            select au_lname ,-- 이름(성) 30              au_fname ,-- 이름 31              phone ,-- 전화번호 */              address -- 주소 33            from authors 34 35            where contract = @pwhere1 -- 사용자 ID 36        end 37 38    else if ( @procid = 'cus01lst' ) 39        begin 40            select city -- 도시 41            from authors 42 43            where au_id = @pwhere1 -- 사용자 ID 44    end 45 46 47    else -- 매치되는 모듈이 없을때.... 48 49        begin 50            select 'ERROR', 'NOCASE' 51            return 1 52        end 53 54 55    -- error 56 57    if @@error <> 0 58        return (99) -- 에러 59    else 60        return (0) -- 성공 61 62 63    go 64    set quoted_identifier off set ansi_nulls on 65    go

이제 지금까지의 흐름을 보자..

get_Table1() ----> spcommon ----> customer_exec ----> board_exec

마치 Tree 구조와 비슷하다.  이러한 방법으로 stored procedure 를 설계하면 프로젝트가 좀더 명료해지고 쉬워진다.