저장 프로시저(Stored Procedure)란?
저장 프로시저는 데이터베이스의 SQL 카탈로그에 미리 저장해 둔 서브루틴으로, 일련의 SQL 문장들을 하나의 이름 아래 묶어 놓은 것입니다. 관계형 데이터베이스에 접근할 수 있는 모든 애플리케이션(Java, Python, PHP 등)은 이렇게 저장된 프로시저를 자유롭게 호출하여 재사용할 수 있습니다.
저장 프로시저는 IN 파라미터, OUT 파라미터 또는 두 가지를 모두 포함할 수 있습니다. 또한 SELECT 문을 사용하는 경우 결과 집합(ResultSet)을 반환할 수 있으며, 필요에 따라 여러 개의 결과 집합을 한 번에 반환하는 것도 가능합니다.
예제: 샘플 테이블과 프로시저 생성
MySQL 데이터베이스에 다음과 같은 데이터를 가진 Dispatches 테이블이 있다고 가정해 보겠습니다.
+--------------+------------------+------------------+------------------+ | Product_Name | Date_Of_Dispatch | Time_Of_Dispatch | Location | +--------------+------------------+------------------+------------------+ | KeyBoard | 1970-01-19 | 08:51:36 | Hyderabad | | Earphones | 1970-01-19 | 05:54:28 | Vishakhapatnam | | Mouse | 1970-01-19 | 04:26:38 | Vijayawada | +--------------+------------------+------------------+------------------+
그리고 이 테이블에서 전체 데이터를 조회하기 위해 다음과 같이 myProcedure라는 이름의 저장 프로시저를 생성했다고 합시다.
Create procedure myProcedure () -> BEGIN -> SELECT * from Dispatches; -> END //
JDBC 프로그램으로 저장 프로시저 호출하기
JDBC에서는 CallableStatement 인터페이스를 사용하여 저장 프로시저를 호출합니다. 호출 구문은 {call 프로시저명()} 형태로 작성하며, prepareCall() 메서드로 객체를 생성한 뒤 실행하면 됩니다.
다음은 위에서 생성한 myProcedure를 JDBC 프로그램으로 호출하는 전체 예제 코드입니다.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallingProcedure {
public static void main(String args[]) throws SQLException {
// 드라이버 등록
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 데이터베이스 연결
String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
// CallableStatement 준비
CallableStatement cstmt = con.prepareCall("{call myProcedure()}");
// 결과 조회
ResultSet rs = cstmt.executeQuery();
while(rs.next()) {
System.out.println("Product Name: "+rs.getString("Product_Name"));
System.out.println("Date Of Dispatch: "+rs.getDate("Date_Of_Dispatch"));
System.out.println("Time Of Dispatch: "+rs.getTime("Time_Of_Dispatch"));
System.out.println("Location: "+rs.getString("Location"));
System.out.println();
}
}
}코드 동작 흐름 살펴보기
- 드라이버 등록: registerDriver() 메서드를 통해 MySQL JDBC 드라이버를 등록합니다.
- 연결 설정: getConnection() 메서드로 URL, 사용자 계정, 비밀번호를 지정하여 데이터베이스와 연결합니다.
- CallableStatement 생성: prepareCall() 메서드에 {call myProcedure()} 구문을 전달하여 프로시저 호출을 준비합니다.
- 실행 및 결과 처리: executeQuery()로 프로시저를 실행하고, ResultSet을 순회하며 각 컬럼 값을 출력합니다.
실행 결과
Connection established...... Product Name: KeyBoard Date of Dispatch: 1970-01-19 Time of Dispatch: 08:51:36 Location: Hyderabad Product Name: Earphones Date of Dispatch: 1970-01-19 Time of Dispatch: 05:54:28 Location: Vishakhapatnam Product Name: Mouse Date of Dispatch: 1970-01-19 Time of Dispatch: 04:26:38 Location: Vijayawada