0207/Procedure

쌍용교육센터에서 공부한 내용을 정리하며 기록하는 글이고 주관적인 생각이 들어갈 수 있습니다.
잘못된 내용이나 피드백 및 생각 공유는 언제든 환영입니다.
댓글로 남겨주시면 확인 후 수정하겠습니다.
내용에 대한 소스 코드는 https://github.com/2jaehoon/sangyoungjdbc에 올려두었습니다.

Procedure

  - 자주 사용될 쿼리문을 미리 작성하여 저장해두고, 필요한 곳에서

    실행하여 사용하기위해 작성하는 것.

  - PL(Procedural Language) /SQL문 중 하나

    ( 기본문법, 연산자, 제어문, function, procedure, trigger, package로 구성 )

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

      암시적 커서 : 자동 생성 커서

      명시적 커서 : 개발자가 정의하는 커서

  - 실행기를 사용한 직접실행을 수행( 함수는 간접 실행- 쿼리문에 넣어서 실행 )

    sql> exec 프로시저명( 값, ..., ... )

  - 반환형이 없다.( out parameter를 사용 – 필요한 수 만큼 외부로 내보낼 수 있다. )

  - 컴파일 ( user_procedures DD에 Procedure가 추가된다 )후 실행한다.

  - 업무 로직은 하나이면서 화면이 다양하게 만들어져야 할 때 사용

 

* Procedure 문법

  1. 선언

-- 선언
create or replace procedure 프로시저명( 매개변수명 종류 데이터형, ... )
is
  변수선언, recode선언, table선언, cursor 선언
begin
  연산자, 제어문, 쿼리문, 커서의 사용

end;
/

  2. 저장 : 프로시저명.sql

  3. 컴파일( DBMS에서 실행 )

@프로시저명.sql

  4. 바인드 변수를 선언( out parameter에 존재하는 값을 저장하기 위해 )

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

  5. 실행

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

  6. 바인드 변수에 값 출력( out parameter 값이 저장된다. ) - 골든에서는 실행만 하면 출력

print 바인드변수명

 

CallableStatement

  - Procedure를 실행하기 위해 제공하는 interface.

  - PreparedStatement의 자식 interface

 

* CallableStatement 사용

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

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

// in parameter : ( PreparedStatement의  값을 설정하는 코드 )
// 정수
cstmt.setInt( 인덱스, 값 ); // 부모의 매개변수 명을 사용

// out parameter : SQL의 바인드 변수가 필요 => out parameter 등록하여 사용
 cstmt.registerOutParameter( 인덱스, Types의 constant );

// 예) Oracle NUMBER가 out parameter
// cstmt.registerOutParameter( 1, Types.NUMERIC );
// 예) Oracle varchar2가 out parameter
// cstmt.registerOutParameter( 1, Types.VARCHAR );

// 프로시저 실행
cstmt.execute();

// Out parameter에 저장된 값 받기
변수 = cstmt.getXxx( 인덱스 );

 

SYS_REFCURSOR

  - PL/SQL에서 여러 행을 조회할 때 사용하는 객체 => CURSOR ( 명시적커서 )

      커서의 생명주기 : 선언 -> 열기 -> 인출 -> 닫기

      SYS_REFCURSOR를 사용하면 생명주기의 관리를 ResultSet 에서 수행

  - PL/SQL에서는 select쿼리로 한 행만 조회 할 수 있다.

  - SYS_REFCURSOR는 커서의 제어권을 프로시저 외부로 내보낼 때 사용하는

    out parameter의 데이터형

 

* SYS_REFCURSOR 사용법

  1. 선언

create or replace procedure 프로시저명( 변수명 out SYS_REFCURSOR)
is


begin
-- 커서를 연다.
open 커서명  for select문 ;

end;
/

 

  2. 저장 : 프로시저명.sql

 

  3. 컴파일( DBMS에서 실행 )

@프로시저명.sql

  4. 바인드 변수를 선언( out parameter에 존재하는 값을 저장하기 위해 )

var cur REFCUSOR

  5. 실행

exec 프로시저명( :cur )

  6. 바인드 변수에 값 출력( out parameter 값이 저장된다. ) - 골든에서는 실행만 하면 출력

print cur

 

* CallableStatement로 SYS_REFCURSOR 받기

// Types.REF_CURSOR 또는 OralceTypes.CURSOR 사용

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

// 바인드 변수 값 설정
cstmt.registerOutParameter( 1, Types.REF_CURSOR );

// 프로시저 실행
cstmt.execute();

// out parameter에 설정된 값 받기 - casting
ResultSet rs=( ResultSet )cstmt.getObject( 인덱스 );