PL/SQL 객체) STORED FUNCTION

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

STORED FUNCTION

- 리턴 값이 반드시 하나 존재하는 프로시저 객체

 

문법

** 매개변수타입, 리턴타입에는 자료형의 크기를 지정하지 않음 ** 

create [or replace] function 함수명 (매개변수명1 타입, 매개변수명2 타입, ...)
return 리턴 타입
is
	-- 지역변수 선언
begin
	-- 실행부
    
    return 리턴값;
exception
	-- 예외처리부
    
    return 예외발생 시 리턴 값;
end;
/

** is구문은 생략이 불가하므로 지역변수가 없으면 아래처럼 선언해야함 ** 
return 리턴 타입
is
begin
	-- 실행부
    
    return 리턴값;
exception
	-- 예외처리부(생략가능)
    
    return 예외발생 시 리턴 값;
end;
/

 

양모자 씌우기 함수 생성

-- 양모자 씌우기 함수
create or replace function foo(p_str varchar2)
return varchar2
is
    result varchar2(32767); -- 최대 크기로 지정
begin
    dbms_output.put_line('p_str : ' || p_str);
    result := 'd' || p_str || 'b';
    return result;
end;
/

DB안에 function foo가 저장이 되었습니다.

 

※ function foo가 "컴파일" 되었다는 구문이 출력 되었습니다.

client는 DB server에게 요청 / server는 client가 요청한 내용을 응답해줍니다.

client가 SQL을 통해 어떤 복잡한 코드를 응답해달라고 요청 했다 가정해보면, server는 매번 caching 작업(기존에 동일한 작업내용이 있으면 casing 되어있음) 을 거친 후 데이터를 parsing하여 complie 절차를 거친 후 응답해줍니다.

복잡한 코드에 대한 작업을 미리 함수로 만들어놓는다면, 함수 객체 내에 즉시 실행 가능한 상태로 컴파일 되어 있습니다.

복잡한 코드를 요청할 때마다 매번 컴파일 할 것인 지 컴파일 되어있는 형태의 함수로 보관해둘 것인 지 생각해보면 당연히 함수로 보관을 해두겠죠.

이러한 이유로 함수를 생성하는 것이고, "컴파일되었습니다."라는 구문이 출력 되는 것입니다.

 

함수 실행 ①

익명 블럭, 다른 프로시저 객체에서 호출

begin
    dbms_output.put_line('호출부 ' || foo('&이름'));
end;
/

foo 함수의 매개변수에 입력한 이름을 넘겨준 후 p_str로 받아 'p_str : 박채니'를 출력한 후,

result에 'd' + '박채니' + 'b'를 담은 후 해당 문자를 호출부로 다시 return 해주어 '호출부 d박채니b'가 출력 되었습니다.

 

함수 실행 ②

DQL 문에서 사용

※ 함수 내 출력 구문은 무시됨

select foo(emp_name)
from employee;

 

DD에서 확인해보기

select * from user_procedures where object_type = 'FUNCTION';

function은 procedure의 하위 카테고리이므로, user_procedures 테이블에서 조회해야 합니다.

 

 

응용) 성별 데이터를 반환하는 함수

-- 성별 데이터를 반환하는 함수
create or replace function get_gender(p_emp_no employee.emp_no%type)
return char
is
    gender char(3);
begin
    case when substr(p_emp_no, 8, 1) in ('1', '3')
        then gender := '남';
    else
        gender := '여';
    end case;
    
    return gender;
end;
/

-- 함수 호출
select
    emp_name,
    emp_no,
    get_gender(emp_no) gender
from employee;

 

응용) 주민번호를 받아서 나이를 리턴하는 함수

-- 주민번호를 받아서 나이를 리턴하는 함수 get_age
create or replace function get_age(p_emp_no employee.emp_no%type)
return number
is
    v_age number;
    v_birth_year number;
begin
    case when substr(p_emp_no, 8, 1) in ('1', '2') then
        v_birth_year := 1900 + substr(p_emp_no, 1, 2);
    else
        v_birth_year := 2000 + substr(p_emp_no, 1, 2);
    end case;
    
    v_age := extract(year from sysdate) - v_birth_year + 1;
    return v_age;
end;
/

-- 30대 사원 조회
select
    emp_name,
    emp_no,
    get_age(emp_no) age
from employee
where get_age(emp_no) between 30 and 39;