[Oracle] PL/SQL_subprogram : function

▶ FUNCTION (내장 함수)

oracle에서 제공하는 내장함수(to_char, to_date 등) 가 아닌 사용자 정의 함수(user defined function) 이다

특정 기능들을 모듈화, 재사용 할 수 있어서 복잡한 query문을 간결하게 만들 수 있다

하지만, 함수 사용이 쿼리 속도를 느리게 만드는 원인이 될 수 있으니까 꼭 필요한 경우가 아니라면 권장하지 않는다

 

호출한 곳으로 반드시 하나의 값을 return해야하는 PL/SQL stored program이다

(무조건) 정해진 작업 수행후 값을 돌려준다.  ( 값 리턴한다! 프로시저와의 차이점! )

즉, PL/SQL 블록 내에서 return문을 통해 반드시 값을 return 해야 한다

 

procedure는 PL/SQL문으로 실행하지만 함수는 식의 일부로서 사용한다

return 절에 지정된 것과 동일한 datatype으로 return 값을 설정해야 한다

 

기본적으로 DML문을 사용할 수 없다

 

 

▷ 함수(FUNCTION) 기본 문법

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

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

CREATE [OR REPLACE] FUNCTION function_name (
	argument, ...
)
RETURN datatype -- 반환되는 값의 datatype
IS [AS]
	[선언부]
BEGIN
	[실행부 - PL/SQL BLOCK]
    [EXCEPTION]
    RETURN 변수; -- 리턴문 필수
END;


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

 

CREATE OR REPLACE FUNCTION s_max_sal(s_deptno t_emp.deptno%TYPE) -- TYPE만 명시하면 된다
RETURN NUMBER
IS 
	max_sal t_emp.sal%TYPE;
BEGIN 
	SELECT max(sal) INTO max_sal
	FROM t_emp
	WHERE deptno = s_deptno;
	RETURN max_sal;  -- 위에서 명시한 RETURN 타입 명시
END;
/

실행!

select s_max_sal(10) from dual; -- 단일행 함수를 실행시킨 것이다

select distinct deptno from t_emp;

select distinct deptno, s_max_sal(deptno)  from t_emp;