[DB] stored function VS stored procedure

Stored function

• 사용자가 정의한 함수
• DBMS에 저장되고 사용됨
• SQL의 select,insert,update,delete에서 사용할 수 있음.
• loop를 돌면서 반복적인 작업을 수행하거나
• case 키워드나 if문을 사용해서 분기 처리하거나,
• 에러를 핸들링하거나 에러를 일으키는 동작이 가능하다.
• 함수 선언
  - 직원의 ID를 5자리 정수로 랜덤하게 발급하는 함수
  - ID의 맨 앞자리는 1로 고정

  mysql
  delimiter $$
  CREATE FUNCTION id_generator() //선언부
  RETURNS int //반환 타입 지정
  NO SQL // mysql의 특수문법
  BEGIN // 함수 body 시작 선언
  	RETURN (100000 + floor(rand() * 100000));
  END //함수 body 종료 선언
  $$
  delimiter ;

• delimiter를 $$로 임시로 바꿔주는 이유?
  함수 body부분에서 ;와 함수 종료 선언의 delimiter를 구분시키기 위함
  새로 선언하는 delimiter는 임의대로 조정가능

• 함수 사용(모든 statement에서 사용가능)

  INSERT INTO employee
  VALUES(id_generator(),"Kims",'1999-02-12','F','PO',105000,1005);

• 또 다른 예시(부서의 ID를 인자로 받으면 해당 부서의 평균 연봉을 리턴하는 함수)

mysql
delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int) //선언부
RETURNS int //반환 타입 지정
READS SQL // mysql의 특수문법
BEGIN // 함수 body 시작 선언
	DECLARE avg_sal int; //변수 선언 DECLARE로 변수 선언할 수 있음
    select avg(salary) into avg_sal
    				from employee
    				where dept_id = d_id;
    RETURN avg_sal;
END //함수 body 종료 선언
$$
delimiter ;

DECLARE로 변수 선언하지 않고, '@'로 반환해도 된다.

select avg(salary) into @avg_sal
				from employee
                where dept_id = d_id;
RETURN @avg_sal;

해당 함수를 사용하면, 다음처럼 사용할 수 있다.

Stored function은 언제 사용해야 될까?

• 참고사항 :일반적인 기준은 없다.(쉬운코드님이 실무에서 일하면서 생긴 데이터를 바탕으로 설명) 회사나 개발팀에 따라서 컨밴션이 다 다르다. 컨밴션에 맞게 사용하는게 맞다.

• Three-tier architecture
  

• Util 함수로 쓰기에는 적합한듯 하다

• 비즈니스 로직을 stored function에 두는 것은 비추천한다.

why?
soted function에 비즈니스 로직을 둘 경우 비즈니스 로직의 일부가 Date tier에 있게 되는 것이므로, 프로젝트의 규모가 커지기 시작하면, 비즈니스 로직의 유지보수가 힘들어 진다.(내가 이 로직을 어디에서 구현했는지 까먹는다고 한다..)

Stored procedure

• 사용자가 정의한 프로시저
• RDBMS에 저장되고 사용되는 프로시저
• 구체적인 하나의 task를 수행한다.
• 조건문, 반복문, 에러 핸들링이나 의도적인 에러발생등 다양한 기능 사용 가능
• 예시 1( 두 정수의 곱셈 결과를 가져오는 프로시저)

mysql
delimiter $$
						//IN은 INPUT Parm, OUT 은 OUTPUT Parm
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
BEGIN
	SET result = a * b ;
END
$$
delimiter ;

parameter에 IN인지 OUT인지를 명시하지 않을 경우 Default는 IN이다.

• 예시 2(swap 프로시저)

mysql
delimiter $$
//INOUT은 해당 변수가 INPUt이자 OUTPUT임을 의미한다.
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
BEGIN
	SET @tmp = a;
    SET b = a;
    SET b= @tmp;
END
$$
delimiter ;

swap 사용

set @a = 5, @b = 7;
call swap(@a,@b); //프로시저는 call로 호출한다.
select @a,@b;

결과
a = 7, b = 5 가 나온다.

stored function VS sored procedure

• 참고 :
  Mysql, Oracle, MS SQL server , PostreSQL의 공통적인 부분을 묶은 내용
  일부 디테일한 부분에서 차이가 있을 수 있다.
  

이외에도 RDBMS마다

• 다른 function/procedure를 내부에서 호출할 수 있는지(재귀등)
• 반환값으로 table을 반환 할 수 있는지
• precompiled execution plan을 만들 수 있는지
• try-catch를 사용할 수 있는 지 등등 이 있다.
  사용하는 RDBMS에 따라 차이점을 숙지하고, 사용해야된다

Ref.
쉬운코드 백발백중