It is also worth mentioning that you can have more than one UDF in a file; You could create as many
UDFs in one file as you wanted. The sample that is provided in the MySQL source is an example of this.After you have finished creating your UDF, you must compile it as a shareable object. (This means that
the platform you are using must support this.) To compile your example, you would enter the following
command:
gcc –shared –o Double_Proc.o Double_Proc.cc
After you have compiled your procedure successfully, you need to move the object to the directory
where the system looks for shared files. On Red Hat, it should be the /usr/lib directory. Copy that
file to this directory. You then need to shut down and restart the MySQL server daemon.
After you have done that, you need to tell MySQL about these new functions. You do this by using the
CREATE FUNCTION command. The CREATE FUNCTION command uses the following syntax:
CREATE FUNCTION function name
RETURNS return type
SONAME shared object name
After the CREATE FUNCTION keyword, use the function name that you have given your UDF. In this
example, it would be Double_Proc. After the function name is the return type of the function. It could
be REAL, INTEGER, or STRING. This should match the return type of your UDF. The final parameter of
the command is the name of the compiled shared object. This is the object that you moved to the
/usr/lib directory. Using the sample function, you would create an entry in the func table of the
mysql database as follows:
CREATE FUNCTION Double_Proc
RETURNS INTEGER
SONAME Double_Proc.so
If you need to delete a function, you must use the DROP FUNCTION command. This command uses the
following syntax:
DROP FUNCTION function name
The keywords are followed by the name of the function that you want to delete. Using the previous
example, you could delete the Double_Proc function by using the following statement:
DROP FUNCTION Double_Proc
The CREATE and DROP functions add and remove entries from the mysql grant tables, namely the
func table. Every time MySQL is started, it will load the functions from this table.
These functions are also available across any of the databases. They do not serve just one particular
database. They can be used by anyone who has SELECT privileges to the database.
As you can see, UDFs are close to a stored procedure but do not provide all the functionality that stored
procedures do. The nice thing about a UDF is that it does not affect the speed of MySQL. A major
problem is that you cannot really embed any SQL commands as you can in a true stored procedure. But
if you needed to verify data, perform complex calculations, or do some very specific manipulation, you
could accomplish all of that with a MySQL user-defined function.
Future versions of MySQL will contain a PROCEDURE statement that will accomplish nearly the same
tasks as a stored procedure. It will allow the manipulation of data before the resultset is released to the
client.
Because MySQL is so fast, the need for stored procedures is relatively slight. Using the various APIs,
you could create functions within those APIs that could produce the same effects as a stored procedure.
For example, using Visual Basic and the ODBC interface, you could create a function that performs a
table-wide update. Take a look at the following code:
Option Explicit
Dim mConn As New ADODB.Connection
Dim mCmd as New ADODB.Command
Public Sub UpdateCustomers(DateOrdered)
With mConn
.ConnectionString = "Driver=MySQL;Server=10.1.2.23;" & _