참고
쉬운코드: SQL에서 stored function을 아시나요?
쉬운코드: SQL에서 stored procedure를 아시나요
쉬운코드: stored procedure를 백엔드 실무에서 쓰기에 조심스러운 이유

Stored Function

• 사용자가 정의한 함수
• DBMS에 저장
• SQL SELECT, INSERT, UPDATE, DELETE문에서 사용 가능

사용 예제

들어가기 전에: Delimiter란

1. MySQL은 기본적으로 ;를 구분자(delimiter)로 사용한다.
2. Stored Function(Procedure)를 구성하는 중에, 중간에 ;가 들어가면 문제가 생길 수 도 있다.
3. 따라서 Stored Function(Procedure)를 구성하는 중에는 $$로 구분자를 변경해 준다.

EX: Id Generator

앞자리가 1로 시작하는 4자리의 랜덤 아이디를 만들고 싶다.

Stored Function 생성

delimiter $$
CREATE FUNCTION id_generator() # 함수 시그니처: 이름(파라미터 타입)
RETURNS int # 함수의 리턴 타입
NO SQL # MySQL 특화 문법
BEGIN # Function Body 시작
	RETURN (1000 + floor(rand() * 1000));
END $$ # Function Body 끝
delimiter ;

Stored Function 사용

INSERT INTO employee
VALUES (id_generator(), ...); # 생성한 Function을 사용

EX: Avg Caculator

부서의 Id가 주어지면, 해당 부서 구성원들의 평균 연봉을 알고 싶다.

생성

delimiter $$
CREATE FUNCTION dept_agv_salary(d_id int) # int 타입 파라미터를 사용한다.
RETURNS int # 반환 타입
READS SQL DATA # MySQL 특화 문법
BEGIN # Function Body 시작
	DECLARE avg_sal int; # 변수 선언: 변수이름 타입
    SELECT AVG(salary) INTO avg_sal
    FROM employee
    WHERE dept_id = d_id;
    RETURN avg_sal;
END $$ # Function Body 끝
delimiter ;

사용

SELECT *, dept_agv_salary(id)
FROM department;

Ex: Pass Validator

점수가 토익 800점을 넘었는지 확인하고 싶다.

생성

delimiter $$
CREATE FUNCTION pass_validator(score int)
RETRUNS char(4)
NO SQL
BEGIN
	DECLARE pass_fail char(4);
    IF scroe is null THEN SET pass_fail = 'fail';
    ELSEIF score < 800 THEN SET pass_fail = 'fail';
    ELSE SET pass_fail = 'true';
    END IF;
END $$
delimiter ;

사용

SELECT *, pass_validator(score)
FROM student;

그 외

1. loop를 돌면서 Stored Function 호출 가능
2. case를 사용해서 분기 처리 가능
3. 에러 핸들링, 에러 일으키기 가능
4. ...

조회하기

# Stored Function 목록 조회
SHOW FUNCTION STATUS
WHERE DB = 'DB_NAME';

# Stored Function 내용 조회
SHOW CREATE FUNCTION function_name;

삭제하기

DROP FUNCTION function_name;

주의사항

• Stored Function은 단순히 Util 함수 정도로만 사용하는게 좋다.
• 비즈니스 로직을 가지고 있으면 역할 분리가 힘들어진다.

Stored Procedure

• 사용자가 정의한 프로시저
• DBMS에 저장
• 구체적인 하나의 task를 수행

사용 예제

들어가기 전에: 변수 생성

# 1. DECLARE 사용
MySQL은 DECLARE를 통한 변수 선언을 Procedure에서만 사용 가능한 것 같음

# 2. SET 사용
MySQL은 SET을 사용해서 변수를 선언 할 수 있음
SET @변수명 = 초기값;
SET @my_var = 1;

EX: 두 수 곱샘

Stored Procedure 생성

DELIMITER $$
CREATE PROCEDURE product(IN a INT, IN b INT, OUT c INT)
BEGIN
	SET c = a * b;
END $$
DELIMITER ;

• IN 키워드를 사용해서, 함수에 값을 전달하는 목적의 파라미터임을 나타냄
• OUT 키워드를 사용해서, 함수의 결과를 반환하는 목적의 파라미터임을 나타냄
• IN은 생략 가능 -> 별도로 명시하지 않으면 기본적으로 IN파라미터로 인식

Stored Procedure 사용

# 변수 선언을 생략하고 바로 사용할 수 있는 것 같음
CALL product(5,4, @result);
SELECT @result;

EX: Swap

Stored Procedure 생성

