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 서버에서 할당한 스택의 메모리가 다 소모돼 버린다면 "스택 메모리가 부족하다(Thread stack overrun)"라는 에러 메시지와 함께 종료된다.
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)
'MySQL > MySQL' 카테고리의 다른 글
Collation (0) | 2024.12.20 |
---|---|
문자열(CHAR와 VARCHAR) (0) | 2024.12.13 |
스토어드 프로그램 본문(Body) 작성 (0) | 2024.12.13 |
스토어드 함수 (0) | 2024.12.13 |
스토어드 프로시저 (0) | 2024.12.13 |