Author: paul
Date: 2009-07-02 01:52:44 +0200 (Thu, 02 Jul 2009)
New Revision: 15554
Log:
r42612@frost: paul | 2009-07-01 18:51:58 -0500
5.4.4 adds SIGNAL/RESIGNAL
Modified:
trunk/dynamic-docs/changelog/mysqld-1.xml
trunk/refman-5.4/faqs.xml
trunk/refman-5.4/introduction.xml
trunk/refman-5.4/restrictions.xml
trunk/refman-5.4/sql-syntax-compound-statements.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:42608
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39180
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:42612
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39180
Modified: trunk/dynamic-docs/changelog/mysqld-1.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld-1.xml 2009-07-01 23:27:28 UTC (rev 15553)
+++ trunk/dynamic-docs/changelog/mysqld-1.xml 2009-07-01 23:52:44 UTC (rev 15554)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 407 bytes
@@ -50910,6 +50910,7 @@
</tags>
<versions>
+ <version ver="5.4.4"/>
<version ver="6.0.11"/>
</versions>
Modified: trunk/refman-5.4/faqs.xml
===================================================================
--- trunk/refman-5.4/faqs.xml 2009-07-01 23:27:28 UTC (rev 15553)
+++ trunk/refman-5.4/faqs.xml 2009-07-01 23:52:44 UTC (rev 15554)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 3; 729 bytes
@@ -940,10 +940,10 @@
<answer>
<para>
- Not in MySQL ¤t-series;. The SQL standard
+ Yes. As of MySQL 5.4.4, MySQL implements the SQL standard
<literal role="stmt">SIGNAL</literal> and
- <literal role="stmt">RESIGNAL</literal> statements are
- implemented in MySQL 6.0.
+ <literal role="stmt">RESIGNAL</literal> statements. See
+ <xref linkend="signal-resignal"/>.
</para>
</answer>
Modified: trunk/refman-5.4/introduction.xml
===================================================================
--- trunk/refman-5.4/introduction.xml 2009-07-01 23:27:28 UTC (rev 15553)
+++ trunk/refman-5.4/introduction.xml 2009-07-01 23:52:44 UTC (rev 15554)
Changed blocks: 1, Lines Added: 17, Lines Deleted: 0; 775 bytes
@@ -462,6 +462,23 @@
</itemizedlist>
+ <para>
+ MySQL ¤t-series; also includes the following features:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Support for the SQL standard
+ <literal role="stmt">SIGNAL</literal> and
+ <literal role="stmt">RESIGNAL</literal> statements. See
+ <xref linkend="signal-resignal"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
</section>
<section id="features-5-4">
Modified: trunk/refman-5.4/restrictions.xml
===================================================================
--- trunk/refman-5.4/restrictions.xml 2009-07-01 23:27:28 UTC (rev 15553)
+++ trunk/refman-5.4/restrictions.xml 2009-07-01 23:52:44 UTC (rev 15554)
Changed blocks: 2, Lines Added: 33, Lines Deleted: 0; 1307 bytes
@@ -453,6 +453,7 @@
</itemizedlist>
+<!--
<formalpara>
<title>Stored routines and triggers in MySQL Cluster</title>
@@ -515,9 +516,41 @@
</para>
</formalpara>
+-->
</section>
+ <section id="signal-restrictions">
+
+ <title>Restrictions on Signals</title>
+
+ <indexterm>
+ <primary>signal restrictions</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>restrictions</primary>
+ <secondary>signal</secondary>
+ </indexterm>
+
+ <para>
+ <literal role="stmt">SIGNAL</literal> and
+ <literal role="stmt">RESIGNAL</literal> are not allowable as
+ prepared statements. For example, this statement is invalid:
+ </para>
+
+<programlisting>
+PREPARE stmt1 FROM 'SIGNAL SQLSTATE "02000"';
+</programlisting>
+
+ <para>
+ <literal>SQLSTATE</literal> values in class
+ <literal>'04'</literal> are not treated specially. They are
+ handled the same as other exceptions.
+ </para>
+
+ </section>
+
<section id="cursor-restrictions">
<title>Restrictions on Server-Side Cursors</title>
Modified: trunk/refman-5.4/sql-syntax-compound-statements.xml
===================================================================
--- trunk/refman-5.4/sql-syntax-compound-statements.xml 2009-07-01 23:27:28 UTC (rev 15553)
+++ trunk/refman-5.4/sql-syntax-compound-statements.xml 2009-07-01 23:52:44 UTC (rev 15554)
Changed blocks: 2, Lines Added: 1208, Lines Deleted: 6; 39200 bytes
@@ -145,12 +145,6 @@
</itemizedlist>
<para>
- The <literal role="stmt">SIGNAL</literal> and
- <literal role="stmt">RESIGNAL</literal> statements are not
- supported until MySQL 6.0.
- </para>
-
- <para>
<literal role="stmt">DECLARE</literal> is allowed only inside a
<literal>BEGIN ... END</literal> compound statement and must be at
its start, before any other statements.
@@ -1545,4 +1539,1212 @@
</section>
+ <section id="signal-resignal">
+
+ <title><literal role="stmt">SIGNAL</literal> and
+ <literal role="stmt">RESIGNAL</literal></title>
+
+ <para>
+ This section documents the <literal role="stmt">SIGNAL</literal>
+ and <literal role="stmt">RESIGNAL</literal> statements. See also
+ <xref linkend="signal-restrictions"/>.
+ </para>
+
+ <section id="signal">
+
+ <title><literal role="stmt">SIGNAL</literal> Syntax</title>
+
+ <indexterm>
+ <primary>SIGNAL</primary>
+ </indexterm>
+
+ <remark role="help-topic" condition="SIGNAL"/>
+
+ <remark role="help-keywords">
+ SIGNAL SET SQLSTATE VALUE CLASS_ORIGIN SUBCLASS_ORIGIN
+ CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME
+ CATALOG_NAME SCHEMA_NAME TABLE_NAME COLUMN_NAME CURSOR_NAME
+ MESSAGE_TEXT MYSQL_ERRNO
+ </remark>
+
+ <remark role="help-syntax"/>
+
+<programlisting>
+SIGNAL <replaceable>condition_value</replaceable>
+ [SET <replaceable>signal_information</replaceable> [, <replaceable>signal_information</replaceable>] ...]
+
+<replaceable>condition_value</replaceable>:
+ SQLSTATE [VALUE] <replaceable>sqlstate_value</replaceable>
+ | <replaceable>condition_name</replaceable>
+
+<replaceable>signal_information</replaceable>:
+ <replaceable>condition_information_item</replaceable> = <replaceable>simple_value_specification</replaceable>
+
+<replaceable>condition_information_item</replaceable>:
+{
+ CLASS_ORIGIN
+ | SUBCLASS_ORIGIN
+ | CONSTRAINT_CATALOG
+ | CONSTRAINT_SCHEMA
+ | CONSTRAINT_NAME
+ | CATALOG_NAME
+ | SCHEMA_NAME
+ | TABLE_NAME
+ | COLUMN_NAME
+ | CURSOR_NAME
+ | MESSAGE_TEXT
+ | MYSQL_ERRNO
+}
+
+<replaceable>simple_value_specification</replaceable>: (see following discussion)
+</programlisting>
+
+ <remark role="help-description-begin"/>
+
+ <para>
+ <literal role="stmt">SIGNAL</literal> is the way to
+ <quote>return</quote> an error.
+ <literal role="stmt">SIGNAL</literal> provides error information
+ to a handler, to an outer portion of the application, or to the
+ client. Also, it provides control over the error's
+ characteristics (error number, <literal>SQLSTATE</literal>
+ value, message). Without <literal role="stmt">SIGNAL</literal>,
+ it is necessary to resort to workarounds such as deliberately
+ referring to a nonexistent table to cause a routine to return an
+ error. This statement was added in MySQL 5.4.4.
+ </para>
+
+ <para>
+ No special privileges are required to execute the
+ <literal role="stmt">SIGNAL</literal> statement.
+ </para>
+
+ <para>
+ The <replaceable>condition_value</replaceable> in a
+ <literal role="stmt">SIGNAL</literal> statement indicates the
+ error value to be returned. It can be an
+ <literal>SQLSTATE</literal> value (a 5-character string literal)
+ or a <replaceable>condition_name</replaceable> that refers to a
+ named condition previously defined with
+ <literal role="stmt" condition="declare-condition">DECLARE ...
+ CONDITION</literal> (see <xref linkend="declare-condition"/>).
+ </para>
+
+ <para>
+ An <literal>SQLSTATE</literal> value can indicate errors,
+ warnings, or <quote>not found.</quote> The first two characters
+ of the value indicate its error class, as discussed in
+ <xref linkend="signal-condition-information-items"/>. Some
+ signal values cause statement termination; see
+ <xref linkend="signal-effects"/>.
+ </para>
+
+ <para>
+ The <literal>SQLSTATE</literal> value for a
+ <literal role="stmt">SIGNAL</literal> statement should not start
+ with <literal>'00'</literal> because such values indicate
+ success and are not valid for signaling an error. This is true
+ whether the <literal>SQLSTATE</literal> value is specified
+ directly in the <literal role="stmt">SIGNAL</literal> statement
+ or in a named condition referred to in the statement. If the
+ value is invalid, a <literal>Bad SQLSTATE</literal> error
+ occurs.
+ </para>
+
+ <para>
+ To signal a generic <literal>SQLSTATE</literal> value, use
+ <literal>'45000'</literal>, which means <quote>unhandled
+ user-defined exception.</quote>
+ </para>
+
+ <para>
+ The <literal role="stmt">SIGNAL</literal> statement optionally
+ includes a <literal>SET</literal> clause that contains multiple
+ signal items, in a comma-separated list of
+ <replaceable>condition_information_item</replaceable> =
+ <replaceable>simple_value_specification</replaceable>
+ assignments.
+ </para>
+
+ <para>
+ All <replaceable>condition_information_item</replaceable> values
+ are standard SQL except <literal>MYSQL_ERRNO</literal>, which is
+ a MySQL extension.
+ <xref linkend="signal-condition-information-items"/>. discusses
+ allowable <replaceable>condition_information_item</replaceable>
+ values.
+ </para>
+
+ <para>
+ Each <replaceable>condition_information_item</replaceable> may
+ be specified only once in the <literal>SET</literal> clause.
+ Otherwise, a <literal>Duplicate condition information
+ item</literal> error occurs.
+ </para>
+
+ <para>
+ For MySQL, valid
+ <replaceable>simple_value_specification</replaceable> terms
+ include local variables declared with
+ <literal role="stmt">DECLARE</literal>, user-defined variables,
+ system variables, parameters (that is, input parameters of
+ functions or procedures), and literals, but not
+ <literal>NULL</literal> values. A character literal may include
+ a <replaceable>_charset</replaceable> introducer.
+ </para>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ The following procedure signals an error or warning depending on
+ the value of <literal>pval</literal>, its input parameter:
+ </para>
+
+ <remark role="help-example"/>
+
+<programlisting>
+CREATE PROCEDURE p (pval INT)
+BEGIN
+ DECLARE specialty CONDITION FOR SQLSTATE '45000';
+ IF pval = 0 THEN
+ SIGNAL SQLSTATE '01000';
+ ELSEIF pval = 1 THEN
+ SIGNAL SQLSTATE '45000'
+ SET MESSAGE_TEXT = 'An error occurred';
+ ELSEIF pval = 2 THEN
+ SIGNAL specialty
+ SET MESSAGE_TEXT = 'An error occurred';
+ ELSE
+ SIGNAL SQLSTATE '01000'
+ SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
+ SIGNAL SQLSTATE '45000'
+ SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
+ END IF;
+END;
+</programlisting>
+
+ <para>
+ If <literal>pval</literal> is 0, <literal>p()</literal> signals
+ a warning because <literal>SQLSTATE</literal> values that begin
+ with <literal>'01'</literal> are signals in the warning class.
+ The warning does not terminate the procedure, and can be seen
+ with <literal role="stmt">SHOW WARNINGS</literal> after the
+ procedure returns.
+ </para>
+
+ <para>
+ If <literal>pval</literal> is 1, <literal>p()</literal> signals
+ an error and sets the <literal>MESSAGE_TEXT</literal> condition
+ information item. The error terminates the procedure, and the
+ text is returned with the error information.
+ </para>
+
+ <para>
+ If <literal>pval</literal> is 2, the same error is signaled,
+ although the <literal>SQLSTATE</literal> value is specified
+ using a named condition in this case.
+ </para>
+
+ <para>
+ If <literal>pval</literal> is anything else,
+ <literal>p()</literal> first signals a warning and sets the
+ message text and error number condition information items. This
+ warning does not terminate the procedure, so execution continues
+ and <literal>p()</literal> then signals an error. The error does
+ terminate the procedure. The message text and error number set
+ by the warning are replaced by the values set by the error,
+ which are returned with the error information.
+ </para>
+
+ <para>
+ <literal role="stmt">SIGNAL</literal> is typically used within
+ compound statements, but it is a MySQL extension that
+ <literal role="stmt">SIGNAL</literal> is allowed outside
+ compound statements, For example, if you invoke the
+ <command>mysql</command> program, you can enter any of these
+ statements at the prompt:
+ </para>
+
+<programlisting>
+mysql> <userinput>SIGNAL SQLSTATE '77777';</userinput>
+mysql> <userinput>CREATE TRIGGER t_bi BEFORE INSERT ON t</userinput>
+ -> <userinput>FOR EACH ROW SIGNAL SQLSTATE '77777';</userinput>
+mysql> <userinput>CREATE EVENT e ON SCHEDULE EVERY 1 SECOND</userinput>
+ -> <userinput>DO SIGNAL SQLSTATE '77777';</userinput>
+</programlisting>
+
+ <para>
+ <literal role="stmt">SIGNAL</literal> executes according to the
+ following rules:
+ </para>
+
+ <para>
+ If the <literal role="stmt">SIGNAL</literal> statement indicates
+ a particular <literal>SQLSTATE</literal> value, that value is
+ used to signal the condition specified. Example:
+ </para>
+
+<programlisting>
+CREATE PROCEDURE p (divisor INT)
+BEGIN
+ IF divisor = 0 THEN
+ SIGNAL SQLSTATE '22012';
+ END IF;
+END;
+</programlisting>
+
+ <para>
+ If the <literal role="stmt">SIGNAL</literal> statement uses a
+ named condition, the condition must satisfy the following
+ requirements:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The condition must be declared in some scope that applies to
+ the <literal role="stmt">SIGNAL</literal> statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The condition must be defined with
+ <literal>SQLSTATE</literal>, not with a MySQL error number.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+CREATE PROCEDURE p (divisor INT)
+BEGIN
+ DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
+ IF divisor = 0 THEN
+ SIGNAL divide_by_zero;
+ END IF;
+END;
+</programlisting>
+
+ <para>
+ If the named condition does not exist in the scope of the
+ <literal role="stmt">SIGNAL</literal> statement, an
+ <literal>Undefined CONDITION</literal> error occurs.
+ </para>
+
+ <para>
+ If <literal role="stmt">SIGNAL</literal> refers to a named
+ condition that is not defined with <literal>SQLSTATE</literal>,
+ a <literal>SIGNAL/RESIGNAL can only use a CONDITION defined with
+ SQLSTATE</literal> error occurs. The following statements cause
+ that error because the condition is associated with a MySQL
+ error number:
+ </para>
+
+<programlisting>
+DECLARE x CONDITION FOR 1234;
+SIGNAL x;
+</programlisting>
+
+ <para>
+ If a named condition is declared multiple times, the declaration
+ with the most local scope applies. Consider the following
+ procedure:
+ </para>
+
+<programlisting>
+CREATE PROCEDURE p (divisor INT)
+BEGIN
+ DECLARE my_error CONDITION FOR SQLSTATE '45000';
+ IF divisor = 0 THEN
+ BEGIN
+ DECLARE my_error CONDITION FOR SQLSTATE '22012';
+ SIGNAL my_error;
+ END;
+ END IF;
+ SIGNAL my_error;
+END;
+</programlisting>
+
+ <para>
+ If <literal>divisor</literal> is 0, the first
+ <literal role="stmt">SIGNAL</literal> statement executes. The
+ innermost <literal>my_error</literal> condition declaration
+ applies, raising <literal>SQLSTATE</literal> value
+ <literal>'22012'</literal>.
+ </para>
+
+ <para>
+ If <literal>divisor</literal> is not 0, the second
+ <literal role="stmt">SIGNAL</literal> statement executes. The
+ outermost <literal>my_error</literal> condition declaration
+ applies, raising <literal>SQLSTATE</literal> value
+ <literal>'45000'</literal>.
+ </para>
+
+ <para>
+ Signals can be raised within exception handlers:
+ </para>
+
+<programlisting>
+CREATE PROCEDURE p ()
+BEGIN
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ SIGNAL SQLSTATE VALUE '99999'
+ SET MESSAGE_TEXT = 'An error occurred';
+ END;
+ DROP TABLE no_such_table;
+END;
+</programlisting>
+
+ <para>
+ <literal>CALL p()</literal> reaches the
+ <literal role="stmt">DROP TABLE</literal> statement. There is no
+ table named <literal>no_such_table</literal>, so the error
+ handler comes into play. The error handler destroys the original
+ error (<quote>no such table</quote>) and makes a new error with
+ <literal>SQLSTATE</literal> value <literal>'99999'</literal> and
+ message <literal>An error occurred</literal>.
+ </para>
+
+ <section id="signal-condition-information-items">
+
+ <title>Signal Condition Information Items</title>
+
+ <para>
+ The following table lists the condition information items that
+ can be set in a <literal role="stmt">SIGNAL</literal>
+ statement. All items are standard SQL except
+ <literal>MYSQL_ERRNO</literal>, which is a MySQL extension.
+ </para>
+
+<programlisting>
+Item Name Definition
+--------- ----------
+CLASS_ORIGIN VARCHAR(64)
+SUBCLASS_ORIGIN VARCHAR(64)
+CONSTRAINT_CATALOG VARCHAR(64)
+CONSTRAINT_SCHEMA VARCHAR(64)
+CONSTRAINT_NAME VARCHAR(64)
+CATALOG_NAME VARCHAR(64)
+SCHEMA_NAME VARCHAR(64)
+TABLE_NAME VARCHAR(64)
+COLUMN_NAME VARCHAR(64)
+CURSOR_NAME VARCHAR(64)
+MESSAGE_TEXT VARCHAR(128)
+MYSQL_ERRNO SMALLINT UNSIGNED
+</programlisting>
+
+ <para>
+ All character items are UTF-8. UTF-8 may have 3-byte
+ characters, so <literal role="type">VARCHAR(128)</literal> can
+ require 384 bytes. Characters that are not valid UTF-8 are
+ converted to <literal>'?'</literal> characters.
+ </para>
+
+ <para>
+ It is illegal to assign <literal>NULL</literal> to a condition
+ information item in a <literal role="stmt">SIGNAL</literal>
+ statement.
+ </para>
+
+ <para>
+ A <literal role="stmt">SIGNAL</literal> statement always
+ specifies an <literal>SQLSTATE</literal> value, either
+ directly, or indirectly by referring to a named condition
+ defined with an <literal>SQLSTATE</literal> value. The first
+ two letters of an <literal>SQLSTATE</literal> value are its
+ class, and the class determines the default value for the
+ condition information items:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Class = <literal>'00'</literal> (success)
+ </para>
+
+ <para>
+ Illegal. This cannot happen because
+ <literal>SQLSTATE</literal> values that begin with
+ <literal>'00'</literal> indicate success and are not valid
+ for <literal role="stmt">SIGNAL</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class = <literal>'01'</literal> (warning)
+ </para>
+
+<programlisting>
+MESSAGE_TEXT = 'Unhandled user-defined warning';
+MYSQL_ERRNO = ER_SIGNAL_WARN
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class = <literal>'02'</literal> (not found)
+ </para>
+
+<programlisting>
+MESSAGE_TEXT = 'Unhandled user-defined not found';
+MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class > <literal>'02'</literal> (exception)
+ </para>
+
+<programlisting>
+MESSAGE_TEXT = 'Unhandled user-defined exception';
+MYSQL_ERRNO = ER_SIGNAL_EXCEPTION
+</programlisting>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For legal classes, the other condition information items are
+ set as follows:
+ </para>
+
+<programlisting>
+CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
+CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
+CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
+CURSOR_NAME = '';
+</programlisting>
+
+ <para>
+ The error values that are accessible after
+ <literal role="stmt">SIGNAL</literal> executes are the
+ <literal>SQLSTATE</literal> value raised by the
+ <literal role="stmt">SIGNAL</literal> statement and the
+ <literal>MESSAGE_TEXT</literal> and
+ <literal>MYSQL_ERRNO</literal> items. These values are
+ available from the C API:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>SQLSTATE</literal> value: Call
+ <literal role="cfunc">mysql_sqlstate()</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MYSQL_ERRNO</literal> value: Call
+ <literal role="cfunc">mysql_errno()</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MESSAGE_TEXT</literal> value: Call
+ <literal role="cfunc">mysql_error()</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ From SQL, the output from <literal role="stmt">SHOW
+ WARNINGS</literal> and <literal role="stmt">SHOW
+ ERRORS</literal> indicates the <literal>MYSQL_ERRNO</literal>
+ and <literal>MESSAGE_TEXT</literal> values in the
+ <literal>Code</literal> and <literal>Message</literal>
+ columns.
+ </para>
+
+ <para>
+ Other condition information items can be set, but currently
+ have no effect, in the sense that they are not accessible from
+ error returns. For example, you can set
+ <literal>CLASS_ORIGIN</literal> in a
+ <literal role="stmt">SIGNAL</literal> statement, but cannot
+ see it after <literal role="stmt">SIGNAL</literal> executes.
+ </para>
+
+ </section>
+
+ <section id="signal-effects">
+
+ <title>Effect of Signals on Handlers, Cursors, and Statements</title>
+
+ <para>
+ Signals have different effects on statement execution
+ depending on the signal class. The class determines how severe
+ an error is. MySQL ignores the
+ <literal role="sysvar">sql_mode</literal> value; in
+ particular, strict SQL mode does not matter. MySQL also
+ ignores <literal>IGNORE</literal>: The intent of
+ <literal role="stmt">SIGNAL</literal> is to raise a
+ user-generated error explicitly, so a signal is never ignored.
+ </para>
+
+ <para>
+ In the following descriptions, <quote>unhandled</quote> means
+ that no handler for the signaled <literal>SQLSTATE</literal>
+ value has been defined with
+ <literal role="stmt" condition="declare-handler">DECLARE ...
+ HANDLER</literal>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Class = <literal>'00'</literal> (success)
+ </para>
+
+ <para>
+ Illegal. This cannot happen because
+ <literal>SQLSTATE</literal> values that begin with
+ <literal>'00'</literal> indicate success and are not valid
+ for <literal role="stmt">SIGNAL</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class = <literal>'01'</literal> (warning)
+ </para>
+
+ <para>
+ The value of the
+ <literal role="sysvar">warning_count</literal> system
+ variable goes up. <literal role="stmt">SHOW
+ WARNINGS</literal> shows the signal.
+ <literal>SQLWARNING</literal> handlers catch the signal.
+ If the signal is unhandled in a function, statements do
+ not end.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class = <literal>'02'</literal> (not found)
+ </para>
+
+ <para>
+ <literal>NOT FOUND</literal> handlers catch the signal.
+ There is no effect on cursors. If the signal is unhandled
+ in a function, statements end.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class > <literal>'02'</literal> (exception)
+ </para>
+
+ <para>
+ <literal>SQLEXCEPTION</literal> handlers catch the signal.
+ If the signal is unhandled in a function, statements end.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Class = <literal>'40'</literal>
+ </para>
+
+ <para>
+ Treated as an ordinary exception.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+mysql> <userinput>delimiter //</userinput>
+mysql> <userinput>CREATE FUNCTION f () RETURNS INT</userinput>
+ -> <userinput>BEGIN</userinput>
+ -> <userinput>SIGNAL SQLSTATE '01234'; -- signal a warning</userinput>
+ -> <userinput>RETURN 5;</userinput>
+ -> <userinput>END//</userinput>
+mysql> <userinput>delimiter ;</userinput>
+mysql> <userinput>CREATE TABLE t (s1 INT);</userinput>
+mysql> <userinput>INSERT INTO t VALUES (f());</userinput>
+</programlisting>
+
+ <para>
+ The result is that a row containing 5 is inserted into table
+ <literal>t</literal>. The warning that is signaled can be
+ viewed with <literal role="stmt">SHOW WARNINGS</literal>.
+ </para>
+
+ </section>
+
+ </section>
+
+ <section id="resignal">
+
+ <title><literal role="stmt">RESIGNAL</literal> Syntax</title>
+
+ <indexterm>
+ <primary>RESIGNAL</primary>
+ </indexterm>
+
+ <remark role="help-topic" condition="RESIGNAL"/>
+
+ <remark role="help-keywords">
+ RESIGNAL SET SQLSTATE VALUE CLASS_ORIGIN SUBCLASS_ORIGIN
+ CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME
+ CATALOG_NAME SCHEMA_NAME TABLE_NAME COLUMN_NAME CURSOR_NAME
+ MESSAGE_TEXT MYSQL_ERRNO
+ </remark>
+
+ <remark role="help-syntax"/>
+
+<programlisting>
+RESIGNAL [<replaceable>condition_value</replaceable>]
+ [SET <replaceable>signal_information</replaceable> [, <replaceable>signal_information</replaceable>] ...];
+
+<replaceable>condition_value</replaceable>:
+ SQLSTATE [VALUE] <replaceable>sqlstate_value</replaceable>
+ | <replaceable>condition_name</replaceable>
+
+<replaceable>signal_information</replaceable>:
+ <replaceable>condition_information_item</replaceable> = <replaceable>simple_value_specification</replaceable>
+
+<replaceable>condition_information_item</replaceable>:
+{
+ CLASS_ORIGIN
+ | SUBCLASS_ORIGIN
+ | CONSTRAINT_CATALOG
+ | CONSTRAINT_SCHEMA
+ | CONSTRAINT_NAME
+ | CATALOG_NAME
+ | SCHEMA_NAME
+ | TABLE_NAME
+ | COLUMN_NAME
+ | CURSOR_NAME
+ | MESSAGE_TEXT
+ | MYSQL_ERRNO
+}
+
+<replaceable>simple_value_specification</replaceable>: (see following discussion)
+</programlisting>
+
+ <remark role="help-description-begin"/>
+
+ <para>
+ <literal role="stmt">RESIGNAL</literal> passes on the error
+ condition information that is available during execution of a
+ condition handler within a compound statement inside a stored
+ procedure or function, trigger, or event.
+ <literal role="stmt">RESIGNAL</literal> may change some or all
+ information before passing it on. This statement was added in
+ MySQL 5.4.4.
+ </para>
+
+ <para>
+ <literal role="stmt">RESIGNAL</literal> makes it possible to
+ both handle an error and return the error information.
+ Otherwise, by executing an SQL statement within the handler,
+ information that caused the handler's activation is destroyed.
+ <literal role="stmt">RESIGNAL</literal> also can make some
+ procedures shorter if a given handler could handle part of a
+ situation, then pass the condition <quote>up the line</quote> to
+ another handler.
+ </para>
+
+ <para>
+ No special privileges are required to execute the
+ <literal role="stmt">RESIGNAL</literal> statement.
+ </para>
+
+ <para>
+ Unless otherwise indicated, the definitions and rules for
+ <replaceable>condition_value</replaceable> and
+ <replaceable>signal_information</replaceable> are the same for
+ the <literal role="stmt">RESIGNAL</literal> statement as for
+ <literal role="stmt">SIGNAL</literal> (see
+ <xref linkend="signal"/>).
+ </para>
+
+ <para>
+ The <literal role="stmt">RESIGNAL</literal> statement takes
+ <replaceable>condition_value</replaceable> and
+ <literal>SET</literal> clauses, both of which are optional. This
+ leads to several possible uses:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal role="stmt">RESIGNAL</literal> alone:
+ </para>
+
+<programlisting>
+RESIGNAL;
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="stmt">RESIGNAL</literal> with new signal
+ information:
+ </para>
+
+<programlisting>
+RESIGNAL SET <replaceable>signal_information</replaceable> [, <replaceable>signal_information</replaceable>] ...;
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="stmt">RESIGNAL</literal> with a condition
+ value and possibly new signal information:
+ </para>
+
+<programlisting>
+RESIGNAL <replaceable>condition_value</replaceable>
+ [SET <replaceable>signal_information</replaceable> [, <replaceable>signal_information</replaceable>] ...];
+</programlisting>
+ </listitem>
+
+ </itemizedlist>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ These use cases all cause changes to the diagnostic and
+ condition areas:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A diagnostics area contains one or more condition areas.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A condition area contains condition information items, such
+ as the <literal>SQLSTATE</literal> value,
+ <literal>MYSQL_ERRNO</literal>, or
+ <literal>MESSAGE_TEXT</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ There is a stack of diagnostics areas. When a handler takes
+ control, it pushes the top of the stack, so there are two
+ diagnostics areas during handler execution:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The current diagnostics area, which starts as a copy of the
+ last diagnostics area, but will be overwritten by the first
+ procedure statement in the handler.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The last diagnostics area, which has the condition areas
+ that were set up before the handler took control.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The maximum number of condition areas in a diagnostics area is
+ determined by the value of the
+ <literal role="sysvar">max_error_count</literal> system
+ variable.
+ </para>
+
+ <section id="resignal-alone">
+
+ <title><literal role="stmt">RESIGNAL</literal> Alone</title>
+
+ <para>
+ A simple <literal role="stmt">RESIGNAL</literal> alone means
+ <quote>pass on the error with no change.</quote> It restores
+ the last diagnostics area and makes it the current diagnostics
+ area. That is, it <quote>pops</quote> the diagnostics area
+ stack.
+ </para>
+
+ <para>
+ Within a condition handler that catches a condition, one use
+ for <literal role="stmt">RESIGNAL</literal> alone is to
+ perform some other actions, and then pass on without change
+ the original condition information (the information that
+ existed before entry into the handler).
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+<!--
+DROP PROCEDURE IF EXISTS p;
+-->
+DROP TABLE IF EXISTS xx;
+delimiter //
+CREATE PROCEDURE p ()
+BEGIN
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ SET @error_count = @error_count + 1;
+ IF @a = 0 THEN RESIGNAL; END IF;
+ END;
+ DROP TABLE xx;
+END//
+delimiter ;
+SET @error_count = 0;
+SET @a = 0;
+CALL p();
+</programlisting>
+
+ <para>
+ The <literal>DROP TABLE xx</literal> statement fails. The
+ diagnostics area stack looks like this:
+ </para>
+
+<programlisting>
+1. ERROR 1051 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ Then execution enters the <literal>EXIT</literal> handler. It
+ starts by pushing the top of the diagnostics area stack, which
+ now looks like this:
+ </para>
+
+<programlisting>
+1. ERROR 1051 (42S02): Unknown table 'xx'
+2. ERROR 1051 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ Usually a procedure statement clears the first diagnostics
+ area (also called the <quote>current</quote> diagnostics
+ area). <literal>BEGIN</literal> is an exception, it does not
+ clear, it does nothing. <literal>SET</literal> is not an
+ exception, it clears, performs the operation, and then
+ produces a result of <quote>success.</quote> The diagnostics
+ area stack now looks like this:
+ </para>
+
+<programlisting>
+1. ERROR 0000 (00000): Successful operation
+2. ERROR 1051 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ At this point, if <literal>@a = 0</literal>,
+ <literal role="stmt">RESIGNAL</literal> pops the diagnostics
+ area stack, which now looks like this:
+ </para>
+
+<programlisting>
+1. ERROR 1051 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ And that is what the caller sees.
+ </para>
+
+ <para>
+ If <literal>@a</literal> is not 0, the handler simply ends,
+ which means that there is no more use for the last diagnostics
+ area (it has been <quote>handled</quote>), so it can be thrown
+ away. The diagnostics area stack looks like this:
+ </para>
+
+<programlisting>
+1. ERROR 0000 (00000): Successful operation
+</programlisting>
+
+ <para>
+ The details make it look complex, but the end result is quite
+ useful: Handlers can execute without destroying information
+ about the condition that caused activation of the handler.
+ </para>
+
+ </section>
+
+ <section id="resignal-with-new-signal">
+
+ <title><literal role="stmt">RESIGNAL</literal> with New Signal Information</title>
+
+ <para>
+ <literal role="stmt">RESIGNAL</literal> with a
+ <literal>SET</literal> clause provides new signal information,
+ so the statement means <quote>pass on the error with
+ changes</quote>:
+ </para>
+
+<programlisting>
+RESIGNAL SET <replaceable>signal_information</replaceable> [, <replaceable>signal_information</replaceable>] ...;
+</programlisting>
+
+ <para>
+ As with <literal role="stmt">RESIGNAL</literal> alone, the
+ idea is to pop the diagnostics area stack so that the original
+ information will go out. Unlike
+ <literal role="stmt">RESIGNAL</literal> alone, anything
+ specified in the <literal>SET</literal> clause changes.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+<!--
+DROP PROCEDURE IF EXISTS p;
+-->
+DROP TABLE IF EXISTS xx;
+delimiter //
+CREATE PROCEDURE p ()
+BEGIN
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ SET @error_count = @error_count + 1;
+ IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF;
+ END;
+ DROP TABLE xx;
+END//
+delimiter ;
+SET @error_count = 0;
+SET @a = 0;
+CALL p();
+</programlisting>
+
+ <para>
+ Remember from the previous discussion that
+ <literal role="stmt">RESIGNAL</literal> alone results in a
+ diagnostics area stack like this:
+ </para>
+
+<programlisting>
+1. ERROR 1051 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ The <literal>RESIGNAL SET MYSQL_ERRNO = 5</literal> statement
+ results in this stack instead:
+ </para>
+
+<programlisting>
+1. ERROR 5 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ In other words, it changes the error number, and nothing else.
+ </para>
+
+ <para>
+ The <literal role="stmt">RESIGNAL</literal> statement can
+ change any or all of the signal information items, making the
+ first condition area of the diagnostics area look quite
+ different.
+ </para>
+
+ </section>
+
+ <section id="resignal-with-condition">
+
+ <title><literal role="stmt">RESIGNAL</literal> with a Condition Value and
+ Optional New Signal Information</title>
+
+ <para>
+ <literal role="stmt">RESIGNAL</literal> with a condition value
+ means <quote>push a condition into the current diagnostic
+ stack area.</quote> If the <literal>SET</literal> clause is
+ present, it also changes the error information.
+ </para>
+
+<programlisting>
+RESIGNAL <replaceable>condition_value</replaceable>
+ [SET <replaceable>signal_information</replaceable> [, <replaceable>signal_information</replaceable>] ...];
+</programlisting>
+
+ <para>
+ This form of <literal role="stmt">RESIGNAL</literal> restores
+ the last diagnostics area and makes it the current diagnostics
+ area. That is, it <quote>pops</quote> the diagnostics area
+ stack, which is the same as what a simple
+ <literal role="stmt">RESIGNAL</literal> alone would do.
+ However, it also changes the diagnostics area depending on the
+ condition value or signal information.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+<!--
+DROP PROCEDURE IF EXISTS p;
+-->
+DROP TABLE IF EXISTS xx;
+delimiter //
+CREATE PROCEDURE p ()
+BEGIN
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION
+ BEGIN
+ SET @error_count = @error_count + 1;
+ IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
+ END;
+ DROP TABLE xx;
+END//
+delimiter ;
+SET @error_count = 0;
+SET @a = 0;
+SET @@max_error_count = 2;
+CALL p();
+SHOW ERRORS;
+</programlisting>
+
+ <para>
+ This is similar to the previous example, and the effects are
+ the same, except that if
+ <literal role="stmt">RESIGNAL</literal> happens the current
+ condition area looks different at the end. (The reason the
+ condition is added rather than replaced is the use of a
+ condition value.)
+ </para>
+
+ <para>
+ The <literal role="stmt">RESIGNAL</literal> statement includes
+ a condition value (<literal>SQLSTATE '45000'</literal>), so it
+ <quote>pushes</quote> a new condition area, resulting in a
+ diagnostics area stack that looks like this:
+ </para>
+
+<programlisting>
+1. (condition 1) ERROR 5 (45000) Unknown table 'xx'
+ (condition 2) ERROR 1051 (42S02): Unknown table 'xx'
+</programlisting>
+
+ <para>
+ The result of <literal role="stmt" condition="call">CALL
+ p()</literal> and <literal role="stmt">SHOW ERRORS</literal>
+ for this example is:
+ </para>
+
+<programlisting>
+mysql> <userinput>CALL p();</userinput>
+ERROR 5 (45000): Unknown table 'xx'
+mysql> <userinput>SHOW ERRORS;</userinput>
++-------+------+----------------------------------+
+| Level | Code | Message |
++-------+------+----------------------------------+
+| Error | 5 | Unknown table 'xx' |
+| Error | 1051 | Unknown table 'xx' |
++-------+------+----------------------------------+
+</programlisting>
+
+ </section>
+
+ <section id="resignal-handler">
+
+ <title><literal role="stmt">RESIGNAL</literal> Requires an Active Handler</title>
+
+ <para>
+ All forms of <literal role="stmt">RESIGNAL</literal> require
+ that a handler be active when it executes. If no handler is
+ active, <literal role="stmt">RESIGNAL</literal> is illegal and
+ a <literal>resignal when handler not active</literal> error
+ occurs. For example:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE PROCEDURE p () RESIGNAL;</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>CALL p();</userinput>
+ERROR 1739 (0K000): RESIGNAL when handler not active
+</programlisting>
+
+ <para>
+ Here is a more difficult example:
+ </para>
+
+<programlisting>
+<!--
+DROP FUNCTION IF EXISTS f;
+DROP PROCEDURE IF EXISTS p;
+-->
+delimiter //
+CREATE FUNCTION f () RETURNS INT
+BEGIN
+ RESIGNAL;
+ RETURN 5;
+END//
+CREATE PROCEDURE p ()
+BEGIN
+ DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f();
+ SIGNAL SQLSTATE '55555';
+END//
+delimiter ;
+CALL p();
+</programlisting>
+
+ <para>
+ At the time the <literal role="stmt">RESIGNAL</literal>
+ executes, there is a handler, even though the
+ <literal role="stmt">RESIGNAL</literal> is not defined inside
+ the handler.
+ </para>
+
+ <para>
+ A statement such as the one following may appear bizarre
+ because <literal role="stmt">RESIGNAL</literal> apparently is
+ not in a handler:
+ </para>
+
+<programlisting>
+CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW RESIGNAL;
+</programlisting>
+
+ <para>
+ But it does not matter.
+ <literal role="stmt">RESIGNAL</literal> does not have to be
+ technically <quote>in</quote> (that is, contained in), a
+ handler declaration. The requirement is that a handler must be
+ active.
+ </para>
+
+ </section>
+
+ </section>
+
+ </section>
+
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r15554 - in trunk: . dynamic-docs/changelog refman-5.4 | paul.dubois | 2 Jul |
