[Oracle] PL/SQL 기초5 - 함수(Function) 첫번째 : 사용자 정의 함수(User Defined Function)

[Oracle] PL/SQL 기초5 - 함수(Function) 첫번째 : 사용자 정의 함수(User Defined Function)

 

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ [Oracle] PL/SQL 기초 - 함수(1) - 사용자 정의 함수 ] 입니다. : ) 

 

0.Oracle Function

▶ 1. 함수(Function)란?

 - 이번에 설명하는 함수는 오라클에서 제공하는 내장함수(TO_CHAR, TO_DATE 등)가 아닌 사용자 정의 함수((User Defined Function)이다.

 

 - 특정 기능들을 모듈화, 재사용 할 수 있어서 복잡한 쿼리문을 간결하게 만들수 있다. 
   하지만, 함수 사용이 쿼리 속도를 느리게 만드는 원인(인덱스를 타지 않는 등)이 될 수 있으니 꼭 필요한 경우가 아니라면 권장하진 않고 싶다.
   차선으로 함수 캐싱, 함수 기반 인덱싱 하는 방법들이 있지만, 이부분은 이번엔 논하지 않을 예정이다. 

 

 - 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program이다. 
   즉, PL/SQL 블록 내에서 RETURN문을 통해 반드시 값을 리턴해야 한다.

 - 프로시저는 PL/SQL 문으로 실행하지만, 함수는 식의 일부로서 사용한다.
 - RETURN 절에 지정된 것과 동일한 datatype으로 RETURN 값을 설정해야 한다.
 
 - 함수 작성시 권한이 필요하다.
   자신의 스키마 : CREATE PROCEDURE 권한
   다른 사용자의 스키마 : CREATE ANY PROCEDURE 권한
 
 - 기본적으로 DML(Insert/update/delete)문을 사용할 수 없다.
   물론, 굳이 사용할 필요도 없으나 BEGIN 전에 'pragma autonomous_transaction;'을 선언하여 사용하게 할 수는 있다.

 

▶ 2. 함수(Function) 기본 문법

CREATE [OR REPLACE] FUNCTION function_name [(

    argument...

)]
RETURN datatype -- 반환되는 값의 datatype
    IS [AS]
        [선언부]
    BEGIN
        [실행부 - PL/SQL Block]
    [EXCEPTION]
        [EXCEPTION 처리]
    RETURN 변수; -- 리턴문 필수
END;

 

 - [ ] : 대괄호 안의 구문은 생략 가능함을 의미 한다.

 - OR_REPLACE : 함수생성 DDL 명령어 / 이미 존재하는 펑션이라면 기존 내용을 지우고 재생성 한다. 생략 가능하다.
 - function_name : 사용자 지정 함수명.
 - argument : 매개변수1, 매개변수2, 매개변수3... 매개변수 선언.
 - datatype : VARCHAR2, NUMBER, DATE 등 반환할 데이터 타입을 지정한다. 

 

 

※ 이해를 위해 한글 표현시

CREATE OR REPLACE FUNCTION 함수명 (
    변수명 IN VARCHAR2     
)
RETURN VARCHAR2 -- 반환되는 값의 datatype
    IS 
        변수 VARCHAR2(10);
        
    BEGIN
        SELECT  리턴할값
        INTO    변수
        FROM    DUAL
        WHERE   1 = 변수;
        
    RETURN 변수; -- 리턴문 필수
END;

 

▶ 3. 함수(Function)   작성 및 사용 예시

EX) 함수 작성 예시

CREATE OR REPLACE FUNCTION FN_GET_DEPT_NAME(
	P_DEPT_NO IN VALCHAR2
) RETURN VARCHAR2
	IS 
		V_TEST_NAME VARCHAR2(10);
	BEGIN
		SELECT	DELP_NAME
		INTO	V_TEST_NAME
		FROM 	DEPT
		WHERE	DEPT_NO = P_DEPT_NO;
		
	RETURN  	V_TEST_NAME;
END;

 

EX) 함수 사용 예시
   - SELECT문장에서 편하게 사용 할 수 있다.

SELECT FN_GET_DEPT_NAME('01') FROM DUAL;

 

EX) 예전에 만들어 보았던 사업자번호 유효성 체크 함수도 참고 하자.
 - 사업자번호 유효성 체크 Function

2017/11/29 - [4. Database/4_1 쿼리_Func_Proc] - [Oracle] 사업자번호 유효성 체크 Function (오라클)

 

이번 포스팅에선 기본적인 사용자 정의 함수를 사용하는 방법을 알아 보았고, 다음 포스팅에선 하나의 값을 리턴하는 것이 아닌, Data Set, 즉 마치 테이블과 같은 결과를 리턴할 수 있는 Table Function에 대해 알아 볼 예정이다.

 

sample.sql

0.00MB