데이터베이스 입출력-2(프로시저와 함수 호출).Java

2023.02.13 - [JAVA/Java 기초] - 데이터베이스 입출력(Database I/O).Java

데이터베이스 입출력(Database I/O).Java

 

이전글에서 계속됩니다.

 

---

 프로시저와 함수(Procedure & Function)

• Oracle DB에 저장되는 PL/SQL 프로그램.
• 클라이언트 프로그램에서 매개값과 함께 프로시저 또는 함수를 호출하면 DB 내부에서 SQL 문을 실행하고, 실행 결과를 클라이언트 프로그램으로 돌려줍니다.
• JDBC에서 프로시저와 함수를 호출 할때 CallableStatement를 사용합니다.
• 프로시저와 함수의 매개변수화된 호출문을 작성하고, Connection의 prepareCall() 메소드로부터 CallableStatement 객체를 얻습니다.

• 프로시저도 리턴값과 유사한 OUT타입의 매개변수를 가질 수 있기 때문에 괄호 안의 ' ? ' 중 일부는 OUT값(리턴값)일수 있습니다.
• prepareCall() 메소드로 CallableStatement를 얻으려면 리턴값에 해당하는 ' ? ' 는 registerOutParameter() 메소드로 지정하고, 그 이외의 ' ? ' 는 호출시 필요한 매개 값으로 Setter 메소드를 사용해서 값을 지정합니다.

 

• execute() 메소드로 프로시저 또는 함수 호출합니다.
• Getter 메소드로 리턴값을 얻습니다.

 

---

프로시저 호출

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

• 매개변수화된 호출문을 작성하고 CallableStatement를 얻습니다.
• ' ? ' 의 값을 지정하고 리턴 타입을 지정합니다.
• 프로시저를 실행하고 리턴값을 얻습니다.

---

package thisisjava;

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

public class ProcedureCallExam {

	public static void main(String[] args) {
	
		Connection conn = null;
		
		try {
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@192.168.111.220:1521/orcl",
					"java",
					"oracle"
					);
			
			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);
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) {
				try {
					conn.close();
				}catch (SQLException e) {}
			}
		}
	}
}

 

---

함수 호출

• user_login()은 2개의 매개변수와 PLS_INTEGER 리턴 타입으로 구성

• 함수를 호출하기 위해 매개변수화된 호출문을 작성하고 CallableStatement를 얻습니다.
• ' ? ' 의 값을 지정하고 리턴타입을 지정합니다.
• user_login() 함수는 userid와 userpassword가 일치하면 0을, userpassword가 틀리면 1을, useri가 존재하지 않으면 2를 리턴합니다.

---

package thisisjava;

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

public class FunctionCallExam {

	public static void main(String[] args) {

		Connection conn = null;
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@192.168.111.220:1521/orcl",
					"java",
					"oracle"
					);
			
			String sql = "{ ? = call user_login(?, ?) }";
			CallableStatement cstmt = conn.prepareCall(sql);
			
			cstmt.registerOutParameter(1, Types.INTEGER);	// 0 : id/pw 맞음, 1:pw 틀림, 2: id가 없음
			cstmt.setString(2, "winter");
			cstmt.setString(3, "12345");
			
			cstmt.execute();
			int result = cstmt.getInt(1);
			
			cstmt.close();
			
			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) {}
			}
		}
	}
}

 

---

트랜잭션

• 기능 처리의 최소 단위.
• 하나의 기능은 여러 소작업들로 구성
• 트랜잭션은 소작업들이 모두 성공하거나 실패해야 합니다.

• 커밋은 내부 작업을 모두 성공 처리하고, 롤백은 실행 전으로 돌아간다는 의미에서 모두 실패처리합니다.
• JDBC에서 트랜잭션을 제어시 Connection의 setAutoCommit()메소드로 자동 커밋 기능을 꺼야합니다.

 

---

package thisisjava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionExam {

	public static void main(String[] args) {

		Connection conn = null;
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@192.168.111.220:1521/orcl",
					"java",
					"oracle"
					);
			
			// 트랜잭션 시작
			// 자동 커밋 기능 끄기
			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) {}
			}
		}
	}
}