2024.03.06

CallableStatement

• Procedure를 호출하기 위해 사용하는 객체.
• PreparedStatement의 자식 인터페이스.

Procedure

• PL(Procedural Language)/SQL 중 하나.
  • PL : 기본문법, 연산자, 제어문 , cursor, function, procedure, trigger, package
    • function -> 간접실행 : 쿼리문 안에 넣어서 실행
      select 함수명(컬럼명),,,,
    • procedure -> 직접실행 : 실행기 (exec,execute)를 사용해서 실행
      exec 프로시저명(값, : 바인드변수명)
• DBMS에서 정보관리 뿐만 아니라 업무로직을 정의할 수 있게 된다.
• 화면이 다양하게 제공되는 환경에서 유리. (업무로직을 집중해서 구현할 수 있다.)
• 코드작성, 컴파일, 바인드변수의 선언, 직접실행의 순서를 가진다.
• 작성된 Procedure는 user_procedures DD에서 확인 가능.
• 함수와는 다르게 업무로직과 쿼리문을 함께 가진다.
• autocommit이 되지 않고, 개발자가 직접 commit을 해야한다.
• 반환형이 없고 매개변수가 반환 값을 저장한다.(여러 개의 값이 반환된다.)
• 프로시저를 사용하면 업무의 연속성을 가질 수 있다.(프로그램 실행 도중 다른 결과를 얻을 수 있다.)
• 작성법 )
  • 주의사항 :문장의 끝에는 ;(세미콜론)을 넣는다.
  • create or replace procedure 프로시저명( 매개변수 타입 데이터형 ,,,,)
    is
    변수선언 , cursor선언, recode선언 , table 선언
    begin
    업무로직 구현
    excetion 처리
    end;
    / (반드시 들어가야함.)
    • 타입
      • in parameter : 생략하면 기본 설정
        • 프로시저 외부의 값을 내부로 전달할 때
      • out parameter : 프로시저 내부의 값을 외부로 전달할 때.
    • cursor선언 : 여러 행 조회
    • recode선언 : 여러 개의 값을 묶어서 저장하기위한 객체
    • table선언 : 한번에 데이터를 모아서 처리
• 순서 )
  1. 프로시저 작성

     • create or replace procedure test_proc()
       is

       begin
       dbms_output.put_line("hello");
       //PL/SQL 출력
       //dbms_output.put_line(값);
       //dbms_output.put_line(값||'붙일 값');
       end;
       /

  2. 저장
     • 파일명.sql
  3. 컴파일
     • @경로명/파일명.sql
  4. 에러보기
     • show error
  5. 바인드 변수 선언 : 프로시저가 실행한 결과를 저장하기 위해
     • var(variable) 변수명 데이터형(크기)
  6. 실행기를 사용한 선언
     • exec 프로시저명(값,,,, : 바인드변수명,,,,)
     • execute 프로시저명(값,,,, : 바인드변수명,,,,)
  7. 바인드 변수에 설정된 값 출력
     • print 바인드변수명 바인드변수명

CallableStatement를 사용한 Procedure 실행

1. CallableStatement얻기
   • CallableStatement cstmt = con.prepareCall("{call 프로시저명(바인드변수,?,,,)}");
2. 바인드변수에 값 설정
   • in parameter : 외부의 값을 Procedure내부로 전달하기 위한 변수.
     • cstmt.setint(인덱스,값);
     • cstmt.setString(인덱스,값);
   • out parameter : Procedure 내부의 값을 Procedure 외부로 전달하기 위한 변수.
     • Java에서는 Out Parameter를 등록하여 사용한다.(Oracle에서는 var로 선언하는 bind변수)
     • cstmt.registerOutParameter(인덱스, 데이터형)
       • 데이터형 : java.sql.Types클래스에서 제공.
3. 프로시저를 실행(method가 없음 => 부모의 method를 사용(PreparedStatement))

• cstmt.execute();

4. out parameter에 저장된 값 받기. (SYS_REFCURSOR가 아닌 이상 ResultSet을 사용하지 않는다.)

• 인덱스 : out parameter의 인덱스
• cstmt.getint(인덱스);
• cstmt.getString(인덱스);
• cstmt.getObject(인덱스);

java.sql.Types

• DBMS에서 사용되는 데이터 형을 자바에서 지원하는 클래스.

• 특정 DBMS만 사용하는 데이터 형을 지원하지 않고, 공통적인 이름의 데이터 형을 지원한다.

• 특정 DBMS에서만 지원하는 데이터형을 사용하고 싶다면, DB Vendor사에서 제공하는 Types 클래스를 사용한다.

• CallableStatemetn에서는 out parameter의 값을 저장하기 위해
  registerOutParameter method의 매개변수로 사용하게 된다.

• 사용법 )

  • 선언
    • create or replace procedure test(num out number, name out varchar2)
      is
  • Java 호출
    • CallableStatement cstmt= con.prepareCall("{call test(?,?)}")
  • number 호출 (Types에는 number가 없으므로 NUMERIC사용)
    - cstmt.registerOutParameter(1,Types.NUMERIC);
  • varchar2 호출 (Types에는 VARCHAR2가 없으므로 VARCHAR를 사용)
    • cstmt.registerOutParameter(2,Types.VARCHAR);
  • 프로시저 실행
    • cstmt.execute();
  • 값 얻기
    • int i = cstmt.getint(1)
    • string s = cstmt.getString(2)

PL/SQL에서 if문

• if 조건식 then
  end if;
  • 조건 맞을 때에만 코드를 실행
• if 조건식 then
  else
  end if;
  • 둘 중 하나의 코드를 실행해야할 때
• if 조건식 then
  elsif 조건식 then
  elsif 조건식 then
  else
  end if;
  • 연관된 여러 조건을 비교할 때