621 lines
35 KiB
HTML
621 lines
35 KiB
HTML
<?xml version="1.0" encoding="utf-8"?>
|
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
|
|
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="dc.language" scheme="rfc1766" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow"/>
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="keywords" content="DECLARE PROCEDURE statement, DECLARE PROCEDURE,
|
|
SQL statements, defining, procedure, CCSID clause,
|
|
data type for DECLARE PROCEDURE, CHAR, DOUBLE PRECISION, DECIMAL, FLOAT, BIGINT,
|
|
INTEGER, NUMERIC, REAL, SMALLINT, VARCHAR, VARGRAPHIC, GRAPHIC,
|
|
FOR BIT DATA clause, FOR MIXED DATA clause, FOR SBCS DATA clause, BINARY,
|
|
VARBINARY, BLOB, CLOB, DBCLOB, DATALINK, ROWID, distinct-type,
|
|
in DECLARE PROCEDURE, procedure-name, IN clause, OUT clause, INOUT clause,
|
|
parameter-name, in DECLARE PROCEDURE statement, data-type, AS LOCATOR clause,
|
|
RESULT SETS clause, LANGUAGE clause, SPECIFIC clause, DETERMINISTIC clause,
|
|
CONTAINS SQL clause, NO SQL clause, READS SQL DATA clause,
|
|
MODIFIES SQL DATA clause, EXTERNAL clause, EXTERNAL NAME clause,
|
|
DECLARE PROCEDURE (External), SQL clause, DB2GENERAL clause, DB2SQL clause,
|
|
GENERAL clause, GENERAL WITH NULLS clause, JAVA clause" />
|
|
<title>DECLARE PROCEDURE</title>
|
|
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
|
|
<link rel="stylesheet" type="text/css" href="ic.css" />
|
|
</head>
|
|
<body>
|
|
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
|
|
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
|
|
|
|
|
|
<a name="dclpef"></a>
|
|
<h2 id="dclpef"><a href="rbafzmst02.htm#ToC_988">DECLARE PROCEDURE</a></h2><a id="idx2289" name="idx2289"></a><a id="idx2290" name="idx2290"></a><a id="idx2291" name="idx2291"></a><a id="idx2292" name="idx2292"></a>
|
|
<a name="h2dclpr"></a>
|
|
<p id="h2dclpr">The DECLARE PROCEDURE statement defines an external procedure.</p>
|
|
<a name="wq1419"></a>
|
|
<h3 id="wq1419"><a href="rbafzmst02.htm#ToC_989">Invocation</a></h3>
|
|
<p>This statement can only be embedded in an application program. It is not
|
|
an executable statement. It must not be specified in REXX.</p>
|
|
<a name="wq1420"></a>
|
|
<h3 id="wq1420"><a href="rbafzmst02.htm#ToC_990">Authorization</a></h3>
|
|
<p>None.</p>
|
|
<a name="wq1421"></a>
|
|
<h3 id="wq1421"><a href="rbafzmst02.htm#ToC_991">Syntax</a></h3>
|
|
<a href="rbafzmstdclpef.htm#synsdp"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq1422"></a>
|
|
<div class="fignone" id="wq1422">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn320.htm"
|
|
border="0" /></span><a href="#skipsyn-319"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-DECLARE--<span class="italic">procedure-name</span>--PROCEDURE--------------------------->
|
|
|
|
>--+-----------------------------------------+--<span class="italic">option-list</span>----><
|
|
'-(--+-------------------------------+--)-'
|
|
| .-,-------------------------. |
|
|
| V | |
|
|
'-----<span class="italic">parameter-declaration</span>---+-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-319" id="skipsyn-319"></a></div>
|
|
<a name="wq1423"></a>
|
|
<div class="fignone" id="wq1423">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn321.htm"
|
|
border="0" /></span><a href="#skipsyn-320"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>parameter-declaration:
|
|
|
|
.-IN----.
|
|
|--+-------+--+----------------+--<span class="italic">data-type</span>--+------------+-----|
|
|
+-OUT---+ '-<span class="italic">parameter-name</span>-' '-AS LOCATOR-'
|
|
'-INOUT-'
|
|
|
|
data-type:
|
|
|
|
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
|
|
'-<span class="italic">distinct-type-name</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-320" id="skipsyn-320"></a></div>
|
|
<a name="wq1424"></a>
|
|
<div class="fignone" id="wq1424">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn322.htm"
|
|
border="0" /></span><a href="#skipsyn-321"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>option-list:
|
|
|
|
(1)
|
|
|--+-----------------------+------------------------------------>
|
|
'-LANGUAGE--+-C-------+-'
|
|
+-C++-----+
|
|
+-CL------+
|
|
+-COBOL---+
|
|
+-COBOLLE-+
|
|
+-FORTRAN-+
|
|
+-JAVA----+
|
|
+-PLI-----+
|
|
+-REXX----+
|
|
+-RPG-----+
|
|
'-RPGLE---'
|
|
|
|
.-PARAMETER STYLE SQL---------------------.
|
|
>--+-----------------------------------------+------------------>
|
|
'-PARAMETER STYLE--+-DB2GENERAL---------+-'
|
|
+-DB2SQL-------------+
|
|
+-GENERAL------------+
|
|
+-GENERAL WITH NULLS-+
|
|
'-JAVA---------------'
|
|
|
|
.-NOT DETERMINISTIC-. .-MODIFIES SQL DATA-.
|
|
>--+-------------------+--+-------------------+----------------->
|
|
'-DETERMINISTIC-----' +-NO SQL------------+
|
|
+-CONTAINS SQL------+
|
|
'-READS SQL DATA----'
|
|
|
|
.-CALLED ON NULL INPUT-. .-DYNAMIC RESULT SETS--<span class="italic">0</span>-------.
|
|
>--+----------------------+--+------------------------------+--->
|
|
'-DYNAMIC RESULT SETS--<span class="italic">integer</span>-'
|
|
|
|
.-NO DBINFO-. .-FENCED-----. .-PROGRAM TYPE MAIN-.
|
|
>--+-----------+--+------------+--+-------------------+--------->
|
|
'-DBINFO----' '-NOT FENCED-'
|
|
|
|
.-EXTERNAL-----------------------------.
|
|
>--+--------------------------------------+--------------------->
|
|
'-EXTERNAL NAME--<span class="italic">external-program-name</span>-'
|
|
|
|
>--+-------------------------+----------------------------------|
|
|
'-SPECIFIC--<span class="italic">specific-name</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-321" id="skipsyn-321"></a>
|
|
<a name="wq1425"></a>
|
|
<div class="notelisttitle" id="wq1425">Notes:</div>
|
|
<ol type="1">
|
|
<li>The optional clauses can be specified in a different order.</li>
|
|
</ol></div>
|
|
<a name="wq1427"></a>
|
|
<div class="fignone" id="wq1427">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn323.htm"
|
|
border="0" /></span><a href="#skipsyn-322"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>built-in-type:
|
|
|
|
|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
|
|
| +-+-INTEGER-+--+ |
|
|
| | '-INT-----' | |
|
|
| '---BIGINT-----' |
|
|
| .-(5,0)------------------------. |
|
|
+-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+
|
|
| | '-DEC-----' | | .-,0--------. | |
|
|
| '-NUMERIC-----' '-(--<span class="italic">integer</span>--+-----------+--)-' |
|
|
| '-<span class="italic">, integer</span>-' |
|
|
| .-(--53--)------. |
|
|
+-+-FLOAT--+---------------+-+--------------------------------------------------------------+
|
|
| | '-(--<span class="italic">integer</span>--)-' | |
|
|
| +-REAL---------------------+ |
|
|
| | .-PRECISION-. | |
|
|
| '-DOUBLE--+-----------+----' |
|
|
| .-(--1--)-------. |
|
|
+-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+
|
|
| | | '-CHAR------' '-(--<span class="italic">integer</span>--)-' | +-FOR BIT DATA---+ | |
|
|
| | '-+-+-CHARACTER-+--VARYING-+--(--<span class="italic">integer</span>--)-' +-FOR SBCS DATA--+ | |
|
|
| | | '-CHAR------' | +-FOR MIXED DATA-+ | |
|
|
| | '-VARCHAR----------------' '-<span class="italic">ccsid-clause</span>---' | |
|
|
| | .-(--1M--)-------------. | |
|
|
| '-----+-+-<span>CHARACTER</span>-+--<span>LARGE OBJECT</span>-+------+----------------------+--+----------------+-' |
|
|
| | '-<span>CHAR</span>------' | '-(--<span class="italic">integer</span>--+---+--)-' +-FOR SBCS DATA--+ |
|
|
| '-<span>CLOB</span>------------------------' +-K-+ +-FOR MIXED DATA-+ |
|
|
| +-M-+ '-<span class="italic">ccsid-clause</span>---' |
|
|
| '-G-' |
|
|
| .-(--1--)-------. |
|
|
+-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+
|
|
| | '-(--<span class="italic">integer</span>--)-' | '-<span class="italic">ccsid-clause</span>-' |
|
|
| +-+-GRAPHIC VARYING-+--(--<span class="italic">integer</span>--)---+ |
|
|
| | '-VARGRAPHIC------' | |
|
|
| | .-(--1M--)-------------. | |
|
|
| '---DBCLOB----+----------------------+-' |
|
|
| '-(--<span class="italic">integer</span>--+---+--)-' |
|
|
| +-K-+ |
|
|
| +-M-+ |
|
|
| '-G-' |
|
|
| .-(--1--)-------. |
|
|
+-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+
|
|
| | | '-(--<span class="italic">integer</span>--)-' | | |
|
|
| | '-+-BINARY VARYING-+--(--<span class="italic">integer</span>--)-' | |
|
|
| | '-VARBINARY------' | |
|
|
| | .-(--1M--)-------------. | |
|
|
| '---+-BLOB----------------+----+----------------------+-' |
|
|
| '-BINARY LARGE OBJECT-' '-(--<span class="italic">integer</span>--+---+--)-' |
|
|
| +-K-+ |
|
|
| +-M-+ |
|
|
| '-G-' |
|
|
+-+-DATE------+-----------------------------------------------------------------------------+
|
|
| +-TIME------+ |
|
|
| '-TIMESTAMP-' |
|
|
| .-(--200--)-----. |
|
|
+---DATALINK--+---------------+--+--------------+-------------------------------------------+
|
|
| '-(--<span class="italic">integer</span>--)-' '-<span class="italic">ccsid-clause</span>-' |
|
|
'---ROWID-----------------------------------------------------------------------------------'
|
|
|
|
ccsid-clause:
|
|
|
|
.-NOT NORMALIZED-.
|
|
|--CCSID--<span class="italic">integer</span>--+----------------+---------------------------|
|
|
'-NORMALIZED-----'
|
|
|
|
</pre>
|
|
<a name="skipsyn-322" id="skipsyn-322"></a></div>
|
|
<p><a id="idx2293" name="idx2293"></a><a id="idx2294" name="idx2294"></a><a id="idx2295" name="idx2295"></a><a id="idx2296" name="idx2296"></a><a id="idx2297" name="idx2297"></a><a id="idx2298" name="idx2298"></a><a id="idx2299" name="idx2299"></a><a id="idx2300" name="idx2300"></a><a id="idx2301" name="idx2301"></a><a id="idx2302" name="idx2302"></a><a id="idx2303" name="idx2303"></a><a id="idx2304" name="idx2304"></a><a id="idx2305" name="idx2305"></a><a id="idx2306" name="idx2306"></a><a id="idx2307" name="idx2307"></a><a id="idx2308" name="idx2308"></a><a id="idx2309" name="idx2309"></a><a id="idx2310" name="idx2310"></a><a id="idx2311" name="idx2311"></a><a id="idx2312" name="idx2312"></a><a id="idx2313" name="idx2313"></a><a id="idx2314" name="idx2314"></a><a id="idx2315" name="idx2315"></a><a id="idx2316" name="idx2316"></a></p>
|
|
<a name="synsdp"></a>
|
|
<h3 id="synsdp"><a href="rbafzmst02.htm#ToC_992">Description</a></h3>
|
|
<p></p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">procedure-name</var> </dt><a id="idx2317" name="idx2317"></a>
|
|
<dd>Names the procedure. The name must not be the same as the name of another
|
|
procedure declared in your source program.
|
|
</dd>
|
|
<dt class="bold">(<var class="pv">parameter-declaration,...)</var></dt>
|
|
<dd>Specifies the number of parameters of the procedure and the data type
|
|
of each parameter. A parameter for a procedure can be used only for input,
|
|
only for output, or for both input and output. Although not required, you
|
|
can give each parameter a name.
|
|
<p>The maximum number of parameters
|
|
allowed in DECLARE PROCEDURE depends on the language and the parameter style:</p>
|
|
<ul>
|
|
<li>If PARAMETER STYLE GENERAL is specified, in C and C++, the maximum is
|
|
1024. Otherwise, the maximum is 255.</li>
|
|
<li>If PARAMETER STYLE GENERAL WITH NULLS is specified, in C and C++, the
|
|
maximum is 1023. Otherwise, the maximum is 254.</li>
|
|
<li>If PARAMETER STYLE SQL or PARAMETER STYLE DB2SQL is specified, in C and
|
|
C++, the maximum is 508. Otherwise, the maximum is 90.</li>
|
|
<li>If PARAMETER STYLE JAVA or PARAMETER STYLE DB2GENERAL is specified, the
|
|
maximum is 90.</li></ul><p class="indatacontent">The maximum number of parameters is also limited by the maximum number
|
|
of parameters allowed by the licensed program used to compile the external
|
|
program or service program.</p>
|
|
<dl class="parml">
|
|
<dt class="bold">IN </dt><a id="idx2318" name="idx2318"></a>
|
|
<dd>Identifies the parameter as an input parameter to the procedure. Any
|
|
changes made to the parameter within the procedure are not available
|
|
to the calling SQL application when control is returned.<sup class="fn"><a id="wq1428" name="wq1428" href="rbafzmstdclpef.htm#wq1429">69</a></sup>
|
|
</dd>
|
|
<dt class="bold">OUT </dt><a id="idx2319" name="idx2319"></a>
|
|
<dd>Identifies the parameter as an output parameter that is returned by
|
|
the procedure.
|
|
<p>A DataLink or a distinct type based on a DataLink may not
|
|
be specified as an output parameter.</p>
|
|
</dd>
|
|
<dt class="bold">INOUT </dt><a id="idx2320" name="idx2320"></a>
|
|
<dd>Identifies the parameter as both an input and output parameter for the
|
|
procedure.
|
|
<p>A DataLink or a distinct type based on a DataLink may not be
|
|
specified as an input and output parameter.</p>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">parameter-name</var> </dt><a id="idx2321" name="idx2321"></a>
|
|
<dd>Names the parameter. The name cannot be the same as any other <var class="pv">parameter-name</var> for the procedure.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">data-type</var> </dt><a id="idx2322" name="idx2322"></a>
|
|
<dd>Specifies the data type of the parameter.
|
|
<p>The data type must be valid
|
|
for the language specified in the language clause. All data types are valid
|
|
for SQL procedures. DataLinks are not valid for external procedures. For more
|
|
information about data types, see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>, and the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.</p>
|
|
<p>If a CCSID is specified, the
|
|
parameter will be converted to that CCSID prior to passing it to the procedure.
|
|
If a CCSID is not specified, the CCSID is determined by the default CCSID
|
|
at the current server at the time the procedure is called.</p>
|
|
</dd><a id="idx2323" name="idx2323"></a>
|
|
<dt class="bold">AS LOCATOR</dt>
|
|
<dd>Specifies that the parameter is a locator to the value rather than the
|
|
actual value. You can specify AS LOCATOR only if the parameter has a LOB data
|
|
type or a distinct type based on a LOB data type. If AS LOCATOR is specified,
|
|
FOR SBCS DATA or FOR MIXED DATA must not be specified.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">DYNAMIC RESULT SETS <var class="pv">integer</var> </dt><a id="idx2324" name="idx2324"></a>
|
|
<dd>Specifies the maximum number of result sets that can be returned from
|
|
the procedure. <var class="pv">integer</var> must be greater than or equal to zero and
|
|
less than 32768. If zero is specified, no result sets are returned. If the
|
|
SET RESULT SETS statement is issued, the number of results returned is the
|
|
minimum of the number of result sets specified on this keyword and the SET
|
|
RESULT SETS statement.
|
|
<p>Result sets are only returned if the procedure
|
|
is directly called or if the procedure is a RETURN TO CLIENT procedure and
|
|
is indirectly called from ODBC, JDBC, OLE DB, .NET, the SQL Call Level Interface,
|
|
or the iSeries Access Family Optimized SQL API. For more information about result sets, see <a href="rbafzmsthsetrs.htm#hsetrs">SET RESULT SETS</a>.</p>
|
|
</dd>
|
|
<dt class="bold">LANGUAGE </dt><a id="idx2325" name="idx2325"></a>
|
|
<dd>Specifies the language that the external program is written in. The
|
|
language clause is required if the external program is a REXX procedure.
|
|
<p>If LANGUAGE is not specified, the LANGUAGE is determined from the program
|
|
attribute information associated with the external program. If the program
|
|
attribute information associated with the program does not identify a recognizable
|
|
language, then the language is assumed to be C. </p>
|
|
<dl class="parml">
|
|
<dt class="bold">C </dt>
|
|
<dd>The external program is written in C.
|
|
</dd>
|
|
<dt class="bold">C++ </dt>
|
|
<dd>The external program is written in C++.
|
|
</dd>
|
|
<dt class="bold">CL </dt>
|
|
<dd>The external program is written in CL.
|
|
</dd>
|
|
<dt class="bold">COBOL</dt>
|
|
<dd>The external program is written in COBOL.
|
|
</dd>
|
|
<dt class="bold">COBOLLE</dt>
|
|
<dd>The external program is written in ILE COBOL.
|
|
</dd>
|
|
<dt class="bold">FORTRAN</dt>
|
|
<dd>The external program is written in FORTRAN.
|
|
</dd>
|
|
<dt class="bold">JAVA</dt>
|
|
<dd>The external program is written in JAVA.
|
|
</dd>
|
|
<dt class="bold">PLI</dt>
|
|
<dd>The external program is written in PL/I.
|
|
</dd>
|
|
<dt class="bold">REXX</dt>
|
|
<dd>The external program is a REXX procedure.
|
|
</dd>
|
|
<dt class="bold">RPG</dt>
|
|
<dd>The external program is written in RPG.
|
|
</dd>
|
|
<dt class="bold">RPGLE</dt>
|
|
<dd>The external program is written in ILE RPG.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">SPECIFIC <var class="pv">specific-name</var> </dt><a id="idx2326" name="idx2326"></a>
|
|
<dd>Specifies a qualified or unqualified name that uniquely identifies the
|
|
procedure. The <span class="italic">specific-name</span>, including the implicit
|
|
or explicit qualifier, must be the same as the <span class="italic">procedure-name</span>.
|
|
<p>If no qualifier is specified, the implicit or explicit qualifier
|
|
of the <var class="pv">procedure-name</var> is used. If a qualifier is specified, the qualifier
|
|
must be the same as the explicit or implicit qualifier of the <var class="pv">procedure-name</var>.</p>
|
|
<p>If <var class="pv">specific-name</var> is not specified, it is the same as
|
|
the procedure name.</p>
|
|
</dd>
|
|
<dt class="bold">DETERMINISTIC <span class="base">or</span> NOT DETERMINISTIC</dt>
|
|
<dd>Specifies whether the procedure returns the same results each time the
|
|
procedure is called with the same IN and INOUT arguments.
|
|
<dl class="parml"><a id="idx2327" name="idx2327"></a>
|
|
<dt class="bold">NOT DETERMINISTIC</dt>
|
|
<dd>The procedure may not return the same result each time the procedure
|
|
is called with the same IN and INOUT arguments, even when the referenced data
|
|
in the database has not changed.
|
|
</dd>
|
|
<dt class="bold">DETERMINISTIC</dt>
|
|
<dd>The procedure always returns the same results each time the procedure
|
|
is called with the same IN and INOUT arguments, provided the referenced data
|
|
in the database has not changed.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA, <span class="base">or</span> NO SQL</dt>
|
|
<dd>Specifies which SQL statements, if any, may be executed in the procedure
|
|
or any routine called from this procedure. See <a href="rbafzmstdiftab.htm#diftab">Appendix B. Characteristics of SQL statements</a> for a
|
|
detailed list of the SQL statements that can be executed under each data access
|
|
indication.
|
|
<dl class="parml">
|
|
<dt class="bold">CONTAINS SQL</dt><a id="idx2328" name="idx2328"></a>
|
|
<dd>Specifies that SQL statements that neither read nor modify SQL data
|
|
can be executed by the procedure.
|
|
</dd>
|
|
<dt class="bold">NO SQL</dt><a id="idx2329" name="idx2329"></a>
|
|
<dd>Specifies that the procedure cannot execute any SQL statements.
|
|
</dd>
|
|
<dt class="bold">READS SQL DATA</dt><a id="idx2330" name="idx2330"></a>
|
|
<dd>Specifies that SQL statements that do not modify SQL data can be included
|
|
in the procedure.
|
|
</dd>
|
|
<dt class="bold">MODIFIES SQL DATA</dt><a id="idx2331" name="idx2331"></a>
|
|
<dd>Specifies that the procedure can execute any SQL statement except statements
|
|
that are not supported in procedures.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">CALLED ON NULL INPUT</dt>
|
|
<dd>Specifies that the function is to be invoked, if any, or all, argument
|
|
values are null, making the function responsible for testing for null argument
|
|
values. The function can return a null or nonnull value.
|
|
</dd>
|
|
<dt class="bold">FENCED <span class="base">or</span> NOT FENCED</dt>
|
|
<dd>This parameter is allowed for compatibility with other products and
|
|
is not used by DB2 UDB for iSeries.
|
|
</dd>
|
|
<dt class="bold">PROGRAM TYPE MAIN</dt>
|
|
<dd>Specifies that the procedure executes as a main routine.
|
|
</dd>
|
|
<dt class="bold">DBINFO</dt>
|
|
<dd>Specifies that the database manager should pass a structure containing
|
|
status information to the procedure. <a href="rbafzmstdclpef.htm#dbinfotb3">Table 54</a> contains a description
|
|
of the DBINFO structure. Detailed information about the DBINFO structure can
|
|
be found in include <span>sqludf</span> in the appropriate source
|
|
file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.
|
|
<p>DBINFO is only allowed with PARAMETER STYLE DB2SQL.</p>
|
|
<a name="dbinfotb3"></a>
|
|
<table id="dbinfotb3" width="100%" summary="" border="1" frame="border" rules="all">
|
|
<caption>Table 54. DBINFO fields</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq1430" width="21%" align="left" valign="bottom">Field</th>
|
|
<th id="wq1431" width="15%" align="left" valign="bottom">Data Type</th>
|
|
<th id="wq1432" width="62%" align="left" valign="bottom">Description</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1430">Relational database</td>
|
|
<td align="left" valign="top" headers="wq1431">VARCHAR(128)</td>
|
|
<td align="left" valign="top" headers="wq1432">The name of the current server.</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1430">Authorization ID</td>
|
|
<td align="left" valign="top" headers="wq1431">VARCHAR(128)</td>
|
|
<td align="left" valign="top" headers="wq1432">The run-time authorization ID.</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1430">CCSID Information</td>
|
|
<td align="left" valign="top" headers="wq1431">
|
|
<div class="lines">INTEGER<br />
|
|
INTEGER<br />
|
|
INTEGER<br />
|
|
</div>
|
|
<div class="lines"> <br />
|
|
</div>
|
|
<div class="lines">INTEGER<br />
|
|
INTEGER<br />
|
|
INTEGER<br />
|
|
</div>
|
|
<div class="lines"> <br />
|
|
</div>
|
|
<div class="lines">INTEGER<br />
|
|
INTEGER<br />
|
|
INTEGER<br />
|
|
</div>
|
|
<div class="lines"> <br />
|
|
</div>
|
|
<div class="lines">INTEGER<br />
|
|
</div>
|
|
<div class="lines"> <br />
|
|
</div>
|
|
<div class="lines">CHAR(8)<br />
|
|
</div></td>
|
|
<td align="left" valign="top" headers="wq1432">The CCSID information of the job. Three sets
|
|
of three CCSIDs are returned. The following information identifies the three
|
|
CCSIDs in each set:
|
|
<ul>
|
|
<li>SBCS CCSID</li>
|
|
<li>DBCS CCSID</li>
|
|
<li>Mixed CCSID</li></ul>Following the three sets of CCSIDs is an integer that indicates which
|
|
set of three sets of CCSIDs is applicable and eight bytes of reserved space.
|
|
<p>If a CCSID is not explicitly specified for a parameter on the CREATE PROCEDURE
|
|
statement, the input string is assumed to be encoded in the CCSID of the job
|
|
at the time the procedure is executed. If the CCSID of the input string is
|
|
not the same as the CCSID of the parameter, the input string passed to the
|
|
external procedure will be converted before calling the external program.</p></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1430">Target Column</td>
|
|
<td align="left" valign="top" headers="wq1431">VARCHAR(128)
|
|
<div class="lines">VARCHAR(128)<br />
|
|
</div>
|
|
<div class="lines">VARCHAR(128)<br />
|
|
</div></td>
|
|
<td align="left" valign="top" headers="wq1432">Not applicable for a call to a procedure.</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1430">Version and release</td>
|
|
<td align="left" valign="top" headers="wq1431">CHAR(8)</td>
|
|
<td align="left" valign="top" headers="wq1432">The version, release, and modification level
|
|
of the database manager.</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1430">Platform</td>
|
|
<td align="left" valign="top" headers="wq1431">INTEGER</td>
|
|
<td align="left" valign="top" headers="wq1432">The server's platform type.</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</dd>
|
|
<dt class="bold">EXTERNAL NAME <var class="pv">external-program-name</var> </dt><a id="idx2332" name="idx2332"></a><a id="idx2333" name="idx2333"></a>
|
|
<dd>Specifies the program that will be executed when the procedure is called
|
|
by the CALL statement. The program name must identify a program that exists
|
|
at the application server. The program cannot be an ILE service program.
|
|
<p>The validity
|
|
of the name is checked at the application server. If the format of the name is not
|
|
correct, an error is returned.</p>
|
|
<p>If external-program-name is not specified,
|
|
the external program name is assumed to be the same as the procedure name.</p>
|
|
</dd>
|
|
<dt class="bold">PARAMETER STYLE</dt>
|
|
<dd>Specifies the conventions used for passing parameters to and returning
|
|
the values from procedures:
|
|
<dl class="parml"><a id="idx2334" name="idx2334"></a>
|
|
<dt class="bold">SQL</dt>
|
|
<dd>Specifies that in addition to the parameters on the CALL statement,
|
|
several additional parameters are passed to the procedure. The parameters
|
|
are defined to be in the following order:
|
|
<ul>
|
|
<li>The first N parameters are the parameters that are specified on the DECLARE
|
|
PROCEDURE statement.</li>
|
|
<li>N parameters for indicator variables for the parameters.</li>
|
|
<li>A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates
|
|
the success or failure of the procedure. The SQLSTATE returned is assigned
|
|
by the external program.
|
|
<p>The user may set the SQLSTATE to any valid value
|
|
in the external program to return an error or warning from the function.</p></li>
|
|
<li>A VARCHAR(517) input parameter for the fully qualified procedure name.</li>
|
|
<li>A VARCHAR(128) input parameter for the specific name.</li>
|
|
<li>A VARCHAR(70) output parameter for the message text.</li></ul>For more information about the parameters passed, see the include <span>sqludf</span> in the appropriate source file in library QSYSINC.
|
|
For example, for C, sqludf can be found in QSYSINC/H.
|
|
<p>PARAMETER STYLE SQL
|
|
cannot be used with LANGUAGE JAVA.</p>
|
|
</dd>
|
|
<dt class="bold">DB2GENERAL </dt><a id="idx2335" name="idx2335"></a>
|
|
<dd>Specifies that the procedure will use a parameter passing convention
|
|
that is defined for use with Java™ methods.
|
|
<p>PARAMETER STYLE DB2GENERAL
|
|
can only be specified with LANGUAGE JAVA. For details on passing parameters
|
|
in JAVA, see the <a href="../rzaha/whatitis.htm">IBM® Developer Kit for Java</a>
|
|
book.</p>
|
|
</dd>
|
|
<dt class="bold">DB2SQL</dt><a id="idx2336" name="idx2336"></a>
|
|
<dd>Specifies that in addition to the parameters on the CALL statement,
|
|
several additional parameters are passed to the procedure. DB2SQL is identical
|
|
to the SQL parameter style, except that the following additional parameter
|
|
may be passed as the last parameter:
|
|
<ul>
|
|
<li>A parameter for the dbinfo structure, if DBINFO was specified on the DECLARE
|
|
PROCEDURE statement.</li></ul>For more information about the parameters passed, see the include <span>sqludf</span> in the appropriate source file in library QSYSINC.
|
|
For example, for C, sqludf can be found in QSYSINC/H.
|
|
<p>PARAMETER STYLE DB2SQL
|
|
cannot be used with LANGUAGE JAVA.</p>
|
|
</dd>
|
|
<dt class="bold">GENERAL </dt><a id="idx2337" name="idx2337"></a>
|
|
<dd>Specifies that the procedure will use a parameter passing mechanism
|
|
where the procedure receives the parameters specified on the
|
|
CALL. Additional arguments are not passed for indicator variables.
|
|
<p>PARAMETER
|
|
STYLE GENERAL cannot be used with LANGUAGE JAVA.</p>
|
|
</dd>
|
|
<dt class="bold">GENERAL WITH NULLS </dt><a id="idx2338" name="idx2338"></a>
|
|
<dd>Specifies that in addition to the parameters on the CALL statement as
|
|
specified in GENERAL, another argument is passed to the procedure.
|
|
This additional argument contains an indicator array with an element for each
|
|
of the parameters of the CALL statement. In C, this would be an array of short
|
|
INTs. For more information about how the indicators are handled, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.
|
|
<p>PARAMETER STYLE GENERAL WITH NULLS
|
|
cannot be used with LANGUAGE JAVA.</p>
|
|
</dd>
|
|
<dt class="bold">JAVA </dt><a id="idx2339" name="idx2339"></a>
|
|
<dd>Specifies that the procedure will use a parameter passing convention
|
|
that conforms to the Java language and SQLJ Routines specification.
|
|
INOUT and OUT parameters will be passed as single entry arrays to facilitate
|
|
returning values. For increased portability, you should write Java procedures
|
|
that use the PARAMETER STYLE JAVA conventions.
|
|
<p>PARAMETER STYLE JAVA can
|
|
only be specified with LANGUAGE JAVA. For details on passing parameters in
|
|
JAVA, see the <a href="../rzaha/whatitis.htm">IBM Developer Kit for Java</a>
|
|
book.</p>
|
|
</dd>
|
|
</dl>
|
|
<p>Note that the language of the external function determines how
|
|
the parameters are passed. For example, in C, any VARCHAR or CHAR parameters
|
|
are passed as NUL-terminated strings. For more information, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book. For Java routines, see the <a href="../rzaha/whatitis.htm">IBM Developer
|
|
Kit for Java</a>.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="wq1433"></a>
|
|
<h3 id="wq1433"><a href="rbafzmst02.htm#ToC_993">Notes</a></h3>
|
|
<p><span class="bold">DECLARE PROCEDURE scope:</span> The scope of the <span class="italic">procedure-name</span> is the source program in which it is defined; that is,
|
|
the program submitted to the precompiler. Thus, a program called from another
|
|
separately compiled program or module will not use the attributes from a DECLARE
|
|
PROCEDURE statement in the calling program.</p>
|
|
<p><span class="bold">DECLARE PROCEDURE rules:</span> The DECLARE PROCEDURE statement
|
|
should precede all CALL statements that reference that procedure.</p>
|
|
<p>The DECLARE PROCEDURE statement only applies to static CALL statements.
|
|
It does not apply to any dynamically prepared CALL statements or a CALL statement
|
|
where the procedure name is identified by a variable.</p>
|
|
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
|
|
supported for compatibility to prior releases. These keywords are non-standard
|
|
and should not be used:</p>
|
|
<ul>
|
|
<li>The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC
|
|
and DETERMINISTIC.</li>
|
|
<li>The keywords NULL CALL and NOT NULL CALL can be used as synonyms for CALLED
|
|
ON NULL INPUT and RETURNS NULL ON NULL INPUT.</li>
|
|
<li>The keywords SIMPLE CALL can be used as a synonym for GENERAL.</li>
|
|
<li>The value DB2GENRL may be used as a synonym for DB2GENERAL.</li>
|
|
<li>The keywords PARAMETER STYLE in the PARAMETER STYLE clause are optional.</li></ul>
|
|
<a name="wq1434"></a>
|
|
<h3 id="wq1434"><a href="rbafzmst02.htm#ToC_994">Example</a></h3>
|
|
<p>Declare an external procedure PROC1 in a C program. When the procedure
|
|
is called using the CALL statement, a COBOL program named PGM1 in library
|
|
LIB1 will be called. </p>
|
|
<pre class="xmp"> EXEC SQL
|
|
<span class="bold">DECLARE</span> PROC1 <span class="bold">PROCEDURE</span>
|
|
(<span class="bold">CHAR</span>(10), <span class="bold">CHAR</span>(10))
|
|
<span class="bold">EXTERNAL NAME</span> LIB1.PGM1
|
|
<span class="bold">LANGUAGE COBOL GENERAL</span>;
|
|
|
|
EXEC SQL
|
|
<span class="bold">CALL</span> PROC1 ('FIRSTNAME ','LASTNAME ');</pre><a id="idx2340" name="idx2340"></a><a id="idx2341" name="idx2341"></a>
|
|
<hr /><div class="fnnum"><a id="wq1429" name="wq1429" href="rbafzmstdclpef.htm#wq1428">69</a>.</div>
|
|
<div class="fntext">When the language
|
|
type is REXX, all parameters must be input parameters.</div>
|
|
<br />
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstdgtt.htm">Previous Page</a> | <a href="rbafzmstdclst.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
|
|
<a href="rbafzmstindex.htm#index">Index</a> ]
|
|
|
|
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
|
|
</body>
|
|
</html>
|