545 lines
33 KiB
HTML
545 lines
33 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="CREATE FUNCTION (SQL Table) statement,
|
|
CREATE FUNCTION (SQL Table), SQL statement, creating, function, SQL,
|
|
CCSID clause, data type for CREATE FUNCTION (SQL Table), 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 CREATE FUNCTION (SQL Table), function-name, RETURNS clause, data-type,
|
|
LANGUAGE clause, SPECIFIC clause, DETERMINISTIC clause, NOT DETERMINISTIC clause,
|
|
EXTERNAL ACTION clause, NO EXTERNAL ACTION clause, CONTAINS SQL clause,
|
|
READS SQL DATA clause, MODIFIES SQL DATA clause, STATIC DISPATCH clause,
|
|
RETURNS NULL ON NULL INPUT clause, CALLED ON NULL INPUT clause, FENCED clause,
|
|
NOT FENCED clause, DISALLOW PARALLEL clause, CARDINALITY clause" />
|
|
<title>CREATE FUNCTION (SQL Table)</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="cftb"></a>
|
|
<h2 id="cftb"><a href="rbafzmst02.htm#ToC_881">CREATE FUNCTION (SQL Table)</a></h2><a id="idx1847" name="idx1847"></a><a id="idx1848" name="idx1848"></a><a id="idx1849" name="idx1849"></a><a id="idx1850" name="idx1850"></a><a id="idx1851" name="idx1851"></a>
|
|
<p>This CREATE FUNCTION (SQL table) statement creates an SQL table function
|
|
at the current server. The function returns a single result table.</p>
|
|
<a name="wq1238"></a>
|
|
<h3 id="wq1238"><a href="rbafzmst02.htm#ToC_882">Invocation</a></h3>
|
|
<p>You can embed this statement in an application program, or you can issue
|
|
this statement interactively. It is an executable statement that can be dynamically
|
|
prepared.</p>
|
|
<a name="wq1239"></a>
|
|
<h3 id="wq1239"><a href="rbafzmst02.htm#ToC_883">Authorization</a></h3>
|
|
<p>The privileges held by the authorization ID of the statement must include
|
|
at least one of the following:</p>
|
|
<ul>
|
|
<li>The privilege to create in the schema. For more information, see <a href="rbafzmstauthown.htm#createin">Privileges necessary to create in a schema</a>.</li>
|
|
<li>Administrative authority</li></ul>
|
|
<p>The privileges held by the authorization id of the statement must include
|
|
at least one of the following: </p>
|
|
<ul>
|
|
<li>For the SYSFUNCS catalog view and SYSPARMS catalog table:
|
|
|
|
<ul>
|
|
<li>The INSERT privilege on the table, and</li>
|
|
<li>The system authority *EXECUTE on library QSYS2</li></ul></li>
|
|
<li>Administrative Authority</li></ul>
|
|
<p>The privileges held by the authorization ID of the statement must also
|
|
include at least one of the following:</p>
|
|
<ul>
|
|
<li>The following system authorities:
|
|
<ul>
|
|
<li>*USE to the Create Service Program (CRTSRVPGM) command or</li></ul></li>
|
|
<li>Administrative authority</li></ul>
|
|
<p>If a distinct type is referenced, the privileges held by the authorization ID of
|
|
the statement must include at least one of the following: </p>
|
|
<ul>
|
|
<li>For each distinct type identified in the statement:
|
|
<ul>
|
|
<li>The USAGE privilege on the distinct type, and</li>
|
|
<li>The system authority *EXECUTE on the library containing the distinct type</li></ul></li>
|
|
<li>Administrative authority</li></ul>
|
|
<p>For information on the system authorities corresponding to SQL privileges,
|
|
see <a href="rbafzmstgnt.htm#eqtablet">Corresponding System Authorities When Checking Privileges to a Table or View</a> and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p>
|
|
<a name="wq1240"></a>
|
|
<h3 id="wq1240"><a href="rbafzmst02.htm#ToC_884">Syntax</a></h3>
|
|
<a href="rbafzmstcftb.htm#synscrtfunst"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq1241"></a>
|
|
<div class="fignone" id="wq1241">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn286.htm"
|
|
border="0" /></span><a href="#skipsyn-285"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-CREATE FUNCTION--<span class="italic">function-name</span>------------------------------->
|
|
|
|
>--(--+---------------------------+--)-------------------------->
|
|
| .-,---------------------. |
|
|
| V | |
|
|
'---<span class="italic">parameter-declaration</span>-+-'
|
|
|
|
.-,-----------------------.
|
|
V |
|
|
>--RETURNS TABLE--(----<span class="italic">column-name</span>--<span class="italic">data-type2</span>-+--)------------->
|
|
|
|
>--LANGUAGE SQL--<span class="italic">option-list</span>--+----------------------+--<span class="italic">SQL-routine-body</span>-><
|
|
'-<span class="italic">SET OPTION-statement</span>-'
|
|
|
|
parameter-declaration:
|
|
|
|
|--<span class="italic">parameter-name</span>--<span class="italic">data-type1</span>-----------------------------------|
|
|
|
|
data-type1, data-type2:
|
|
|
|
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
|
|
'-<span class="italic">distinct-type-name</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-285" id="skipsyn-285"></a></div>
|
|
<a name="wq1242"></a>
|
|
<div class="fignone" id="wq1242">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn287.htm"
|
|
border="0" /></span><a href="#skipsyn-286"><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-------------------+----------------------------------------------------------------+
|
|
| | .-(--0--)-. | |
|
|
| +-TIME--+---------+------+ |
|
|
| | .-(--6--)-. | |
|
|
| '-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-286" id="skipsyn-286"></a></div><a id="idx1852" name="idx1852"></a><a id="idx1853" name="idx1853"></a><a id="idx1854" name="idx1854"></a><a id="idx1855" name="idx1855"></a><a id="idx1856" name="idx1856"></a><a id="idx1857" name="idx1857"></a><a id="idx1858" name="idx1858"></a><a id="idx1859" name="idx1859"></a><a id="idx1860" name="idx1860"></a><a id="idx1861" name="idx1861"></a><a id="idx1862" name="idx1862"></a><a id="idx1863" name="idx1863"></a><a id="idx1864" name="idx1864"></a><a id="idx1865" name="idx1865"></a><a id="idx1866" name="idx1866"></a><a id="idx1867" name="idx1867"></a><a id="idx1868" name="idx1868"></a><a id="idx1869" name="idx1869"></a><a id="idx1870" name="idx1870"></a><a id="idx1871" name="idx1871"></a><a id="idx1872" name="idx1872"></a><a id="idx1873" name="idx1873"></a><a id="idx1874" name="idx1874"></a><a id="idx1875" name="idx1875"></a>
|
|
<a name="wq1243"></a>
|
|
<div class="fignone" id="wq1243">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn288.htm"
|
|
border="0" /></span><a href="#skipsyn-287"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>option-list:
|
|
|
|
(1) .-NOT DETERMINISTIC-.
|
|
|--+-------------------------+-------+-------------------+------>
|
|
'-SPECIFIC--<span class="italic">specific-name</span>-' '-DETERMINISTIC-----'
|
|
|
|
.-EXTERNAL ACTION----. .-READS SQL DATA----.
|
|
>--+--------------------+--+-------------------+---------------->
|
|
'-NO EXTERNAL ACTION-' +-CONTAINS SQL------+
|
|
'-MODIFIES SQL DATA-'
|
|
|
|
.-STATIC DISPATCH-. .-CALLED ON NULL INPUT-------.
|
|
>--+-----------------+--+----------------------------+---------->
|
|
'-RETURNS NULL ON NULL INPUT-'
|
|
|
|
.-FENCED-----.
|
|
>--+------------+----DISALLOW PARALLEL-------------------------->
|
|
'-NOT FENCED-'
|
|
|
|
>--+----------------------+-------------------------------------|
|
|
'-CARDINALITY--<span class="italic">integer</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-287" id="skipsyn-287"></a>
|
|
<a name="wq1244"></a>
|
|
<div class="notelisttitle" id="wq1244">Notes:</div>
|
|
<ol type="1">
|
|
<li>The optional clauses can be specified in a different
|
|
order.</li>
|
|
</ol></div>
|
|
<a name="wq1246"></a>
|
|
<div class="fignone" id="wq1246">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn289.htm"
|
|
border="0" /></span><a href="#skipsyn-288"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>SQL-routine-body:
|
|
|
|
|----<span class="italic">SQL-control-statement</span>--------------------------------------|
|
|
|
|
</pre>
|
|
<a name="skipsyn-288" id="skipsyn-288"></a></div>
|
|
<a name="synscrtfunst"></a>
|
|
<h3 id="synscrtfunst"><a href="rbafzmst02.htm#ToC_885">Description</a></h3><a id="idx1876" name="idx1876"></a>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">function-name</var></dt>
|
|
<dd>Names the user-defined function. The combination of name, schema name,
|
|
the number of parameters, and the data type of each parameter (without regard
|
|
for any length, precision, scale, or CCSID attributes of the data type) must
|
|
not identify a user-defined function that exists at the current server.
|
|
<p>For SQL naming, the function will be created in the schema specified by the
|
|
implicit or explicit qualifier.</p>
|
|
<p>For system naming, the function will
|
|
be created in the schema that is specified by the qualifier. If no qualifier
|
|
is specified:</p>
|
|
<ul>
|
|
<li>If the value of the CURRENT SCHEMA special register is *LIBL, the function
|
|
will be created in the current library (*CURLIB).</li>
|
|
<li>Otherwise, the function will be created in the current schema.</li></ul>
|
|
<p>In general, more than one function can have the same name if the
|
|
function signature of each function is unique.</p>
|
|
<p>Certain function names
|
|
are reserved for system use. For more information see <a href="rbafzmstcreatef.htm#fnamerst">Choosing the Schema and Function Name</a>.</p>
|
|
</dd>
|
|
<dt class="bold">(<var class="pv">parameter-declaration,...)</var></dt>
|
|
<dd>Specifies the number of parameters of the function and the data type
|
|
of each parameter. Although not required, you can give each parameter a name.
|
|
<p>The maximum number of parameters allowed is 90.</p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">parameter-name</var></dt>
|
|
<dd>Names the parameter. The name is used to refer to the parameter
|
|
within the body of the function. The name cannot be the same as any other <var class="pv">parameter-name</var> in the parameter list.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">data-type1</var></dt>
|
|
<dd>Specifies the data type of the input parameter. The data
|
|
type can be a built-in data type or a distinct data type.
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">built-in-type</var></dt>
|
|
<dd>Specifies a built-in data type. For a more complete description of each
|
|
built-in data type, see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">distinct-type-name</var></dt>
|
|
<dd>Specifies a distinct type. The length, precision, or scale attributes
|
|
for the parameter are those of the source type of the distinct type (those
|
|
specified on CREATE DISTINCT TYPE). For more information on creating a distinct
|
|
type, see <a href="rbafzmstcrtudt.htm#crtudt">CREATE DISTINCT TYPE</a>.
|
|
<p>If the name of the distinct type is unqualified,
|
|
the database manager resolves the schema name by searching the schemas in
|
|
the SQL path.</p>
|
|
</dd>
|
|
</dl>
|
|
<p>If a CCSID is specified, the parameter will be converted to that
|
|
CCSID prior to passing it to the function. If a CCSID is not specified, the
|
|
CCSID is determined by the default CCSID at the current server at the time
|
|
the function is invoked.</p>
|
|
</dd>
|
|
</dl>
|
|
</dd><a id="idx1877" name="idx1877"></a>
|
|
<dt class="bold">RETURNS TABLE</dt>
|
|
<dd>Specifies the output table of the function.
|
|
<p>Assume the number of parameters
|
|
is N. There must be no more than (247-(N*2))/2 columns.</p><a id="idx1878" name="idx1878"></a><a id="idx1879" name="idx1879"></a>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">column-name</var></dt>
|
|
<dd>Specifies the name of a column of the output table. Do not specify the
|
|
same name more than once.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">data-type2</var> </dt>
|
|
<dd>Specifies the data type and attributes of the output.
|
|
|
|
<p>You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC)
|
|
or a distinct type.</p>
|
|
<p>If a CCSID is specified and the CCSID of the return
|
|
data is encoded in a different CCSID, the data is converted to the specified
|
|
CCSID.</p>
|
|
<p>If a CCSID is not specified the return data is converted to the
|
|
CCSID of the job (or associated graphic CCSID of the job for graphic string
|
|
return values), if the CCSID of the return data is encoded in a different
|
|
CCSID. To avoid any potential loss of characters during the conversion, consider
|
|
explicitly specifying a CCSID that can represent any characters that will
|
|
be returned from the function. This is especially important if the data type
|
|
is graphic string data. In this case, consider using CCSID 1200 or 13488 (UTF-16
|
|
or UCS-2 graphic string data).</p>
|
|
</dd>
|
|
</dl>
|
|
</dd><a id="idx1880" name="idx1880"></a>
|
|
<dt class="bold">LANGUAGE SQL</dt>
|
|
<dd>Specifies that this is an SQL function.
|
|
</dd><a id="idx1881" name="idx1881"></a>
|
|
<dt class="bold">SPECIFIC <var class="pv">specific-name</var></dt>
|
|
<dd>Provides a unique name for the function. The name is implicitly or explicitly
|
|
qualified with a schema name. The name, including the schema name, must not
|
|
identify the specific name of another function or procedure that exists at
|
|
the current server. If unqualified, the implicit qualifier is the same as
|
|
the qualifier of the function name. If qualified, the qualifier must be the
|
|
same as the qualifier of the function name.
|
|
<p>If specific name is not specified,
|
|
it is set to the function name. If a function or procedure with that specific
|
|
name already exists, a unique name is generated similar to the rules used
|
|
to generate unique table names.</p>
|
|
</dd>
|
|
<dt class="bold">DETERMINISTIC <span class="base">or</span> NOT DETERMINISTIC</dt><a id="idx1882" name="idx1882"></a><a id="idx1883" name="idx1883"></a>
|
|
<dd>Specifies whether the function is deterministic.
|
|
<dl class="parml">
|
|
<dt class="bold">NOT DETERMINISTIC</dt>
|
|
<dd>Specifies that the function will not always return the same result from
|
|
successive function invocations with identical input arguments. NOT DETERMINISTIC
|
|
should be specified if the function contains a reference to a special register,
|
|
a non-deterministic function, or a sequence.
|
|
</dd>
|
|
<dt class="bold">DETERMINISTIC</dt>
|
|
<dd>Specifies that the function will always return the same result from
|
|
successive invocations with identical input arguments.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">EXTERNAL ACTION <span class="base">or</span> NO EXTERNAL ACTION</dt><a id="idx1884" name="idx1884"></a><a id="idx1885" name="idx1885"></a>
|
|
<dd>Specifies whether the function contains an external action.
|
|
<dl class="parml">
|
|
<dt class="bold">EXTERNAL ACTION</dt>
|
|
<dd>The function performs some external action (outside the scope of the
|
|
function program). Thus, the function must be invoked with each successive
|
|
function invocation. EXTERNAL ACTION should be specified if the function contains
|
|
a reference to another function that has an external action.
|
|
</dd>
|
|
<dt class="bold">NO EXTERNAL ACTION</dt>
|
|
<dd>The function does not perform an external action. It need not be called
|
|
with each successive function invocation.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">CONTAINS SQL, READS SQL DATA, <span class="base">or</span> MODIFIES
|
|
SQL DATA</dt>
|
|
<dd>Specifies whether the function can execute any SQL statements and, if
|
|
so, what type. The database manager verifies that the SQL issued by the function is
|
|
consistent with this specification. 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="idx1886" name="idx1886"></a>
|
|
<dd>The function does not execute SQL statements that read or modify data.
|
|
</dd>
|
|
<dt class="bold">READS SQL DATA</dt><a id="idx1887" name="idx1887"></a>
|
|
<dd>The function does not execute SQL statements that modify data.
|
|
</dd>
|
|
<dt class="bold">MODIFIES SQL DATA</dt><a id="idx1888" name="idx1888"></a>
|
|
<dd>The function can execute any SQL statement except those statements that
|
|
are not supported in any function.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">STATIC DISPATCH</dt><a id="idx1889" name="idx1889"></a>
|
|
<dd>Specifies that the function is dispatched statically. All functions
|
|
are statically dispatched.
|
|
</dd>
|
|
<dt class="bold">RETURNS NULL ON NULL INPUT <span class="base">or</span> CALLED ON
|
|
NULL INPUT</dt><a id="idx1890" name="idx1890"></a><a id="idx1891" name="idx1891"></a>
|
|
<dd>Specifies whether the function is called if any of the input arguments
|
|
is null at execution time.
|
|
<dl class="parml">
|
|
<dt class="bold">RETURNS NULL ON INPUT</dt>
|
|
<dd>Specifies that the function is not invoked if any of the input arguments
|
|
is null. The result is the null value.
|
|
</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>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">FENCED <span class="base">or</span> NOT FENCED</dt><a id="idx1892" name="idx1892"></a><a id="idx1893" name="idx1893"></a>
|
|
<dd>Specifies whether the SQL function runs in an environment that is isolated
|
|
from the database manager environment.
|
|
<dl class="parml">
|
|
<dt class="bold">FENCED</dt>
|
|
<dd>The function will run in a separate thread.
|
|
<p>FENCED functions cannot
|
|
keep SQL cursors open across individual calls to the function. However, the
|
|
cursors in one thread are independent of the cursors in any other threads
|
|
which reduces the possibility of cursor name conflicts.</p>
|
|
</dd>
|
|
<dt class="bold">NOT FENCED</dt>
|
|
<dd>The function may run in the same thread as the invoking SQL statement.
|
|
<p>NOT FENCED functions can keep SQL cursors open across individual calls to
|
|
the function. Since cursors can be kept open, the cursor position will also
|
|
be preserved between calls to the function. However, cursor names may conflict
|
|
since the UDF is now running in the same thread as the invoking SQL statement
|
|
and other NOT FENCED UDFs.</p>
|
|
<p>NOT FENCED functions usually perform better
|
|
than FENCED functions.</p>
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">DISALLOW PARALLEL</dt><a id="idx1894" name="idx1894"></a>
|
|
<dd>Specifies that the function cannot be run in parallel. Table functions
|
|
cannot run in parallel.
|
|
</dd>
|
|
<dt class="bold">CARDINALITY <var class="pv">integer</var></dt><a id="idx1895" name="idx1895"></a>
|
|
<dd>This optional clause provides an estimate of the expected number of
|
|
rows to be returned by the function for optimization purposes. Valid values
|
|
for integer range from 0 to 2 147 483 647 inclusive.
|
|
<p>If the CARDINALITY
|
|
clause is not specified for a table function, the database manager will assume a finite
|
|
value as a default.</p>
|
|
</dd>
|
|
<dt class="bold"><span class="italic">SET OPTION-statement</span></dt>
|
|
<dd>Specifies the options that will be used to create the function. For
|
|
example, to create a debuggable function, the following statement could be
|
|
included:
|
|
<pre class="xmp"><span class="bold">SET OPTION DBGVIEW = *SOURCE</span> </pre>For
|
|
more information, see <a href="rbafzmstsoption.htm#soption">SET OPTION</a>.
|
|
<p>The options CLOSQLCSR,
|
|
CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION
|
|
statement.</p>
|
|
</dd>
|
|
<dt class="bold"><span class="italic">SQL-routine-body</span></dt>
|
|
<dd>Specifies a single SQL statement, including a compound statement. See <a href="rbafzmstsqlcontstmts.htm#sqlcontstmts">SQL control statements</a> for more information about defining SQL functions.
|
|
<p>A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT,
|
|
COMMIT, ROLLBACK and SET TRANSACTION statement is not allowed in a function.</p>
|
|
<p>If the <var class="pv">SQL-routine-body</var> is a compound statement, it must contain
|
|
exactly one RETURN statement and it must be executed when the function is
|
|
called.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="wq1247"></a>
|
|
<h3 id="wq1247"><a href="rbafzmst02.htm#ToC_886">Notes</a></h3>
|
|
<p><span class="bold">General considerations for defining user-defined functions:</span> See <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a> for general information on defining user-defined
|
|
functions.</p>
|
|
<p><span class="bold">Function ownership:</span> If SQL names were
|
|
specified:</p>
|
|
<ul>
|
|
<li>If a user profile with the same name as the schema into which the function
|
|
is created exists, the <span class="italic">owner</span> of the function is that
|
|
user profile.</li>
|
|
<li>Otherwise, the <span class="italic">owner</span> of the function is the user
|
|
profile or group user profile of the job executing the statement.</li></ul>
|
|
<p>If system names were specified, the <span class="italic">owner</span> of the
|
|
function is the user profile or group user profile of the job executing the
|
|
statement.</p>
|
|
<p><span class="bold">Function authority:</span> If SQL names are used, functions
|
|
are created with the system authority of *EXCLUDE on *PUBLIC. If system names
|
|
are used, functions are created with the authority to *PUBLIC as determined
|
|
by the create authority (CRTAUT) parameter of the schema.</p>
|
|
<p>If the owner of the function is a member of a group profile (GRPPRF keyword)
|
|
and group authority is specified (GRPAUT keyword), that group profile will
|
|
also have authority to the function.</p>
|
|
<p><span class="bold">Creating the function:</span> When an SQL function is created, the database manager creates
|
|
a temporary source file that will contain C source code with embedded SQL
|
|
statements. A *SRVPGM object is then created using the CRTSRVPGM command.
|
|
The SQL options used to create the service program are the options that are
|
|
in effect at the time the CREATE FUNCTION statement is executed. The service
|
|
program is created with ACTGRP(*CALLER).</p>
|
|
<p>The specific name is used to determine the name of the source file member
|
|
and *SRVPGM object. If the specific name is a valid system name, it will used
|
|
as the name of member and program. If the member already exists, it will be
|
|
overlaid. If a program already exists in the specified library, a unique name
|
|
is generated using the rules for generating system table names. If the specific
|
|
name is not a valid system name, a unique name is generated using the rules
|
|
for generating system table names.</p>
|
|
<p>The function's attributes are saved in the associated service program object.
|
|
If the *SRVPGM object is saved and then restored to this or another system,
|
|
the catalogs are automatically updated with those attributes.</p>
|
|
<p>During restore of the function:</p>
|
|
<ul>
|
|
<li>If the specific name was specified when the function was originally created
|
|
and it is not unique, an error is issued.</li>
|
|
<li>If the specific name was not specified, a unique name is generated if
|
|
necessary.</li>
|
|
<li>If the same function signature already exists in the catalog:
|
|
<ul>
|
|
<li>If the name of the service program is the same as the one registered in
|
|
the catalog, the function information in the catalog will be replaced.</li>
|
|
<li>Otherwise, the function cannot be registered, and an error is issued.</li></ul></li></ul>
|
|
<p><span class="bold">Identifier resolution:</span> If the tables specified in
|
|
a routine body exist, all references in the routine body of an SQL routine
|
|
are resolved to identify a particular column, SQL parameter, or SQL variable
|
|
at the time the SQL routine is created. If the tables do not exist, all names
|
|
that exist as SQL variables or parameters are resolved to identify the variable
|
|
or parameter when the function is created. The remaining names are assumed
|
|
to be columns bound to the tables when the function is invoked.</p>
|
|
<p>If duplicate names are used for columns and SQL variables and parameters,
|
|
qualify the duplicate names by using the table designator for columns, the
|
|
function name for parameters, and the label name for SQL variables.</p>
|
|
<p><span class="bold">Invoking the function:</span> When an SQL function is invoked,
|
|
it runs in the activation group of the calling program.</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 IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.</li></ul>
|
|
<a name="wq1248"></a>
|
|
<h3 id="wq1248"><a href="rbafzmst02.htm#ToC_887">Example</a></h3>
|
|
<p>Define a table function that returns the employees in a specified department
|
|
number.</p>
|
|
<pre class="xmp"><span class="bold">CREATE FUNCTION</span> DEPTEMPLOYEES <span class="bold">(</span>DEPTNO <span class="bold">CHAR(</span>3<span class="bold">))</span>
|
|
<span class="bold">RETURNS TABLE (</span>EMPNO <span class="bold">CHAR(</span>6<span class="bold">)</span>,
|
|
LASTNAME <span class="bold">VARCHAR(</span>15<span class="bold">)</span>,
|
|
FIRSTNAME <span class="bold">VARCHAR(</span>12<span class="bold">))</span>
|
|
<span class="bold">LANGUAGE SQL</span>
|
|
<span class="bold">READS SQL DATA</span>
|
|
<span class="bold">NO EXTERNAL ACTION</span>
|
|
<span class="bold">DETERMINISTIC</span>
|
|
<span class="bold">DISALLOW PARALLEL</span>
|
|
<span class="bold">RETURN</span>
|
|
<span class="bold">SELECT</span> EMPNO,LASTNAME,FIRSTNME
|
|
<span class="bold">FROM</span> EMPLOYEE
|
|
<span class="bold">WHERE</span> EMPLOYEE.WORKDEPT =DEPTEMPLOYEES.DEPTNO</pre>
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcfsc.htm">Previous Page</a> | <a href="rbafzmstxcindx.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>
|