[Java] DB 프로시저와 함수 호출

이 게시글은 이것이 자바다(저자 : 신용권, 임경균)의 책과 동영상 강의를 참고하여 개인적으로 정리하는 글임을 알립니다. 

---

프로시저와 함수

이 글에서는 Oracle DB에 적용되는 프로시저와 함수를 다룬다.

 

클라이언트 프로그램(DB가 서버이고, DB를 이용하는 프로그램이 클라이언트)에서 매개값과 함께 프로시저 또는 함수를 호출하면 DB 내부에서 일련의 SQL문을 실행하고, 실행 결과를 클라이언트 프로그램으로 돌려주는 역할을 한다.

즉, 프로그램 내부에서 SQL문의 작업내용이 처리되지 않고 DB에서 처리가 된다.

클라이언트에서 처리하기 부담스러운 처리를 서버로 돌릴 수 있게 된다.

 

• 프로시저 : 리턴하는 값이 있지만, 주로 리턴하지 않고 작업 처리를 할 때 사용
  예를 들어, 데이터 삽입, 삭제, 데이터 확인 등
• 함수 : 작업 처리를 하고 특정 값을 리턴할 때 사용
  예를 들어, 연산 작업을 하고 연산의 값을 리턴

 

JDBC에서 프로시저와 함수를 호출할 때는 CallableStatement를 사용한다.

프로시저와 함수의 매개변수화된 호출문을 작성하고 Connection의 prepareCall() 메소드로부터 CallableStatement객체를 얻을 수 있다.

프로시저와 함수의 매개변수화된 호출문은 조금 차이가 있다.

중괄호로 감싼 call문이라는 점은 동일하지만, 함수는 call 문의 시행 결과를 대입할 좌측 리턴값의 자리(?=)를 명시해야 한다.

 

프로시저명과 함수명의 괄호 안에 작성된 ?는 호출 시 필요한 매개값의 자리이다.

주의할 점은 프로시저도 리턴값과 유사한 OUT 타입의 매개변수를 가질 수 있기 때문에 괄호 안의 ?중 일부는 OUT값(리턴값)일 수 있다는 점이다.

 

PrepareCall() 메소드로 CallableStatement를 얻었다면 리턴 값에 해당하는 ?는 registerOutParameter() 메소드로 지정하고, 그 이외의 ? 는 호출 시 필요한 매개값으로 Setter 메소드를 사용해서 값을 지정해야 한다.

 

함수는 첫 번째 ?가 무조건 리턴값이다.

 

프로시저

String sql = "{call 프로시저명(?, ?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.setString(1, "값");
cstmt.setString(2, "값");
cstmt.registerOutParameter(3, 리턴타입); //세 번째 ?는 OUT값(리턴값)임을 지정

 

함수

String sql = "{? = call 함수명(?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, 리턴타입);//첫 번째 ?는 리턴값임을 지정
cstmt.setString(2, "값");
cstmt.setString(3, "값");

프로시저와 함수의 리턴 값을 받기 위해 registerOutParameter()에 들어가는 두 번째 매개값(리턴 타입)의 종류는 아래의 링크에서 자세히 확인할 수 있다.(공식 API Document)
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Types.html

 

?에 대한 설정이 끝나면 프로시저 또는 함수를 호출하기 위해 execute() 메소드를 아래와 같이 호출한다.

cstmt.execute();

 

호출 후에는 Getter 메소드로 리턴값을 얻을 수 있다. 리턴 타입이 정수라고 가정하면, 프로시저의 세 번째 ?의 리턴값과 함수의 리턴값은 아래와 같이 얻을 수 있다.

 

더 이상 CallableStatement를 사용하지 않는다면 close() 메소드로 사용했던 메모리를 해제해야 한다.

cstmt.close();

 

 

---

 

프로시저 호출

create or replace PROCEDURE user_create (     a_userid        IN  users.userid%TYPE,      a_username      IN  users.username%TYPE,     a_userpassword  IN  users.userpassword%TYPE,     a_userage       IN  users.userage%TYPE,     a_useremail     IN  users.useremail%TYPE,     a_rows          OUT PLS_INTEGER )  IS BEGIN     INSERT INTO users (userid, username, userpassword, userage, useremail)     VALUES (a_userid, a_username, a_userpassword,  a_userage, a_useremail);     a_rows := SQL%ROWCOUNT;     COMMIT; END;

DB에 위와 같이 프로시저가 정의되어 있다.

위 프로시저는 SQL문에 의해 실행된 명령(users 테이블 행 추가)의 수를 반환하는 프로시저이다.

즉, 프로그램에서 매개값으로 넘긴 값들을 INSERT문으로 행을 추가하고, 처리된 행의 수를 리턴하는 것이다.

IN 매개변수는 호출 시 필요한 매개값으로 사용되며, OUT 매개변수는 리턴값으로 사용된다.

 

위와 같이 작성된 프로시저를 호출하기 위해 아래와 같이 매개변수화된 호출문을 작성하고 CallableStatement를 얻는다.

String sql = "{call user_create(?, ?, ?, ?, ?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);

위 호출문에서 5번째 ?까지가 매개값이고 6번째 ?가 리턴값이다.

