1. DB에서의 SP

📖 개요

• 여러 SQL 문장들을 SQL 서버에서 한 단위로 저장해서 편리하게 호출할 수 있게 만든 것
• 영구저장모듈(Persistence Storage Module)

  데이터베이스에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 것(지속성)

📖 생성

✅ 기본 SP 생성

CREATE PROCEDURE {프로시저명} AS {저장할 SQL문장들}

💻 예제

CREATE PROCEDURE SimpleSP  //프로시저명
AS SELECT Id,Score FROM [Scores]  //저장할 SQL문장들

✅ 파라미터가 있는 SP 생성

• 저장 프로시저 안에서 사용할 입력 파라미터 및 출력 파라미터 사용 가능

💻 예제

-- 입력 파라미터 @class, @minScore
-- 출력 파라미터 @avergage
--
CREATE PROCEDURE sp_ClassAvg
   @class int,
   @minScore int,
   @average int output
AS
 DECLARE @sum int
 DECLARE @cnt int
 
 SELECT @sum=SUM(Score),@cnt=COUNT(Score)
 FROM [Scores]
 WHERE Score >= @minScore

 SET @average = @sum/@cnt

 INSERT INTO RunData
 VALUES (@class,@minScore,@avergage)
GO

• 프로시저명과 AS 절 사이에 @로 시작되는 파라미터명과 타입을 적어 콤마(,)로 구분하여 넣음
• 출력 파라미터의 경우 OUTPUT 키워드를 뒤에 붙임
• 출력 파라미터가 존재하는 경우, 프로시저 내에서 출력 파라미터에 값 할당

📖 실행

• EXECUTE(또는 EXEC)문이나 sp_executesql 사용
• Stored Procedure가 문장의 처음에 나오는 경우
  → Stored Procedure명만으로 직접 실행 가능
• 파라미터가 존재하는 경우
  • 파라미터를 순서대로 적어줌
  • Named Parameter로 [파라미터명=파라미터값]의 형식으로 적어줌

💻 예제

-- SimpleSP 호출
EXECUTE SimpleSP

-- sp_ClassAvg 호출
-- Ordered Parameters
DECLARE @avg int
EXEC sp_ClassAvg 10, 50, @avg output
SELECT @avg
GO

-- sp_ClassAvg 호출
-- Named Parameters
DECLARE @avg int
EXEC sp_ClassAvg @minScore=50, @class=10, @average=@avg output
SELECT @avg
GO

• sp_executesql은 EXECUTE보다 보안이나 성능면에서 더 향상된 기능 제공
  ex) SQL Injection → Parameterized Query를 실행하여 해결

📖 특징

✅ 장점

1. 네트워크 부하 감소

   하나의 요청으로 여러 SQL문 실행 가능

2. 처리시간 감소

   사전에 구문 분석 및 내부 중간 코드로 변환을 마쳐야 함

3. 데이터베이스의 데이터 참조 무결성 유지

   데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 앞뒤가 맞게 될 수 있음

4. JAVA 등의 호스트 언어와 SQL 문장이 확실하게 분리된 소스 코드의 전망이 좋아짐

5. 뛰어난 보수성

   웹사이트 등 운용 중에도 저장 프로시저의 교체에 의한 수정 가능

✅ 단점

1. 코드 자산으로서의 재사용성이 나쁨

   데이터베이스 제품에 대해 설명하는 구문 규칙이 SQL / PSM 표준과의 호환성이 낮음

2. 불필요한 수고와 변경 실수에 의한 장애 발생 가능성 존재

   비즈니스 로직의 일부로 사용하는 경우, 업무의 사양 변경 시 외부 응용 프로그램과 함께 저장 프로시저의 정의를 변경할 필요 존재

---

2. JPA에서의 SP

JPA 2.1부터 JPA 기준 쿼리 API를 사용하여 저장 프로시저 호출 지원

📖 매개변수 모드

• IN : 입력 매개변수
• OUT : 출력 매개변수
• INOUT : 입력 및 출력에 사용되는 매개변수
• REF_CURSOR : 결과 집합의 커서

📖 IN 및 OUT 매개변수가 있는 SP

💻 저장 프로시저

<!-- 2개의 IN 매개변수 를 취하여 합산하고 결과를 OUT 매개변수 로 반환 -->
CREATE OR REPLACE FUNCTION calculate(
    IN x double precision,
    IN y double precision,
    OUT sum double precision)
  RETURNS double precision AS
$BODY$
BEGIN
    sum = x + y;
END;
$BODY$
  LANGUAGE plpgsql

💻 @NamedStoredProcedureQuery

@NamedStoredProcedureQuery(
    name = "calculate", //쿼리 이름
    procedureName = "calculate", //데이터베이스의 저장 프로시저명
    parameters = { //저장 프로시저의 매개변수와 일치하는 매개변수 목록
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"), 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"), 
        @StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum")
    }
)

💻 호출

//정의된 @NamedStoredProcedureQuery에 대한 StoredProcedureQuery 얻음
StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");

• StoredProcedureQuery

  IN 매개변수의 값을 설정하고, 저장 프로시저를 실행하여 OUT 매개변수를 가져오는 데 필요한 메소드 제공

📖 REF_CURSOR가 있는 SP

• 저장 프로시저가 쿼리 결과를 반환할 때, 간단한 입력 및 출력 매개변수로는 충분하지 않은 경우가 많음

💻 저장 프로시저

CREATE OR REPLACE FUNCTION get_reviews(bookid bigint)
  RETURNS refcursor AS
$BODY$
    DECLARE
      reviews refcursor;           – Declare cursor variables                         
    BEGIN
      OPEN reviews FOR SELECT id, comment, rating, version, book_id FROM review WHERE book_id = bookId;
      RETURN reviews;
    END;
  $BODY$
  LANGUAGE plpgsql

💻 @NamedStoredProcedureQuery

@NamedStoredProcedureQuery(
    name = "getReviews", 
    procedureName = "get_reviews", 
    resultClasses = Review.class, //저장 프로시저 호출에 의해 반환된 결과 클래스 정의
    parameters = {  //위치 매개변수 매핑 사용
        @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class), 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class)
    }
)

💻 호출

List<Book> books = (List<Book>) this.em.createNamedStoredProcedureQuery("getBooks").getResultList();
for (Book b : books) {
    StoredProcedureQuery q = this.em.createNamedStoredProcedureQuery("getReviews");
    q.setParameter(2, b.getId());
    List<Review> reviews = q.getResultList();
    for (Review r : reviews) {
        // do something
    }
}

• 쿼리 결과를 얻기 위해 getResultList() 호출
  → 데이터베이스의 저장 프로시저를 호출하기 위해 이전에 사용한 execute() 메서드를 암시적으로 호출

📖 참고

• 예제로 배우는 SQL 프로그래밍
• [wikipedia] 저장 프로시저
• How to call stored procedures in JPA