http://cafe.naver.com/mysqlpg/413
중첩 커서 사용시 고려해야 할 부분에 대해서 정리해보았습니다.
중첩 커서 부분은 아래 링크의 내용을 참조한 것입니다.
http://rpbouman.blogspot.kr/2005/10/nesting-mysql-cursor-loops.html
Single Cursor Loops
일반적인 케이스는 간단한 한 개의 cursor loop 입니다.
더 이상의 레코드가 없을 때까지 loop을 돌면서 하나의 레코드씩 fetch를 하는 경우 입니다.
begin
declare v_col1 int; -- cursor 에서 fetch 할 레코드의 데이터를 담을 변수
declare no_more_rows boolean := FALSE; -- cursor looping을 control할 변수 선언
declare cursor1 cursor for -- cursor 선언
select col1
from myTable;
declare continue handler for not found -- cursor 에서 더이상 fetch 할 레코드가 없을 때 실행될 handler
set no_more_rows := TRUE;
open cursor1; -- cursor 선언시 지정된 쿼리를 실행하고 결과 셋을 저장하여 record를 looping 할 준비
LOOP1: loop -- looping LOOP1 block 의 시작
fetch cursor1 -- 현재 cursor pointer가 가리키는 record 를 fetch 하여 v_col1 에 넣는다
into v_col1; -- 여기서 fetch 할 row가 없을 때 NOT FOUND condition 이 되고 위에 선언된 not found handler 의set no_more_rows := TRUE 가 실행된다.
if no_more_rows -- fetch시에 record가 없었는지 확인
then
close cursor1; -- cursor1을 close 하여 resource 해제
leave LOOP1; -- LOOP1 block 을 빠져 나간다.
end if;
--
-- 기타 코드 들
--
end loop LOOP1; -- looping LOOP1 block 의 끝
end;
간단하고 전형적인 mysql procedure 코드 입니다.
여기서 OPEN, FETCH, CLOSE 는 다 선언 된 cursor1 과 코드 상에서 연관되어 일어나는 action들 입니다.
그런데 handler 는 선언 시 특정 cursor에 연관되어 선언되지 않습니다.
cursor1 에서 마지막 레코드가 fetch 되고 더이상 fetch 될 레코드가 없을 때
cursor1 이 NOT FOUND condition을 raise 하게 되면 이 때 NOT FOUND condition handler 와 연관을 갖게
되는데 cursor1 의 scope 내에 가장 가까운 NOT FOUND handler에 의해서 해당 condition 이 잡혀서
지정된 exception 처리를 하게 됩니다. 이런 부분 때문에 중첩 cursor loop 사용시 자신이 원하는대로
condition handler가 작동되도록 주의가 필요합니다.
Nested Cursor Loops
종종 두 개의 cursor를 중첩할 필요가 생깁니다.
중첩 cursor 사용시 중요한 것은 앞에 언급한 것처럼
handler 를 특정 cursor 와 선언 과정에서 연관시킬 수 없기 때문에
특정 cursor 가 fetch loop을 돌면서 NOT FOUND에 도달 했을 때 원하는 handler를 실행시키고 그 에 따라
두 개의 loop 중 해당 cursor 와 관련된 loop만 종료되게 하도록 코딩하는 것입니다.
1) Using a seperate block
가장 깔끔한 방법 중에 하나로 중첩 cursor 각각에 begin .. end 로 명확히 block을 나누어
각 block 내에서 각 cursor 에 적용될 not found handler를 선언해 주는 것입니다.
각 cursor 에서 raise 된 not found condition은in -> out 으로 전파 되는데
이 과정 중에 가장 가까운 handler 에 잡히게 됩니다. 즉, inner block(BLOCK2) 에서 발생한 condition은 inner block 에서 선언 된 handler 에 잡히게 됩니다.
그리고 outer block (BLOCK1) 에서 발생한 condition 은 inner block 에 있는 handler 에 잡힐 수 없기 때문에 inner block condition 은 inner block handler 에서 outer block condition 은 outer block handler 에서 exception 처리가 되어 의도한 대로 looping 을 빠져나갈 수 있습니다.
BLOCK1: begin
declare v_col1 int;
declare no_more_rows boolean := FALSE;
declare cursor1 cursor for
select col1
from MyTable;
declare continue handler for not found
set no_more_rows1 := TRUE;
open cursor1;
LOOP1: loop
fetch cursor1
into v_col1;
if no_more_rows1
then
close cursor1;
leave LOOP1;
end if;
BLOCK2: begin
declare v_col2 int;
declare no_more_rows2 boolean := FALSE;
declare cursor2 cursor for
select col2
from MyOtherTable
where ref_id = v_col1;
declare continue handler for not found
set no_more_rows2 := TRUE;
open cursor2;
LOOP2: loop
fetch cursor2
into v_col2;
if no_more_rows2
then
close cursor2;
leave LOOP2;
end if;
end loop LOOP2;
end BLOCK2;
end loop LOOP1;
end BLOCK1;
2) Resetting the loop control variable
1) 의 방법이 명확하긴 하지만 코드가 중복된다는 단점이 있습니다.
이를 피하고 의도한 대로 not found condition 발생시 해당 cursor와 연관된 loop 만을
종료하기 위해서는 loop control variable 을 inner loop 을 빠져나가는 단계에서 초기화 해주면 됩니다.
begin
declare no_more_rows boolean default false;
declare v_col1 int;
declare v_col2 int;
declare cursor1 cursor for
select col1
from MyTable;
declare cursor2 cursor for
select col2
from MyOtherTable
where ref_id = v_col1;
declare continue handler for not found
set no_more_rows := true;
open cursor1:
LOOP1: loop
fetch cursor1 into v_col1;
if no_more_rows
then
close cursor1;
leave LOOP1;
end if;
open cursor2;
LOOP2: loop
fetch cursor2 into v_col2;
if no_more_rows
then
set no_more_rows := false;
close cursor2;
leave LOOP2;
end if;
end loop LOOP2;
end loop LOOP1;
end;
이렇게 하면 코드 중복 없이 inner loop 이 종료 되면서 의도치 않게 outer loop 까지 종료 되는
것을 방지할 수 있습니다.
3) inner cursor loop 전용 프로시저를 만든다.
1) 과 유사한 방법으로 inner loop 을 위해 프로시저를 따로 생성하고
outer loop 에서 해당 프로시저를 호출하는 방법입니다.
아래와 같이 작성하는 방식은 관리 효율성적인 측면에 이점이 있습니다.
inner loop 을 재사용하거나 debugging 시에 각 모듈별로 할수 있다는 이점이 있습니다.
create procedure p_inner_loop(
IN p_col1 int
)
begin
declare v_col int;
declare no_more_rows boolean := FALSE;
declare cursor cursor1 for
select col2
from MyOtherTable
where ref_id = p_col1;
declare continue handler for not found
set no_more_rows := TRUE;
open cursor1;
LOOP1: loop
fetch cursor1
into v_col1;
if no_more_rows
then
close cursor1;
leave LOOP1;
end if;
end loop LOOP1;
end;
BLOCK1: begin
declare v_col1 int;
declare no_more_rows boolean1 := FALSE;
declare cursor1 cursor for
select col1
from MyTable;
declare continue handler for not found
set no_more_rows1 := TRUE;
open cursor1;
LOOP1: loop
fetch cursor1
into v_col1;
if no_more_rows1
then
close cursor1;
leave LOOP1;
end if;
call p_inner_loop(v_col1);
end loop LOOP1;
end BLOCK1;
Declare cursor position
문서에 다음과 같이 나와 있습니다.
Cursor declarations must appear before handler declarations and after variable and condition declarations.
그리고 cursor 선언 전에 dml 도 안 됩니다.
그래서 아래와 같이 하면 ERROR 납니다. 그런데 그냥 syntax 에러라고 문서에서 문법 체크하라고만 메시지가 나와서
모르고 하다 보면 이유가 뭔지 찾기가 어렵습니다. ㅠㅜ
begin
declare var1;
declare var2;
update t_table set status='E' where status='D' and reg_date > timestampadd(minute,-2,now());
declare c_status_E CURSOR for
select a,status from t_table where status = 'E';
...
...
end;
ERROR syntax 어쩌구 저쩌구 please 문법 체크
그래서 아래와 같이 begin end 로 block 을 나눠줘야 합니다.
begin
declare var1;
declare var2;
update t_table set status='E' where status='D' and reg_date > timestampadd(minute,-2,now());
begin
declare c_status_E CURSOR for
select a,status from t_table where status = 'E';
...
...
end;
end;
'연구개발 > MYSQL' 카테고리의 다른 글
| deterministic (0) | 2015.02.19 |
|---|---|
| MySQL optimize/analyze table (0) | 2015.02.18 |
| binlog 파일 관리 (0) | 2015.02.11 |
| Linux time. (real, user, sys) (0) | 2015.02.10 |
| [모니터링] 쿼리 (0) | 2015.02.10 |