Author: paul
Date: 2006-08-04 16:25:41 +0200 (Fri, 04 Aug 2006)
New Revision: 2951
Log:
r13268@frost: paul | 2006-08-04 09:23:58 -0500
Add explanation of IN, OUT, INOUT parameter types.
Add example showing how to use OUT, INOUT parameters.
Modified:
trunk/refman-5.0/stored-procedures.xml
trunk/refman-5.1/stored-procedures.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:12466
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:13261
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:9385
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:12466
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:13268
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:9385
Modified: trunk/refman-5.0/stored-procedures.xml
===================================================================
--- trunk/refman-5.0/stored-procedures.xml 2006-08-04 14:18:15 UTC (rev 2950)
+++ trunk/refman-5.0/stored-procedures.xml 2006-08-04 14:25:41 UTC (rev 2951)
Changed blocks: 3, Lines Added: 78, Lines Deleted: 7; 5045 bytes
@@ -332,13 +332,23 @@
<para>
The parameter list enclosed within parentheses must always be
present. If there are no parameters, an empty parameter list of
- <literal>()</literal> should be used. Each parameter is an
- <literal>IN</literal> parameter by default. To specify otherwise
- for a parameter, use the keyword <literal>OUT</literal> or
- <literal>INOUT</literal> before the parameter name.
+ <literal>()</literal> should be used.
</para>
<para>
+ Each parameter can be declared to use any valid data type,
+ except that the <literal>COLLATE</literal> attribute cannot be
+ used.
+ </para>
+
+ <para>
+ Each parameter is an <literal>IN</literal> parameter by default.
+ To specify otherwise for a parameter, use the keyword
+ <literal>OUT</literal> or <literal>INOUT</literal> before the
+ parameter name.
+ </para>
+
+ <para>
<emphasis role="bold">Note</emphasis>: Specifying a parameter as
<literal>IN</literal>, <literal>OUT</literal>, or
<literal>INOUT</literal> is valid only for a
@@ -348,12 +358,29 @@
</para>
<para>
- Each parameter can be declared to use any valid data type,
- except that the <literal>COLLATE</literal> attribute cannot be
- used.
+ An <literal>IN</literal> parameter passes a value into a
+ procedure. The procedure might modify the value, but the
+ modification is not visible to the caller when the procedure
+ returns. An <literal>OUT</literal> parameter passes a value from
+ the procedure back to the caller. Its initial value is
+ <literal>NULL</literal> within the procedure, and its value is
+ visible to the caller when the procedure returns. An
+ <literal>INOUT</literal> parameter is initialized by the caller,
+ can be modified by the procedure, and any change made by the
+ procedure is visible to the caller when the procedure returns.
</para>
<para>
+ For each <literal>OUT</literal> or <literal>INOUT</literal>
+ parameter, pass a user-defined variable so that you can obtain
+ its value when the procedure returns. (For an example, see
+ <xref linkend="call"/>.) If you are calling the procedure from
+ within another stored procedure or function, you can also pass a
+ routine parameter or local routine variable as an
+ <literal>IN</literal> or <literal>INOUT</literal> parameter.
+ </para>
+
+ <para>
The <literal>RETURNS</literal> clause may be specified only for
a <literal>FUNCTION</literal>, for which it is mandatory. It
indicates the return type of the function, and the function body
@@ -857,6 +884,50 @@
<remark role="help-description-end"/>
<para>
+ To get back a value from a procedure using an
+ <literal>OUT</literal> or <literal>INOUT</literal> parameter,
+ pass the parameter by means of a user variable, and then check
+ the value of the variable after the procedure returns. (If you
+ are calling the procedure from within another stored procedure
+ or function, you can also pass a routine parameter or local
+ routine variable as an <literal>IN</literal> or
+ <literal>INOUT</literal> parameter.) For an
+ <literal>INOUT</literal> parameter, initialize its value before
+ passing it to the procedure. The following procedure has an
+ <literal>OUT</literal> parameter that the procedure sets to the
+ current server version, and an <literal>INOUT</literal> value
+ that the procedure increments by one from its current value:
+ </para>
+
+<programlisting>
+CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
+BEGIN
+ # Set value of OUT parameter
+ SELECT VERSION() INTO ver_param;
+ # Increment value of INOUT parameter
+ SET incr_param = incr_param + 1;
+END;
+</programlisting>
+
+ <para>
+ Before calling the procedure, initialize the variable to be
+ passed as the <literal>INOUT</literal> parameter. After calling
+ the procedure, the values of the two variables will have been
+ set or modified:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @increment = 10;</userinput>
+mysql> <userinput>CALL p(@version, @increment);</userinput>
+mysql> <userinput>SELECT @version, @increment;</userinput>
++------------+------------+
+| @version | @increment |
++------------+------------+
+| 5.0.25-log | 11 |
++------------+------------+
+</programlisting>
+
+ <para>
If you write C programs that execute stored procedures with the
<literal>CALL</literal> SQL statement, you
<emphasis>must</emphasis> set the
Modified: trunk/refman-5.1/stored-procedures.xml
===================================================================
--- trunk/refman-5.1/stored-procedures.xml 2006-08-04 14:18:15 UTC (rev 2950)
+++ trunk/refman-5.1/stored-procedures.xml 2006-08-04 14:25:41 UTC (rev 2951)
Changed blocks: 3, Lines Added: 78, Lines Deleted: 7; 5070 bytes
@@ -323,13 +323,23 @@
<para>
The parameter list enclosed within parentheses must always be
present. If there are no parameters, an empty parameter list of
- <literal>()</literal> should be used. Each parameter is an
- <literal>IN</literal> parameter by default. To specify otherwise
- for a parameter, use the keyword <literal>OUT</literal> or
- <literal>INOUT</literal> before the parameter name.
+ <literal>()</literal> should be used.
</para>
<para>
+ Each parameter can be declared to use any valid data type,
+ except that the <literal>COLLATE</literal> attribute cannot be
+ used.
+ </para>
+
+ <para>
+ Each parameter is an <literal>IN</literal> parameter by default.
+ To specify otherwise for a parameter, use the keyword
+ <literal>OUT</literal> or <literal>INOUT</literal> before the
+ parameter name.
+ </para>
+
+ <para>
<emphasis role="bold">Note</emphasis>: Specifying a parameter as
<literal>IN</literal>, <literal>OUT</literal>, or
<literal>INOUT</literal> is valid only for a
@@ -339,12 +349,29 @@
</para>
<para>
- Each parameter can be declared to use any valid data type,
- except that the <literal>COLLATE</literal> attribute cannot be
- used.
+ An <literal>IN</literal> parameter passes a value into a
+ procedure. The procedure might modify the value, but the
+ modification is not visible to the caller when the procedure
+ returns. An <literal>OUT</literal> parameter passes a value from
+ the procedure back to the caller. Its initial value is
+ <literal>NULL</literal> within the procedure, and its value is
+ visible to the caller when the procedure returns. An
+ <literal>INOUT</literal> parameter is initialized by the caller,
+ can be modified by the procedure, and any change made by the
+ procedure is visible to the caller when the procedure returns.
</para>
<para>
+ For each <literal>OUT</literal> or <literal>INOUT</literal>
+ parameter, pass a user-defined variable so that you can obtain
+ its value when the procedure returns. (For an example, see
+ <xref linkend="call"/>.) If you are calling the procedure from
+ within another stored procedure or function, you can also pass a
+ routine parameter or local routine variable as an
+ <literal>IN</literal> or <literal>INOUT</literal> parameter.
+ </para>
+
+ <para>
The <literal>RETURNS</literal> clause may be specified only for
a <literal>FUNCTION</literal>, for which it is mandatory. It
indicates the return type of the function, and the function body
@@ -829,6 +856,50 @@
<remark role="help-description-end"/>
<para>
+ To get back a value from a procedure using an
+ <literal>OUT</literal> or <literal>INOUT</literal> parameter,
+ pass the parameter by means of a user variable, and then check
+ the value of the variable after the procedure returns. (If you
+ are calling the procedure from within another stored procedure
+ or function, you can also pass a routine parameter or local
+ routine variable as an <literal>IN</literal> or
+ <literal>INOUT</literal> parameter.) For an
+ <literal>INOUT</literal> parameter, initialize its value before
+ passing it to the procedure. The following procedure has an
+ <literal>OUT</literal> parameter that the procedure sets to the
+ current server version, and an <literal>INOUT</literal> value
+ that the procedure increments by one from its current value:
+ </para>
+
+<programlisting>
+CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
+BEGIN
+ # Set value of OUT parameter
+ SELECT VERSION() INTO ver_param;
+ # Increment value of INOUT parameter
+ SET incr_param = incr_param + 1;
+END;
+</programlisting>
+
+ <para>
+ Before calling the procedure, initialize the variable to be
+ passed as the <literal>INOUT</literal> parameter. After calling
+ the procedure, the values of the two variables will have been
+ set or modified:
+ </para>
+
+<programlisting>
+mysql> <userinput>SET @increment = 10;</userinput>
+mysql> <userinput>CALL p(@version, @increment);</userinput>
+mysql> <userinput>SELECT @version, @increment;</userinput>
++-----------------+------------+
+| @version | @increment |
++-----------------+------------+
+| 5.1.12-beta-log | 11 |
++-----------------+------------+
+</programlisting>
+
+ <para>
If you write C programs that execute stored procedures with the
<literal>CALL</literal> SQL statement, you
<emphasis>must</emphasis> set the
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r2951 - in trunk: . refman-5.0 refman-5.1 | paul | 4 Aug |
