정보처리기사 PART 8 정리 - 사용자 정의함수

사용자 정의함수(User-Defined Funtion)

 

사용자 정의함수는 절차형 SQL을 이용하여 사용자가 직접 정의하고 작성한 기능을 수행합니다. 수행이 종료되면 단일 결과 값을 반환합니다.

 

이제 사용자 정의함수의 구조에 대해서 이야기하겠습니다. 

 

 

첫번째로 이야기할 구조는 선언부(DECLARE)입니다. 선언부에서는 사용자 정의함수의 이름과 변수, 데이터 타입 등을 정의하는 부분입니다. 

 

CREATE FUNCTION GET_AGE(V_BIRTH_DATE IN CHAR(8))
IS

 

다음 코드는 CREATE FUNTION을 이용해 GET_AGE라는 사용자 함수를 정의합니다. 그 후  운영체제에서 파라미터를 입력받는 문자열 V_BIRTH_DATE을 정의합니다.

 

 

다음은 시작(BEGIN)과 종료부(END)입니다.

 

시작부와 종료부는 다수의 실행을 제어하는 기본 단위로 프로시저의 시작과 종료를 표현하고 있습니다. 반드시 시작부가 나오면 종료부로 마무리 지어야합니다.

 

BEGIN은 선언부의 뒤에 오며 선언부를 제외한 프로시저의 모든 구조들은 BEGIN과 END사이에 정의되어야합니다.  

 

BEGIN
V_CURRENT_YEAR CHAR(4);
V_BIRTH_YEAR CHAR(4);
V_AGE NUMBER;

 

-- 제어부, SQL, 예외부, 반환부가 BEGIN과 END사이에 들어간다.

 

END;

 

BEGIN문 뒤에 사용자 정의 함수에서 사용할 V_CURRENT_YEAR, V_BIRTH_YEAR, V_AGE 변수를 선언합니다.

 

 

세번째로 이야기 할 사용자 정의 함수의 구조는 제어부(CONTROL)입니다.

 

사용자 정의 함수에서는 조건문인 IF문, CASE문을 이용해 문장을 실행하게 됩니다.

 

 

네번째는 SQL로 데이터 조회 용도로만 사용하기 때문에 SELECT만 이용합니다.

 

SELECT TO_CHAR(SYSDATE, 'YYYY'), SUBSTR(V_BIRTH_DATE,1,4)
INTO V_CURRENT_YEAR, V_BIRTH_YEAR
FROM DUAL;

 

SET AGE = TO_NUMBER(V_CURRENT_YEAR) - TO_NUMBER(V_BIRTH_YEAR) + 1;

 

SQL에서는 SYSDATE로 현재 날짜를 조회를 합니다. 조회된 날짜의 연도 값만 파싱 후 변수 V_CURRENT_YEA에 입력합니다.

 

그 후 V_BIRTH_DATE에서 연도값만 자른 뒤 변수 V_BIRTH_DATE에 다시 입력하는 동작을 수행합니다.

마지막으로는 두 변수의 차이를 구해 1을 더해 나이를 구한 후 AGE에 입력하는 동작을 하는 SQL입니다.
 

 

다음으로 설명한 예외부 (EXCEPTION)는 BEGIN~END문 사이의 SQL문의 실행 중 예외 발생시 처리 방법을 정의하고 있습니다. 

 

 

마지막은 사용자 정의 함수만 가지고 있는 구조인 반환문(RETURN)입니다. 반환문에서는 하나의 값만 결과로 넘길 수 있습니다. 

RETURN AGE;

 

사용자 정의 함수의 최종 결과값으로 AGE라는 값을 넘겨주고 함수는 종료됩니다. 

 

 

정의된 사용자 함수를 이용하기 위해서는 다양한 방법으로 호출 가능합니다.

 

SELECT문을 이용한 방법은 다음과 같습니다. 

 

SELECT GET_AGE("20100525")

FROM DUAL;

 

SELECT문에서 "20100525"라는 값을 V_BIRTH_DATE에 넘겨주게 되면 SELECT의 결과로 AGE라는 사용자 함수의 결과를 반환받습니다.

 

 

다른 방법으로는 업데이트에서 사용하는 방법입니다.

 

UPDATE EMPLOYEE

SET AGE = GET_AGE(BIRTH_DATE)

WHERE EMPOYEE_ID = '202020';

 

직원 테이블을 업데이트하기 위해서 직원번호가 202020인 직원의 나이를 사용자 정의함수를 통해 변경가능합니다.