delimiter $$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
BEGIN
	DECLARE temp int; 
    SET temp = a;
    # 위 두 문장을 'SET @temp = a;'로 축약 가능
    SET a = b;
    SET b = temp;
END $$
delimiter ;

• INOUT를 사용해서, 파라미터를 값을 전달, 반환하는 목적으로 동시에 사용 가능

Stored Procedure 사용

SET @a = 5;
SET @b = 1;
CALL swap(@a, @b);
SELECT @a, @b; # @a = 1, @b = 5

EX: 부서별 평균 연봉 조회

Stored Procedure 생성

delimiter $$
CREATE PROCEDURE avg_sal()
BEGIN
	SELECT dept_id, avg(salary)
    FROM department
    GROUP BY dept_id;
END $$
delimiter ;

• 명시적인 반환값이 없음

Stored Procedure 사용

CALL avg_sal()

• Procedure가 명시적인 반환 값이 없음에도, Procedure body의 결과값인 Table(dept_id, avg)이 Procedure 호출 값으로 반환됨

EX: 닉네임 변경 & 로깅

새로운 닉네임으로 변경하고, 기존 닉네임을 로깅하고 싶다.

Stored Procedure 생성

delimiter $$
CREATE PROCEDURE changeAndLog(user_id INT, c VARCHAR(30))
BEGIN
	INSERT INTO nickname_log (
    	SELECT id, nickname, now()
        FROM users
        WHERE id = user_id
    );
    UPDATE users 
    SET nickname = newNickname 
    WHERE id = user_Id;
END $$
delimiter ;

Stored Procedure 사용

CALL changeAndLog(1, "newNickname");

조회하기

# 프로시저 목록 조회
SHOW PROCEDURE STATUS; 

# 특정 프로시저 상세 정보
SHOW CREATE PROCEDURE procedure_name;

삭제하기

DROP PROCEDURE procedure_name;

Stored Function과의 차이점

• MySQL 기준 둘의 차이점을 비교해보면

1. 문법
   • Function은 CREATE FUNCTION ...
   • Procedure는 CREATE PROCEDURE ...
2. 값 반환
   • Function은 RETURN 키워드로 1개의 값 반환 가능
   • Function은 무조건 값을 반환해야 함
   • Procedure는 OUT, INOUT 키워드로 파라미터를 통한 여러 값 반환 가능
   • Procedure는 꼭 값 반환 안 해도 됨
3. SQL statement에서 호출 가능
   • Function은 다음과 같은 사용 가능

   SELECT *, product(price, order_count) FROM Orders;

   • Procedure는 불가능, 아래와 같이 CALL 키워드를 통한 단독 호출만 가능

   CALL MyProcedure();

4. 트랜잭션 사용 (여러 Statement를 atomic하게 사용 가능한가)
   • Function은 불가능
   • Procedure는 가능
5. 사용 목적
   • Function은 단순 computation (like Util method)
   • Proceduer는 business logic 수행

Stored Procedure 장단점

장점

1. Application에 transparent하게 로직 변경 가능
   • 여러 App 서버에서 DB에 접근하는 상황
   • DB의 프로시저만 변경하면, App 서버는 다시 배포하지 않아도 됨
2. network traffic을 줄일 수 있음
   • App 서버에서 n번 DB를 사용 -> n번의 네트워크 비용
   • App 서버에서 프로시저 1번 사용 -> 1번의 네트워크 비용
3. 여러 서비스에서 재사용 가능
   • Java로 작성된 서버, Python으로 작성된 서버, ... -> n개의 공통된 비즈니스 로직 작성 필요
4. 민감 정보 접근 제한 가능
   • 프로시저 호출 = App 서버에서 데이터를 직접 다루지 않음

단점

1. 비즈니스 로직이 분산됨
   • 파악 힘듦, 관리 힘듦, SQL 문법 익혀야 함
2. 비즈니스 로직 수행을 위한 자원 사용 부담이 App 서버에서 DB로 전가됨
   • DB 복제 = 데이터 복제 부담
   • 차라리 App 서버 scale out이 편함
3. 항상 transparent하지 못함
   • 프로시저 이름 변경 = App에서도 호출 할 이름 변경해야 함
   • 새로운 프로시저 추가 = 새로운 호출 로직 추가
4. transparent가 과연 좋은가?
   • 버그가 있는 프로시저 vs 버그가 있는 1개의 App
   • 프로시저가 버그 파장 범위가 더 크다 (문제 최소화)
5. 성능은 프로시저 말고도 다양한 방법으로 개선 가능
   • 동기 -> 비동기
   • 캐싱
6. SQL보다 Java로 비즈니스 로직 작성하는게 더 쉽다 + 가독성