How to throw an error in MySql procedure?

本是古典何须时尚 2021-12-17 15:07 198阅读 0赞

http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <span><span>9<a title="This question does not show any research effort; it is unclear or not useful" rel="nofollow">down vote</a><a href="http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure#" rel="nofollow">favorite</a></span></span> 
     <div>
       &nbsp; 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <p>What is the mechanism to force the MySQL to throw an error within the stored procedure?</p> 
      <p>I have a procedure which call s another function:</p> 
      <pre><code><code><span>PREPARE my_cmd <span>FROM<span> <span>@<span>jobcommand<span>;<span> <span>EXECUTE<span> my_cmd<span>;<span> <span>DEALLOCATE<span> PREPARE my_cmd<span>;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></code></pre> 
      <p>the job command is:</p> 
      <pre><code><code><span>jobq<span>.<span>exec<span>(<span>"Select 1;wfdlk# to simulatte an error"<span>);</span></span></span></span></span></span></code></code></pre> 
      <p>then:</p> 
      <pre><code><code><span>CREATE<span> <span>PROCEDURE<span> jobq<span>.`<span>exec<span>`(<span>jobID VARCHAR<span>(<span>128<span>),<span>cmd TEXT<span>)<span> <span>BEGIN<span> <span>DECLARE<span> result INT <span>DEFAULT<span> <span>0<span>;<span> <span>SELECT<span> sys_exec<span>(<span> CONCAT<span>(<span>'echo '<span>,<span>cmd<span>,<span>' | base64 -d &gt; '<span>,<span> <span>'/tmp/jobq.'<span>,<span>jobID<span>,<span>'.sh ; bash /tmp/jobq.'<span>,<span>jobID<span>,<span>'.sh &amp;&gt; /tmp/jobq.'<span>,<span>jobID<span>))<span> <span>INTO<span> result<span>;<span> <span>IF<span> result<span>&gt;<span>0<span> <span>THEN<span> <span>#<span> call raise_mysql_error<span>(<span>result<span>);<span> <span>END<span> <span>IF<span>;<span> <span>END<span>;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></code></pre> 
      <p>My jobq.<code>exec</code>&nbsp;is always succeeding. Are there way to rise an error? How to implement raise_mysql_error function??</p> 
      <p>BTW I am using MySQL 5.5.8</p> 
      <p>thanks Arman.</p> 
     </div> 
     <div> 
      <a title="show questions tagged 'mysql'" href="http://stackoverflow.com/questions/tagged/mysql" rel="nofollow">mysql</a>&nbsp; 
      <a title="show questions tagged 'stored-procedures'" href="http://stackoverflow.com/questions/tagged/stored-procedures" rel="nofollow">stored-procedures</a>&nbsp; 
      <a title="show questions tagged 'throw'" href="http://stackoverflow.com/questions/tagged/throw" rel="nofollow">throw</a>&nbsp; 
      <a title="show questions tagged 'stored-functions'" href="http://stackoverflow.com/questions/tagged/stored-functions" rel="nofollow">stored-functions</a> 
     </div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <div> 
          <a title="short permalink to this question" href="http://stackoverflow.com/q/4862911" rel="nofollow">share</a> 
          <span><a title="" href="http://stackoverflow.com/posts/4862911/edit" rel="nofollow">improve this question</a></span> 
         </div> </td> 
        <td> 
         <div> 
          <div>
            asked&nbsp; 
           <span title="2011-02-01 13:06:01Z">Feb 1 '11 at 13:06</span> 
          </div> 
          <div> 
           <div> 
            <img src="https://www.gravatar.com/avatar/0ad8723b4215182dd5500fc8948042ee?s=32&amp;d=identicon&amp;r=PG" alt="" width="32" height="32"> 
           </div> 
          </div> 
          <div> 
           <a href="http://stackoverflow.com/users/235125/arman" rel="nofollow">Arman</a> 
           <div> 
            <span title="reputation score">1,896<span title="5 gold badges"><span><span>5<span title="29 silver badges"><span><span>29<span title="52 bronze badges"><span><span>52</span></span></span></span></span></span></span></span></span></span> 
           </div> 
          </div> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td><span title="number of 'useful comment' votes received">1</span></td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>related :&nbsp;<a title="raise error within mysql function" href="http://stackoverflow.com/questions/465727/raise-error-within-mysql-function" rel="nofollow">stackoverflow.com/questions/465727/…</a>&nbsp;–&nbsp;<a title="64,982 reputation" href="http://stackoverflow.com/users/74314/haim-evgi" rel="nofollow">Haim Evgi</a>&nbsp;<span><a href="http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure#comment5403869_4862911" rel="nofollow">Feb 1 '11 at 13:09</a></span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>also read this chapter&nbsp;<a href="http://www.docstoc.com/docs/687360/Error-Handling-In-Stored-Procedure" rel="nofollow">docstoc.com/docs/687360/Error-Handling-In-Stored-Procedure</a>&nbsp;–&nbsp;<a title="64,982 reputation" href="http://stackoverflow.com/users/74314/haim-evgi" rel="nofollow">Haim Evgi</a>&nbsp;<span><a href="http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure#comment5403899_4862911" rel="nofollow">Feb 1 '11 at 13:11</a></span></span> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> 
    <div> 
     <a title="Use comments to ask for more information or suggest improvements. Avoid answering questions in comments." rel="nofollow">add a comment</a> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## 2 Answers ##

