Stored Procedure

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;

ㅤㅤㅤㅤ
⬇️ 두 정수를 맞바꾸는 프로시저

delimiter $$
CREATE PROCEDURE swap(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 $$
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_nick WHERE id = user_id;
END
$$

delimiter ;

- 파라미터에 IN, OUT 지정하지 않으면 default는 IN

---

stored procedure의 장점

• application에 transparent하다. => 기존 로직의 변경 없이 procedure body의 수정 만으로 내용 변경이 가능하다.
• network traffic을 줄여서 응답 속도를 향상시킬 수 있다. (큰 장점은 아닌 이유 : 비즈니스 로직을 소스 코드에 두고도 응답 속도를 향상시킬 수 있음)
• 여러 서비스에서 재사용 가능하다.
• 민감함 정보에 대한 접근을 제한할 수 있다.

stored procedure의 단점(실무에서 쓰기 조심스러운 이유)

• 유지 보수 비용이 커진다. => 소스 코드 뿐만 아니라 프로시저의 버전 관리, 코드 관리도 해주어야 함 & 프로시저의 문법도 숙지해야 함
• DB 서버를 추가하는 것은 간단한 작업이 아니다. => 트래픽이 갑자기 늘면 DB의 부하량(CPU 사용량, 메모리 사용량 등)이 급증한다. (logic tier에서의 애플리케이션 서버 투입은 간단하다.)
• stored procedure가 언제나 transparent인 것은 아니다. => 프로시저의 이름 변경 시 변경된 이름의 프로시저를 새로 만든 후 비즈니스 로직의 코드를 업데이트 후 재시작, 그 다음에 기존 프로시저를 삭제시켜 주어야 한다.
• transparent하다고 무조건 좋은 건 아니다! => procedure body에 버그가 있을 시 모든 트래픽에 영향
• 통제되지 않는 재사용으로 모두에게 문제가 발생할 수 있다. (=> Restful API)
• stored procedure가 민감한 정보에 대한 접근을 완벽히 제한할 수는 없다.
• DB 혹은 테이블 접근을 막으면 개발 및 CS 업무의 신속함이 떨어진다.

---

stored procedure & stored function