함수 (Function)

이번 포스팅에서는 오라클 PL/SQL의 Function(함수)에 다루고자 한다.

 

프로시저(Procedure)가 일련의 정해진 작업들을 순차적으로 처리하는데 사용된다면, 함수(Function)는 작업식을 계산하여 특정 값을 리턴받고자 할 때 사용된다. 그럼 Function의 정의, 선언 방법, 문법 및 실행방법에 대해 알아본다.

 

함수(Function)란?

- 보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용한다.

- 대부분 구성이 프로시저와 유사 하지만IN 파라미터만 사용할 수 있다.

- 반드시 반환 될 값의 데이터 타입을RETURN문에 선언해야 한다.

- 또한 PL/SQL블록 내에서RETURN문을 통해서 반드시 값을 반환해야 한다.

출처 :http://www.gurubee.net/lecture/1042

 

 

함수의 구조

함수의 구조, 문법은 다음과 같다.

 

1. 함수(Function)의 선언

CREATE OR REPLACE FUNCTION 함수이름

여기까지는 프로시저의 선언방식과 동일하다. 사용자가 정의한 함수이름이 이미 존재하면 기존 것을 대체하고, 존재하지 않는다면 새로 만든다는 의미이다. 다음은 'fn_info'라는 이름을 가진 함수를 선언하는 방법이다.

CREATE OR REPLACE FUNCTION fn_info

 

 

2. 매개변수의 선언

편의상 파라미터라고 한다. Function 내 계산식에서 사용할 갖가지 매개변수들을 명시하는데, 방법은 프로시저에서 매개변수들을 선언할 때와 동일하다. 여러 개의 파라미터가 필요하다면, 쉼표로 구분하여 나란히 적어준다. (매개변수명1 IN 변수타입, 매개변수명2 IN 변수타입, ...)

 

다만, FUNCTION은 프로시저와 다르게 'IN' 파라미터만 허용한다. (OUT 파라미터는 사용 불가)

CREATE OR REPLACE FUNCTION fn_info (name IN VARCHAR2, age IN NUMBER)

 

 

3. Return 타입의 선언

다음으로 return 타입의 선언부가 있다. 함수는 반드시 return 값을 가지는데, 그 값이 어떤 타입인지(VARCHAR, NUMBER, CHAR, ...)를 적어준다. 단, return 값은 함수의 실행부에서 넣어주기 때문에 여기에서는 단순히 '리턴 타입'만 적어준다.

RETURN 변수타입

RETURN VARCHAR2

RETURN NUMBER

CREATE OR REPLACE FUNCTION fn_info (name IN VARCHAR2, age IN NUMBER) RETURN VARCHAR2

 

4. 지역변수의 선언

이제는 BLOCK 안에서만 사용될 지역변수의 선언부이다. 이곳도 프로시저의 지역변수 선언부와 동일하다. 리턴 타입의 선언이 끝나면, 'IS'로 구분하여 함수의 실행부에서 사용될 지역변수들에 대한 정의를 한다.

변수명 변수타입;

 

변수타입은 VARCHAR2, NUMBER와 같이 오라클 자료형을 그대로 써줘도 되고, 존재하는 테이블의 특정 컬럼 타입을 형태를 그대로 가져올 수도 있다.

name_result VARCHAR2(300); --OR name_result TABLE.COLUMN%TYPE; 

age_result NUMBER;

CREATE OR REPLACE FUNCTION fn_info
(name IN VARCHAR2, age IN NUMBER)

RETURN VARCHAR2 

IS 

name_result VARCHAR2(300); --OR name_result TABLE.COLUMN%TYPE;
age_result NUMBER;

 

5. 실행부

여기까지 왔다면 이제FUNCTION 내에서 실제 실행할 핵심 내용을 BEGIN ~ END 구문 안에 적어주면 된다.IF, WHILE, FOR, LOOP 등의 제어문을 사용해도 되고, 특정 테이블에 대한 CRUD 작업을 진행해도 된다. 그러나 실행부가 끝나기 전에, 반드시 위에 기술한 RETURN 타입의 반환값을 뱉어야 한다.리턴값이 없으면, 함수 선언시 에러가 발생한다.

BEGIN 실행내용; 

RETURN 리턴값;

END;

BEGIN 

    SELECT name, age 
    INTO   name_result, age_result
    FROM DUAL; 
    
    RETURN '이름 : ' || name_result || ', 나이 : ' || age_result; 
    
END;

 

여기서 주의할 점은 위의 함수 선언부에서 정의한 리턴 타입이 실행부에서 뱉은 리턴값의 타입과 일치해야 한다는 것이다. (리턴 값 정의는 NUMBER인데, 실행부에서 VARCHAR를 리턴하면 안됨)

CREATE OR REPLACE FUNCTION fn_info 
(name IN VARCHAR2, age IN NUMBER) 

RETURN VARCHAR2

IS 

name_result VARCHAR2(300); --OR name_result TABLE.COLUMN%TYPE; 

age_result NUMBER; 

BEGIN 
       
       SELECT name, age 
       INTO   name_result, age_result 
       FROM DUAL; 
       
	  RETURN '이름 : ' || name_result || ', 나이 : ' || age_result;
       
END;

 

 

6. 함수의 실행

함수를 실행하는 방법에는 SELECT 절을 이용하는 방법과 EXECUTE 구문을 이용하는 방법이 있다.

(EXECUTE로 실행하는 방법은 추후 포스팅...)

 

함수의 리턴값을 단독적으로 사용할 경우에는 간단히 DUAL 구문을 사용할 수 있다.

SELECT fn_info('정영훈', 23) AS RESULT FROM DUAL;

          RESULT
-------------------------- 
이름 : 정영훈, 나이 : 23

 

나이를 속이지 않고 비밀로 하고 싶어서, 매개변수 타입을 마음대로 조작하면 다음과 같이 에러가 난다...

SELECT fn_info('정영훈', '비밀') AS RESULT FROM DUAL;

SQL > SELECT fn_info('정영훈', '비밀') AS RESULT FROM DUAL 


ORA-01722: invalid number

Error code: 1722 

SQL state: 42000

또한, 다른 테이블과 연동하여 사용하는 것도 가능하다.

SELECT COL_1, fn_info('정영훈', 23) AS RESULT FROM TABLE_1;

    COL_1             RESULT 
------------ ---------------------------- 
     
     3960     이름 : 정영훈, 나이 : 23 
  
  4724.3196   이름 : 정영훈, 나이 : 23
   
     ...                 ... 
    
     ...                 ...

 

지금까지 오라클의 Function(함수)에 대해 알아보았다.

 

감사합니다!