| List: | Commits | « Previous MessageNext Message » | |
| From: | paul.dubois | Date: | September 19 2008 2:54pm |
| Subject: | svn commit - mysqldoc@docsrva: r11855 - in trunk: . refman-5.0 refman-5.1 refman-6.0 | ||
| View as plain text | |||
Author: paul Date: 2008-09-19 14:54:24 +0200 (Fri, 19 Sep 2008) New Revision: 11855 Log: r34064@frost: paul | 2008-09-19 15:54:41 +0300 Add section specifically for describing the use of "delimiter" for creating stored programs. Modified: trunk/refman-5.0/programs-client-core.xml trunk/refman-5.0/sql-syntax-compound-statements.xml trunk/refman-5.0/sql-syntax-data-definition.xml trunk/refman-5.0/stored-programs-views.xml trunk/refman-5.1/programs-client-core.xml trunk/refman-5.1/sql-syntax-compound-statements.xml trunk/refman-5.1/sql-syntax-data-definition.xml trunk/refman-5.1/stored-programs-views.xml trunk/refman-6.0/programs-client-core.xml trunk/refman-6.0/sql-syntax-compound-statements.xml trunk/refman-6.0/sql-syntax-data-definition.xml trunk/refman-6.0/stored-programs-views.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:34062 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:33247 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:34064 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:33247 Modified: trunk/refman-5.0/programs-client-core.xml =================================================================== --- trunk/refman-5.0/programs-client-core.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.0/programs-client-core.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 683 bytes @@ -1919,7 +1919,7 @@ server side comes into play for multiple-statement execution (see <xref linkend="c-api-multiple-queries"/>), and for parsing the body of stored procedures and functions and - triggers (see <xref linkend="create-procedure"/>). + triggers (see <xref linkend="stored-programs-defining"/>). </para> </listitem> Modified: trunk/refman-5.0/sql-syntax-compound-statements.xml =================================================================== --- trunk/refman-5.0/sql-syntax-compound-statements.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.0/sql-syntax-compound-statements.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 640 bytes @@ -65,7 +65,7 @@ Changing the <literal>;</literal> end-of-statement delimiter (for example, to <literal>//</literal>) allows <literal>;</literal> to be used in a routine body. For an example, see - <xref linkend="create-procedure"/>. + <xref linkend="stored-programs-defining"/>. </para> <para> Modified: trunk/refman-5.0/sql-syntax-data-definition.xml =================================================================== --- trunk/refman-5.0/sql-syntax-data-definition.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.0/sql-syntax-data-definition.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 2, Lines Added: 2, Lines Deleted: 2; 564 bytes @@ -4233,7 +4233,7 @@ b4 INT DEFAULT 0 ); -DELIMITER | +delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN @@ -4243,7 +4243,7 @@ END; | -DELIMITER ; +delimiter ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), Modified: trunk/refman-5.0/stored-programs-views.xml =================================================================== --- trunk/refman-5.0/stored-programs-views.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.0/stored-programs-views.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 107, Lines Deleted: 0; 4277 bytes @@ -105,6 +105,113 @@ </itemizedlist> + <section id="stored-programs-defining"> + + <title>Defining Stored Programs</title> + + <para> + 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 (<literal>;</literal>) + characters. For example, the following stored procedure has a body + made up of a <literal>BEGIN ... END</literal> block that contains + a <literal>SET</literal> statement and a <literal>REPEAT</literal> + loop that itself contains another <literal>SET</literal> + statement: + </para> + +<programlisting> +CREATE PROCEDURE dorepeat(p1 INT) +BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; +END +</programlisting> + + <para> + If you use the <command>mysql</command> client program to define a + stored program that contains the semicolon characters within its + definition, a problem arises. By default, <command>mysql</command> + itself recognizes semicolon as a statement delimiter, so you must + redefine the delimiter temporarily to cause + <command>mysql</command> to pass the entire stored program + definition to the server. + </para> + + <para> + To redefine the <command>mysql</command> delimiter, use the + <literal>delimiter</literal> command. The following example shows + how to do this for the <literal>dorepeat()</literal> procedure + just shown. The delimiter is changed to <literal>//</literal> to + enable the entire definition to be passed to the server as a + single statement, and then restored to <literal>;</literal> before + invoking the procedure. This allows the <literal>;</literal> + delimiter used in the procedure body to be passed through to the + server rather than being interpreted by <command>mysql</command> + itself. + </para> + +<programlisting> +<!-- +mysql> DROP PROCEDURE IF EXISTS dorepeat; +--> +mysql> <userinput>delimiter //</userinput> + +mysql> <userinput>CREATE PROCEDURE dorepeat(p1 INT)</userinput> + -> <userinput>BEGIN</userinput> + -> <userinput>SET @x = 0;</userinput> + -> <userinput>REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;</userinput> + -> <userinput>END</userinput> + -> <userinput>//</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>delimiter ;</userinput> + +mysql> <userinput>CALL dorepeat(1000);</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>SELECT @x;</userinput> ++------+ +| @x | ++------+ +| 1001 | ++------+ +1 row in set (0.00 sec) +</programlisting> + + <para> + You can redefine the delimiter to a string other than + <literal>//</literal>, and the delimiter can consist of a single + character or multiple characters. You should avoid the use of the + backslash (<quote><literal>\</literal></quote>) character because + that is the escape character for MySQL. + </para> + + <para> + 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 + <literal>delimiter</literal> because the function definition + contains no internal <literal>;</literal> statement delimiters: + </para> + +<programlisting> +mysql> <userinput>CREATE FUNCTION hello (s CHAR(20))</userinput> +mysql> <userinput>RETURNS CHAR(50) DETERMINISTIC</userinput> + -> <userinput>RETURN CONCAT('Hello, ',s,'!');</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>SELECT hello('world');</userinput> ++----------------+ +| hello('world') | ++----------------+ +| Hello, world! | ++----------------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="stored-routines.xml"/> <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="triggers.xml"/> Modified: trunk/refman-5.1/programs-client-core.xml =================================================================== --- trunk/refman-5.1/programs-client-core.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.1/programs-client-core.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 617 bytes @@ -1962,7 +1962,7 @@ (see <xref linkend="c-api-multiple-queries"/>), and for parsing the body of stored procedures and functions, triggers, and events (see - <xref linkend="create-procedure"/>). + <xref linkend="stored-programs-defining"/>). </para> </listitem> Modified: trunk/refman-5.1/sql-syntax-compound-statements.xml =================================================================== --- trunk/refman-5.1/sql-syntax-compound-statements.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.1/sql-syntax-compound-statements.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 640 bytes @@ -65,7 +65,7 @@ Changing the <literal>;</literal> end-of-statement delimiter (for example, to <literal>//</literal>) allows <literal>;</literal> to be used in a routine body. For an example, see - <xref linkend="create-procedure"/>. + <xref linkend="stored-programs-defining"/>. </para> <para> Modified: trunk/refman-5.1/sql-syntax-data-definition.xml =================================================================== --- trunk/refman-5.1/sql-syntax-data-definition.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.1/sql-syntax-data-definition.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 6, Lines Added: 9, Lines Deleted: 9; 1381 bytes @@ -3120,7 +3120,7 @@ </para> <programlisting> -DELIMITER | +delimiter | CREATE EVENT e_daily ON SCHEDULE @@ -3134,13 +3134,13 @@ DELETE FROM site_activity.sessions; END | -DELIMITER ; +delimiter ; </programlisting> <para> - Note the use of the <literal>DELIMITER</literal> command to change - the statement delimiter, as with stored routines. See - <xref linkend="create-procedure"/>. + Note the use of the <literal>delimiter</literal> command to change + the statement delimiter. See + <xref linkend="stored-programs-defining"/>. </para> <para> @@ -3150,7 +3150,7 @@ </para> <programlisting> -DELIMITER | +delimiter | CREATE EVENT e ON SCHEDULE @@ -3169,7 +3169,7 @@ END WHILE; END | -DELIMITER ; +delimiter ; </programlisting> <para> @@ -7272,7 +7272,7 @@ b4 INT DEFAULT 0 ); -DELIMITER | +delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN @@ -7282,7 +7282,7 @@ END; | -DELIMITER ; +delimiter ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), Modified: trunk/refman-5.1/stored-programs-views.xml =================================================================== --- trunk/refman-5.1/stored-programs-views.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-5.1/stored-programs-views.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 107, Lines Deleted: 0; 4277 bytes @@ -119,6 +119,113 @@ </itemizedlist> + <section id="stored-programs-defining"> + + <title>Defining Stored Programs</title> + + <para> + 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 (<literal>;</literal>) + characters. For example, the following stored procedure has a body + made up of a <literal>BEGIN ... END</literal> block that contains + a <literal>SET</literal> statement and a <literal>REPEAT</literal> + loop that itself contains another <literal>SET</literal> + statement: + </para> + +<programlisting> +CREATE PROCEDURE dorepeat(p1 INT) +BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; +END +</programlisting> + + <para> + If you use the <command>mysql</command> client program to define a + stored program that contains the semicolon characters within its + definition, a problem arises. By default, <command>mysql</command> + itself recognizes semicolon as a statement delimiter, so you must + redefine the delimiter temporarily to cause + <command>mysql</command> to pass the entire stored program + definition to the server. + </para> + + <para> + To redefine the <command>mysql</command> delimiter, use the + <literal>delimiter</literal> command. The following example shows + how to do this for the <literal>dorepeat()</literal> procedure + just shown. The delimiter is changed to <literal>//</literal> to + enable the entire definition to be passed to the server as a + single statement, and then restored to <literal>;</literal> before + invoking the procedure. This allows the <literal>;</literal> + delimiter used in the procedure body to be passed through to the + server rather than being interpreted by <command>mysql</command> + itself. + </para> + +<programlisting> +<!-- +mysql> DROP PROCEDURE IF EXISTS dorepeat; +--> +mysql> <userinput>delimiter //</userinput> + +mysql> <userinput>CREATE PROCEDURE dorepeat(p1 INT)</userinput> + -> <userinput>BEGIN</userinput> + -> <userinput>SET @x = 0;</userinput> + -> <userinput>REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;</userinput> + -> <userinput>END</userinput> + -> <userinput>//</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>delimiter ;</userinput> + +mysql> <userinput>CALL dorepeat(1000);</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>SELECT @x;</userinput> ++------+ +| @x | ++------+ +| 1001 | ++------+ +1 row in set (0.00 sec) +</programlisting> + + <para> + You can redefine the delimiter to a string other than + <literal>//</literal>, and the delimiter can consist of a single + character or multiple characters. You should avoid the use of the + backslash (<quote><literal>\</literal></quote>) character because + that is the escape character for MySQL. + </para> + + <para> + 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 + <literal>delimiter</literal> because the function definition + contains no internal <literal>;</literal> statement delimiters: + </para> + +<programlisting> +mysql> <userinput>CREATE FUNCTION hello (s CHAR(20))</userinput> +mysql> <userinput>RETURNS CHAR(50) DETERMINISTIC</userinput> + -> <userinput>RETURN CONCAT('Hello, ',s,'!');</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>SELECT hello('world');</userinput> ++----------------+ +| hello('world') | ++----------------+ +| Hello, world! | ++----------------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="stored-routines.xml"/> <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="triggers.xml"/> Modified: trunk/refman-6.0/programs-client-core.xml =================================================================== --- trunk/refman-6.0/programs-client-core.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-6.0/programs-client-core.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 617 bytes @@ -1964,7 +1964,7 @@ (see <xref linkend="c-api-multiple-queries"/>), and for parsing the body of stored procedures and functions, triggers, and events (see - <xref linkend="create-procedure"/>). + <xref linkend="stored-programs-defining"/>). </para> </listitem> Modified: trunk/refman-6.0/sql-syntax-compound-statements.xml =================================================================== --- trunk/refman-6.0/sql-syntax-compound-statements.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-6.0/sql-syntax-compound-statements.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 640 bytes @@ -65,7 +65,7 @@ Changing the <literal>;</literal> end-of-statement delimiter (for example, to <literal>//</literal>) allows <literal>;</literal> to be used in a routine body. For an example, see - <xref linkend="create-procedure"/>. + <xref linkend="stored-programs-defining"/>. </para> <para> Modified: trunk/refman-6.0/sql-syntax-data-definition.xml =================================================================== --- trunk/refman-6.0/sql-syntax-data-definition.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-6.0/sql-syntax-data-definition.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 6, Lines Added: 9, Lines Deleted: 9; 1381 bytes @@ -2501,7 +2501,7 @@ </para> <programlisting> -DELIMITER | +delimiter | CREATE EVENT e_daily ON SCHEDULE @@ -2515,13 +2515,13 @@ DELETE FROM site_activity.sessions; END | -DELIMITER ; +delimiter ; </programlisting> <para> - Note the use of the <literal>DELIMITER</literal> command to change - the statement delimiter, as with stored routines. See - <xref linkend="create-procedure"/>. + Note the use of the <literal>delimiter</literal> command to change + the statement delimiter. See + <xref linkend="stored-programs-defining"/>. </para> <para> @@ -2531,7 +2531,7 @@ </para> <programlisting> -DELIMITER | +delimiter | CREATE EVENT e ON SCHEDULE @@ -2550,7 +2550,7 @@ END WHILE; END | -DELIMITER ; +delimiter ; </programlisting> <para> @@ -6313,7 +6313,7 @@ b4 INT DEFAULT 0 ); -DELIMITER | +delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN @@ -6323,7 +6323,7 @@ END; | -DELIMITER ; +delimiter ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), Modified: trunk/refman-6.0/stored-programs-views.xml =================================================================== --- trunk/refman-6.0/stored-programs-views.xml 2008-09-19 11:54:13 UTC (rev 11854) +++ trunk/refman-6.0/stored-programs-views.xml 2008-09-19 12:54:24 UTC (rev 11855) Changed blocks: 1, Lines Added: 107, Lines Deleted: 0; 4277 bytes @@ -119,6 +119,113 @@ </itemizedlist> + <section id="stored-programs-defining"> + + <title>Defining Stored Programs</title> + + <para> + 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 (<literal>;</literal>) + characters. For example, the following stored procedure has a body + made up of a <literal>BEGIN ... END</literal> block that contains + a <literal>SET</literal> statement and a <literal>REPEAT</literal> + loop that itself contains another <literal>SET</literal> + statement: + </para> + +<programlisting> +CREATE PROCEDURE dorepeat(p1 INT) +BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; +END +</programlisting> + + <para> + If you use the <command>mysql</command> client program to define a + stored program that contains the semicolon characters within its + definition, a problem arises. By default, <command>mysql</command> + itself recognizes semicolon as a statement delimiter, so you must + redefine the delimiter temporarily to cause + <command>mysql</command> to pass the entire stored program + definition to the server. + </para> + + <para> + To redefine the <command>mysql</command> delimiter, use the + <literal>delimiter</literal> command. The following example shows + how to do this for the <literal>dorepeat()</literal> procedure + just shown. The delimiter is changed to <literal>//</literal> to + enable the entire definition to be passed to the server as a + single statement, and then restored to <literal>;</literal> before + invoking the procedure. This allows the <literal>;</literal> + delimiter used in the procedure body to be passed through to the + server rather than being interpreted by <command>mysql</command> + itself. + </para> + +<programlisting> +<!-- +mysql> DROP PROCEDURE IF EXISTS dorepeat; +--> +mysql> <userinput>delimiter //</userinput> + +mysql> <userinput>CREATE PROCEDURE dorepeat(p1 INT)</userinput> + -> <userinput>BEGIN</userinput> + -> <userinput>SET @x = 0;</userinput> + -> <userinput>REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;</userinput> + -> <userinput>END</userinput> + -> <userinput>//</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>delimiter ;</userinput> + +mysql> <userinput>CALL dorepeat(1000);</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>SELECT @x;</userinput> ++------+ +| @x | ++------+ +| 1001 | ++------+ +1 row in set (0.00 sec) +</programlisting> + + <para> + You can redefine the delimiter to a string other than + <literal>//</literal>, and the delimiter can consist of a single + character or multiple characters. You should avoid the use of the + backslash (<quote><literal>\</literal></quote>) character because + that is the escape character for MySQL. + </para> + + <para> + 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 + <literal>delimiter</literal> because the function definition + contains no internal <literal>;</literal> statement delimiters: + </para> + +<programlisting> +mysql> <userinput>CREATE FUNCTION hello (s CHAR(20))</userinput> +mysql> <userinput>RETURNS CHAR(50) DETERMINISTIC</userinput> + -> <userinput>RETURN CONCAT('Hello, ',s,'!');</userinput> +Query OK, 0 rows affected (0.00 sec) + +mysql> <userinput>SELECT hello('world');</userinput> ++----------------+ +| hello('world') | ++----------------+ +| Hello, world! | ++----------------+ +1 row in set (0.00 sec) +</programlisting> + + </section> + <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="stored-routines.xml"/> <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="triggers.xml"/>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r11855 - in trunk: . refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 19 Sep |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.651 sec. using MySQL 5.1.14-beta-log