<p><br></p><h5 style="width: 1110px; color: rgb(33, 37, 41);"><a href="https://hvy.kr/post/426" target="_blank">[MySQL &amp; MariaDB] 반복문 while loop, cursor 대체</a></h5><p><br></p><p><br></p><p>회사 직원이랑 이야기하다보니 mariadb의 커서는 while문보다 빠르다길래 일단 형태만 정리해본다.</p><p><br></p><p><br></p><p><br></p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div>DELIMITER $$</div><br><div><span style="color: #569cd6;">USE</span> <span style="color: #ce9178;">`test`</span>$$</div><br><div><span style="color: #569cd6;">DROP</span> <span style="color: #569cd6;">PROCEDURE</span> <span style="color: #569cd6;">IF</span> <span style="color: #569cd6;">EXISTS</span> <span style="color: #ce9178;">`test_proc`</span>$$</div><br><div><span style="color: #569cd6;">CREATE</span> DEFINER=<span style="color: #ce9178;">`motolies`</span>@<span style="color: #ce9178;">`localhost`</span> <span style="color: #569cd6;">PROCEDURE</span> <span style="color: #ce9178;">`test_proc`</span>(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">IN</span> iPoint <span style="color: #569cd6;">INT</span>,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">OUT</span> oResultCode <span style="color: #569cd6;">CHAR</span>(<span style="color: #b5cea8;">1</span>)</div><div>)</div><div>COMMENT <span style="color: #ce9178;">'테스트 프로시저'</span></div><div><span style="color: #569cd6;">BEGIN</span></div><div>    <span style="color: #6a9955;">/* 커서가 가지고 있는 row를 모두 소진했는지에 대한 변수 */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span> mDone <span style="color: #569cd6;">INT</span> DEFAULT <span style="color: #b5cea8;">0</span>;</div><br><div>    <span style="color: #6a9955;">/* 커서에 fetch될 데이터들은 변수로 가지고 있어야 함 */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span> mPoint <span style="color: #569cd6;">INT</span> DEFAULT <span style="color: #b5cea8;">0</span>;</div><div>    <span style="color: #569cd6;">DECLARE</span> mTmpData <span style="color: #569cd6;">INT</span> DEFAULT <span style="color: #b5cea8;">0</span>;</div><br><div>    <span style="color: #6a9955;">/* 커서가 처음 가지고 있을 데이터 */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span> cur CURSOR FOR</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span> iPoint, <span style="color: #b5cea8;">3</span> <span style="color: #569cd6;">from</span> dual;</div><br><div>    <span style="color: #6a9955;">/* 커서가 데이터를 찾지 못하면 mDone이라는 변수에 1을 할당함 */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span> <span style="color: #569cd6;">CONTINUE</span> HANDLER FOR <span style="color: #569cd6;">NOT</span> FOUND <span style="color: #569cd6;">SET</span> mDone = <span style="color: #b5cea8;">1</span>;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">/* 만약 SQL에러라면 ROLLBACK 처리한다. */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span> EXIT HANDLER FOR SQLEXCEPTION</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">BEGIN</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span> oResultCode = <span style="color: #ce9178;">'E'</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">ROLLBACK</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span>;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">START</span> <span style="color: #569cd6;">TRANSACTION</span>;</div><div>        <span style="color: #6a9955;">/* 커서오픈 */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">OPEN</span> cur;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;read_loop : <span style="color: #569cd6;">LOOP</span></div><div>            <span style="color: #6a9955;">/* cursor의 데이터 fetch */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FETCH</span> cur <span style="color: #569cd6;">INTO</span> mPoint, mTmpData;</div><br><div>            <span style="color: #6a9955;">/* 데이터가 있는지 확인 */</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">IF</span> mDone <span style="color: #569cd6;">THEN</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  LEAVE read_loop;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span> <span style="color: #569cd6;">IF</span>;</div><br><div>            <span style="color: #569cd6;">SELECT</span> mPoint, mTmpData <span style="color: #569cd6;">FROM</span> dual;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span> <span style="color: #569cd6;">LOOP</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CLOSE</span> cur;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span> oResultCode = <span style="color: #ce9178;">'A'</span>;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">COMMIT</span>;</div><br><div><span style="color: #569cd6;">END</span>$$</div><br><div>DELIMITER ;</div></div><p><br></p><p><br></p><p><br></p>


[MySQL & MariaDB] 커서 cursor