Let's have a cup of coffee.

1.세션변수

​

스토어드 프로그램에서는 DECLARE 명령을 이용해 스토어드 프로그램의 로컬 변수를 정의할 수 있다. 또한 스토어드 프로그램 내에서는 "@"로 시작하는 사용자 변수를 사용할 수도 있다. DECLARE로 스토어드 프로그램의 변수를 정의할 때는 정확한 타입과 길이를 명시해야 하지만 사용자 변수는 이런 제약이 없다.

mysql> DELIMITER ;;
mysql> CREATE FUNCTION sf_getsum(p_arg1 INT, p_arg2 INT)
    -> RETURNS INT
    -> BEGIN
    ->  DECLARE v_sum INT DEFAULT 0;
    ->  SET v_sum=p_arg1 + p_arg2;
    ->  SET @v_sum=v_sum;
    ->  RETURN v_sum;
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT sf_getsum(1,2);;
+----------------+
| sf_getsum(1,2) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @v_sum;;
+--------+
| @v_sum |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> DELIMITER ;

사용자 변수는 타입을 지정하지 않기 때문에 데이터 타입에 대해 안전하지 않고, 영향을 미치는 범위가 넓기 때문에 그만큼 느리게 처리된다. 또한 사용자 변수는 적어도 그 커넥션에서는 계속 그 값을 유지한 채 남아 있기 때문에 항상 사용하기 전에 적절한 값으로 초기화하고 사용해야 한다.

스토어드 프로그램에서 프리페어 스테이트먼트를 실행하려면 반드시 세션 변수를 사용할 수 밖에 없다. 하지만 이러한 경우가 아니라면 가능한 세션 변수보다는 스토어드 프로그램의 로컬 변수를 사용하자.

2.재귀호출

​

재귀 호출은 스토어드 프로시저에서만 사용 가능하면 스토어드 함수와 트리거 그리고 이벤트에서는 사용할 수 없다. 또한 프로그래밍 언어에서처럼 재귀 호출이 무한 반복되거나 너무 많이 반복해서 호출되면 스택의 메모리 공간이 모자라서 오류가 발생할 수도 있다.

MySQL에서는 최대 몇 번까지 재귀 호출을 허용할지를 설정하는 시스템 변수가 있다. max_sp_recursion_depth인데 기본적으로 0으로 설정돼어 있다. 이 설정값을 변경하지 않으면 재귀 호출을 사용할 수 없고 에러 메시지가 출력되고 프로시저의 실행은 종료된다.

mysql> show variables like 'max_sp_recursion_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_sp_recursion_depth | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

다음 예제는 "SET max_sp_recursion_depth=50;"를 이용해 최대 재귀 호출 가능 횟수를 50회로 설정했다. 예제의 스토어드 프로시저는 1씩 감소하면서 재귀 호출을 수행하므로 max_sp_recursion_depth 시스템 변수가 50으로 설정되면 재귀 호출도 50이상은 할 수 없다는 것을 의미한다.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sf_getfactorial(IN p_max INT, OUT p_sum INT)
    -> BEGIN
    ->  SET max_sp_recursion_depth=5;
    ->  SET p_sum=1;
    ->  
    ->  IF p_max>1 THEN
    ->   CALL sf_decreaseandmultiply(p_max,p_sum);
    ->  END IF;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sf_decreaseandmultiply(IN p_current INT, INOUT p_sum INT)
    -> BEGIN
    ->  SET p_sum=p_sum*p_current;
    ->  SELECT CONCAT('>p_sum:',p_sum);
    ->  SELECT CONCAT('>p_current:',p_current);
    ->  
    ->  IF p_current>1 THEN
    ->    CALL sf_decreaseandmultiply(p_current-1,p_sum);
    ->  END IF;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sf_getfactorial(3,@factorial);
+-------------------------+
| CONCAT('>p_sum:',p_sum) |
+-------------------------+
| >p_sum:3                |
+-------------------------+
1 row in set (0.01 sec)

+---------------------------------+
| CONCAT('>p_current:',p_current) |
+---------------------------------+
| >p_current:3                    |
+---------------------------------+
1 row in set (0.01 sec)

