스토어드 함수

스토어드 함수(Stored Function)

​

스토어드 함수는 하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용한다. 각 부서별로 가장 최근에 배속된 사원을 2명씩 가져오는 기능을 생각해 보자. dept_emp 테이블의 데이터를 부서별로 그룹핑하는 것까지는 가능하지만, 해당 부서 그룹별로 최근 2명씩만 잘라서 가져오는 방법은 없다. 이럴 때 부서 코드를 인자로 입력받아 최근 2명의 사원 번호만 SELECT하고 문자열로 결합해서 반환하는 함수를 만들어서 사용하면 된다. 만약 이런 스토어드 함수가 준비되면 다음과 같이 사용하면 된다.

select dept_no, sf_getRecentEmp(dept_no)
  from dept_emp 
 group by dept_no;

 

SQL 문장과 관계없이 별도로 실행되는 기능이라면 굳이 스토어드 함수를 개발할 필요가 없다. 독립적으로 실행돼도 된다면 스토어드 프로시저를 사용하는 것이 좋다. 상대적으로 스토어드 함수는 스토어드 프로시저보다 제약 사항이 많기 때문이다. 스토어드 프로시저와 비교했을 때 스토어드 함수의 장점은 SQL 문장의 일부로 사용할 수 있다는 것이다.

 

 

1. 스토어드 함수 생성 및 삭제

​

스토어드 함수는 CREATE FUNCTION 명령으로 생성할 수 있으며, 모든 입력 파라미터는 읽기 전용이라서 IN이나 OUT 또는 INOUT과 같은 형식을 지정할 수 없다. 그리고 스토어드 함수는 반드시 정의부에 RETURNS 키워드를 이용해 반환되는 값의 타입을 명시해야 한다.

​

mysql> CREATE FUNCTION sf_sum(param1 INTEGER, param2 INTEGER)
    ->  RETURNS INTEGER
    -> BEGIN
    ->  DECLARE param3 INTEGER DEFAULT 0;
    ->  SET param3 = param1+param2;
    ->  RETURN param3;
    -> END;;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

mysql> show global variables like 'log_bin_trust_function_creators';;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

 

SET GLOBAL log_bin_trust_function_creators = 1; -- ON FUNCTION 생성 가능

SET GLOBAL log_bin_trust_function_creators = 0; -- OFF

(복사하면 공백문자로 에러나면 직접 기입하자.)

 

mysql> DELIMITER ;;
mysql> create function sf_sum(param1 integer, param2 integer)
    ->  returns integer
    -> begin
    ->  declare param3 integer default 0;
    ->  set param3 = param1 + param2;
    ->  return param3;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select sf_sum(1,2);
+-------------+
| sf_sum(1,2) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

 

스토어드 함수가 스토어드 프로시저와 크게 다른 부분은 다음 두 가지다.

- 함수 정의부에 RETURNS로 반환되는 값의 타입을 명시해야 한다.

- 함수 본문 마지막에 정의부에 지정된 타입과 동일한 타입의 값을 RETURN 명령으로 반환해야 한다.

​

스토어드 프로시저와는 달리 스토어드 프로그램의 본문(BEGIN ... END)에서는 다음과 같은 사항을 사용하지 못한다.

- PREFARE와 EXECUTE 명령을 이용한 프리페어 스테이트먼트를 사용할 수 없다.

- 명시적 또는 묵시적인 ROLLBACK/COMMIT을 유발하는 SQL 문장을 사용할 수 없다.

- 재귀 호출(Recursive call)을 사용할 수 없다.

- 스토어드 함수 내에서 프로시저를 호출할 수 없다.

- 결과 셋을 반환하는 SQL 문장을 사용할 수 없다.

​

결과 셋을 Fetch하지 않아서 결과 셋이 클라이언트로 전송되는 스토어드 함수를 생성하면 어떤 에러가 발생하는지 테스트로 한번 살펴보자. 스토어드 함수의 본문에서 커서나 SELECT 쿼리를 사용해도 특별히 문제되지 않지만 스토어드 프로시저에서와 같이 디버깅 용도로 화면에 메시지를 출력하기 위해서는 사용할 수 없다. 즉 스토어드 함수에서 커서를 정의하면 반드시 오픈해야 하며, "SELECT .. INTO .."가 아닌 단순히 SELECT 쿼리만을 실행해서는 안 된다. 스토어드 함수 내에서 오픈되지 않는 커서나 단순 SELECT 쿼리가 실행되는 것은 결과적으로 클라이언트로 쿼리의 결과 셋을 반환하는 것과 똑같기 때문이다. 동일한 효과를 만들어 내는 SHOW 또는 EXPLAIN 등의 명령도 사용할 수 없다.

 

mysql> DELIMITER ;;
mysql> CREATE FUNCTION sf_resultset_test()
    ->  RETURNS INTEGER
    -> BEGIN
    ->  DECLARE param3 INTEGER DEFAULT 0;
    ->  SELECT 'Start stored function' AS debug_message;
    ->  RETURN param3;
    -> END;;
ERROR 1415 (0A000): Not allowed to return a result set from a function

 

 

​

2. 스토어드 함수 실행

​

스토어드 함수는 CALL 명령으로 실행할 수 없다. SELECT 문장을 이용해 실행한다.

mysql> DELIMITER ;
mysql> select sf_sum(1,2) as sum;
+------+
| sum  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> call sf_sum(1,2);
ERROR 1305 (42000): PROCEDURE employees.sf_sum does not exist