User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To create a new function in the database.

Syntax

     CREATE FUNCTION [ owner.]function-name ( [ parameter , ... ] )
          ... RETURNS data-type
          ... { EXTERNAL NAME library-call |
          ... [ ON EXCEPTION RESUME ]
          ... compound-statement }

Parameters

     parameter:
          parameter-name data-type

     library-call:
          '[operating-system:]function-name@library.dll; ...'

     operating-system:
          OS2
          | Windows3X
          | Windows95
          | WindowsNT
          | NetWare

Usage

Anywhere.

Permissions

Must have RESOURCE authority.

For external functions, must have DBA authority.

Side effects

Automatic commit.

See also

• DROP statement
• Compound statements
• CREATE PROCEDURE statement
• RETURN statement
• The chapter "Using Procedures, Triggers, and Batches"

Description

The CREATE FUNCTION statement creates (stores) a user-defined function in the database. A function can be created for another user by specifying an owner name. Subject to permissions, a user-defined function can be used in exactly the same way as other nonaggregate functions.

Parameter names must conform to the rules for other database identifiers such as column names. They must be one of the types supported by SQL Anywhere (see "SQL Anywhere Data Types"), and must be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the procedure.

A function using the EXTERNAL NAME clause is a wrapper around a call to an external dynamic link library, and is called an external stored procedure. An external stored procedure can have no clauses other than the EXTERNAL NAME clause following the RETURNS clause. For a description of external procedures, see "Calling external libraries from stored procedures".

Example

The following function concatenates a firstname string and a lastname string.

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

The following ISQL statements illustrate the use of the fullname function.

Return a full name from two supplied strings:

     SELECT fullname ('joe','smith')

fullname('joe','smith')
joe smith

List the names of all employees:

     SELECT fullname (emp_fname, emp_lname)
     FROM employee

fullname (emp_fname, emp_lname)
Fran Whitney
Matthew Cobb
Philip Chin
Julie Jordan
Robert Breault
...