0207 국비 수업 JDBC transaction처리 procedure 작성 CallableStatement

*transaction 처리 (DB업무처리의 단위 – insert, update, delete로 구성)

- java.sql.Connection은 auto commit이 기본 설정
    Connection.setAutoCommit( true ); => 쿼리문 하나로 Transaction이 완료됨

 

  <n개의 쿼리일 경우>

 - 여러 개의 쿼리문이 (insert, update, delete) 하나의 Transaction을 구성해야하는 경우
   setAutoCommit( false )로 바꿔 설정하고, 쿼리 수행한 행수를 비교해서 commit() 또는 rollback()을 수행

 - Connection이 close가 된다면(정상 종료) commit을 수행한 후 연결이 끊어짐 

( 쿼리문 수행 중 Connection이 close 되면 안됨 – 여러 개의 쿼리문 중 하나의 쿼리가 실패하더라도 나머지는 
    commit이 되는 일관성의 문제 발생) >> 모든 작업이 실패 혹은 성공하는 일관성이 필요함

 

 

 

• 다른 method에서 Connection을 끊을려면 Field에 connection을 올려야함
• transaction() : int // 여러 개의 쿼리문으로 작업이 구성된 메소드, 실행된 행수를 반환
• useTransaction() : void // 실행된 행수를 받아 트랜잭션 실행(목표로 하는 행수와 비교해서) commit 혹은 rollback, DBMS 연결끊기

 

*여러 개의 insert가 하나의 Transaction으로 구성될 때에는 commit 또는 예외로 처리
*여러 개의 update나 delete가 하나의 Transaction으로 구성될 때에는 commit과 rollback을 처리

 

---

*Procedure

 - 자주 사용될 쿼리문을 미리 작성하여 저장해두고, 필요한 곳에서 실행하여 사용하기 위해 작성하는 것
 - PL(Procedural Language) /SQL문 중 하나
   ( 기본문법, 연산자, 제어문, function, procedure, trigger, package로 구성 )

• function: 쿼리문을 사용하지 않는 업무로직 중점
• procedure : 쿼리문을 사용하고 업무로직도 같이 구현
• trigger: 연쇄작용. 하나의 쿼리가 실행되면 자동으로 실행되는 것

 - 암시적 커서와 명시적 커서를 사용 할 수 있음

• 암시적 커서: insert,update,delete 쿼리문이 수행된 행수를 저장하고 있는 자동 생성되는 커서
                sql    %rowcount
  (암시적 커서) (속성)
• 명시적 커서: 여러 행의 결과가 나오는 select문을 저장하고 실행하기 위한, 개발자가 정의하는 커서

                                                                          select 함수명(컬럼명) 

- 실행기를 사용한 직접 실행을 수행  (함수는 간접 실행- 쿼리문에 넣어서 실행)
   exec [ execute ]  <- sql shell에서 직접 실행
  
  sql> exec 프로시저명(값,,,)

 - 반환형이 없음( out parameter를 사용 – 필요한 수 만큼 외부로 내보낼 수 있음)
 - 컴파일을 하면 (DD에 Procedure가 추가됨) 그 후 실행함 // user_procedures
 - 업무로직은 하나이면서 화면이 다양하게 만들어져야 할 때 procedure를 만들고 사용함

동일한 업무를 3번 만들어 코딩하면 업무가 바뀌었을 때 각각 3번 코드를 수정해야 함 > 업무로직을 DB쪽으로 옮긴다 >>> procedure를 각각 가져다가 쓸 수 있음 DB에서 하나의 업무로직을 가져다 쓰면 >> 뷰가 다양해도 모두 수정된 업무 로직을 받아 쓸 수 있음
Windows에서  사용자 편의성을 고려한 프로그램 1.Java 2.MFC 3.VB 4.Python	Web에서  사용자 편의성을 고려한 프로그램 1.JSP 2.ASP 3.PHP 4.Python	APP으로  사용자 편의성을 고려한 프로그램 1. Android 2. IOS 3. Hybrid App

---

*Procedure 작성

 

