<p><br></p><h6 style="width: 1110px; color: rgb(33, 37, 41);"><a href="https://hvy.kr/post/432" target="_blank">[MySQL &amp; MariaDB] 커서 cursor</a></h6><p><br></p><h6 style="width: 1110px; color: rgb(33, 37, 41);"><a href="https://hvy.kr/post/425" target="_blank">[MySQL &amp; MariaDB] 문자열 분리해서 임시테이블에 넣기 split</a></h6><p><br></p><p><br></p><p>아래꺼보다 보기 쉽고 내부변수로 변경함</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&nbsp;$$</div><br><div><span style="color: #569cd6;">DROP</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;<span style="color: #ce9178;">`while_loop_example`</span>$$</div><div><span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #ce9178;">`while_loop_example`</span>(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">IN</span>&nbsp;<span style="color: #ce9178;">`iCount`</span>&nbsp;<span style="color: #569cd6;">INT</span></div><div>)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;COMMENT&nbsp;<span style="color: #ce9178;">'while&nbsp;샘플'</span></div><div><span style="color: #569cd6;">BEGIN</span></div><div>&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mI&nbsp;<span style="color: #569cd6;">INT</span>&nbsp;DEFAULT&nbsp;<span style="color: #b5cea8;">0</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mMaxI&nbsp;<span style="color: #569cd6;">INT</span>&nbsp;DEFAULT&nbsp;<span style="color: #b5cea8;">0</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;iCount는&nbsp;최대치&nbsp;이다</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span>&nbsp;mMaxI&nbsp;=&nbsp;iCount;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHILE</span>&nbsp;mI&nbsp;&lt;&nbsp;mMaxI&nbsp;DO</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;mI&nbsp;변수에&nbsp;값을&nbsp;증가</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span>&nbsp;mI&nbsp;=&nbsp;mI&nbsp;+&nbsp;<span style="color: #b5cea8;">1</span>;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;mI;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span>&nbsp;<span style="color: #569cd6;">WHILE</span>;</div><br><div><span style="color: #569cd6;">END</span>$$</div><div>DELIMITER&nbsp;;</div></div><p><br></p><p><br></p><p><br></p><p>커서가 느리다는 말을 듣고나서 어느순간 커서를 while 문으로 변환하여 대체하고 있다.</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&nbsp;$$</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DROP</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;while_loop_example$$</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;while_loop_example</div><div>&nbsp;&nbsp;&nbsp;&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">in</span>&nbsp;Ids&nbsp;<span style="color: #569cd6;">TEXT</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">LANGUAGE</span>&nbsp;<span style="color: #569cd6;">SQL</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">NOT</span>&nbsp;DETERMINISTIC</div><div>&nbsp;&nbsp;&nbsp;&nbsp;CONTAINS&nbsp;<span style="color: #569cd6;">SQL</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SQL</span>&nbsp;<span style="color: #569cd6;">SECURITY</span>&nbsp;DEFINER</div><div>&nbsp;&nbsp;&nbsp;&nbsp;COMMENT&nbsp;<span style="color: #ce9178;">'while&nbsp;예제'</span></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: #6a9955;">--&nbsp;결과용&nbsp;테이블</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DROP</span>&nbsp;TEMPORARY&nbsp;<span style="color: #569cd6;">TABLE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;tRESULT;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CREATE</span>&nbsp;TEMPORARY&nbsp;<span style="color: #569cd6;">TABLE</span>&nbsp;tRESULT</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Id&nbsp;<span style="color: #569cd6;">INT</span>&nbsp;<span style="color: #569cd6;">NOT</span>&nbsp;<span style="color: #569cd6;">NULL</span>,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Memo&nbsp;<span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">128</span>)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;ENGINE&nbsp;=&nbsp;MEMORY;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;입력값&nbsp;나누기</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CALL</span>&nbsp;<span style="color: #dcdcaa;">string_split</span>(Ids,&nbsp;<span style="color: #ce9178;">'|'</span>);</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;while&nbsp;문을&nbsp;위해서&nbsp;분리한&nbsp;값에&nbsp;row&nbsp;number를&nbsp;주기위한&nbsp;임시&nbsp;테이블</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DROP</span>&nbsp;TEMPORARY&nbsp;<span style="color: #569cd6;">TABLE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;tLoop;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CREATE</span>&nbsp;TEMPORARY&nbsp;<span style="color: #569cd6;">TABLE</span>&nbsp;tLoop</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RN&nbsp;<span style="color: #569cd6;">INT</span>&nbsp;<span style="color: #569cd6;">NOT</span>&nbsp;<span style="color: #569cd6;">NULL</span>,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Content&nbsp;<span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">4096</span>)&nbsp;<span style="color: #569cd6;">NOT</span>&nbsp;<span style="color: #569cd6;">NULL</span>&nbsp;DEFAULT&nbsp;<span style="color: #ce9178;">''</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;ENGINE&nbsp;=&nbsp;MEMORY;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;row&nbsp;number를&nbsp;포함해서&nbsp;임시테이블에&nbsp;입력</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">INSERT</span>&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;tLoop(RN,&nbsp;Content)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;(@RN&nbsp;:=&nbsp;@RN&nbsp;+&nbsp;<span style="color: #b5cea8;">1</span>)&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;ROWNUM,&nbsp;T.Content</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;tmpContents&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;T</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CROSS</span>&nbsp;<span style="color: #569cd6;">JOIN</span>&nbsp;(<span style="color: #569cd6;">SELECT</span>&nbsp;@RN&nbsp;:=&nbsp;<span style="color: #b5cea8;">0</span>)&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;R</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">ORDER&nbsp;BY</span>&nbsp;Content;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;while&nbsp;문을&nbsp;돌기위해&nbsp;시작값,&nbsp;종료값&nbsp;변수에&nbsp;대입</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #dcdcaa;">MIN</span>(RN),&nbsp;<span style="color: #dcdcaa;">MAX</span>(RN)&nbsp;&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;&nbsp;@i,&nbsp;@maxi&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;tLoop;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">while</span>&nbsp;@i&nbsp;&lt;=&nbsp;@maxi&nbsp;do</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;cursor&nbsp;의&nbsp;fetch&nbsp;작업과&nbsp;같이&nbsp;분리한&nbsp;테이블에서&nbsp;값을&nbsp;변수에&nbsp;대입하고</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;from_base64(Content)&nbsp;<span style="color: #569cd6;">into</span>&nbsp;@Id&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;tLoop&nbsp;<span style="color: #569cd6;">where</span>&nbsp;RN&nbsp;=&nbsp;@i;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;결과용&nbsp;테이블에&nbsp;값&nbsp;넣기</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">INSERT</span>&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;tRESULT(Id,&nbsp;Memo)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;id,&nbsp;memo&nbsp;<span style="color: #569cd6;">from</span>&nbsp;ex_table</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">where</span>&nbsp;id&nbsp;=&nbsp;@Id;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;@i&nbsp;변수에&nbsp;값을&nbsp;증가</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span>&nbsp;@i&nbsp;=&nbsp;@i&nbsp;+&nbsp;<span style="color: #b5cea8;">1</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">end</span>&nbsp;<span style="color: #569cd6;">while</span>;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;결과용&nbsp;테이블&nbsp;반환</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">select</span>&nbsp;*&nbsp;<span style="color: #569cd6;">from</span>&nbsp;tRESULT;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span>$$</div><div>DELIMITER&nbsp;;</div></div><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p>


[MySQL & MariaDB] 반복문 while loop, cursor 대체