Procedure Cursor 를 이용한 ResultSet 반환

프로시저란 Transact SQL 의 집합으로 어떠한 처리를 순차적으로 일괄처리 작업하는데 주로 사용한다.

매우 빠르며 파라미터를 사용할 수 있다.

​

아래와 같은 형태로 생성하여 사용한다.

CREATE OR REPLACE PROCEDURE YHKIM_TEST ( 
  변수이름 IN 데이터타입, ... 
  변수이름 OUT 데이터 타입 ... 
) 
IS[AS] 변수, 상수 선언 
BEGIN 
실행 
[EXCEPTION 처리] 
END [PROCEDURE NAME]

[특징]

- 좋은 성능 (빠름)

- 모듈식 프로그래밍 가능

- 보안강화 (프로시저 마다의 권한 할당 등으로)

- 네트워크 전송량 감소 (긴 쿼리문장의 단순화)

- application 개발자 입장에서는 유지보수가 어렵다.

​

[Parameter]

- IN: 내부 프로그램에 제공

- OUT: 호출자에게 제공

프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고 프로시저 실행부에서 이 매개변수에 특정값 할당

- IN OUT: 입력과 동시에 출력용으로 사용할 수 있음

​

[Return by OUT]

- 보통 output parameter를 이용하여 결과를 받아서 사용한다.

​

[Return by Cursor]

- OUT 이 아닌 프로시저 내부에서 Cursor를 선언하여 해당 cursor를 리턴하는 경우도 있다.

- 아래 예제는 DB2

CREATE PROCEDURE DB2INST1.YHKIM_TEST() 
SPECIFIC DB2INST1.YHKIM_TEST LANGUAGE SQL 
DYNAMIC RESULT SETS 2 
MODIFIES SQL DATA 

BEGIN 

DECLARE c1 CURSOR WITH RETURN FOR 
SELECT * FROM MEMBER 
FETCH FIRST 1 ROWS ONLY ; 

DECLARE c2 CURSOR WITH RETURN FOR 
SELECT * FROM ACT FETCH FIRST 1 ROWS ONLY; 

OPEN c1; 
OPEN c2; 

END

위와 같이 CURSOR를 이용하여 return 할 경우 OUT 이 아닌 ResultSet에 결과를 담아 각각 Return 할 수 있다.

이렇게 Return 할 경우 Application 에서는 여러개의 ResultSet을 받아 처리해야 한다.

String sql = "CALL db2inst1.YHKIM_TEST4()"; 
CallableStatement callStmt = con.prepareCall(sql); 
boolean isResultSet = callStmt.execute(); 

while(true) { 
  if (isResultSet) { 
    ResultSet rs = callStmt.getResultSet(); 
    fetchAll(rs); 
    rs.close(); 
  } else { 
    if (callStmt.getUpdateCount() == -1) { 
      break; 
    } 
  } 
  isResultSet = callStmt.getMoreResults(); 
} 
callStmt.close();

getReultSet 호출 시 첫번째 cursor가 return 한 결과를 ResultSet 으로 받아오며

statement 에 getMoreResults() 를 이용하여 더 받을 수 있는 ResultSet이 있는지 확인한 후

N번째 cursor 가 반환하는 모든 결과를 확인할 수 있다.