Stored Function

• 사용자가 미리 DBMS에 저장해두고 사용하는 함수를 stored function이라 함
• SQL의 select, insert, update, delete 문에서 사용할 수 있음

-- 임직원의 id를 1로 시작하는 10자리 정수로 랜덤하게 발급하는 함수
delimiter $$ -- delimiter(구문을 끝내는 표현)을 '$$'로 변경
CREATE FUNCTION id_generator()
RETURNS int -- 반환할 타입
BEGIN
    -- delimiter를 바꾸지 않을시 여기서 앞선 구문들이 실행
	RETURN(1000000000 + floor(rand() * 1000000000)); 
END
$$
delimiter ; -- delimiter 원상복원

-- id_generator를 사용해 임직원 데이터 추가
INSERT INTO employee
VALUES(id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);

-- 부서의 ID를 파라미터로 받아 해당 부서의 평균 연봉을 알려주는 함수
delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
BEGIN
	DECLARE avg_sal int;
    SELECT avg(salary) into avg_sal from employee WHERE dept_id = d_id;
    return sav_sal;
END
$$
delimiter ;

-- dept_avg_salary를 통해 각 부서별 평균 연봉 구하기
SELECT *, dept_avg_salary(id) FROM department;

-- 토익 점수가 800이상인지 확인하는 함수
delimiter $$
-- 함수이름 앞에 함수를 저장할 DB를 명시해줄 수 있음
CREATE FUNCTION school.toeic_pass_fail(toeic_score int)
RETURNS char(4)
BEGIN
	DECLARE pass_fail char(4);
    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을 사용해 반복문을 돌거나, 값에 따라 분기 처리를 하거나, 예외를 발생시키고 에러를 헨들링 하는등의 기능을 사용할 수 있음
• stored function을 삭제할 경우에는 DROP FUNCTION stored_function_name; 구문 사용
• 등록된 stored function을 확인하기 위해서는 SHOW FUNCTION STATUS 구문 사용

SHOW FUNCTION STATUS WHERE DB = 'company';

-- 실제 함수 내용을 확인하고 싶을떄는 SHOW CREATE FUNTION 사용
SHOW CREATE FUNCTION STATUS id_generator;

• stored function을 사용하면 DB작업의 생산성을 높여줄 수 있지만, 비지니스 로직이 stored function에 들어갈 경우 DB서버와 백엔드 서버에 비즈니스 로직이 분산될 수 있음에 유의

Stored Procedure

• RDBMS에 저장되고 사용되는 프로시저로, 구체적인 하나의 task를 수행함

- 두 정수의 곱셈 결과를 가져오는 프로시저
delimiter $$
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
BEGIN
	SET result = a * b;
END
$$
delimiter ;
CALL product(5, 7, @result); -- @기호를 통해 직접 변수선언가능
SELECT @result -- 테이블에 35 출력

- 두 정수를 맞바꾸는 가져오는 프로시저
delimiter $$
CREATE PROCEDURE product(INOUT a int, INOUT b int)
BEGIN
	SET @temp = a;
    SET a = b;
	SET b = @temp;
END
$$
delimiter ;
SET @a = 5, @b = 7;
CALL swap(@a, @b);
SELECT @a, @b;

-- 각 부서별 평균 연봉을 가져오는 프로시저
delimiter $$
CREATE PROCEDURE get_dept_avg_salary()
BEGIN
	SELECT dept_id, avg(salary) FROM employee GROUP BY dept_id;
END
$$
delimiter ;
CALL get_dept_avg_salary();

-- 사용자가 닉네임을 변경하면 기존 닉네임을 로그에 저장하고 새로운 닉네임으로 변경하는 프로시저
delimiter $$
-- IN, OUT, INOUT을 명시하지 않을시 기본값은 IN
CREATE PROCEDURE change_nickname(user_id INT, new_nick varchar(30))
BEGIN
	INSERT INTO nickname_logs(
    	SELECT id, nickname, now() from users WHERE id = user_id
    );
    UPDATE users SET nickname = new_nickname where id = user_id;
END
$$
delimiter ;
CALL change_nickname(1, 'ZIDANE');

• stored function과의 차이는 아래와 같음

특징	Stored Procedure	Stored Function
CREATE 문법	CREATE PROCEDURE	CREATE FUNCTION
RETURN 키워드로 값 반환	불가능	가능
파라미터로 값 가능	가능	postgreSQL, oracle(권장x) 가능
값의 필수 반환 여부	필수 아님	필수
SQL문에서 호출	불가능	가능
Transaction 사용	가능	불가능(oracle은 가능)
주된 사용 목적	비지니스 로직 처리	computation

SQL Trigger

• SQL에서 데이터에 변경이 되었을 때 자동으로 실행되는 프로시저를 SQL Trigger라 부름

-- 사용자의 닉네임 변경 이력을 제작하는 트리거
delimiter $$
CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE
ON users FOR EACH ROW
BEGIN
	INSERT INTO users_log VALUES(OLD.id, OLD.nickname, now());
END
$$
delimiter ;

- 사용자가 마트에서 구매할때마다 누적 구매액을 구하는 트리거
delimeter $$
CREATE TRIGGER sum_buy_prices_trigger
AFTER INSERT
ON buy FOR EACH ROW
BEGIN
	DECLARE total INT;
    DECLARE user_id INT DEFAULT NEW.user_id;
    
    SELECT SUM(price) INTO total FROM buy WHERE user_id = user_id;
    UPDATE user_buy_stats SER price_sum = total WHERE user_id = user_id;
END
$$
delimiter ;

• PostgreSQL에서는 한 트리거로 여러 이벤트를 한번에 감지 가능

CREATE TRIGGER avg_empl_salary_triger
AFTER INSERT OR UPDATE OR DELETE
ON employee FOR EACH STATEMENT -- PostgreSQL에서는 statement단위로 트리거 실행 가능
EXECUTE FUNCTION update_avg_empl_salary();

• PostgreSQL에서는 트리거가 실행할 조건을 걸어줄 수 있음

CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE
ON users FOR EACH ROW
WHEN (NEW.nickname IS DISTINCT FROM(OLD.nickname)
EXECUTE FUNCTION log_user_nickname();

• 트리거는 소스 코드는 발견할 수 없는 로직이기 떄문에 어떠한 동작이 파악하기 어렵고 문제가 발생했을 때 대응하기 어렵다는 점에 주의해야 함

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