10. stored function

Stored Function

 

delimiter $$

원래 SQL에서의 delimiter는 세미콜론(;)이다.

그러나 우리가 c/c++과 같은 프로그래밍 언어로 함수를 작성할 때에도 끝을 알리는 용도로 세미콜론(;)을 사용을 하는데,

SQL에서도 FUNCTION을 작성 시, 명령어의 끝을 알려 주기 위하여 세미콜론(;)을 사용해야 한다.

그런데 이때 문제점이 하나가 있다. FUNCTION을 작성 시, 만약 명령어 해석기가 세미콜론( ; )을 만났을 때, 이 세미콜론이

FUNCTION 내부에서 단순히 명령어의 끝을 알리는 세미콜론을 의미하는지, 아니면 SQL문의 작성이 끝났음을 알리는 세

미콜론인지 구분이 가지가 않는다.

이러한 문제점을 해결하고자 FUNCTION 작성 전, delimiter $$로 SQL문의 끝 이젠 세미콜론이 아니고 $$로 대체한다는 것

을 알려 줘야 한다. 

그리고 FUNCTION의 작성이 끝나고 다시 delimiter ; 에 의해서 원래의 SQL문법대로 되돌려 놓으면 된다. 

(참고로, 꼭 $$이 아니여도 상관이 없다.)

 

1.CREATE FUNCTION id_gererator( )

CREATE FUNTION은 FUNCTION을 만들겠다는 명령어이고, id_generator (  )운 함수명과 매개 변수를 정의하고 있다.

(이 예제에서는 함수에 매개변수가 없다.)

2.RETURNS int

함수의 반환형!!! 그리고 꼭 RETURN뒤에 S를 붙여 줘야 한다.

3.NO SQL

NO SQL은 MySQL에서만 제공하는 것으로 굳이 여기서는 설명을 하지 않겠다.( 알고 싶으면 MySQL의 메뉴얼 참고)

 

아래 1~3은 함수의 BODY부분이다.

1.BEGIN

2.RETURN (1000000000 + floor( rand( ) + 1000000000 )

3.END

-> 참고로, rand( )는 0~1사이의 임의의 수를 반환을 한다. 

 

예제

CREATE FUNCTION dept_avg_salary( d_id int )

이전 예제와는 달리 int형의 매개변수를 받는다.

READS SQL DATA 

이것 또한 MySQL에서만 사용하는 문법으로서 굳이 여기서 설명을 하지는 않겠다.

 

함수의 BODY

BEGIN

DECLARE avg_sal int; // 변수를 선언할 때, [DECLARE 변수명 자료형]을 적어 준다. 

select avg(salary) into avg_sal  // avg(salary)가 반환(출력)하는 값을 avg_sal에 넣어 준다. 

                             from employee

                             where dept_id = d_id;

RETURN avg_sal;

END

$$

 

@avg_sal 

만약 DECLARE avg_sal int를 쓰기 귀찮다면, 위의 그림에서와 같이 @를 붙여 주면 된다.

예제

예제

dept_avg_salary(id)

여기에서 받은 매개변수 id는 FROM뒤가 department 테이블이므로, department.id를 받게 된다.

그리고 그 앞의 "SELECT  * " 에 의해 department 테이블의 모든 tuple들이 반환(출력)되기 때문에 department 테이블의

모든 id를 매개변수를 받는다. 

 

IF ~~ THEN ~~

ELSE IF ~~ THEN ~~

,,,,,,,,,

END IF;  // IF문이 다 끝나면 반드시 이 END키워드를 이용하여 IF문이 끝났다는 것을 알려줘야 한다. 

 

 

 

 

등록된 stored function 파악하기

Q. 우리는 위에서 FUNCTION을 정의를 할 때, 이 FUNCTION이 company라는 DB에 저장을 하겠다라는 명령을 해주지 않

았는데도 자동으로 company DB에 stored돼 있는가?

A. 우리가 use company라는 명령어로 company DB를 활성화(사용)하겠다라고 맨~~처음 명령을 했기 때문에 굳이

FUNCTION이 stored될 DB를 명시하지 않아도 자동으로 company DB에 stored이 된다.

만약 FUNCTION을 현재 활성화(사용)돼 있는 company가 아닌 다른 DB에 stored시키고 싶다면 CREATE FUNCTON 뒤에

다음과 같은 명령어를 추가해야 한다. 

CREATE FUNCTION DB명.FUNCTION 이름

만약, FUNCTION의 BODY 부분을 보고 싶다면 위와 같이 명령어를 타이핑하면 된다. 

(BODY 부분이 그렇게 깔끔하게 출력이 되지는 않는다. ㅠㅠㅠ)

 

만약에 비지니스 로직은 stored function으로 두게 되면, Logic tier에서도 stored function이 있고, Data tier에서도 stored

functino이 중복 존재하게 된다. 

이 상태에서 만약 개발을 하게 되면, Logic tier와 Data tier를 왔다 갔다 하면서 이중으로 봐줘야 하는 부담이 생긴다.

그래서 비지니스 로직이 아닐 때에 주로 stored function을 만드는 것을 추천한다.