Stored Procedure란?

💡Stored Procedure란?

• 사용자가 정의한 프로시저
• RDBMS에 저장되고 사용되는 프로시저
• 구체적인 하나의 task를 수행한다.
• 조건문을 통해 분기처리를 하거나 반복문을 수행하거나 에러를 핸들링하거나 에러를 일으키는 등의 다양한 로직을 정의

💡Stored Procedure 작성

delimiter $$
CREATE PROCEDURE 프로시저이름(IN/OUT 파라미터이름 파라미터타입)
BEGIN
   body
END
$$
delimiter ;

call 프로시저이름();

✔️ IN/OUT을 명시하지 않으면, default 값인 IN으로 동작

• IN : 호출하면서 파라미터 값을 전달
• OUT : 반환값을 파라미터에 저장

💡예제

• 두 정수의 곱셈 결과를 가져오는 프로시저

  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;

  ✔️ INOUT : IN, OUT 역할 모두 수행
  

• 각 부서별 평균 연봉을 가져오는 프로시저

  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();

• 사용자가 프로필 닉네임을 바꾸면 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트하는 프로시저

  select * from users;  -- id, nickname
  select * from nickname_logs;  -- 닉네임의 history 기록
  
  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;
  
  call change_nickname(1,'taetae');

💡Stored Procedure vs Stored Function

	Stored Procedure	Stored Function
create 문법	CREATE PROCEDURE	CREATE FUNCTION
return 키워드로 값 반환	불가능(SQL server는 상태코드 반환용으로는 사용 가능)	가능(MySQL, SQL server는 값 반환하려면 필수)
파라미터로 값 반환	가능(값을 반환할 때, 필수)	일부 가능(oracle 가능하나 권장안함, postgreSQL 가능)
값 반환	필수 아님	필수
SQL statement에서 호출	불가능	가능
transaction 사용	가능	불가능(oracle의 경우 가능)
주된 사용 목적	business logic	computation
내부적으로 다른 function 호출	가능	가능
내부적으로 다른 procedure 호출	가능	불가능
resultset(table) 반환	cursor나 임시 테이블을 사용하여 반환	단일값 또는 테이블 형태로 변환
precompiled execution plan	첫 실행 시 execution plan이 생성되고 캐시됨, 이후 실행 시 캐시된 execution plan 재사용 가능	첫 실행 시 execution plan이 생성되고 캐시됨
try-catch 사용	가능	불가능

✔️ 트랜잭션❓
여러개의 SQL문을 하나로 묶어서 사용하고 싶을때, 여러 SQL문이 모두 성공적으로 수행되면 반영되고 그렇지 않으면 아무런 일이 일어나지 않은 것처럼 동작

💡3계층 구조에서 Stored Procedure의 의미

1. Presentation 계층
   • 사용자에게 보여지는 부분을 담당하는 계층
2. Logic tier
   • 서비스와 관련된 기능과 정책 등등 비즈니스 로직을 담당하는 계층
3. Data tier
   • 데이터를 저장하고 관리하고 제공하는 역할을 하는 계층

=> Stored Procedure의 주요 사용 목적은 비즈니스 로직 구현

💡Stored Procedure의 장단점

• 장점
  1. application에 transparent하다.
  2. 네트워크 트래픽을 줄여서 응답 속도를 향상시킬 수 있다.
     • stored procedure를 사용하지 않은 경우, select/inser/update 할때마다 네트워크 트래픽 발생
     • stored procedure를 사용한 경우, 프로시저를 호출할때 네트워크 트래픽 발생
  3. 여러 서비스에서 재사용 가능하다.
  4. 민감한 정보에 대한 접근을 제한할 수 있다.
• 단점
  1. 유지 관리 보수 비용 증가
  2. DB 서버 추가 복잡
  3. stored procedure가 항상 transparent하다고 보기 어렵다.
  4. 재사용이 가능하다는 것이 오히려 독이 될 수 있다.
     • DBMS로의 호출이 증가하면 트래픽 사용량이 증가하여 통제하기 어려움으로 인해 문제 발생 우려
       => Data Service를 통해 원하는 로직을 사용할 수 있도록 해야한다.
  5. 가독성이 떨어지고, 디버깅이 어렵다.