oracle procedure 사용 방법

l  프로시저

함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값을 반환하지는 않는 서브 프로그램이다. 일반적으로 프로젝트 현장에서는 시스템 설계가 끝난 후 업무를 분할하고 이 분할한 업무 단위로 로직을 구현해야 하는데, 개별적인 단위 업무는 주로 프로시저로 구현해 처리한다. 즉 테이블에서 데이터를 추출해 입맛에 맞게 조작하고 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 프로시저를 사용한다.

l  프로시저 생성

함수나 프로시저 모두 DB에 저장된 객체이므로 프로시저를 스토어드(Stored, 저장된) 프로시저라고 부르기도 한다.

Create or replace procedure 프로시저 이름 (매개변수명1[in |out |in out] 데이터 타입[:= 디폴트 값], 매개변수2[in |out |int out] 데이터 타입[:= 디폴트 값], … ) Is[as] 변수, 상수 등 선언 Begin 실행부 [exception 예외 처리부] End [프로시저 이름];

 

l  Create or replace procedure:함수와 마찬가지로 create or replace 구문을 사용해 프로시저를 생성한다.

l  매개변수:IN은 입력, OUT은 출력, IN OUT은 입력과 출력을 동시에 한다는 의미다. 아무것도 명시하지 않으면 디폴트 IN 매개변수임을 뜻한다. OUT 매개변수는 프로시저 내에서 로직 처리 후, 해당 매개변수에 값을 할당해 프로시저 호출 부분에서 이 값을 참조할 수 있다. 그리고 IN 매개변수에는 디폴트 값 설정이 가능하다.

 

l  프로시저 실행

함수는 반환 값을 받으므로 실행할 때 ‘호출’이라고 명명하지만 프로시저는 ‘호출’ 혹은 ‘실행’한다고 표현하는데, 실제로는 후자를 많이 사용하는 편이다. 프로시저는 반환 값이 없으므로 함수처럼 select 절에는 사용할 수 없고 다음과 같이 실행해야 한다.

 

<프로시저 실행1>

Exec 혹은 execute 프로시저명(매개변수1 값, 매개변수2 값, …);

 

<프로시저 실행 2>

Exec 혹은 EXECUTE 프로시저명(매개변수1 => 매개변수1 값,

                                  매개변수1 => 매개변수2 값 값, …);

l  매개변수 디폴트 값 설정

프로시저를 실행할 때는 반드시 매개변수의 개수에 맞춰 값을 전달해 실행해야 한다.

 

l  OUT, INT OUT 매개변수

프로시저와 함수의 가장 큰 차이점은 반환 값의 존재 여부다. 그런데 프로시저에서도 값을 반환하는 방법이 있는데 바로 OUT 매개변수를 통해서 실현할 수 있다. OUT 매개변수란 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해 값을 가져올 수 있는 것이다. 프로시저 생성시 매개변수명과 데이터 타입만 명시하면 디폴트로 IN 매개변수가 되지만 OUT 매개변수는 반드시 OUT 키워드를 명시해야 한다. 예를 들어 my_new_job_proc에서 갱신일자를 받는 OUT 매개변수가 있다.

 

l  RETURN 문

함수에서 사용한 RETURN문을 프로시저에서도 사용할 수 있는데 그 쓰임새와 처리 내용은 다르다.

함수에서는 일정한 연산을 수행하고 결과값을 반환하는 역할을 했지만, 프로시저에서 RETURN문을 만나면 이후 로직을 처리하지 않고 수행을 종료, 즉 프로시저를 빠져나가 버린다. 반복문에서 일정 조건에 따라 루프를 빠져나가기 위해 EXIT를 사용하는 것과 유사하다.

예를 들어, my_new_job_proc 프로시저에서 세 번째 매개변수인 최소 급여값은 반드시 1000 이상이어야 하며, 이보다 작은 값이 들어오면 INSERT나 UPDATE를 수행하지 않아야 한다고 해보자. 그렇다면 최소 급여값을 체크해서 1000보다 작으면 아무 것도 처리하지 않아야 하는데 이럴 때 RETURN문을 사용하면 편리하다.