[active][] [oldest][] [votes][]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <a title="This answer is useful" rel="nofollow">up vote</a> 
     <span>7<a title="This answer is not useful" rel="nofollow">down vote</a><span title="loading when this answer was accepted...">accepted</span></span> 
    </div> </td> 
   <td> 
    <div> 
     <p>Yes, there is: use the&nbsp;<a href="http://dev.mysql.com/doc/refman/5.5/en/signal.html" rel="nofollow"><code>SIGNAL</code></a>&nbsp;keyword.</p> 
    </div> 
    <table> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <a title="short permalink to this answer" href="http://stackoverflow.com/a/4863060" rel="nofollow">share</a> 
         <span><a title="" href="http://stackoverflow.com/posts/4863060/edit" rel="nofollow">improve this answer</a></span> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div> 
          <a title="show all edits to this post" href="http://stackoverflow.com/posts/4863060/revisions" rel="nofollow">edited&nbsp;<span title="2014-09-02 16:52:26Z">Sep 2 '14 at 16:52</span></a> 
         </div> 
         <div> 
          <div> 
           <img src="https://i.stack.imgur.com/y1Mud.gif?s=32&amp;g=1" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/2359271/air" rel="nofollow">Air</a> 
          <div> 
           <span title="reputation score">3,752<span title="1 gold badge"><span><span>1<span title="24 silver badges"><span><span>24<span title="46 bronze badges"><span><span>46</span></span></span></span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div>
           answered&nbsp; 
          <span title="2011-02-01 13:20:45Z">Feb 1 '11 at 13:20</span> 
         </div> 
         <div> 
          <div> 
           <img src="https://www.gravatar.com/avatar/3802e831c9313f6ffe1d1d9b69ac1515?s=32&amp;d=identicon&amp;r=PG" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/598400/halasy" rel="nofollow">Halasy</a> 
          <div> 
           <span title="reputation score">86<span title="1 bronze badge"><span><span>1</span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td><span title="number of 'useful comment' votes received">2</span></td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>Thank you! DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err= 1 exactly that what I need!!!&nbsp;–&nbsp;<a title="1,896 reputation" href="http://stackoverflow.com/users/235125/arman" rel="nofollow">Arman</a>&nbsp;<span><a href="http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure#comment5418500_4863060" rel="nofollow">Feb 2 '11 at 11:11</a></span></span> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> 
    <div> 
     <a title="Use comments to ask for more information or suggest improvements. Avoid comments like “+1” or “thanks”." rel="nofollow">add a comment</a> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

