<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="stored-routines">
      </a>
      27.2 Using Stored Routines
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="stored-routines-syntax.html">
       27.2.1 Stored Routine Syntax
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="stored-routines-privileges.html">
       27.2.2 Stored Routines and MySQL Privileges
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="stored-routines-metadata.html">
       27.2.3 Stored Routine Metadata
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="stored-routines-last-insert-id.html">
       27.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045082566208">
  </a>
  <a class="indexterm" name="idm46045082565136">
  </a>
  <a class="indexterm" name="idm46045082563648">
  </a>
  <a class="indexterm" name="idm46045082562576">
  </a>
  <a class="indexterm" name="idm46045082561088">
  </a>
  <a class="indexterm" name="idm46045082560016">
  </a>
  <p>
   MySQL supports stored routines (procedures and functions). A stored
    routine is a set of SQL statements that can be stored in the server.
    Once this has been done, clients don't need to keep reissuing the
    individual statements but can refer to the stored routine instead.
  </p>
  <p>
   Stored routines can be particularly useful in certain situations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When multiple client applications are written in different
        languages or work on different platforms, but need to perform
        the same database operations.
     </p>
    </li>
    <li class="listitem">
     <p>
      When security is paramount. Banks, for example, use stored
        procedures and functions for all common operations. This
        provides a consistent and secure environment, and routines can
        ensure that each operation is properly logged. In such a setup,
        applications and users would have no access to the database
        tables directly, but can only execute specific stored routines.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Stored routines can provide improved performance because less
    information needs to be sent between the server and the client. The
    tradeoff is that this does increase the load on the database server
    because more of the work is done on the server side and less is done
    on the client (application) side. Consider this if many client
    machines (such as Web servers) are serviced by only one or a few
    database servers.
  </p>
  <p>
   Stored routines also enable you to have libraries of functions in
    the database server. This is a feature shared by modern application
    languages that enable such design internally (for example, by using
    classes). Using these client application language features is
    beneficial for the programmer even outside the scope of database
    use.
  </p>
  <p>
   MySQL follows the SQL:2003 syntax for stored routines, which is also
    used by IBM's DB2. All syntax described here is supported and any
    limitations and extensions are documented where appropriate.
  </p>
  <h3>
   <a name="idm46045082552592">
   </a>
   Additional Resources
  </h3>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You may find the
      <a class="ulink" href="https://forums.mysql.com/list.php?98" target="_blank">
       Stored
        Procedures User Forum
      </a>
      of use when working with stored
        procedures and functions.
     </p>
    </li>
    <li class="listitem">
     <p>
      For answers to some commonly asked questions regarding stored
        routines in MySQL, see
      <a class="xref" href="faqs-stored-procs.html" title="A.4 MySQL 8.4 FAQ: Stored Procedures and Functions">
       Section A.4, “MySQL 8.4 FAQ: Stored Procedures and Functions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      There are some restrictions on the use of stored routines. See
      <a class="xref" href="stored-program-restrictions.html" title="27.8 Restrictions on Stored Programs">
       Section 27.8, “Restrictions on Stored Programs”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Binary logging for stored routines takes place as described in
      <a class="xref" href="stored-programs-logging.html" title="27.7 Stored Program Binary Logging">
       Section 27.7, “Stored Program Binary Logging”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
