Stored Procedure

본 부록에서는 Stored Procedure를 사용하는 방법에 대해서 설명한다.

1. 개요

Stored procedure는 DBMS에서 DB 서버와 함께 저장되어 있는 연산(subroutine)을 말하며, 일반적으로 SQL로 작성된다. 특히, 복잡한 연산 수행을 Stored Procedure를 사용하여 작성하면 클라이언트 애플리케이션은 프러시저 호출을 통해 기능을 제공받을 수 있으며 프러시저가 수정되었을 때 동일한 변경사항을 적용받을 수 있다.

2. 일반적인 Stored Procedure 호출

Stored Procedure는 일반 룰을 이용하여 호출할 수 있으며 JDBC에서 정의된 프러시저 호출 문법을 사용한다.

프러시저 호출 문법의 예는 다음과 같다.

  • 반환 파라미터가 있는 경우

    {<return> = call <procedure-name>[<arg1>,<arg2>, ...]}

  • 반환 파라미터가 없는 경우

    {call <procedure-name>[<arg1>,<arg2>, ...]}

각 파라미터는 IN, IN OUT, OUT 타입 중 하나가 될 수 있으며, IN은 입력 매핑, IN OUT은 입출력 매핑, OUT은 출력 매핑이 가능하다. 각 타입은 [IN], [INOUT] , [OUT] Annotation으로 지정된다. Annotation은 콜론(:)의 바로 뒤에 지정한다.

  • IN 타입 변수의 경우

    입력 메시지 DTO의 특정 필드와 매핑되어야 하며 일반적인 입력 매핑 방법과 동일하게 왼쪽의 DTO 트 리에서 소스 필드를 드래그 앤드 드롭한 뒤 [IN] Annotation을 추가한다.

    다음은 설정 예이다.

    • 원래의 입력 매핑

      {call myproc(:/path)} Annotation

    • Annotation 추가 후

      {call myproc(:[IN]/path)}

  • IN OUT 타입의 경우

    IN 타입과 동일하게 드래그 앤드 드롭 후 [INOUT] Annotation을 추가한다. IN OUT 타입은 IN 타입과 달리 출력 매핑 화면을 통한 출력 매핑도 가능하다.

    다음은 설정 예이다.

    • 원래의 입력 매핑

      {call myproc(:/path)}

    • Annotation 추가 후

      {call myproc(:[INOUT]/path)}

  • OUT 타입인 경우

    입력 매핑이 없기 때문에 입력 DTO의 필드와 매핑을 할 수 없으므로 직접 전체 표현식(expression)을 입력한다. Annotation이 된 OUT 타입 변수는 출력 매핑 화면에서 지정된 레이블로 표현된다.

    {call myproc(:[OUT]myOutputVar)}
    {call :[OUT]myReturnVar := myproc()}

    OUT 타입 파라미터 중에서 매핑되지 않은 파라미터는 [SQL 타입] 버튼을 클릭해서 수동으로 적절한 타입을 지정해야 프러시저 호출이 가능하다.

3. ResultSet을 반환하는 Stored Procedure 호출

ResultSet을 반환하는 프러시저의 경우 다음과 같이 정의한다.

  1. [프러시저 결과값 맵핑] 버튼을 클릭해서 ReulstSet과 매핑을 정의한다.

    figure procedure resultset button
    Figure 1. 프러시저 결과값 맵핑 버튼

  2. 버튼을 클릭하면 나타나는 다음과 같은 대화상자에서 [Add] 버튼과 [Remove] 버튼을 클릭해서 ResultSet을 추가하고 제거할 수 있다.

    figure procedure resultset
    Figure 2. ResultSet 정의 대화 상자

    ResultSet 정의 방식은 DBMS의 지원 방식에 따라 크게 2가지 방법으로 나뉘어 진다. MS SQL 서버와 같이 프러시저가 곧바로 결과 집합을 반환하는 ResultSet을 직접 추가해야 한다. 하나의 프러시저가 복수의 ResultSet을 리턴하는 경우 해당 개수만큼 추가한다.

    ResultSet의 순서는 룰의 동작에 영향을 미친다. 즉, ResultSet을 리턴되는 순서대로 정의해야 한다.

  3. 'ResultSet List'에서 추가한 ResultSet을 선택한 뒤 'Selected resultset parameter' 항목의 [Add] 또는 [Remove] 버튼을 클릭해서 해당하는 ResultSet의 파라미터를 정의한다.

    다음은 ‘name’과 ‘id’라는 2개의 파라미터를 가지는 하나의 ResultSet을 반환하는 예이다.

    figure procedure resultset ex
    Figure 3. ResultSet을 추가한 예 주의

    ResultSet과 마찬가지로 파라미터도 이름이 아닌 순서가 중요하다. 즉, ResultSet을 생성하는 SQL 문이 select id, name from.. 과 같더라도, 이 화면에서 name, id의 순서로 정의하면 이후 매핑화면의 name 파라미터가 실제 Select 문의 id 파라미터에 대응되게 된다.

  4. 'ReulstSet List'의 [Mapping] 버튼을 클릭해서 매핑을 정의한다. 이때 매핑의 소스는 앞서 정의한 파라미터들이 되며 타겟은 룰의 출력 메시지가 된다. 매핑 정의 화면은 일반적인 매핑 화면과 동일하다. Oracle 및 Tmax Tibero는 ResultSet이 커서(Cursor) 타입의 OUT 파라미터로 넘겨지는 경우 직접 ResultSet을 추가하는 대신 [SQL 타입] 버튼을 통해 해당하는 OUT 파라미터를 CURSOR 타입으로 선언한다.

    figure db sql type
    Figure 4. SQL 타입 버튼

  5. 출력 파라미터를 CURSOR 타입으로 지정하면 다음과 같이 자동으로 ResultSet list에 추가된다. CURSOR 타입 파라미터의 경우 '(Unremovable)'이라고 표시되며 삭제가 불가능하다.

    figure db cursor ex
    Figure 5. 커서 타입으로 지정한 예
    figure db rcursor parameter
    Figure 6. 커서 타입 파라미터가 추가된 예

  6. 이후 앞서 설명한 것과 동일하게 ResultSet의 파라미터를 선언한 뒤 매핑한다.