JDBC - 오라클 저장 프로시저

JDBC - 오라클 저장 프로시저에 대해서 알아보자.

오라클 저장 프로시저

저장 프로시저는 복잡한 쿼리문을 매번 사용할 때 다시 입력할 필요 없이 간단하게 저장 프로시저로 정의해 놓고 호출해서 복잡한 쿼리문에 대한 실행 결과를 얻으려고 할 때 주요 사용한다. 일련의 작업들을 하나로 묶어서 저장하고 호출해 복잡한 SQL문을 단순화 시켜준다.

 

저장프로시저를 사용하면 성능도 향상되고, 호환성 문제도 해결된다.

 

<저장프로시저 만드는 순서>

 

1. sqlplus 계정/비밀번호로 로그인 한다.

 

2. ed 저장프로시저로 작성할 sql 스크립트 파일명을 입력한다. (sql파일을 생성할 경로를 잘 확인한다. 여기서 ed board로 입력했다.)

 

sql 파일을 만들 경로를 확인하고, ed 파일명으로 sql 파일을 생성한다.

예를 눌러 새 파일을 만들면 된다. 

 

3. 만들어진 sql파일에서 저장프로시저 생성 쿼리문을 입력한다. 메모장에서 바로 입력해도 되고, 이클립스에서 입력해도 상관 없다.

 

<저장프로시저 사용 문법>

저장프로시저 사용 문법은 아래와 같다.

(해당 문법은 PL-SQL문으로 오라클에서만 사용 가능하며 이클립스에서는 사용하지 못한다.)

 

*PL-SQL문 ? Procedual Language / SQL로 SQL을 이용하는 절자형 프로그램 언어의 약어이다.

SQL은 구조화된 조회 언어라는 의미로, 집합적 언어라서 절차적 프로그래밍은 지원되지 않는다. SQL을 이용하면서 절차적인 프로그래밍도 함께 필요한 경우의 요구때문에 따로 만든 것으로 주로 프로시져, 사용자함수, 트리거 등을 만드는데 사용한다. 내부에서 다양한 SQL을 복합적으로 구성이 가능하기 때문에 단순 select결과로 나오지 않는다.

 

 

이클립스에서 해당 경로로 확인하면 ed명령어로 만든 sql파일을 확인할 수 있다. 해당 파일에서 저장프로시저 생성 쿼리문을 입력하면 된다. create or replace procedure sel_board6에서 or replace는 같은 이름의 저장프로시저를 생성할 경우 기존 프로시저를 삭제하고 새롭게 기술한 내용으로 재생성하는 옵션이다. sel_board6은 저장프로시저 이름이다.

 

in, out등을 mode 매개변수라고 하며, mode 매개변수의 종류는 3가지가 있다. in은 값을 전달받을 때 사용하며, out은 디비 레코드값을 되돌려 받을 때 사용하고 출력 결과물을 받을 때 사용하며, inout은 두가지 목적에 모두 사용할 경우 사용한다.

 

is

begin~end; 에서 begin과 end사이에 실제 실행할 쿼리문 문장이 들어가면 된다.

 

4. 작성한 sql파일을 저장하고, cmd 창에서 @파일명 명령어로 저장 프로시저를 생성한다.

 

5. execute 저장프로시저이름(전달될값); 으로 실행한다. 이전 실행명령어를 취소하고 싶으면 트랜잭션의 rollback; 하면 쿼리문 실행이 취소된다. 반대로 commit;하면 저장 프로시저 실행이 성공적으로 완료된다.

 

 

해당상태에서 excute sel_board;를 입력하면 바인드 변수가 정의되지 않는다고 뜬다.

out매개변수를 가진 프로시저를 호출하려면 매개변수에 변수를 지정해서 값을 받아와야한다. out매개변수로 나올 바인드 변수를 variable 매개변수 변수타입(크기);로 선언한 하면된다.

 

저장프로시저 실행화면.

 

여기서는 vname, vtitle, vcont가 out매개변수로 되어있어 변수를 선언해주고, 값을 받아오기 위해서 변수 앞에 ":"을 붙인다. 또 바인드 변수를 출력하기 위해서는 print 변수명;으로 출력 가능하다. 여기서는 13번 레코드에 대한 값을 불러온다.

 

13번 레코드