+-------------------------+
| CONCAT('>p_sum:',p_sum) |
+-------------------------+
| >p_sum:6                |
+-------------------------+
1 row in set (0.01 sec)

+---------------------------------+
| CONCAT('>p_current:',p_current) |
+---------------------------------+
| >p_current:2                    |
+---------------------------------+
1 row in set (0.01 sec)

+-------------------------+
| CONCAT('>p_sum:',p_sum) |
+-------------------------+
| >p_sum:6                |
+-------------------------+
1 row in set (0.01 sec)

+---------------------------------+
| CONCAT('>p_current:',p_current) |
+---------------------------------+
| >p_current:1                    |
+---------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

3. 중첩 커서 사용

​

중첩된 루프 안에서 두 개의 커서를 동시에 열어서 사용할 때는 특별히 예외 핸들링 부분에 주의해야 한다.

​

​

1) 중첩된 커서 사용

​

아래 예제는 외부 루프와 내부 루프에서 각각 동시에 커서를 하나씩 사용하고 있다. 하지만 커서를 끝까지 읽었는지 판단하는 핸들러는 하나만 정의해서 공통으로 사용하고 있다. 이러한 이유로 LOOP_INNER의 반복이 끝나고 나면 반드시 v_no_more_rows를 FALSE로 변경해야만 LOOP_OUTER가 계속 반복 실행할 수가 있다. 즉, 하나의 로컬 변수로 두 개의 반복 루프를 제어하다보니 이런 이해하기 힘든 조작이 필요해진 것이다.

mysql> DROP PROCEDURE sp_updateemployeehiredate;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;;
mysql> 
mysql> CREATE PROCEDURE sp_updateemployeehiredate()
    -> BEGIN
    ->   -- // 첫 번째 커서로부터 읽은 부서 번호를 저장
    ->   DECLARE v_dept_no CHAR(4);
    ->   -- // 두 번째 커서로부터 읽은 사원 번호를 저장
    ->   DECLARE v_emp_no INT;
    ->   -- // 커서를 끝까지 읽었는지 여부를 나타내는 플래그를 저장
    ->   DECLARE v_no_more_rows BOOLEAN DEFAULT FALSE;
    ->   
    ->   -- // 부서 정보를 읽는 첫 번째 커서
    ->   DECLARE v_dept_list CURSOR FOR
    ->      SELECT dept_no FROM departments;
    ->  
    ->   -- // 부서별 사원 1명을 읽는 두 번째 커서
    ->   DECLARE v_emp_list CURSOR FOR
    ->      SELECT emp_no FROM dept_emp WHERE dept_no=v_dept_no LIMIT 1;
    ->  
    ->   -- // 커서의 레코드를 끝까지 다 읽은 경우에 대한 핸들러
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_rows := TRUE;
    ->   
    ->   OPEN v_dept_list;
    ->   
    ->   LOOP_OUTER: LOOP -- // 외부 루프 시작
    ->     FETCH v_dept_list INTO v_dept_no;
    -> IF v_no_more_rows THEN  -- // 레코드를 모두 읽었으면 커서 종료 및 외부 루프 종료  
    ->   CLOSE v_dept_list;
    ->   SELECT CONCAT(' <<<<<<<< LOOP OUTER END >>>>>>>> ') AS debug_message;
    ->   LEAVE loop_outer;  
    -> END IF;
    -> SELECT CONCAT(' > v_dept_no :', v_dept_no) AS debug_message;
    -> 
    -> OPEN v_emp_list;
    -> 
    -> LOOP_INNER: LOOP  -- // 내부 루프 시작 
    ->   FETCH v_emp_list INTO v_emp_no;  
    ->   -- // 레코드를 모두 읽었으면 커서 종료 및 내부 루프를 종료
    ->   IF v_no_more_rows THEN
    ->   -- // 반드시 no_more_rows를 FALSE로 다시 변경해야 한다.
    ->   -- // 그렇지 않으면 내부 루프 때문에 외부 루프까지 종료돼 버린다.
    ->     SET v_no_more_rows := FALSE;
    ->     CLOSE v_emp_list;
    ->     SELECT CONCAT(' <<<<<<<< LOOP INNER END >>>>>>>> ') AS debug_message;    -> 
    ->     LEAVE loop_inner;
    ->   END IF;
    ->   SELECT CONCAT(' > v_emp_no :', v_emp_no) AS debug_message;
    ->   
    ->     END LOOP loop_inner; -- // 내부 루프 종료
    -> 
    ->   END LOOP loop_outer; -- // 외부 루프 종료
    ->  
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;

