10. DBMS에 저장해서 쓰는 함수 1

Stored Function

• 사용자가 정의한 함수
• DBMS에 저장되고 사용되는 함구
• SQL의 select, insert, update, delete statement에서 사용할 수 있다.

예제 1.

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

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

mysql> delimiter $$ // 함수를 만들어 주기위해 종료시 사용되는 delimiter을 변경해주었다. 
mysql> CREATE FUNCTION id_generator()
    -> RETURNS int
    -> NO SQL
    -> BEGIN
    ->  RETURN (1000000000 + floor(rand() *1000000000));
    -> END
    -> $$
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO employee
    -> VALUES (id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);
    // 함수를 이용해서 숫자를 램덤 생성한 데이터를 입력해준다.
mysql> SELECT * from employee;
+------------+---------+------------+------+-----------+-----------+---------+
| id         | name    | birth_date | sex  | position  | salary    | dept_id |
+------------+---------+------------+------+-----------+-----------+---------+
|          1 | MESSI   | 1987-02-01 | M    | DEV_BACK  | 100000000 |    1003 |
|          2 | JANE    | 1996-05-05 | F    | DSGN      | 180000000 |    1004 |
|          3 | JENNY   | 2000-10-12 | F    | DEV_BACK  |  50000000 |    1003 |
|          4 | BROWN   | 1996-03-13 | M    | CEO       | 240000000 |    NULL |
|          5 | DINGYO  | 1990-11-05 | M    | CTO       | 120000000 |    NULL |
|          6 | JULIA   | 1986-12-11 | F    | CFO       | 240000000 |    NULL |
|          7 | MINA    | 1993-06-17 | F    | DSGN      | 160000000 |    1004 |
|          9 | HENRY   | 1982-05-20 | M    | HR        |  82000000 |    NULL |
|         10 | NICOLE  | 1991-03-26 | F    | DEV_FRONT | 180000000 |    1003 |
|         11 | SUZANNE | 1993-03-23 | F    | PO        |  75000000 |    1005 |
|         12 | CURRY   | 1998-01-15 | M    | PLN       | 170000000 |    1004 |
|         13 | JISUNG  | NULL       | M    | PO        |  90000000 |    1005 |
|         14 | SAM     | NULL       | M    | DEV_INFRA |  70000000 |    NULL |
|         15 | SIMON   | 1982-08-04 | M    | PL        |  90000000 |    NULL |
| 1260254158 | JEHN    | 1991-08-04 | F    | PO        | 100000000 |    1005 |
+------------+---------+------------+------+-----------+-----------+---------+
15 rows in set (0.00 sec)

예제 2.

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

mysql> CREATE FUNCTION dept_avg_salary(d_id int) // 파라미터를 입력받는 함수 생성
-> RETURNS int
-> READS SQL DATA
-> BEGIN
-> DECLARE avg_sal int; // 변수 선언
-> select abg(salary) into avg_sal
-> from employee
-> where dept_id = d_id;
-> RETURN abg_sal;
-> END
-> $$

mysql> CREATE FUNCTION dept_avg_salary(d_id int) // 파라미터를 입력받는 함수 생성
    -> RETURNS int
    -> READS SQL DATA
    -> BEGIN
    ->  select abg(salary) into avg_sal @avg_sal //변수를 선언하지 않고 사용 할 수 있다.
    ->                  from employee
    ->                  where dept_id = d_id;
    ->  RETURN @avg_sal 
    -> END
    -> $$

mysql> SELECT*, dept_avg_salary(dept_id)
    -> FROM department;
+---------+-------------+-----------+--------------------------+
| dept_id | name        | leader_id | dept_avg_salary(dept_id) |
+---------+-------------+-----------+--------------------------+
|    1001 | headquarter |         4 |                     NULL |
|    1002 | HR          |      NULL |                     NULL |
|    1003 | development |         1 |                110000000 |
|    1004 | design      |         3 |                170000000 |
|    1005 | product     |        13 |                 88333333 |
+---------+-------------+-----------+--------------------------+
5 rows in set (0.01 sec)

예제 3.

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

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
    -> $$
Query OK, 0 rows affected (0.01 sec)

Stored Function

• 위 예제 외에도 loop를 돌면서 반복적인 작업을 수행할 수 있다.
• case 키워드를 사용하여 값에 따라 분기 처리 하거나 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있다.
• Function을 삭제하려면 DROP FUNCTION stored_function_name; 를 입력하면 삭제할 수 있다.

등록된 stored fuction 파악하기

mysql> SHOW FUNCTION STATUS where DB = 'company';
+---------+-----------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+-----------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| company | dept_avg_salary | FUNCTION | root@localhost | 2023-07-24 23:35:03 | 2023-07-24 23:35:03 | DEFINER | | euckr | euckr_korean_ci | utf8mb4_0900_ai_ci |
| company | id_generator | FUNCTION | root@localhost | 2023-07-24 23:11:21 | 2023-07-24 23:11:21 | DEFINER | | euckr | euckr_korean_ci | utf8mb4_0900_ai_ci |
| company | toeic_pass_fail | FUNCTION | root@localhost | 2023-07-24 23:49:59 | 2023-07-24 23:49:59 | DEFINER | | euckr | euckr_korean_ci | utf8mb4_0900_ai_ci |
+---------+-----------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.01 sec)

예제로 만든 stored function을 확인할 수 있다.

특정 DB에 함수를 만드는 방법

CREATE FUNCTION 특정DB.함수이름

stored function은 언제 사용해야 할까?

• Three-tier architecture