User's Guide

Part III. Using SQL Anywhere

Chapter 20. Using Procedures, Triggers, and Batches

User-defined functions are a class of procedures that return a single value to the calling environment. This section introduces creating, using, and dropping user-defined functions

Creating user-defined functions

User-defined functions are created using the CREATE FUNCTION statement. You must have RESOURCE authority in order to create a user-defined function.

The following simple example creates a function that concatenates two strings, together with a space, to form a full name from a first name and a last name.

You can create the example function fullname by connecting to the sample database from the ISQL utility as user ID DBA, using password SQL, and typing the statement in the command window.

If you are using a tool other than ISQL or Sybase Central, you may need to change the command delimiter away from the semicolon before entering the CREATE FUNCTION statement.

     CREATE FUNCTION fullname (firstname CHAR(30),
         lastname CHAR(30))
     RETURNS CHAR(61)
     BEGIN
         DECLARE name CHAR(61);
         SET name = firstname || ' ' || lastname;
         RETURN ( name );
     END

For a complete description of the CREATE FUNCTION syntax, see "CREATE FUNCTION statement".

The CREATE FUNCTION syntax differs slightly from that of the CREATE PROCEDURE statement. The following are distinctive differences:

• No IN, OUT, or INOUT keywords are required, as all parameters are IN parameters.
• The RETURNS clause is required to specify the data type being returned.
• The RETURN statement is required to specify the value being returned.

Calling user-defined functions

A user-defined function can be used, subject to permissions, in any place that a built-in non-aggregate function is used.

The following statement in ISQL displays a full name from two columns containing a first and last name:

     SELECT fullname (emp_fname, emp_lname)
     FROM employee;

fullname (emp_fname, emp_lname)

Phillip Chin

The following statement in ISQL displays a full name from a supplied first and last name:

     SELECT fullname ('Jane', 'Smith');

fullname ('Jane','Smith')

Jane Smith

The fullname function can be used by any user who has been granted EXECUTE permission for the function.

Dropping user-defined functions

Once a user-defined function is created, it remains in the database until it is explicitly removed. Only the owner of the function or a user with DBA authority can drop a function from the database.

The following statement removes the function fullname from the database:

     DROP FUNCTION fullname

Permissions to execute user-defined functions

A user-defined function is owned by the user who created it and that user can execute it without permission. Permission to execute it can be granted to other users using the GRANT EXECUTE command.

For example, the creator of the function fullname could allow another_user to use fullname with the statement:

     GRANT EXECUTE ON fullname TO another_user

The following statement revokes permission to use the function:

     REVOKE EXECUTE ON fullname FROM another_user

  For more information on managing user permissions on functions, see "Granting permissions on procedures".