위 프로시저를 실행 시 Display all 1003 possibilities? (y or n) 같은 에러가 나면은 Notepad와 같은 곳에서 바꾸기로 \t 을 공백으로 만들어 주면 된다.

mysql> call sp_updateemployeehiredate;
+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d009 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10011 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d005 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10001 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d002 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10042 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d003 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10005 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d001 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10017 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d004 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10003 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d006 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10009 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d008 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10007 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d007 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10002 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP OUTER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

2) 중첩된 커서 사용(핸들러 처리 보완)

​

반복 루프가 여러 번 중첩되어 커서가 사용될 때는 LOOP_OUTER와 LOOP_INNER를 서로 다른 BEGIN ... END 블록으로 구분해서 작성할 수 있다. 스토어드 프로시저 코드의 처리 중 발생한 에러나 예외는 항상 가장 가까운 블럭에 정의된 핸들러가 사용되므로 각 반복루프를 블록으로 처리할 수 있다.

아래 예제는 사원을 조회하는 커서와 부서를 조회하는 각 커서에 대해 예외 핸들러를 정의 하였다. 중첩된 커서를 각 프로시저 블럭에 작성함으로써 각 커서별로 이벤트 핸들러를 생성할 수 있게 된 것이다.

mysql> DELIMITER ;;
mysql> 
mysql> CREATE PROCEDURE sp_updateemployeehiredate1()
    -> BEGIN
    ->   -- // 첫 번째 커서로부터 읽은 부서 번호를 저장
    ->   DECLARE v_dept_no CHAR(4);
    ->   -- // 커서를 끝까지 읽었는지 여부를 나타내는 플래그를 저장
    ->   DECLARE v_no_more_depts BOOLEAN DEFAULT FALSE;  
    ->   -- // 부서 정보를 읽는 첫 번째 커서
    ->   DECLARE v_dept_list CURSOR FOR
    ->      SELECT dept_no FROM departments; 
    ->   -- // 부서 커서의 레코드를 끝까지 다 읽은 경우에 대한 핸들러
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_depts := TRUE;
    ->   
    ->   OPEN v_dept_list;
    -> 
    ->   LOOP_OUTER: LOOP -- // 외부 루프 시작
    ->     FETCH v_dept_list INTO v_dept_no;
    -> IF v_no_more_depts THEN  -- // 레코드를 모두 읽었으면 커서 종료 및 외부 루프 종료  
    ->   CLOSE v_dept_list;
    ->   LEAVE loop_outer;  
    -> END IF;
    -> SELECT CONCAT(' > v_dept_no :', v_dept_no) AS debug_message;
    -> 
    ->   BLOCK_INNER: BEGIN -- // 내부 프로시저 블럭 시작 
    ->     -- // 두 번째 커서로부터 읽은 사원 번호 저장
    ->     DECLARE v_emp_no INT;
    -> -- // 사원 커서를 끝까지 읽었는지 여부를 위한 플래그 저장 
    -> DECLARE v_no_more_employees BOOLEAN DEFAULT FALSE;
    ->     -- // 부서별 사원 1명을 읽는 두 번째 커서
    ->     DECLARE v_emp_list CURSOR FOR
    ->      SELECT emp_no FROM dept_emp WHERE dept_no=v_dept_no LIMIT 1;
    ->     -- // 사원 커서의 레코드를 끝까지 다 읽은 경우에 대한 핸들러
    ->     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_employees := TRUE;
    -> 
    -> OPEN v_emp_list;
    -> 
    -> LOOP_INNER: LOOP  -- // 내부 루프 시작 
    ->   FETCH v_emp_list INTO v_emp_no;  
    ->   -- // 레코드를 모두 읽었으면 커서 종료 및 내부 루프를 종료
    ->   IF v_no_more_employees THEN   
    ->     CLOSE v_emp_list;
    ->     LEAVE loop_inner;
    ->   END IF;
    ->   SELECT CONCAT(' > v_emp_no :', v_emp_no) AS debug_message;  
    -> END LOOP loop_inner; -- // 내부 루프 종료
    -> 
    ->    END block_inner; -- // 내부 프로시저 블럭 종료
    -> 
    ->   END LOOP loop_outer; -- // 외부 루프 종료
    ->  
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

