[JDBC] Procedure 호출 / in parameter / out parameter / CallableStatement 활용

 

 Procedure 호출 

- Procedure : 쿼리문을 저장하고 필요한 곳에서 실행해야 할 때 사용하는 DBMS 객체

- DBMS에서 언어적인 요소(제어, 연산)를 가져야 할 때

- Oracle DBMS에서는 user_procedures 딕셔너리에서 생성된 Procedure를 확인할 수 있음

- CallableStatement 객체로 Procedure를 호출할 수 있음

- 자바에서 호출 시, SQL의 프로시저가 컴파일 되어있어야 함

 

* Oracle의 Procedure *

- PL/SQL : DBMS에서 언어적인 요소를 구현할 때 사용

- 기본문법(데이터형, 변수, 연산자, 제어문), cursor, function, procedure, trigger, package를 지원

- 컴파일을 한 후 실행함

    > 컴파일 : @파일명.sql

    > 함수 : 간접실행 (쿼리문에 함수를 포함시켜 실행하는 방법)

                   직접실행 (실행기를 사용하여 실행하는 방법 -> execute 또는 exec 사용)

- 작성법) 

    create or replace procedure 프로시저명 (매개변수,,,) 

    is  -- 변수 선언, record 선언(java의 VO같은 거), table 선언(java의 배열 같은 거), cursor 선언

    begin -- 코드 작성(연산, 제어, 쿼리작성 등등)

    end;

    /

- 선언 시 주의)

   > 매개변수 종류 : in parameter (프로시저 외부의 값을 프로시저 내부로 전달하는 일)  //  in은 생략하고 선언 가능

                                   our parameter (프로시저 안에서 처리한 값을 외부로 전달하는 일)

   > 매개변수명은 테이블의 컬럼명과 동일하면 절대 안됨! 식별될 수 있도록 선언 

        ex) sal로 쓰면 sal=sal 항상 참이 될 수 있으므로 in paramter면 in_sal로 선언

- 컴파일) @파일명.sql   -- DOS에서 sqlplus에서 로그인하고 하면 됨

- 실행)

   1) bind 변수 선언 (프로시저 내부의 처리된 값을 SQLPlus에서 저장할 변수)

        > 프로시저 내부의 처리된 값은 out parameter를 타고 나옴

        >  bind 변수는 일시적이라 실행될 때 마다 선언해주어야 함

             var | variable 데이터형 변수명 (크기) -- 크기는 넣어도 되고 안 넣어도 됨

    2) 프로시저를 실행 (직접실행)

        execute | exec 프로시저명 (값,,, : 바인드변수명 ,,,) 

        > 값 : in parameter로 들어가는 값 

        > : 바인드면수명 : out parameter로 나오는 값

    3) bind 변수에 저장된 값 출력 (procedure가 처리한 결과값)

        > print 바인드변수명 바인드변수명 바인드변수명 -- 띄어쓰기로 여러 개의 값 줄 수 있음

 

-프로시저 삭제) drop procedure 프로시저명;

 

 

 Java에서의 Procedure 호출 

* CallabeStatement

- procedure를 호출할 때 사용하는 객체

- PreparedStatement Interface의 하위 Interface

- PreparedStatement bind 변수를 사용하여 값을 입력할 수 있음

- SQLPlus의 bind 변수는 CallableStatement의 registerOutparameter로 처리

   > SQLPlus의 bind 변수 : var 변수명 데이터형(크기), procedure에서는 :변수명 으로 사용

 

- 사용법)

1) 쿼리문 생성객체 얻기

    CallabeStatement cstmt = con.prepareCall(프로시저명);

    // 프로시저명에는 이렇게 !  "{ call 프로시저명(?,?,…) }"

2) CallableStatement 바인드 변수에 값 설정

 ① in parameter에 대응되는 bind 변수 (프로시저에 값 할당)

      - cstmt.setXXX (인덱스, 값);

         number -> cstmt.setInt (인덱스, 값), cstmt.setDouble (인덱스, 값);

         varchar2, char -> cstmt.setString(인덱스, 값);

 ② out parameter에 대응되는 bind 변수 (SQLPlus의 bind 변수를 자바 형식으로 선언하는 것)

      - cstmt.registerOutParameter (인덱스, java.sql.Types.상수) ;

      - Oracle에서는 var 변수명 데이터형(크기) 이렇게 했던 걸 위처럼 처리

    * java.sql.Types 클래스 

       - DBMS의 bind변수를 정의할 때 사용하는 데이터형을 java에서 사용할 수 있도록 구현한 클래스 

       - 특정 DBMS에서만 제공하는 데이터형을 지원하지 않음

            > Oracle의 NUMBER 대신 Types.NUMERIC 사용 

            > Oracle의 VARCHAR2 -> Types.VARCHAR

3) 프로시저 실행 

 - CallableStatement는 실행 method가 없으므로, 부모 Interface의 실행 method를 사용함

     > executeQuery(), executeUpdate(), execute() 셋 중에서 실행했다는 의미로 excute()를 사용!

        (어차피 셋다 반환형(rs,int,boolean)이 제대로 나오지 않음)

 * 프로시저를 실행하면 바인드변수(registerOutParameter)에 프로시저가 실행된 결과가 저장됨

4) 실행결과가 저장된 값 받기

- ResultSet 안 씀.

- CallableStatement의 method 사용 : cstmt.getXXX (바인드변수의 인덱스)

 예) registerOutParameter(Types.NUMERIC) 일 때 : cstmt.getInt(인덱스); cstmt.getDouble(인덱스)

        registerOutParameter(Types.VARCHAR) 일 때 : cstmt.getString(인덱스);

 

---

# 오늘의 코딩 #

- Java에서 Oracle에 생성한 procedure를 사용해보자 

 

# 생성한 프로시저

- in parameter로 name을 받고, out parameter로 msg를 내보냄!

 

# Java에서 호출

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

import javax.swing.JOptionPane;

import test.dao.DbConnection;

/**
 * 이름(varchar2)을 입력하여 (in parameter) 설정된 msg(varcahr2)를 받는 일 (out parameter)
 * @author user
 */
 
public class UseCallableStatement {

	public UseCallableStatement() throws SQLException {

		Connection con = null;
		CallableStatement cstmt = null;

		DbConnection db = DbConnection.getInstance(); // 중복된 일이라 method 만들어 놓음
		
		try {
			// 1.드라이버로딩& 2.커넥션얻기
			con = db.getConn();
			// 3.쿼리문 생성객체 얻기 (프로시저명은 proc_hello)
			cstmt = con.prepareCall("{call proc_hello (?,?)}");
			// 4.바인드 변수 설정
			// in parameter
			cstmt.setString(1, JOptionPane.showInputDialog("이름 입력"));
			// out parameter
			cstmt.registerOutParameter(2, Types.VARCHAR);
			// 5.쿼리수행 후 결과 얻기
			cstmt.execute();
			//프로시저가 실행된 결과는 Out Parameter에 저장됨
			String msg = cstmt.getString(2);
			System.out.println(msg);
			
		} finally {
			// 6.연결 끊기 : CallableStatement는 PreparedStatement의 자식이므로, 종료 method를 부모로 사용 가능
			db.closeDB(null, cstmt, con);
		} // end finally

	}// UseCallableStatement

	public static void main(String[] args) {

		try {
			new UseCallableStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		} // end catch
	}// main

}// class

- name은 다이얼로그를 띄워서 입력받음 (동동)

 

# 출력 결과 #

-> 완료!! ㅎㅎ