[MySQL] 스토어드 함수 stored function

참고 자료 

유투브 쉬운코드 stored function

 

stored function?

 

사용자가 정의한 함수

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

SELECT, INSERT, UPDATE, DELETE statement 에서 사용할 수 있다.

stored function 예제1

1. 임직원의 ID를 열자리 정수로 랜덤하게 발급하고 싶다

2. ID의 맨 앞자리는 1로 고정이다. 

 

delimiter

DELIMITER는 MySQL과 같은 데이터베이스 시스템에서 사용되는 명령어로, SQL 문에서 기본 구분자인 세미콜론(;) 외에 다른 구분자를 설정하고, 이후 다시 기본 구분자로 돌아가는 데 사용된다. 

 

쿼리 (id_generator 생성)

delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL 
BEGIN
	RETURN (1000000000 + floor(rand() * 1000000000));
END
$$
delimiter ;

 

쿼리 (id_generator 사용)

INSERT INTO employee 
VALUES(id_generator(), 'JEWOO', '1998-12-12', 'M', 'DEV_BACK', 100000000, 1003); 

SELECT * FROM employee WHERE name = 'JEWOO';

stored function 예제2

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

 

쿼리1 (dept_avg_salary 생성)

DELIMITER ^^
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA
BEGIN
 	DECLARE avg_sal int;
	SELECT AVG(SALARY) into avg_sal
	FROM employee
 	WHERE dept_id = d_id;
	RETURN avg_sal;
END
^^
DELIMITER ;

 

쿼리2 (dept_avg_salary 생성) - 변수 선언 X

DELIMITER *
CREATE FUNCTION dept_avg_salary(dep_id int)
RETURNS int
READS SQL DATA
BEGIN
	SELECT AVG(SALARY) into @AVG_SALARY
	FROM employee
	WHERE dept_id = dep_id;
	RETURN @AVG_SALARY;
END
*
DELIMITER ;

stored function 예제3

졸업 요건 중 하나인 토익 800점 이상을 충족했는지를 알려주는 함수를 작성하자 

DELIMITER &&
CREATE FUNCTION toeic_pass_fail(toeic_score int)
RETURNS char(4)
NO SQL
BEGIN
	DECLARE pass_fail char(4);
    IF    oeic_score IS NULL THEN SET pass_fail = 'fali';
    ELSEIF toeic_score < 800 THEN SET pass_fail = 'fail';
    ELSE                          SET pass_fail = 'pass';
	END IF;
    RETURN pass_fail;
END
&&

stored function이 할 수 있는 일

loop를 돌면서 반복적인 작업을 수행 

case 키워드를 사용해서 값에 따라 분기 처리

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

stored function 조회 및 삭제 

삭제

DROP FUNCTION IF EXISTS function_name;

 

함수 조회

SHOW FUNCTION STATUS WHERE DB = 'company';

 

함수 정의 조회 

SHOW CREATE FUNCTION dept_avg_salary;