[JDBC] 2021.01.04. day_48 CallableStatement, Procedure

 

◎ CallableStatement

• DBMS에서 제작된 Procedure를 호출하기 위해 만들어진 객체

• PreparedStatement의 하위 interface(bind변수를 사용)

사용법

 

// 1. 드라이버 로딩 2. 커넥션 얻기 
// 3. 쿼리문 생성객체 얻기

String callProcedure = "{ call 프로시저명(바인드변수,,,) }";
CallableStatement cstmt = con.prepareCall( 쿼리문 );

// 4. 바인드 변수에 값 설정

- in parameter : procedure안에 값을 넣는 것
cstmt.setXxxx(바인드변수의인덱스, 값-argument);

- out parameter : procedure가 처리한 결과를 받는 것
cstmt.registerOutParameter(인덱스, java.sql.Types.상수);
//상수는 특정 DB에 귀속되는 데이터형을 제공하지 않는다
//상위개념 제공 : number --> NUMERIC , varchar2 --> VARCHAR ,,,,

// 5. 쿼리문 실행 후 결과 얻기 (부모의 method 를 사용) : out parameter에 값이 젖아된다
cstmt.execute();

//6. out parameter에 저장된 값 받기
위의 registerOutParameter에 인덱스와 동일한 인덱스를 사용하여 값을 받는다

cstmt.getInt(인덱스)

---

◎ Procedure

• DBMS에서 자주 사용될 쿼리문을 미리 구현해두고 필요한 곳에서 호출하여 사용할 때
• 직접호출하여 사용 (SQLPlus에서 실행기 (exec | execute) 를 사용하여 호출)
• 함수보다 개선된 매개변수를 지원 (return 값이 여러 개가 될 수 있다)
• 컴파일 (@파일명.sql) 후 실행 (exec 프로시저명(값,,,)) 을 수행
• 매개변수는 외부의 값을 받아들이는 in parameter와 프로시저의 처리결과를 반환하는 out parameter가 존재

사용법

_ 1 작성

create만 쓰면 중복이 안된다// or replace 까지 쓰면 존재하면 치환한다는 의미

create or replace procedure 프로시저명( 데이터형 매개변수명,,,)
is
  -- 변수 선언, record선언(VO와 비슷), table선언(배열과 비슷), corsor선언
begin
  -- 코드작성 : 연산, 제어, 쿼리문 (transaction을 개발자가 처리)

  -- 예외처리
end;
/

_ 2 컴파일 ) SQLPlus 에서 컴파일 수행

sql> @경로/파일명.sql

_ 3 실행 ) SQLPlus 에서 실행

_ 3-1 바인드 변수를 선언(값과 변수가 따로 떨어져있으나 묶여서 같이 사용되는 변수)를 선언

sql> var [or variable] 변수명 데이터형(크기)

var aaa aaa --> 실행될 수 없는 데이터형을 넣으면, 데이터형을 알려준다

var 로 만든 바인드 변수를 조회할 수 있다

_ 3-2 프로시저를 실행

sql> exec[or execute] 프로시저명(값,,,,,,, :바인드변수명, :바인드변수명,,,,,,,)

in parameter 
out parameter

_ 3-3 바인드변수를 출력

sql> print 바인드변수명 바인드변수명 바인드변수명

---

※ Procedure 생성 호출 해보기

--1. 프로시저 작성
--in이 default이기 때문에 명시하지 않아도 된다, out은 꼭 명시
create or replace procedure age_proc(year in number, age out number, zodiac out varchar2)
is

begin
	age := to_char(sysdate, 'yyyy') - (year+1);
    zodiac := '소띠';
    
    exception
    when others then
    dbms_output.put_line('예외발생');
end;
/

--2. 저장 : c/dev/test.sql
--3. 컴파일 : sql>@c/dev/test.sql
--4. out parameter 값을 저장하기 위한 bind 변수 선언
sql> var b_age number
sql> var b_zodiac varchar2(200)

--5. 실행기를 사용하여 실행
sql> exec age_proc(2021, :b_age, :b_zodiac)

--6.bind변수의 값 출력
sql> print b_age b_zodiac

 

※ Procedure 조회, 삭제

--조회 : user_procedures
select * from user_procedures;

--삭제 
drop procedures 프로시저명;