문법)

1.생성 :

create or replace procedure 프로시저명( 매개변수명 종류 데이터형,,, )
is                                    
  변수선언, record선언, table선언, cursor 선언  // record: VO와 비슷한 객체, 여러 값을 묶어서 한번에 저장

                                                                            // table: SQL의 테이블이 아닌 배열과 비슷한 객체
begin
  연산자, 제어문, 쿼리문, 커서의 사용

end;
/

2. 저장 : 파일명.sql

3. 컴파일 (sqlplus에 접속) : sql> @파일명

--cp_emp에 추가하는 procedure
create or replace procedure insert_cp_emp (empno number, ename varchar2, row_cnt out number, msg out varchar2)

is
  --변수, 커서 등 선언
 cnt number(1) := 0;

begin
 -- 연산자,제어문,쿼리문,커서 사용
 insert into cp_emp ( empno, ename)
 values(empno, ename)

 cnt : = sql%rowcount; -- 쿼리문이 실행된 행수를 저장하는 암시적 커서 사용 
 
 if cnt = 1 then
   msg := empno||'번 사원 정보 입력 성공';
   commit;
 end if;

 row_cnt := cnt; -- out parameter에 변수cnt값 저장

 exception
 when DUP_VAL_ON_INDEX then
   msg : = '중복키 존재';

 when others then
   msg := '알 수 없는 예외';

end;
/

---

(1,2,3은 오라클에서 / 4,5,6은 자바에서 수행 - CallableStatement를 써서)

4. 바인드 변수를 선언 ( 프로시저가 내보낸 out parameter에 존재하는 값을 저장하기 위해)

out parameter명과는 관련 없음    

var[iable] 변수명 데이터형(크기)

var r number

var m varchar2(100)

*자바에서 cstmt.registerOutParameter( 인덱스, Types의 constant  ); // java.sql.Types 클래스 사용 

 

5. 실행

sql> exec 프로시저명( 값,,,  : 바인드변수명,,, ) // 직접 실행 //out param에 들어갈 값은 :써서

exec insert_cp_emp (사원번호, '사원명', '직무', '연봉', 부서번호, :bind변수명, :bind변수명 )  ;

*자바에서 cstmt.execute();

6. 바인드변수에  저장된 값 출력 (out parameter에 값이 저장됨)

  print 바인드변수명

*자바에서 cstmt.getXxx(인덱스);

 

---

*CallableStatement

-자바에서 Procedure를 실행하기 위해 제공하는 interface

-PreparedStatement의 자식 인터페이스 (PreparedStatement와 Statement가 제공하는 메서드 모두 사용 가능)

 

사용법)

1. CallableStatement를 Connection에서 얻기

  CallableStatement cstmt = con.prepareCall(    "{ call 프로시저명 (?,?,?) }"   );

                                                                                                         bind변수의 사용

 

2. 바인드 변수에 값 할당 (procedure의 매개변수는 2개)

• in parameter: 외부 값을 procedure 내부로 전달하기/받기 위한 매개변수
  PreparedStatement의 값을 설정하는 코드 그대로
  -정수: cstmt.setInt(인덱스,값) // 부모 메서드를 씀, 부모의 매개변수명을 사용
  
  
  
• out parameter: procedure 내부의 값을 procedure 외부로 내보내기 위한 매개변수
  SQL의 바인드 변수가 필요함 (자바에서 선언해야함) > out parameter를 등록하여 사용
  cstmt.registerOutParameter( 인덱스, Types의 constant  );
  
   // Types: 특정 DBMS에 귀속되지 않는 공통 데이터형을 정의한 클래스
      Number는 없음(오라클에만 존재하는 데이터형) >> NUMERIC (상위의 데이터형)
      Varchar2는 오라클에만 존재 >> VARCHAR (상위의 데이터형)
  
  ex) Oracle의 Number가 out parameter인 경우
  cstmt.registerOutParameter(  인덱스,  Types.NUMERIC );
  
  ex) Oracle의 Varchar2가 out parameter인 경우
  cstmt.registerOutParameter(  인덱스,  Types.VARCHAR );
  
  

