<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="cursors">
      </a>
      15.6.6 Cursors
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="close.html">
       15.6.6.1 Cursor CLOSE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="declare-cursor.html">
       15.6.6.2 Cursor DECLARE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="fetch.html">
       15.6.6.3 Cursor FETCH Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="open.html">
       15.6.6.4 Cursor OPEN Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="cursor-restrictions.html">
       15.6.6.5 Restrictions on Server-Side Cursors
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045176169264">
  </a>
  <p>
   MySQL supports cursors inside stored programs. The syntax is as in
      embedded SQL. Cursors have these properties:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Asensitive: The server may or may not make a copy of its
          result table
     </p>
    </li>
    <li class="listitem">
     <p>
      Read only: Not updatable
     </p>
    </li>
    <li class="listitem">
     <p>
      Nonscrollable: Can be traversed only in one direction and
          cannot skip rows
     </p>
    </li>
   </ul>
  </div>
  <p>
   Cursor declarations must appear before handler declarations and
      after variable and condition declarations.
  </p>
  <p>
   Example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28874666"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> curdemo<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">DECLARE</span> done <span class="token datatype">INT</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">FALSE</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> a <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> b<span class="token punctuation">,</span> c <span class="token datatype">INT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> cur1 <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span> <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span><span class="token keyword">data</span> <span class="token keyword">FROM</span> test<span class="token punctuation">.</span>t1<span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> cur2 <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span> <span class="token keyword">SELECT</span> i <span class="token keyword">FROM</span> test<span class="token punctuation">.</span>t2<span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token operator">NOT</span> <span class="token keyword">FOUND</span> <span class="token keyword">SET</span> done <span class="token operator">=</span> <span class="token boolean">TRUE</span><span class="token punctuation">;</span>

  <span class="token keyword">OPEN</span> cur1<span class="token punctuation">;</span>
  <span class="token keyword">OPEN</span> cur2<span class="token punctuation">;</span>

  read_loop: <span class="token keyword">LOOP</span>
    <span class="token keyword">FETCH</span> cur1 <span class="token keyword">INTO</span> a<span class="token punctuation">,</span> b<span class="token punctuation">;</span>
    <span class="token keyword">FETCH</span> cur2 <span class="token keyword">INTO</span> c<span class="token punctuation">;</span>
    <span class="token keyword">IF</span> done <span class="token keyword">THEN</span>
      <span class="token keyword">LEAVE</span> read_loop<span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
    <span class="token keyword">IF</span> b <span class="token operator">&lt;</span> c <span class="token keyword">THEN</span>
      <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test<span class="token punctuation">.</span>t3 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">ELSE</span>
      <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test<span class="token punctuation">.</span>t3 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>a<span class="token punctuation">,</span>c<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token keyword">LOOP</span><span class="token punctuation">;</span>

  <span class="token keyword">CLOSE</span> cur1<span class="token punctuation">;</span>
  <span class="token keyword">CLOSE</span> cur2<span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
