[java] 이것이 자바다 ch20 데이터베이스 입출력 3(프로시저, 함수 호출, 트랜잭션)
bobo122023. 2. 14. 18:44
728x90
1. 프로시저와 함수 ▷ Oracle DB에 저장되는 PL/SQL 프로그램. 클라이언트 프로그램에서 매개값과 함께 프로시저 또는 함수를 호출하면 DB 내부에서 SQL 문을 실행하고, 실행 결과를 클라이언트 프로그램으로 돌려줌 ▷JDBC에서 프로시저와 함수를 호출 시 CallableStatement를 사용. 프로시저와 함수의 매개변수화된 호출문을 작성하고 Connection의 prepareCall() 메소드로부터 CallableStatement 객체를 얻음
▷ 프로시저도 리턴값과 유사한 OUT 타입의 매개변수를 가질 수 있기 때문에 괄호 안의 ?중 일부는 OUT값(리턴값)일 수 있음
▷prepareCall() 메소드로 CallableStatement을 얻으면 리턴값에 해당하는 ?는 registerOutParameter() 메소드로 지정하고, 그 이외의 ?는 호출 시 필요한 매개값으로 Setter 메소드를 사용해서 값을 지정
▷execute() 메소드로 프로시저 또는 함수 호출. Getter 메소드로 리턴값 얻음
◎ 프로시저 호출 ▷ IN 매개변수는 호출 시 필요한 매개값으로 사용되며, OUT 매개변수는 리턴값으로 사용
▷매개변수화된 호출문을 작성하고 CallableStatement를 얻음 ▷?의 값을 지정하고 리턴 타입을 지정 ▷프로시저를 실행하고 리턴값 얻음
◎ 함수 호출 ▷ user_login()은 2개의 매개변수와 PLS_INTEGER 리턴 타입으로 구성 ▷함수를 호출하기 위해 매개변수화된 호출문을 작성하고 CallableStatement를 얻음 ▷?의 값을 지정하고 리턴 타입을 지정 ▷user_login() 함수는 userid와 userpassword가 일치하면 0을, userpassword가 틀리면 1을, userid가 존재하지 않으면 2를 리턴
◎ 함수 호출 예제
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:@연결 주소:포트/SID",
"오라클 ID",
"오라클 PW"
);
// 매개변수화된 호출문 작성과 CallableStatement 얻기
String sql = "{? = call user_login(?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);
// ? 값 지정 및 리턴 타입 지정
// 0 : id/pw 맞음, 1 : pw 틀림, 2 : id 틀림
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "winter");
cstmt.setString(3, "12345");
// 함수 실행 및 리턴값 얻기
cstmt.execute();
int result = cstmt.getInt(1);
// CallableStatement 닫기
cstmt.close();
// 로그인 결과 1(Switch Expresstion 이용)
String message = switch(result) {
case 0 -> "로그인 성공";
case 1 -> "비밀번호가 틀림";
default -> "아이디가 존재하지 않음";
};
// 로그인 결과 2(Switch Expresstion 이용)
// String message = "";
//
// switch(result) {
// case 0: message += "로그인 성공"; break;
// case 1: message += "비밀번호가 틀림"; break;
// default: message += "아이디가 존재하지 않음";
// }
System.out.println(message);
} catch(Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
// 연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
}
}
// 출력 :
// 로그인 성공
◎ 트랜잭션 ▷ 기능 처리의 최소 단위. 하나의 기능은 여러 소작업들로 구성 ▷트랜잭션은 소작업들이 모두 성공하거나 실패해야 함
▷커밋은 내부 작업을 모두 성공 처리하고, 롤백은 실행 전으로 돌아간다는 의미에서 모두 실패 처리 ▷JDBC에서 트랜잭션을 제어 시 Connection의 setAutoCommit() 메소드로 자동 커밋 기능을 꺼야 함
◎ 트랜잭션 사용 예제
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TransactionExample {
public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@연결 주소:포트/SID",
"오라클 ID",
"오라클 PW"
);
// 자동 커밋 기능 끄기
conn.setAutoCommit(false);
// 출금 작업
String sql1 = "UPDATE accounts SET balance=balance-? Where ano=?";
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
pstmt1.setInt(1, 10000);
pstmt1.setString(2, "111-111-1111");
int rows1 = pstmt1.executeUpdate();
if(rows1 == 0) throw new Exception("출금되지 않았음");
pstmt1.close();
// 입금 작업
String sql2 = "UPDATE accounts SET balance=balance+? Where ano=?";
PreparedStatement pstmt2 = conn.prepareStatement(sql2);
pstmt2.setInt(1, 10000);
pstmt2.setString(2, "222-222-2222");
int rows2 = pstmt2.executeUpdate();
if(rows2 == 0) throw new Exception("입금되지 않았음");
pstmt2.close();
// 수동 커밋 : 모두 성공 처리
conn.commit();
System.out.println("계좌 이체 성공");
} catch(Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {}
System.out.println("계좌 이체 실패");
e.printStackTrace();
} finally {
if(conn != null) {
try {
// 원래대로 자동 커밋 기능 켜기
conn.setAutoCommit(true);
// 연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
}
}
// 출력 1 :
// 계좌 이체 성공
// 출력 2(임의로 오류 생성시) :
// 계좌 이체 실패
// java.lang.Exception: 입금되지 않았음
// at ch20.oracle.sec11.TransactionExample.main(TransactionExample.java:39)
1만원 출금, 입금의 결과를 확인할 수 있습니다.
프로시저, 함수 호출, 트랜잭션을 이용해서 java와 오라클을 연결해보는 예제들을 수행해보았습니다.
용어들을 많이 접하다보니 해당 용어는 기억은 나고 어떻게 사용하는지는 알지만 정확한 기능과 의미를 모르는 경우가 많네요,,
