45일차 - 프로시저 예제/JDBC 프로시저 실행

 

프로시저 추가 내용

 

프로시저의 begin~end; 사이의 SQL 명령들은 여러 개인 경우가 대부분입니다.

그 여러 개의 명령어가 하나의 업무를 처리합니다.

 

트랜잭션

 

하나의 업무를 처리하는 SQL 명령들을 트랜잭션이라고 합니다.

트랜잭션은 모두 정상 실행되어야합니다. 

정상 실행 완료 후 COMMIT 명령을 실행합니다.

명령 처리중 오류가 발생하면 이미 실행된 SQL 명령은 취소가 되어야 합니다.

ROLLBACK 명령을 실행하여 이전 COMMIT 또는 ROLLBACK 지점으로 돌아갑니다.

 

프로시저 예제1

 

buy# 테이블의 money 컬럼에 수량과 단가를 곱하여 나온 금액을 저장합니다. 

1) test 테이블에 입력받은 매개변수를 INSERT합니다. (테스트용)

2) 매개변수를 입력받아 buy# 테이블에 값을 INSERT합니다.

3) 시퀀스.currval 로 현재 시퀀스를 조회하여 vseq 변수에 저장합니다.

 SELECT tbl_buy_seq.currval INTO vseq FROM dual;

4) 입력받은 pcode와 일치하는 행의 price를 조회하여 vprice 변수에 저장한비다.

5) BUY_SEQ 컬럼값이 vseq인 행을 조회하여 MONEY 컬럼값을 vprice*quantity 값으로 변경합니다.

6) 모든 명령이 실행되면 '실행 성공'을 출력하고 출력 매개변수 isSuccess에 'success'를 저장합니다.

7) COMMIT 명령을 실행합니다.

오류 발생시

1) '실행 실패' 메시지를 출력하고 ROLLBACK 명령을 실행합니다.

2) 출력 매개변수 isSuccess에 'success'를 저장합니다.

 

프로시저 예제2 : 도서관 반납 업무

회원번호, 책코드, 반납일자를 입력받아 반납기록을 저장하는 프로시저를 생성합니다.

 

회원 테이블 생성

 

책 테이블 생성

 

대여 태이블 생성

 

회원 테이블 값 입력

 

책 테이블 값 입력

 

대여 테이블 값 입력

 

프로시저 생성

조건 : 1명의 회원은 1권의 책을 빌릴 수 있다.

1) 입력받은 abcode, amem_idx를 가지고 있고, return_date가 null인 행의 수를 조회하여 변수 cnt에 저장합니다.

2) 입력받은 abcode,a mem_idx를 가지고 있고, return_date가 null인 행의 rent_no,mem_idx를 변수에 각각 저장합니다.

3) cnt=1이라면(대여 후 반납을 아직 안했다면) 반납일자를 입력받은 areturn_date로 변경합니다.

4) cnt=1이라면(대여 후 반납을 아직 안했다면) 연체일수(반납일자-반납예정일자)를 계산하여 값을 저장합니다.

5) 모든 명령을 실행 완료하면 출력 매개변수에 'success'를 저장합니다.

6) COMMIT 명령을 실행하고 'success 성공'을 출력합니다.

7) 예외 발생시 'fail 실패'를 출력, ROLLBACK 명령을 실행, 출력 매개변수 success에 'fail을 저장합니다.

 

* SQL에서 조건문 형식

IF(조건) THEN 명령문 END IF;

 

프로시저 실행

 실행할 때 출력 매개변수 선언 시에는 크기도 같이 선언해 주어야 합니다.

 

실행 결과

 

JDBC 프로시저 실행

 

호출 시 SQL문은 { call 프로시저이름(매개변수) }로 합니다.

입력 매개변수를 필드로 선언합니다.

프로시저 실행은 PreparedStatement 인터페이스 대신 CallableStatement 인터페이스를 이용합니다.

Connection 객체의 prepareCall(sql) 메소드를 CallableStatement 변수에 저장합니다.

입력 매개변수값은 CallablesStatement 객체의 setXXX() 메소드로 설정합니다.

출력 매개변수값은 CallableStatement 객체의 registerOutParameter() 메소드로 설정합니다.

registerOutParameter() 인자

 → 정수/실수 : Types.NUMERIC

 → 문자 : Types.VARCHAR

 → 날짜 : Types.DATE

프로시저 실행 메소드

 → execute() : boolean 리턴

 → executeUpdate() : int 리턴

 

예제2 프로시저 실행