[데이터베이스(강의)] 9. stored function

 

* stored function

 - 사용자가 정의한 함수

 - DBMS에 저장되고 사용되는 함수

 - SQL의 select, insert, update, delete statement에서 사용할 수 있다.

---

 

 - 임직원의 ID를 열자리 정수로 랜덤하게 발급하고 싶다.

 - ID의 맨 앞자리는 1로 고정이다.

 

mysql> delimiter $$
mysql> CREATE FUNCTION id_generator()
    -> RETURNS int
    -> NO SQL
    -> BEGIN
    ->           RETURN (1000000000 + floor(rand()*1000000000));  => 여기에서 끝내지 않기 위해 
    -> END
    -> $$  => 임시로 바꿔준거다

 

mysql> delimiter ;    => 다시 원래대로 바꿔준다

 

 

 

mysql> CREATE FUNCTION id_generator()   함수이름
    -> RETURNS int    반환타입
    -> NO SQL        MySQL에서만 > 설명생략
    -> BEGIN
    ->     RETURN (1000000000 + floor(rand()*1000000000));
    -> END

 

 

=> (파라미터 없는) id_generator 라는 이름의 함수 정의

=> BEGIN ~~ END 사이에 로직 적어주면 됨. ( 1 뒤에 랜덤숫자 9개가 오는 로직 )

 

---

 

이제 함수를 이용해서 employee 를 추가해보자.

 

INSERT INTO employee

VALUES (id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);

 

원하는대로 id가 생성됐는지 확인해보면

SELECT * FROM employee WHERE name = 'JEHN';

 

---

 

- 부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수를 작성하자.

 

mysql> CREATE FUNCTION dept_avg_salary(d_id int)     함수이름 (파라미터 데이터타입)
    -> RETURNS int
    -> READS SQL DATA
    -> BEGIN
    ->             DECLARE avg_sal int;                   변수선언
    ->             select avg(salary) into avg_sal        변수에 저장

                                                 from employee

                                                 where dept_id = d_id;
    ->             RETURN avg_sal;
    -> END
    -> $$

 

 

(변수선언 생략가능)

 

mysql> CREATE FUNCTION dept_avg_salary(d_id int)     
    -> RETURNS int
    -> READS SQL DATA
    -> BEGIN
    ->             select avg(salary) into @avg_sal         @ _변수 선언 없이 사용 가능

                                                 from employee

                                                 where dept_id = d_id;
    ->             RETURN @avg_sal;
    -> END
    -> $$

---

 

이렇게 만든 dept_avg_salary(d_id int) 를 활용해보자.

department 테이블

 

 

- 부서 정보와 부서 평균 연봉을 함께 가져오기.

 

mysql> SELECT *, dept_avg_salary(id)
        -> FROM department;

 

---

 

- 졸업 요건 중 하나인 토익 800 이상을 충족했는지를 알려주는 함수를 작성하자.

 

mysql> delimiter $$
mysql> CREATE FUNCTION toeic_pass_fail(toeic_score int)
    -> RETURNS char(4)
    -> NO SQL
    -> BEGIN

    ->             DECLARE pass_fail char(4);        변수선언
    ->             IF toeic_score is null             THEN SET pass_fail = 'fail';    
    ->             ELSEIF toeic_score < 800    THEN SET pass_fail = 'fail';
    ->             ELSE                                               SET pass_fail = 'pass';
    ->             END IF;
    ->             RETURN pass_fail;
    -> END
    -> $$

 

 

(변수선언 생략가능)

 

mysql> delimiter $$
mysql> CREATE FUNCTION toeic_pass_fail(toeic_score int)
    -> RETURNS char(4)
    -> NO SQL
    -> BEGIN
    ->             IF toeic_score is null             THEN SET @pass_fail = 'fail';    @ _변수 선언 없이 사용 가능
    ->             ELSEIF toeic_score < 800    THEN SET @pass_fail = 'fail';
    ->             ELSE                                               SET @pass_fail = 'pass';
    ->             END IF;
    ->             RETURN @pass_fail;
    -> END
    -> $$

 

 

 

이렇게 만든 toeic_pass_fail(toeic) 함수를 활용해보자.

 

SELECT *, toeic_pass_fail(toeic)

FROM student;

 

---

 

* stored function

 - 이외에도 loop를 돌면서 반복적인 작업을 수행하거나

 - case 키워드를 사용해서 값에 따라 분기 처리 하거나

 - 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있다.

 

---

 

* stored function 삭제하기

 

 - DROP FUNCTION stored_function_name;

 

---

 

* 등록된 stored function 파악하기

 

SHOW FUNCTION STATUS where DB = 'company';

 

 

=> 현재 활성화 되어 있는 DB인 'company'에 해당하는 function들이 만들어져 있다.

=> 다른 DB의 function을 만들고 싶다면 function 생성 시 DB이름 명시

                                                                CREATE FUNCTION db이름.FUNCTION

---

 

* DB이름 확인

 

SHOW DATABASES; 

 

---

 

* 함수의 코드(로직) 확인

 

SHOW CREATE FUNCTION id_generator;

 

 CREATE DEFINER=`root`@`localhost` ~~~

            함수 정의한 사람  = root계정 @ (명시하지 않으면 default값 => localhost)

 

 

 

 

* 이 내용은 MySQL 기준 *

---

 

* stored function은 언제 써야 할까?  (참고만*)

 

[ Three-tier architecture ]

 

 - util 함수로 쓰기에는 괜찮을 것 같다.

 - 비즈니스 로직을 stored function에 두는 것은 좋지 않을 것 같다.

  => 비즈니스 로직이 Logic tier 가 아닌 Data tier에 있게 되면 유지보수 힘듬. 

 

---

stored function            비즈니스 로직?

 

dept_avg_salary                   X

id_generator                         △

toeic_pass_fail                      O

 

---

 

https://www.youtube.com/watch?v=I1jjR58Rzic&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=10