Oracle 5.0 Reference Manual Download

Page: 2829 / 3232

Features of the User-Defined Function Interface

2809

CREATE FUNCTION

and

DROP FUNCTION

statements. See

Section 13.7.3.1, “

CREATE FUNCTION

Syntax for User-defined Functions”

• You can add functions as native (built-in) MySQL functions. Native functions are compiled into the

mysqld

server and become available on a permanent basis.

• Another way to add functions is by creating stored functions. These are written using SQL

statements rather than by compiling object code. The syntax for writing stored functions is not
covered here. See

Section 18.2, “Using Stored Routines (Procedures and Functions)”

Each method of creating compiled functions has advantages and disadvantages:

• If you write user-defined functions, you must install object files in addition to the server itself. If you

compile your function into the server, you don't need to do that.

• Native functions require you to modify a source distribution. UDFs do not. You can add UDFs to a

binary MySQL distribution. No access to MySQL source is necessary.

• If you upgrade your MySQL distribution, you can continue to use your previously installed UDFs,

unless you upgrade to a newer version for which the UDF interface changes. For native functions,
you must repeat your modifications each time you upgrade.

Whichever method you use to add new functions, they can be invoked in SQL statements just like
native functions such as

ABS()

[908]

SOUNDEX()

[893]

See

Section 9.2.3, “Function Name Parsing and Resolution”

, for the rules describing how the server

interprets references to different kinds of functions.

The following sections describe features of the UDF interface, provide instructions for writing UDFs,
discuss security precautions that MySQL takes to prevent UDF misuse, and describe how to add native
MySQL functions.

For example source code that illustrates how to write UDFs, take a look at the

sql/udf_example.c

file that is provided in MySQL source distributions.

21.2.1. Features of the User-Defined Function Interface

The MySQL interface for user-defined functions provides the following features and capabilities:

• Functions can return string, integer, or real values and can accept arguments of those same types.

• You can define simple functions that operate on a single row at a time, or aggregate functions that

operate on groups of rows.

• Information is provided to functions that enables them to check the number, types, and names of the

arguments passed to them.

• You can tell MySQL to coerce arguments to a given type before passing them to a function.

• You can indicate that a function returns

NULL

or that an error occurred.

21.2.2. Adding a New User-Defined Function

For the UDF mechanism to work, functions must be written in C or C++ and your operating system
must support dynamic loading. MySQL source distributions include a file

sql/udf_example.c

that

defines five UDF functions. Consult this file to see how UDF calling conventions work. The

include/

mysql_com.h

header file defines UDF-related symbols and data structures, although you need not

include this header file directly; it is included by

mysql.h

A UDF contains code that becomes part of the running server, so when you write a UDF, you are
bound by any and all constraints that apply to writing server code. For example, you may have
problems if you attempt to use functions from the

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands