<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="stored-programs-defining">
      </a>
      27.1 Defining Stored Programs
     </h2>
    </div>
   </div>
  </div>
  <p>
   Each stored program contains a body that consists of an SQL
      statement. This statement may be a compound statement made up of
      several statements separated by semicolon (
   <code class="literal">
    ;
   </code>
   )
      characters. For example, the following stored procedure has a body
      made up of a
   <a class="link" href="begin-end.html" title="15.6.1 BEGIN ... END Compound Statement">
    <code class="literal">
     BEGIN ...
      END
    </code>
   </a>
   block that contains a
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   statement and a
   <a class="link" href="repeat.html" title="15.6.5.6 REPEAT Statement">
    <code class="literal">
     REPEAT
    </code>
   </a>
   loop that
      itself contains another
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66409300"><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> dorepeat<span class="token punctuation">(</span>p1 <span class="token datatype">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
  <span class="token keyword">REPEAT</span> <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token variable">@x</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span> <span class="token keyword">UNTIL</span> <span class="token variable">@x</span> <span class="token operator">&gt;</span> p1 <span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you use the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client program to define a
      stored program containing semicolon characters, a problem arises.
      By default,
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   itself recognizes the
      semicolon as a statement delimiter, so you must redefine the
      delimiter temporarily to cause
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   to pass
      the entire stored program definition to the server.
  </p>
  <p>
   To redefine the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   delimiter, use the
   <code class="literal">
    delimiter
   </code>
   command. The following example shows
      how to do this for the
   <code class="literal">
    dorepeat()
   </code>
   procedure
      just shown. The delimiter is changed to
   <code class="literal">
    //
   </code>
   to
      enable the entire definition to be passed to the server as a
      single statement, and then restored to
   <code class="literal">
    ;
   </code>
   before
      invoking the procedure. This enables the
   <code class="literal">
    ;
   </code>
   delimiter used in the procedure body to be passed through to the
      server rather than being interpreted by
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   itself.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35841050"><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 prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token comment" spellcheck="true">//</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> dorepeat<span class="token punctuation">(</span>p1 <span class="token datatype">INT</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">BEGIN</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">REPEAT</span> <span class="token keyword">SET</span> <span class="token variable">@x</span> <span class="token operator">=</span> <span class="token variable">@x</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span> <span class="token keyword">UNTIL</span> <span class="token variable">@x</span> <span class="token operator">&gt;</span> p1 <span class="token keyword">END</span> <span class="token keyword">REPEAT</span><span class="token punctuation">;</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">END</span>
<span class="token prompt">    -&gt;</span> <span class="token comment" spellcheck="true">//</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> dorepeat<span class="token punctuation">(</span><span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@x</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @x   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1001 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   You can redefine the delimiter to a string other than
   <code class="literal">
    //
   </code>
   , and the delimiter can consist of a single
      character or multiple characters. You should avoid the use of the
      backslash (
   <code class="literal">
    \
   </code>
   ) character because that is the
      escape character for MySQL.
  </p>
  <p>
   The following is an example of a function that takes a parameter,
      performs an operation using an SQL function, and returns the
      result. In this case, it is unnecessary to use
   <code class="literal">
    delimiter
   </code>
   because the function definition
      contains no internal
   <code class="literal">
    ;
   </code>
   statement delimiters:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30022022"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> hello <span class="token punctuation">(</span>s <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">RETURNS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">DETERMINISTIC</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">RETURN</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'Hello, '</span><span class="token punctuation">,</span>s<span class="token punctuation">,</span><span class="token string">'!'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> hello<span class="token punctuation">(</span><span class="token string">'world'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> hello('world') <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Hello, world!  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
