[DB] stored function

delimiter 변경

delimiter $$
CREATE FUNCTION id_generator()
RETURNS int //S붙여야함 RETURN(x) + 반환타입
NO SQL //MySQL에서 사용하는 것. CREATE FUNCTION과는 상관X
BEGIN // BODY부분, BEGIN~END
	RETURN (1000000000 + floor(rand()*1000000000));
	//delimiter를 바꿔주지 않으면 ;인 여기서 끝난 줄 인식함   
END
$$ //실제는 여기서 종료되기 때문에 delimiter를 선언하여 변경한 것
delimiter ;

stored function

: 사용자가 정의한 함수이며 DBMS에 저장되고 사용되는 함수.
SELECT, INSERT, UPDATE, DELETE에서 사용할 수 있다.

언제 사용?

utility 함수: 주로 간단한 작업이나 공통적으로 사용되는 기능을 수행하며 여러곳에서 재사용되는 일반적이고 유용한 기능을 제공하는 함수

비즈니스 로직의 일부를 stored function에 두게 되면 그 로직의 일부가 Data tier에도 있게 되므로 서비스의 규모가 커질 수록 비즈니스 로직의 관리 비용이 늘어나게 됨
따라서 비즈니스 로직은 가능한 Logic tier에 존재하도록 하는 것이 좋음.

비즈니스 로직: 예를들어, 회원제 포인트 서비스를 구현하려면 어떤 방식으로 포인트를 할당할 건지, 회원 등급은 어떻게 될 건지, 혜택은 어떻게 차등분배 할 건지에 대한 것.

예제

임직원의 id를 첫숫자는 1로 고정하고 열자리 정수로 랜덤하게 발급하기

delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL 
BEGIN 
	RETURN (1000000000 + floor(rand() * 1000000000));
    // 1로 고정한 열자리 정수 
    // + rand함수(0=< <1 난수반환) * 열자리 정수
    // 0과 1사이의 부동 소수점 숫자 * 열자리 정수
    // floor함수로 내림하여 소수점 이하를 버리고 정수만 가져옴
    // 10000000000 + 0 ~ 999999999 사이의 정수가 생성 
    // 최종적으로 1000000000에서 1999999999 사이의 열자리 정수 반환
END
$$ 
delimiter ;

위의 함수를 활용하여 직원정보 추가

INSERT INTO employee
VALUES(id_generator(), 'JEN', '1991-08-30','F', 10000000, 1005);

1로된 id발급 완성

• 변수 직접 선언

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

delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA //MySQL에서 사용되는 것
BEGIN 
	DECLARE avg_sal int; // DECLARE사용하여 변수 선언
    select avg(salary) into avg_sal //평균연봉을 변수에 저장
    					from employee
                        where dept_id = d_id;
                      //파라미터로 받은 d_id와 부서 아이디가 같으면
    RETURN avg_sal;
END
$$ 
delimiter ;

• 변수 간단 선언

delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA 
BEGIN 
    select avg(salary) into @avg_sal //평균연봉을 변수에 저장
    					from employee
                        where dept_id = d_id;
    RETURN @avg_sal;
END
$$ 
delimiter ;

dept_avg_salary(id) 함수 사용

부서정보와 부서 평균 연봉을 같이 가져오기
SELECT *, dept_avg_salary(id)
FROM department;

각 부서에 대해서 평균 연봉 가져옴

stored function 삭제하기

DROP FUNCTION stored_function_name;

등록된 stored function과 database 파악

각각의 함수의 코드가 궁금하다면

SHOW CREATE FUNCTION 함수명;을 적어주면 된다
DIFINER는 이 함수를 정의한 사람에 대한 정보. 위에서는 따로 명시하지 않았기 때문에 디폴트값인 계정@localhost(컴퓨터로 작성)으로 명시된다.

하위 4개는 MySQL 자체적으로 존재하는 것이고 최상단의 company가 만들어준 DB

출처: https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=10