oracle] PL/SQL의 사용자 정의 함수

 

1. 함수 생성

CREATE OR REPLACE FUNTION 함수 이름 (매개변수 1, 매개변수 2...)
RETURN 데이터 타입;
IS [AS]
	변수, 상수 등 선언
BEGIN
	실행부
    
    RETURN 반환값;
 [EXCEPTION 
 	예외처리부]
END [함수 이름];

 

-CREATE OR REPLACE FUNTION : 구문을 사용해 함수를 생성. 최초 함수를 만들고 나서 수정을 하더라도 이 구문을 사용해 계속  컴파일 할 수 있고 마지막으로 수정된 최종본이 반영된다.

-매개변수: 함수로 전달되는 매개변수로, "매개변수명 데이터 타입" 형태로 명시한다. [생략가능 ]

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

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

CREATE OR REPLACE FUNCTION my_mod (num1 NUMBER ,num2 NUMBER)
	RETURN NUMBER  --반환 데이터 타입은 NUMBER 
IS 
	vn_remainder NUMBER := 0;		-- 반환할 나머지
    vn_quotient NUMBER := 0;		--몫
BEGIN
	vn_quotient :=FLOOR(num1 / num2) ; 		--피젯수 /젯수 결과에서 정수 부분만 사용
    vn_remainder := num1 - (num2 * vn_quotient); 		--나머지 = 피젯수 -(젯수 *몫)
    
    RETURN vn_remainder;		--나머지를 반환
	
END;

 

 

 

2. 함수 호출 

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

  함수명 혹은 함수명 ()

 

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

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

SELECT my_mod(14,3) remainder
	FROM DUAL;
---------------
    2

 

---

예시 

CREATE OR REPLACE FUNTION fn_get_country_name(p_country_id NUMBER)
	RETURN VARCHAR2		--국가명을 반환     
IS
	vs_country_name COUNTRIES.COUNTRY_NAME%TYPE%;
    vn_count NUMBER := 0;
    
BEGIN
	SELECT COUNT(*)
    	INTO vn_count
        FROM countries
       WHERE country_id =p_country_id;
       
		IF vn_count = 0 THEN
		  vs_country_name : = '해당국가 없음';     	
		ELSE 
			SELECT country_name
            	INTO vs_country_name
            FROM countries
         	WHERE country_id =p_country_id; 
		END IF;
        
        RETURN vs_country_name ;		--국가명 반환
END;

-> 매개변수로 들어오는 국가가 있는지 체크하고 count 수가 0이면 '해당국가 없음'이란 문자열 값을 할당. 

-> 0이 아니면 다시 국가명을 선택해 vs_country_name 변수에 할당해서 반환 

 

SELECT fn_get_country_name (5277) COUN1 , fn_get_country_name (10000) COUN2
FROM DUAL;

------------------------
COUNT1	   COUNT2
---------  -------------
Denmark    해당국가 없음