DB function procedure package

오라클 SQL과 PL/SQL을 다루는 기술책을 보면서 정리

l 함수

-       함수 정의

매개변수를 받아 뭔가를 처리해 그 결과를 반환하는 데이터베이스 객체이며 사용자 정의 함수도 마찬가지이다.

-       함수 생성

Create or replace function 함수 이름 (매개변수1, 매개변수2, …) Return 데이터타입; Is[as] 변수, 상수 등 선언 Begin 실행부   Return 반환값; [exception 예외 처리부] End [함수 이름];

 

-  매개변수: 함수로 전달되는 매개변수로, “매개변수명 데이터 타입” 형태로 명시한다. 매개변수는 생략할 수 있다.

-  Return 데이터 타입: 함수가 반환할 데이터 타입을 지정한다.

-  Return 반환값: 매개변수를 받아 특정 연산을 수행한 후 반환할 값을 명시한다.

 

 

함수 호출

함수 호출 방식은 매개변수의 존재 유무에 따라 함수명과 매개변수를 명시하기도 하고, 함수명만 명시하기도 한다. 그리고 함수는 반환 값이 있으므로 SELECT 문장에서 사용할 수도 있고 PL/SQL 블록 내에서도 사용할 수 있다.

<매개변수가 없는 함수 호출>

함수명 혹은 함수명()

<매개변수가 있는 함수 호출>

함수명(매개변수1, 매개변수2,…)

 

l 프로시저

-       프로시저 정의

함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시는 특정한 로직을 처리하기만 하고 결과 값을 반환하지는 않는 서브 프로그램이다.

-       프로시저 생성

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

Create or replace procedure 프로시저 이름            (매개변수1[in | out | in out] 데이터타입[:= 디폴트 값],            (매개변수2[IN | OUT | IN OUT] 데이터타입[:= 디폴트 값], … ) IS[AS] 변수, 상수 등 선언 BEGIN 실행부   [EXCEPTION 예외 처리부] END [프로시저 이름]

 

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

-       프로시저 실행

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

<프로시저 실행1>

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

ð  EXEC my_new_job_proc (‘SM_JOB1’, ‘Sample JOB1’, 1000, 5000); => 신규 JOB을 넣는 프로시저

실행 후 SELECT 문으로 프로시저가 잘 실행되었는지 확인하면 된다.

ð  Select * from jobs where jbo_id = ‘sm_job’;

프로시저의 매개변수가 많으면 실행할 때 매개변수 값의 개수나 순서를 혼동할 소지가 매우 많다. 이런 경우에는 다음과 같은 형태로 매개변수와 입력 값을 매핑해 실행하면 매우 편리하다.

<프로시저 실행2>

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

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

‘=>’ 기호를 사용해 해당 매개변수명과 값을 연결하는 형태로 실행할 수 있다.

ð  EXECUTE my_new_job_proc (p_job_id => ‘SM_JOB1’, p_job_title => ‘sample job1’, p_min_sal => 2000, p_max_sal => 7000);

 

매개변수 디폴트 값 설정

프로시저를 실행할 때는 반드시 매개변수의 개수에 맞춰 값을 전달해 실행해야 한다. 만약 매개변수 값을 누락하면 다음과 같이 오류가 발생한다.

 

OUT, IN OUT 매개변수

프로시저와 함수의 가장 큰 차이점은 반환 값의 존재 여부인데, 프로시저에서도 OUT 매개변수를 통해서 실현할 수 있다. OUT 매개변수란 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해 값을 가져올 수 있는 것이다.

 

RETURN문

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

 

패키지

-       패키지 정의

패키지란 한마디로 논리적 연관성이 있는 PL/SQL 타입, 변수, 상수, 서브 프로그램, 커서, 예외 등의 항목을 묶어 놓은 객체다.

<패키지 사용 - 함수>

패키지명.서브프로그램명

ð  Select hr_pkg.fn_get_emp_name (171) from dual;

<패키지 사용 – 프로시저>

ð  Exec hr_pkg.new_emp_proc (Julia robers’, ‘2014-01-10’);