[DB] Stored Function: 언제 사용하는 게 좋을까? (개념과 예시, 명령어 정리!)

반응형

 

소스 코드 ⇒ https://github.com/gitchan-Study/2023-sql-sample

GitHub - gitchan-Study/2023-sql-sample: 깃짱의 데이터베이스 학습 레포지토리

 

Computer Science 모아보기 👉🏻 https://github.com/seoul-developer/CS

GitHub - seoul-developer/CS: 주니어 개발자를 위한 전공 지식 모음.zip

 

💋 Stored Function란?

✔️ 개념

• 사용자가 정의해서, DBMS에서 저장되고 사용되는 메서드
• SQL의 SELECT, INSERT, UPDATE, DELETE에서 모두 사용할 수 있다.

 

✔️ 예시 1: 두 숫자를 더하는 메서드

DELIMITER //

CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
BEGIN
	DECLARE sum INT;
	SET sum = a + b;
	RETURN sum;
END 
//

DELIMITER ;

 

• DELIMITER //
• 딜리미터를 새롭게 정의한다.
• stored function은 여러 SQL 문을 포함하기 때문에 각 SQL문 끝에 세미콜론(;)을 가지고 있는데, 새롭게 정의하지 않는다면 SQL문 하나가 끝날 때 stored function이 끝났다고 인식될 수 있기 때문이다.
• 근데 나는 이거 없이 그냥 딜리미터를 ;로 해서 실행했는데도 잘 되긴 했다. 설명과 실전은 다른건가?
• AddNumbers는 함수의 이름
• a와 b는 함수에 전달되는 두 정수형 매개변수
• RETURNS INT는 함수가 정수형 값을 반환한다는 것을 의미
• DECLARE sum INT;은 함수 내에서 사용될 변수 sum을 선언
• SET sum = a + b;은 두 숫자를 더한 값을 변수 sum에 할당
• RETURN sum;은 함수가 더한 값을 반환

 

✔️ 예시 2: 부서의 ID를 파라미터로 받아, 평균 연봉을 반환하는 메서드

 

DELIMITER //

CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS INT 
READS SQL DATA
BEGIN
		DECLARE avg_salary INT;
		SELECT avg(salary) into avg_salary
												from employee where dept_id = d_id;
		RETURN avg_salary;
END
//

DELIMITER ;

 

avg_salary라는 변수를 선언하지 않고도, 아래와 같이 사용할 수도 있다.

 

DELIMITER //

CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS INT 
READS SQL DATA
BEGIN
		SELECT avg(salary) into @avg_salary
												from employee where dept_id = d_id;
		RETURN @avg_salary;
END
//

DELIMITER ;

 

이 메서드를 사용해서, 부서 정보와 부서의 평균 연봉을 가져올 수 있다.

 

SELECT *, dept_avg_salary(id) FROM department;

 

 

💋 Stored Function 명령어

✔️ 삭제

 

DROP FUNCTION [stored function 이름];

 

✔️ DB에 등록된 stored function 조회

 

show function status where DB=[데이터베이스 이름];

 

나의 경우에는, 하나의 stored function만 등록해서 아래와 같은 결과를 얻었다.

어떤 DB들이 있는지 알려면 show databases;

 

show function status where DB = '2023_sql_sample';

 

 

✔️ 등록된 stored function 코드 조회

 

show create function [stored function 이름];

 

그러면, 결과에서 함수가 어떻게 선언되어 있는지 확인할 수 있다.

 

 

create function 행을 보면 어떤 내용이 있는지 나온다.

누가 만들었는지 definer에 대한 부분이 추가된다.

 

CREATE DEFINER=`root`@`localhost` 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

 

💋 Stored Function은 언제 사용할까?

• util 함수로 쓰기에는 괜찮을 것 같다. (단순 평균 구하기 등…)
• 비즈니스 로직을 stored function에 두면 데이터베이스 레이어에 비즈니스 로직이 존재하게 되어 유지보수가 어려워질 수 있다.

 

💋 참고자료

• https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=11

 

도움이 되었다면, 공감/댓글을 달아주면 깃짱에게 큰 힘이 됩니다!🌟
비밀댓글과 메일을 통해 오는 개인적인 질문은 받지 않고 있습니다. 꼭 공개댓글로 남겨주세요!