282 lines
15 KiB
HTML
282 lines
15 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="creating, function, name restrictions,
|
|
input parameters, locators, specific name, extending a built-in function,
|
|
overriding a built-in function" />
|
|
<title>CREATE FUNCTION</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="createf"></a>
|
|
<h2 id="createf"><a href="rbafzmst02.htm#ToC_851">CREATE FUNCTION</a></h2><a id="idx1628" name="idx1628"></a>
|
|
<p>The CREATE FUNCTION statement defines a user-defined function at the current
|
|
server. The following types of functions can be defined: </p>
|
|
<ul>
|
|
<li>External Scalar
|
|
<p>The function is written in a programming language
|
|
such as C or Java™ and returns a scalar value. The external program
|
|
is referenced by a function defined at the current server along with various
|
|
attributes of the function. See <a href="rbafzmstcfsce.htm#cfsce">CREATE FUNCTION (External Scalar)</a>.</p></li>
|
|
<li>External Table
|
|
<p>The function is written in a programming language
|
|
such as C or Java and returns a set of rows. The external program
|
|
is referenced by a function defined at the current server along with various
|
|
attributes of the function. See <a href="rbafzmstcftbe.htm#cftbe">CREATE FUNCTION (External Table)</a>.</p></li>
|
|
<li>Sourced
|
|
<p>The function is implemented by invoking another function (built-in,
|
|
external, sourced, or SQL) that already exists at the current server. A sourced
|
|
function can return a scalar result, or the result of an aggregate function.
|
|
See <a href="rbafzmstcfso.htm#cfso">CREATE FUNCTION (Sourced)</a>. The function inherits attributes of the underlying
|
|
source function.</p></li>
|
|
<li>SQL Scalar
|
|
<p>The function is written exclusively in SQL and returns
|
|
a scalar value. The function body is defined at the current server along with
|
|
various attributes of the function. See <a href="rbafzmstcfsc.htm#cfsc">CREATE FUNCTION (SQL Scalar)</a>.</p></li>
|
|
<li>SQL TABLE
|
|
<p>The function is written exclusively in SQL and returns
|
|
a set of rows. The function body is defined at the current server along with
|
|
various attributes of the function. See <a href="rbafzmstcftb.htm#cftb">CREATE FUNCTION (SQL Table)</a>.</p></li></ul>
|
|
<a name="wq1188"></a>
|
|
<h3 id="wq1188"><a href="rbafzmst02.htm#ToC_852">Notes</a></h3>
|
|
<a name="fnamerst"></a>
|
|
<p id="fnamerst"><span class="bold">Choosing the schema and function name:</span> If a qualified function name is
|
|
specified, the <var class="pv">schema-name</var> cannot be QSYS2, QSYS, QTEMP, or SYSIBM.
|
|
If <var class="pv">function-name</var> is not qualified, it is implicitly qualified with
|
|
the default schema name.<a id="idx1629" name="idx1629"></a></p>
|
|
<p>The unqualified function name must not be one of the following names reserved
|
|
for system use even if they are specified as delimited identifiers:</p>
|
|
<a name="wq1189"></a>
|
|
<table id="wq1189" width="100%" summary="" border="1" frame="hsides" rules="cols">
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td width="28%" align="center" valign="top">=</td>
|
|
<td width="23%" align="center" valign="top"><</td>
|
|
<td width="23%" align="center" valign="top">></td>
|
|
<td width="23%" align="center" valign="top">>=</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top"><=</td>
|
|
<td align="center" valign="top"><></td>
|
|
<td align="center" valign="top">¬=</td>
|
|
<td align="center" valign="top">¬<</td>
|
|
</tr>
|
|
<tr>
|
|
<td>¬<</td>
|
|
<td>!=</td>
|
|
<td>!<</td>
|
|
<td>!></td>
|
|
</tr>
|
|
<tr>
|
|
<td>ALL</td>
|
|
<td align="center" valign="top">DISTINCT</td>
|
|
<td align="center" valign="top">NODENAME</td>
|
|
<td align="center" valign="top">SOME</td>
|
|
</tr>
|
|
<tr>
|
|
<td>AND</td>
|
|
<td align="center" valign="top">EXCEPT</td>
|
|
<td align="center" valign="top">NODENUMBER</td>
|
|
<td align="center" valign="top">STRIP</td>
|
|
</tr>
|
|
<tr>
|
|
<td>ANY</td>
|
|
<td align="center" valign="top">EXISTS</td>
|
|
<td align="center" valign="top">NOT</td>
|
|
<td align="center" valign="top">SUBSTRING</td>
|
|
</tr>
|
|
<tr>
|
|
<td>BETWEEN</td>
|
|
<td align="center" valign="top">EXTRACT</td>
|
|
<td align="center" valign="top">NULL</td>
|
|
<td align="center" valign="top">TABLE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">BOOLEAN</td>
|
|
<td align="center" valign="top">FALSE</td>
|
|
<td align="center" valign="top">ONLY</td>
|
|
<td align="center" valign="top">THEN</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">CASE</td>
|
|
<td align="center" valign="top">FOR</td>
|
|
<td align="center" valign="top">OR</td>
|
|
<td align="center" valign="top">TRIM</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">CAST</td>
|
|
<td align="center" valign="top">FROM</td>
|
|
<td align="center" valign="top">OVERLAPS</td>
|
|
<td align="center" valign="top">TRUE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">CHECK</td>
|
|
<td align="center" valign="top">HASHED_VALUE</td>
|
|
<td align="center" valign="top">PARTITION</td>
|
|
<td align="center" valign="top">TYPE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DATAPARTITIONNAME</td>
|
|
<td align="center" valign="top">IN</td>
|
|
<td align="center" valign="top">POSITION</td>
|
|
<td align="center" valign="top">UNIQUE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DATAPARTITIONNUM</td>
|
|
<td align="center" valign="top">IS</td>
|
|
<td align="center" valign="top">RRN</td>
|
|
<td align="center" valign="top">UNKNOWN</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DBPARTITIONNAME</td>
|
|
<td align="center" valign="top">LIKE</td>
|
|
<td align="center" valign="top">SELECT</td>
|
|
<td align="center" valign="top">WHEN</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DBPARTITIONNUM</td>
|
|
<td align="center" valign="top">MATCH</td>
|
|
<td align="center" valign="top">SIMILAR</td>
|
|
<td align="center" valign="top"></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p><span class="bold">Defining the parameters:</span> The input parameters for
|
|
the function are specified as a list within parenthesis.</p>
|
|
<p>The maximum number of parameters allowed in CREATE FUNCTION is 90.</p>
|
|
<p>A function can have no input parameters. In this case, an empty set of
|
|
parenthesis must be specified, for example: </p>
|
|
<pre class="xmp"> <span class="bold">CREATE FUNCTION</span> WOOFER()</pre>
|
|
<p>The data type of the result of the function is specified in the RETURNS
|
|
clause for the function.</p>
|
|
<ul>
|
|
<li><span class="bold">Choosing data types for parameters:</span> When choosing
|
|
the data types of the input and result parameters for a function, the rules
|
|
of promotion that can affect the values of the parameters need to be considered.
|
|
See <a href="rbafzmstpromo.htm#promo">Promotion of data types</a>. For example, a constant that is one of the input
|
|
arguments to the function might have a built-in data type that is different
|
|
from the data type that the function expects, and more significantly, might
|
|
not be promotable to that expected data type. Based on the rules of promotion,
|
|
we recommend using the following data types for parameters:
|
|
<ul>
|
|
<li>INTEGER instead of SMALLINT</li>
|
|
<li>DOUBLE instead of REAL</li>
|
|
<li>VARCHAR instead of CHAR</li>
|
|
<li>VARGRAPHIC instead of GRAPHIC</li></ul><a id="idx1630" name="idx1630"></a>
|
|
<p>For portability of functions across platforms that are not DB2 UDB for iSeries,
|
|
do not use the following data types, which might have different representations
|
|
on different platforms: </p>
|
|
<ul>
|
|
<li>FLOAT. Use DOUBLE or REAL instead.</li>
|
|
<li>NUMERIC. Use DECIMAL instead.</li></ul></li>
|
|
<li><span class="bold">Specifying AS LOCATOR for a parameter:</span> Passing a
|
|
locator instead of a value can result in fewer bytes being passed in or out
|
|
of the function. This can be useful when the value of the parameter is very
|
|
large. The AS LOCATOR clause specifies that a locator to the value of the
|
|
parameter is passed instead of the actual value. Specify AS LOCATOR only for
|
|
parameters with a LOB data type or a distinct type based on a LOB data type.<a id="idx1631" name="idx1631"></a>
|
|
<p>The
|
|
AS LOCATOR clause has no effect on determining whether data types can be promoted,
|
|
nor does it affect the function signature, which is used in function resolution.</p>
|
|
<p>AS LOCATOR cannot be specified for SQL functions.</p></li></ul>
|
|
<p><span class="bold">Determining the uniqueness of functions in a schema:</span> The
|
|
same name can be used for more than one function in a schema if the function
|
|
signature of each function is unique. The function signature is the qualified
|
|
function name combined with the number and data types of the input parameters.
|
|
The combination of name, schema name, the number of parameters, and the data
|
|
type each parameter (without regard for other attributes such as length, precision,
|
|
scale, or CCSID) must not identify a user-defined function that exists at
|
|
the current server. The return type has no impact on the determining uniqueness
|
|
of a function. Two different schemas can each contain a function with the
|
|
same name that have the same data types for all of their corresponding data
|
|
types. However, a schema must not contain two functions with the same name
|
|
that have the same data types for all of their corresponding data types.</p>
|
|
<p>When determining whether corresponding data types match, the database manager
|
|
does not consider any length, precision, or scale attributes in the comparison.
|
|
The database manager considers the synonyms of data types a match. For example,
|
|
REAL and FLOAT, and DOUBLE and FLOAT are considered a match. Therefore, CHAR(8)
|
|
and CHAR(35) are considered to be the same, as are DECIMAL(11,2), and DECIMAL(4,3).
|
|
Furthermore, the character and graphic types are considered to be the same.
|
|
For example, the following are considered to be the same type: CHAR and GRAPHIC,
|
|
VARCHAR and VARGRAPHIC, and CLOB and DBCLOB. CHAR(13) and GRAPHIC(8) are considered
|
|
to be the same type. An error is returned if the signature of the function
|
|
being created is a duplicate of a signature for an existing user-defined function
|
|
with the same name and schema.</p>
|
|
<p>Assume that the following statements are executed to create four functions
|
|
in the same schema. The second and fourth statements fail because they create
|
|
functions that are duplicates of the functions that the first and third statements
|
|
created.</p>
|
|
<pre class="xmp"><span class="bold">CREATE FUNCTION</span> PART <span class="bold">(INT, CHAR(</span>15<span class="bold">)</span> ...
|
|
<span class="bold">CREATE FUNCTION</span> PART <span class="bold">(INTEGER, CHAR(</span>40<span class="bold">)</span> ...
|
|
|
|
<span class="bold">CREATE FUNCTION</span> ANGLE <span class="bold">(DECIMAL(</span>12,2<span class="bold">))</span> ...
|
|
<span class="bold">CREATE FUNCTION</span> ANGLE <span class="bold">(DEC(</span>10,7<span class="bold">))</span> ...
|
|
</pre>
|
|
<p><span class="bold">Specifying a specific name for a function:</span> When defining
|
|
multiple functions with the same name and schema (with different parameter
|
|
lists), it is recommended that a specific name also be specified. The specific
|
|
name can be used to uniquely identify the function such as when sourcing on
|
|
this function, dropping the function, or commenting on the function. However,
|
|
the function cannot be invoked by its specific name.<a id="idx1632" name="idx1632"></a></p>
|
|
<p>The specific name is implicitly or explicitly qualified with a schema name.
|
|
If a schema name is not specified on CREATE FUNCTION, it is the same as the
|
|
explicit or implicit schema name of the function name (<span class="italic">function-name</span>). If a schema name is specified, it must be the same as
|
|
the explicit or implicit schema name of the function name. The name, including
|
|
the schema name must not identify the specific name of another function or
|
|
procedure that exists at the current server.</p>
|
|
<p>If the SPECIFIC clause is not specified, a specific name is generated.</p>
|
|
<p><span class="bold">Extending or overriding a built-in function:</span> Giving
|
|
a user-defined function the same name as a built-in function is not a recommended
|
|
practice unless the functionality of the built-in function needs to be extended
|
|
or overridden.<a id="idx1633" name="idx1633"></a><a id="idx1634" name="idx1634"></a></p>
|
|
<ul>
|
|
<li><span class="bold">Extending the functionality of existing built-in functions:</span>
|
|
<p>Create the new user-defined function with the same name as the built-in
|
|
function, and a unique function signature. For example, a user-defined function
|
|
similar to the built-in function ROUND that accepts the distinct type MONEY
|
|
as input rather than the built-in numeric types might be necessary. In this
|
|
case, the signature for the new user-defined function named ROUND is different
|
|
from all the function signatures supported by the built-in ROUND function.</p></li>
|
|
<li><span class="bold">Overriding a built-in function:</span>
|
|
<p>Create the new
|
|
user-defined function with the same name and signature as an existing built-in
|
|
function. The new function has the same name and data type as the corresponding
|
|
parameters of the built-in function but implements different logic. For example,
|
|
a user-defined function similar to the built-in function ROUND that uses different
|
|
rules for rounding than the built-in ROUND function might be necessary. In
|
|
this case, the signature for the new user-defined function named ROUND will
|
|
be the same as a signature that is supported by the built-in ROUND function.</p>
|
|
<p>Once a built-in function has been overridden, an application that uses
|
|
the unqualified function name and was previously successful using the built-in
|
|
function of that name might fail, or perhaps even worse, appear to run successfully
|
|
but provide a different result if the user-defined function is chosen by the database manager rather
|
|
than the built-in function.</p></li></ul>
|
|
<p><span class="bold">Special registers in functions:</span> The settings of the
|
|
special registers of the invoker are inherited by the function on invocation
|
|
and restored upon return to the invoker.</p>
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcrtudt.htm">Previous Page</a> | <a href="rbafzmstcfsce.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>
|