3. procedure 실행

cstmt.execute();

 

4. out parameter에 저장된 값을 받기

변수 = cstmt.getXxx(  인덱스  );

 

5. 변수를 사용 (출력,연산,,)

---

사용법)

3.프로시저를 호출하는 객체 얻기

  CallableStatement cstmt=con.prepareCall(“{ call 프로시저명(?,?,?, ? ,? ,? ,?)  }”);

  //in parameter 5개, out parameter 2개
  
  CallableStatement cstmt=con.prepareCall(“{ call insert_cp_emp(?,?,?, ? ,? ,? ,?) }”);

4. 바인드변수에 값 설정

•   in parameter ( 부모인 PreparedStatement의 method를 사용)
     cstmt.setInt(1, empno );
     cstmt.setString( 2, ename );
     cstmt.setInt(3, job);
     cstmt.setInt(4, sal );
     cstmt.setInt(5, deptno );
  
  
•   out parameter : oracle bind 변수가 필요( var 변수명 데이터형(크기 )
     cstmt.registerOutParameter( 6, Types.NUMERIC );
     cstmt.registerOutParameter( 7, Types.VARCHAR );

 

5. 프로시저 실행 ( 부모 인터페이스인 PreparedStatement의 실행 method를 호출)

  cstmt.execute();

 

 

6. Oracle의 bind변수 (out parameter) 에 저장된 값 받기 ( cstmt.getXxx( out parameter의 인덱스) )

  int cnt=cstmt.getInt(out paramaeter 인덱스);
  String msg=cstmt.getString(out paramaeter 인덱스);

   int row_cnt=cstmt.getInt(6);
   String msg=cstmt.getString(7); // 오라클에서 준비된 메시지 > view의 유연성이 떨어짐

 

public class UseInsertCpEmp {

	public ResultVO insertCpEmp(cpEmpVO ceVO) throws SQLException {
		// 매개변수의 간소화 위해 VO로 넣어줌
		//procedure 수행 후 반환되는 out parameter값들을 저장함
		ResultVO rVO = new ResultVO();

		DbConnection db = DbConnection.getInstance();
		Connection con = null;
		CallableStatement cstmt = null;

		try {
			// 1.
			// 2.
			con = db.getConn();
            
			// 3.
			//procedure를 쓰면 자바에서는 쿼리문이 없음
			//con에서 callableStatement 얻기 (프로시저를 호출하는 객체)
            cstmt = con.prepareCall("{call insert_cp_emp(?,?,?,?)}");
            
			// 4.바인드 변수에 값 할당
			// in param(procedure 내부로 값 전달)
			cstmt.setInt(1, ceVO.getEmpno());
			cstmt.setString(2, ceVO.getEname());

			// out param (procedure 외부로 값 전달)
			// row_cnt out number(수행된 행수), msg out varchar2(메시지)
			cstmt.registerOutParameter(3, Types.NUMERIC);
			cstmt.registerOutParameter(4, Types.VARCHAR);

			// 5.procedure 실행
			cstmt.execute();
            
			// 6. out parameter에 저장된 값 받아서 사용하기
			int cnt = cstmt.getInt(3);
			String msg = cstmt.getString(4);

			rVO.setCnt(cnt);
			rVO.setMsg(msg);

		} finally {
			// 7.연결 끊기
			db.dbClose(null, cstmt, con);
		} // end finally

		return rVO;
        
	}// insertCpEmp
    
    
    public static void main(String[] args) {
		
        //VO 생성
		cpEmpVO ceVO = new cpEmpVO(1112, "박oo");
		UseInsertCpEmp uice = new UseInsertCpEmp();
        
		try {
        	//VO 넣어 procedure 호출 및 수행
			ResultVO rVO = uice.insertCpEmp(ceVO);
            
			System.out.println(rVO.getCnt() + "건 추가");
			System.out.println(rVO.getMsg());

		} catch (SQLException e) {
			e.printStackTrace();
		} // end catch

	}// main
  
  }//class