SQL의 stored function이란?

stored function이란?

• 사용자가 정의한 함수

• DBMS에 저장되고 사용되는 함수

• SQL의 select, insert, update, delete statement에서 사용할 수 있습니다.

• 에러를 핸들링하거나 에러를 일으키는 다양한 동작을 정의할 수 있습니다.

stored function 사용법

임직원 ID의 맨 앞자리는 1로 고정하며 열자리 정수로 랜덤하게 발급

delimiter $$ -- 구분 문자를 ; -> $$로 변경
create function id_generator()
returns int -- return type 지정
no sql --SQL을 사용하지 않겠다는 의미
begin
    return (1000000000 + floor(rand() * 1000000000));
end
$$
delimiter ; -- 구분 문자를 $$ -> ;로 변경

• 위 코드는 1로 시작하는 10 자리의 랜덤한 숫자를 stored function 입니다.

부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수

delimiter $$
create function dept_avg_salar(d_id int)
returns int
reads sql data
begin
    declare avg_sal int; -- 변수 선언
    select avg(salary)
    into avg_sal -- 변수를 선언하는 대신에 @avg_sal를 사용할 수 있습니다.
    from employee
    where dept_id = d_id;
    return avg_sal;
end
$$
delimiter ;

토익 800 이상을 충족했는지 알려주는 함수

delimiter $$
create function toeic_pass_fail(toeic_score int)
returns char(4)
NO SQL
begin
    if toeic_score is null
        then set @pass_fail = 'fail';
    elseif toeic_score < 800
        then set @pass_fail = 'fail';
    else
        set @pass_fail = 'pass';
    end if;
    return @pass_fail;
end
$$
delimiter ;

select *, toeic_pass_fail(toeic)
from student;

stored function 삭제하기

drop function stored_function_name

• drop function 을 통해서 stored function을 삭제할 수 있습니다.

stored function 조회하기

-- company db에 stored function들이 있는지 확인
show function status where DB = 'company';

-- id_generator가 어떻게 정의되었는지 확인
show create function id_generator;