따라서 아래와 같이 ?의 값을 지정하고 리턴 타입을 지정한다.

cstmt.setString(1, "summer");
cstmt.setString(2, "한여름");
cstmt.setString(3, "12345");
cstmt.setInt(4, 26);
cstmt.setString(5, "summer@mycompany.com");
cstmt.registerOutParameter(6, Types.INTEGER);

프로시저와 함수의 리턴 값을 받기 위해 registerOutParameter()에 들어가는 두 번째 매개값(리턴 타입)의 종류는 아래의 링크에서 자세히 확인할 수 있다.(공식 API Document)
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Types.html

 

이제 user_create 프로시저를 실행하고, 아래와 같이 리턴값을 얻는다.

user_create 프로시저의 리턴값은 사용자 정보가 성공적으로 저장되었을 때 항상 1이 된다.

cstmt.execute();
int rows = cstmt.getInt(6); //6번째 ? 값 얻기

 

사용 예제(Oracle)

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class ProcedureCallExample {

	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 호출문 작성과 CallableStatement 얻기
			String sql = "{call user_create(?, ?, ?, ?, ?, ?)}";
			CallableStatement cstmt = conn.prepareCall(sql);
			
			//? 값 지정 및 리턴 타입 지정
			cstmt.setString(1, "summer");
			cstmt.setString(2, "한여름");
			cstmt.setString(3, "12345");
			cstmt.setInt(4, 26);
			cstmt.setString(5, "summer@mycompany.com");
			cstmt.registerOutParameter(6, Types.INTEGER);
			
			
			//함수 실행 및 리턴값 얻기
			cstmt.execute();
			int rows = cstmt.getInt(6);
			System.out.println("저장된 행 수 " + rows);
			
			//CallableStatement 닫기
			cstmt.close();
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}
/*
저장된 행 수 1
*/

 

---

 

함수 호출

create or replace FUNCTION user_login (     a_userid        users.userid%TYPE,      a_userpassword  users.userpassword%TYPE ) RETURN PLS_INTEGER IS     v_userpassword users.userpassword%TYPE;     v_result PLS_INTEGER; BEGIN     SELECT userpassword INTO v_userpassword     FROM users     WHERE userid = a_userid;     IF v_userpassword = a_userpassword THEN         RETURN 0;     ELSE         RETURN 1;     END IF; EXCEPTION     WHEN NO_DATA_FOUND THEN          RETURN 2; END;

DB에 함수는 위와 같이 선언되어 있다.

 

user_login()은 2개의 매개변수와 PLS_INTEGER 리턴 타입으로 구성되어 있다. 2개의 매개변수는 호출 시 값을 제공하고, 호출 후에는 정수 값을 리턴한다.

 

위 함수는 받은 매개값(ID와 PW) 을 DB에서 검색을 하고, ID와 PW가 일치하면 0을 리턴하고, 일치하지 않으면 1을 리턴하고, 해당 ID가 없으면 2를 리턴한다.

 

user_login() 함수를 호출하기 위해 아래와 같이 매개변수화된 호출문을 작성하고 CallableStatement를 얻는다.

String sql = "{? = call user_login(?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);

첫 번째 ?가 리턴값이고, 괄호 안에 있는 ?들이 매개값이다.

그래서 아래와 같이 ?의 값을 지정하고 리턴 타입을 지정한다.

프로시저와 함수의 리턴 값을 받기 위해 registerOutParameter()에 들어가는 두 번째 매개값(리턴 타입)의 종류는 아래의 링크에서 자세히 확인할 수 있다.(공식 API Document)
https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Types.html

cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "winter");
cstmt.setString(3, "12345");

 

user_login() 함수는 userid와 userpassword가 일치하면 0을, userpassword가 틀리면 1을, userid가 존재하지 않으면 2를 리턴한다.

cstmt.execute();
int result = cstmt.getInt(1);

 

사용 예제(Oracle)

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class FunctionCallExample {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			//JDBC Driver 등록
			Class.forName("oracle.jdbc.OracleDriver");
			
			//연결하기
			conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/orcl", 
				"java", 
				"oracle"
			);	
			
			//매개변수화된 호출문 작성과 CallableStatement 얻기
			String sql = "{? = call user_login(?, ?)}";
			CallableStatement cstmt = conn.prepareCall(sql);
			
			//? 값 지정 및 리턴 타입 지정
			cstmt.registerOutParameter(1, Types.INTEGER);
			cstmt.setString(2, "winter");
			cstmt.setString(3, "12345");
			
			//함수 실행 및 리턴값 얻기
			cstmt.execute();
			int result = cstmt.getInt(1);
			
			//CallableStatement 닫기
			cstmt.close();
			
			//로그인 결과(Switch Expressions 이용)
			String message = switch(result) {
				case 0 -> "로그인 성공";
				case 1 -> "비밀번호가 틀림";
				default -> "아이디가 존재하지 않음";
			};
			System.out.println(message);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(conn != null) {
				try { 
					//연결 끊기
					conn.close(); 
				} catch (SQLException e) {}
			}
		}
	}
}
/*
로그인 성공
*/