Caché SQL Reference
[Back] [Next]
Go to:

Creates a function as a method in a class.
CREATE FUNCTION name(parameter_list) characteristics code_body
name The name of the function to be created, which is an identifier. This function name may be unqualified (StoreName) or qualified (Patient.StoreName). The name must be followed by parentheses, even if no parameters are specified. For further details see the “Identifiers” chapter of Using Caché SQL.
parameter_list Optional — A list of parameters used to pass values to the function. The parameter list is enclosed in parentheses, and parameters in the list are separated by commas. The parentheses are mandatory, even when no parameters are specified.
characteristics Optional — One or more keywords specifying the characteristics of the function. Permitted keywords are FOR, FINAL, PRIVATE, PROCEDURE, RETURNS, SELECTMODE. Multiple characteristics are separated by whitespace (a space or line break). Characteristics can be specified in any order.
Optional — The programming language used for code_body. Specify LANGUAGE OBJECTSCRIPT (for Caché ObjectScript) or LANGUAGE SQL. If the LANGUAGE clause is omitted, SQL is the default.
The program code for the method.
SQL program code is prefaced with a BEGIN keyword and concludes with an END keyword. Each complete SQL statement within code_body ends with a semicolon (;).
Caché ObjectScript program code is enclosed in curly braces. ObjectScript code lines must be indented.
The CREATE FUNCTION statement creates a function as a method in a class. This class method is projected as an SQL Stored Procedure. You can also use the CREATE PROCEDURE statement to create a method which is projected as an SQL Stored Procedure. CREATE FUNCTION should be used when the method is to return a value, but it can be used to create a method that does not return a value.
In order to create a function, you must have %CREATE_FUNCTION administrative privilege, as specified by the GRANT command.
For information on calling SQL functions from within SQL statements, refer to User-defined Functions in the “Querying the Database” chapter of Using Caché SQL. For calling SQL stored procedures in a variety of contexts, refer to the CALL statement.
The name of the function (method) to be created. This name may be unqualified (StoreName) and take the default schema name, or qualified by specifying the schema name (Patient.StoreName). If you specify _CURRENT_USER as the schema name, Caché uses the default schema name. You can use the $SYSTEM.SQL.DefaultSchema() method to determine the default schema name. The default schema name SQLUser corresponds to the class package name User.
Note that the FOR characteristic (described below) overrides the class name specified in name. If a method with this name already exists, the operation fails with an SQLCODE -361 error.
If the function name is unqualified, the name of the generated class is the default schema name, followed by a dot, followed by “func”, followed by the specified name. Thus the unqualified name StoreName results in a class name such as the following: User.funcStoreName. For further details, see SQL to Class Name Transformations in the “Defining and Using Stored Procedures” chapter of Using Caché SQL.
Caché SQL does not allow you to specify a duplicate function name that differs only in letter case. Specifying a function name that differs only in letter case from an existing function name results in an SQLCODE -400 error.
A list of parameters used to pass values to the function. The parameter list is enclosed in parentheses, and parameter declarations in the list are separated by commas. Each parameter declaration in the list consists of (in order):
The following example specifies two input parameters, both of which have default values. The optional DEFAULT keyword is specified for the first parameter, omitted for the second parameter:
CREATE FUNCTION RandomLetter(IN firstlet CHAR DEFAULT 'A',IN lastlet CHAR 'Z')
-- SQL program code
The available keywords are as follows:
FOR className Specifies the name of the class in which to create the method. If the class does not exist, it will be created. You can also specify a class name by qualifying the function name. The class name specified in the FOR clause overrides a class name specified by qualifying the function name.
FINAL Specifies that subclasses cannot override the method. By default, methods are not final. The FINAL keyword is inherited by subclasses.
PRIVATE Specifies that the method can only be invoked by other methods of its own class or subclasses. By default, a method is public, and can be invoked without restriction. This restriction is inherited by subclasses.
PROCEDURE Specifies that the method is projected as an SQL stored procedure. Stored procedures are inherited by subclasses. Because CREATE FUNCTION always projects an SQL stored procedure, this keyword is optional. This keyword can be abbreviated as PROC.
RETURNS datatype Specifies the data type of the value returned by a call to the method. If RETURNS is omitted, the method cannot return a value. This specification is inherited by subclasses, and can be modified by subclasses. This datatype can specify type parameters such as MINVAL, MAXVAL, and SCALE. For example RETURNS DECIMAL(19,4). Note that when returning a value, Caché ignores the length of datatype; for example, RETURNS VARCHAR(32) can receive a string of any length that is returned by a call to the method.
SELECTMODE mode Only used when LANGUAGE is SQL (the default). When specified, Caché adds an #SQLCOMPILE SELECT=mode statement to the corresponding class method, thus generating the SQL statements defined in the method with the specified SELECTMODE. The possible mode values are LOGICAL, ODBC, RUNTIME, and DISPLAY. The default is LOGICAL.
The SELECTMODE clause is used for SELECT query operations and for INSERT and UPDATE operations. It specifies the compile-time select mode. The value that you specify for SELECTMODE is added at the beginning of the Caché ObjectScript class method code as: #SQLCompile Select=mode. For further details, see #SQLCompile Select in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using Caché ObjectScript.
When the SQL code is executed, the %SQL.Statement class %SelectMode property specifies the execution-time select mode, as described in Using Dynamic SQL chapter of Using Caché SQL. For further details on SelectMode options, refer to Data Display Options in the “Caché SQL Basics” chapter of Using Caché SQL.
A keyword clause specifying the language you are using for code_body. Permitted clauses are LANGUAGE OBJECTSCRIPT (for Caché ObjectScript) or LANGUAGE SQL. If the LANGUAGE clause is omitted, SQL is the default.
The program code for the method to be created. You specify this code in either SQL or Caché ObjectScript. The language used must match the LANGUAGE clause. However, code specified in Caché ObjectScript can contain embedded SQL.
Caché uses the code you supply to generate the actual code of the method. If the code you specify is SQL, Caché provides additional lines of code when generating the method that embed the SQL in a Caché ObjectScript “wrapper,” provide a procedure context handler (if necessary), and handle return values. The following is an example of this Caché-generated wrapper code:
   &sql( SELECT col FROM tbl )
   QUIT $GET(title)
