mysql 프로시저질문

Question

프로시저 생성시 select한 결과값이 row가 여러개일경우 반복문을 통해 인덱스별로 변수에 값을담아 원하는 쿼리를 처리할수있을까요? 찾아보니 cursor를 사용하면 되는거같기도한데 select한 결과값으로 돌릴수있는지 잘몰라서 질문드립니다

캐티 · Answer

이렇게 되지 않겠나요.아주 단순한 예시이지만 적절한 참고가 되었을 것이어요.DECLARE row1 INT UNSIGNED DEFAULT 0; DECLARE row2 INT UNSIGNED DEFAULT 0; SELECT pk INTO row1 FROM sometable1 WHERE pk=1 LIMIT 1; SELECT pk INTO row2 FROM sometable1 WHERE pk=2 LIMIT 1;

르매 · Answer

RDB인 만큼 집합적으로 사고하면 대부분 LOOP 처리하지 않고 한 번에 해결할 수 있긴 합니다만, 구체적으로 어떤 사례인지 모르니.. 말씀하신 LOOP를 돌며 절차적으로 처리할 수 있는 방법을 알려드릴게요.CURSOR 사용-- SELECT 한 값을 담을 변수 (컬럼의 개수 만큼 선언) DECLARE vColumn01 int; DECLARE vColumn02 int; DECLARE vColumn03 int; -- 루프가 마지막 행까지 도달했는지 여부, false로 초기화 DECLARE vIsFinished boolean DEFAULT FALSE; -- 커서 선언 (FOR 이후에 LOOP 처리할 SELECT문을 작성) DECLARE vCursor CURSOR FOR SELECT column_01, column_02, column_03 FROM demo_table; -- NOT FOUND에 대해 CONTINUE HANDLER를 선언: -- ㄴ NOT FOUND가 발생할 때 CONTINUE 하겠다. -- ㄴ 단, BEGIN ~ END 사이의 구문을 자동 실행 후 CONTINUE 한다는 뜻 DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET vIsFinished &#61; TRUE; -- LOOP가 마지막 행에 도달해서 더 이상 가져올 행이 없을 때 vIsFinished 값을 TRUE로 변경 END; -- 커서 열기 OPEN vCursor; cursor_loop: LOOP FETCH vCursor -- 커서를 선언할 때 사용한 SELECT문에서 1개의 행을 가져온다. 즉, fetch한다. INTO vColumn01, vColumn02, vColumn03; -- 가져온 행의 각각의 컬럼에 담긴 값을 각각의 변수에 담는다. -- LOOP를 돌때마다 다음 1개의 행을 가져오는데, 더 이상 행이 없으면 NOT FOUND 발생 -- ㄴ 1. CONTINUE HANDLER가 vIsFinished 변수의 값을 TRUE로 바꾼다 -- ㄴ 2. 루프를 탈출한다. IF vIsFinished THEN LEAVE cursor_loop; END IF; /* 여기서 변수에 담은 값으로 필요한 처리를 진행합니다. */ END LOOP; -- 커서 닫기 (커서는 RDB 서버에 생성되는 자원이기 때문에 사용하지 않는 커서는 빨리 닫아서 지원을 반환해야 합니다) CLOSE vCursor;임시 테이블 사용-- SELECT 한 값을 담을 변수 (컬럼의 개수 만큼 선언) DECLARE vColumn01 int; DECLARE vColumn02 int; DECLARE vColumn03 int; -- WHILE LOOP에 사용할 포인터를 담을 변수 DECLARE vI int DEFAULT 1; -- 1로 초기화 DECLARE vJ int; -- LOOP를 돌아야 하는 총 횟수가 담길 변수 -- 임시 테이블 생성 CREATE TEMPORARY TABLE tmp_cursor ( row_id smallint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, column_01 int, column_02 int, column_03 int ) ENGINE &#61; MEMORY; -- 담아야 하는 행의 수가 매우 많다면 INNODB를 사용합니다. -- LOOP 처리할 SELECT문을 tmp_cursor에 INSERT 합니다. INSERT INTO tmp_cursor (column_01, column_02, column_03) SELECT column_01, column_02, column_03 FROM demo_table; SET vJ &#61; ROW_COUNT(); -- INSERT한 행의 수를 vJ 변수에 넣습니다. WHILE vI <&#61; vJ DO SELECT column_01, column_02, column_03 INTO vColumn01, vColumn02, vColumn03 FROM tmp_cursor WHERE row_id &#61; vI; /* 여기서 변수에 담은 값으로 필요한 처리를 진행합니다. */ SET vI &#61; vI &#43; 1; END WHILE; -- 임시 테이블 삭제 DROP TEMPORARY TABLE tmp_cursor;커서 객체도 내부적으로는 임시 테이블을 사용합니다. 차이가 있다면 어떤 스토리지 엔진을 사용할지는 서버에서 max_heap_table_size 이나 tmp_table_size 를 참조해서 담아야 하는 데이터량과 비교하여 결정되는데, 적은 양이면 MEMORY 많은 양이면 MyISAM을 사용하죠.커서를 사용하지 않고 직접 임시 테이블을 작성하는 것은 개발자 입장에서는 좀 더 직관적이라는 장점이 있습니다. 필요하면 INNODB 스토리지 엔진을 사용할 수도 있고.. Read Only인 커서와 다르게 로직 처리 중 임시 테이블의 값을 변경할 수도 있겠죠.결론: SP안에서 LOOP를 돌며 절차적으로 처리하는 것은 최대한 피한다. 사실 대부분의 작업은 한 방 쿼리로 해결할 수 있고, 그렇지 않은 경우는 매우 드물다.하지만 절차적으로 처리해야 한다면 커서와 임시테이블을 사용하는 방법이 있는데, 어느 쪽이 낫다라고 단언하긴 어렵다. (개인적으로는 임시 테이블 사용이 제 취향이긴 합니다.)

mysql 프로시저질문

2개의 답변

기술 목록