사원 정보에 대한 커서가 끝까지 읽히면 v_no_more_employees라는 변수가 TRUE로 설정되며, 부서 정보에 대한 커서가 끝까지 읽히면 v_no_more_depts라는 변수가 TRUE로 설정된다. 1)번 예제에서와 같이 플래그 변수의 값을 다시 조정하는 작업을 하지 않아도 된다.

mysql> call sp_updateemployeehiredate1;
+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d009 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10011 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d005 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10001 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d002 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10042 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d003 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10005 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d001 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10017 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d004 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10003 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d006 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10009 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d008 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10007 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d007 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10002 |
+--------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

스토어드 함수(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

mysql> DELIMITER ;;
mysql> create procedure sp_sum (IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
    -> begin
    -> set param3 = param1 + param2;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> set @result:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_sum(1,3,@result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

mysql> set @param1:=2;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param2:=4;
Query OK, 0 rows affected (0.00 sec)

mysql> set @result:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_sum(@param1,@param2,@result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

3. 스토어드 프로시저의 커서 반환

​

스토어드 프로시저 내에서 커서를 오픈하지 않거나 SELECT 쿼리의 결과 셋을 Fetch 하지 않으면 해당 쿼리의 결과 셋은 클라이언트로 바로 전송된다.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sp_selectEmployees (IN in_empno INTEGER)
    -> BEGIN
    ->   SELECT * FROM employees WHERE emp_no=in_empno;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call sp_selectEmployees(10001);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

실행 결과를 보면 스토어드 프로시저에서 SELECT 쿼리의 결과 셋을 별도로 반환하는 OUT 변수에 담거나 화면에 출력하는 처리를 하지도 않았는데 쿼리의 결과가 클라이언트로 전송된 것을 확인할 수 있다. 물론 이 기능은 JDBC를 이용하는 자바 프로그램에서도 그대로 이용할 수 있으며, 하나의 스토어드 프로시저에서 2개 이상의 결과 셋을 반환할 수도 있다. 스토어드 프로시저에서 쿼리의 결과 셋을 클라이언트로 전송하는 기능은 스토어드 프로시저의 디버깅 용도로도 자주 사용한다.

​

다음 예제는 프로시저의 본문 처리가 시작되기 전에 입력된 값을 화면에 표시하도록 스토어드 프로시저의 내용을 조금 변경하는 것이다.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sp_sum1 (IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
    -> BEGIN
    -> SELECT '> Stored procedure started.' AS debug_message;
    -> SELECT CONCAT(' > param1 : ', param1) AS debug_message;
    -> SELECT CONCAT(' > param2 : ', param2) AS debug_message;
    -> SET param3 = param1+param2;
    -> SELECT '> Stored procedure completed.' AS debug_message;
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @result:=0;;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_sum1(4,6,@result);;
+-----------------------------+
| debug_message               |
+-----------------------------+
| > Stored procedure started. |
+-----------------------------+
1 row in set (0.00 sec)

+---------------+
| debug_message |
+---------------+
|  > param1 : 4 |
+---------------+
1 row in set (0.00 sec)

+---------------+
| debug_message |
+---------------+
|  > param2 : 6 |
+---------------+
1 row in set (0.00 sec)

+-------------------------------+
| debug_message                 |
+-------------------------------+
| > Stored procedure completed. |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @result;;
+---------+
| @result |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)