![i.gif_e_eyJhdiI6NDgxLCJhdCI6NCwiYnQiOjAsImNtIjo0ODYxMDUsImNoIjoxMTc4LCJjayI6e30sImNyIjoxNjgxNTczLCJkaSI6ImU1ZjllNmZmMGQyMTQ2Zjk5YjVhZDcyYThiNzhhMDNlIiwiZG0iOjEsImZjIjoxOTk3NzI3LCJmbCI6MjMzMTU3MiwiaXAiOiI3My45Ny4yMjYuMTExIiwia3ciOiJteXNxbCxzdG9yZWQtcHJvY2VkdXJlcyx0aHJvdyxzdG9yZWQtZnVuY3Rpb25zIiwibnciOjIyLCJwYyI6My4zNSwiZWMiOjMuMzUsInByIjo3NjUsInJ0IjoyLCJyZiI6Imh0dHBzOi8vd3d3Lmdvb2dsZS5jb20vIiwic3QiOjgyNzcsInVrIjoidWUxLTVjMjc0YjQ0NWIzMjQ3MzRhYjIyY2UwODM0NmNjOWU3Iiwiem4iOjQ0LCJ0cyI6MTQ3NDI2NDczMzA0NCwiYmYiOnRydWUsInBuIjoiYWR6ZXJrNDYxNDcxNTgzIiwiZnEiOjB9_s_qVFAtE2EE3KsyVHHSSfXpeKsKaw][]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <a title="This answer is useful" rel="nofollow">up vote</a> 
     <span>5<a title="This answer is not useful" rel="nofollow">down vote</a></span> 
    </div> </td> 
   <td> 
    <div> 
     <p>You may use following stored procedure to emulate error-throwing:</p> 
     <pre><code><code><span>CREATE<span> <span>PROCEDURE<span> <span>`<span>raise<span>`(`<span>errno<span>`<span> BIGINT UNSIGNED<span>,<span> <span>`<span>message<span>`<span> VARCHAR<span>(<span>256<span>))<span> <span>BEGIN<span> SIGNAL SQLSTATE <span>'ERR0R'<span> <span>SET<span> MESSAGE_TEXT <span>=<span> <span>`<span>message<span>`,<span> MYSQL_ERRNO <span>=<span> <span>`<span>errno<span>`;<span> <span>END</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></code></pre> 
     <p>Example:</p> 
     <pre><code><code><span>CALL <span>`<span>raise<span>`(<span>1356<span>,<span> <span>'My Error Message'<span>);</span></span></span></span></span></span></span></span></span></code></code></pre> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/kungfupanda/p/5887410.html

[active]: http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure?answertab=active#tab-top
[oldest]: http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure?answertab=oldest#tab-top
[votes]: http://stackoverflow.com/questions/4862911/how-to-throw-an-error-in-mysql-procedure?answertab=votes#tab-top
[i.gif_e_eyJhdiI6NDgxLCJhdCI6NCwiYnQiOjAsImNtIjo0ODYxMDUsImNoIjoxMTc4LCJjayI6e30sImNyIjoxNjgxNTczLCJkaSI6ImU1ZjllNmZmMGQyMTQ2Zjk5YjVhZDcyYThiNzhhMDNlIiwiZG0iOjEsImZjIjoxOTk3NzI3LCJmbCI6MjMzMTU3MiwiaXAiOiI3My45Ny4yMjYuMTExIiwia3ciOiJteXNxbCxzdG9yZWQtcHJvY2VkdXJlcyx0aHJvdyxzdG9yZWQtZnVuY3Rpb25zIiwibnciOjIyLCJwYyI6My4zNSwiZWMiOjMuMzUsInByIjo3NjUsInJ0IjoyLCJyZiI6Imh0dHBzOi8vd3d3Lmdvb2dsZS5jb20vIiwic3QiOjgyNzcsInVrIjoidWUxLTVjMjc0YjQ0NWIzMjQ3MzRhYjIyY2UwODM0NmNjOWU3Iiwiem4iOjQ0LCJ0cyI6MTQ3NDI2NDczMzA0NCwiYmYiOnRydWUsInBuIjoiYWR6ZXJrNDYxNDcxNTgzIiwiZnEiOjB9_s_qVFAtE2EE3KsyVHHSSfXpeKsKaw]: http://engine.adzerk.net/i.gif?e=eyJhdiI6NDgxLCJhdCI6NCwiYnQiOjAsImNtIjo0ODYxMDUsImNoIjoxMTc4LCJjayI6e30sImNyIjoxNjgxNTczLCJkaSI6ImU1ZjllNmZmMGQyMTQ2Zjk5YjVhZDcyYThiNzhhMDNlIiwiZG0iOjEsImZjIjoxOTk3NzI3LCJmbCI6MjMzMTU3MiwiaXAiOiI3My45Ny4yMjYuMTExIiwia3ciOiJteXNxbCxzdG9yZWQtcHJvY2VkdXJlcyx0aHJvdyxzdG9yZWQtZnVuY3Rpb25zIiwibnciOjIyLCJwYyI6My4zNSwiZWMiOjMuMzUsInByIjo3NjUsInJ0IjoyLCJyZiI6Imh0dHBzOi8vd3d3Lmdvb2dsZS5jb20vIiwic3QiOjgyNzcsInVrIjoidWUxLTVjMjc0YjQ0NWIzMjQ3MzRhYjIyY2UwODM0NmNjOWU3Iiwiem4iOjQ0LCJ0cyI6MTQ3NDI2NDczMzA0NCwiYmYiOnRydWUsInBuIjoiYWR6ZXJrNDYxNDcxNTgzIiwiZnEiOjB9&s=qVFAtE2EE3KsyVHHSSfXpeKsKaw