If the code you specify is OBJECTSCRIPT, the Caché ObjectScript code must be enclosed in curly braces. All code lines must be indented from column 1, except for labels and macro preprocessor directives. A label or macro directive must be prefaced by a colon (:) in column 1.
For Caché ObjectScript code, you must explicitly define the “wrapper” (which NEWs variables, and uses QUIT to exit and (optionally) to return a value upon completion).
When a stored procedure is called, an object of the class %Library.SQLProcContext is instantiated in the %sqlcontext variable. This procedure context handler is used to pass the procedure context back and forth between the procedure and its caller (for example, the ODBC server).
%sqlcontext consists of several properties, including an Error object, the SQLCODE error status, the SQL row count, and an error message. The following example shows the values used to set several of these:
  SET %sqlcontext.%SQLCODE=SQLCODE
  SET %sqlcontext.%Message=%msg
The values of SQLCODE and %ROWCOUNT are automatically set by the execution of an SQL statement. The %sqlcontext object is reset before each execution.
Alternatively, an error context can be established by instantiating a %SYSTEM.Error object and setting it as %sqlcontext.Error.
An SQLCODE -361 error is generated if the specified function already exists.
Executing a User-defined Function
You can execute a function in a SELECT statement, such as the following:
SELECT StudentName,StudentAge,Sample.HalfAge() AS HalfTheAge
FROM Sample.MyStudents
An SQLCODE -359 error is generated if the function does not exist. If you do not specify a schema, Caché looks for the function in the default schema.
An SQLCODE -149 error is generated if the execution of the function results in a error. The type of error is described in %msg.
The following example creates the RandomLetter() function (method) stored as a procedure that generates a random capital letter. You can then invoke this function in a SELECT statement. A DROP FUNCTION is provided to delete the RandomLetter() function.
 SET x=$RANDOM(90)
 IF x<65 {GOTO Top}
SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetter()
The following example creates a function that invokes Caché ObjectScript code, which in turn contains embedded SQL:
  &sql(CREATE FUNCTION TraineeName(
   SSN VARCHAR(11), 
   OUT Name VARCHAR(50) )
    FOR Sample.MyStudents
        SET Name=""
        &sql(SELECT Name INTO :Name FROM Sample.Employee 
             WHERE SSN = :SSN)
        IF $GET(%sqlcontext)'= "" {
           SET %sqlcontext.%SQLCODE=SQLCODE
           SET %sqlcontext.%ROWCOUNT=%ROWCOUNT }
           QUIT Name
    IF SQLCODE=0 { WRITE !,"Created a function" QUIT}
    ELSE { WRITE !,"CREATE FUNCTION error: ",SQLCODE," ",%msg,!
      &sql(DROP FUNCTION TraineeName FROM Sample.MyStudents) }
      IF SQLCODE=0 { WRITE !,"Dropped a function" QUIT}
      ELSE { WRITE !,"Drop error: ",SQLCODE }
It uses the %sqlcontext object, and sets its %SQLCODE and %ROWCOUNT properties using the corresponding SQL variables. Note the curly braces enclosing the Caché ObjectScript code following the function’s LANGUAGE OBJECTSCRIPT keyword. Within the Caché ObjectScript code there is Embedded SQL code, marked by &sql and enclosed in parentheses.
See Also