InterSystems SQL Reference
CREATE FUNCTION
|
|
Creates a function as a method in a class.
Synopsis
CREATE FUNCTION name(parameter_list) [characteristics]
[ LANGUAGE SQL ]
BEGIN
code_body ;
END
CREATE FUNCTION name(parameter_list) [characteristics]
LANGUAGE OBJECTSCRIPT
{ code_body }
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.
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, InterSystems IRIS 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.
InterSystems 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):
-
An optional keyword specifying whether the parameter mode is IN (input value), OUT (output value), or INOUT (modify value). If omitted, the default parameter mode is IN.
-
The parameter name. Parameter names are case-sensitive.
-
-
Optional: A default value for the parameter. You can specify the DEFAULT keyword followed by a default value; the DEFAULT keyword is optional. If no default is specified, the assumed default is NULL.
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')
BEGIN
-- SQL program code
END
The available keywords are as follows:
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 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 ObjectScript.
-
In a
SELECT query, the SELECTMODE specifies the mode in which data is returned. If the
mode value is LOGICAL, then logical (internal storage) values are returned. For example, dates are returned in $HOROLOG format. If the
mode value is ODBC, logical-to-ODBC conversion is applied, and ODBC format values are returned. If the
mode value is DISPLAY, logical-to-display conversion is applied, and display format values are returned. If the
mode value is RUNTIME, the display mode can be set (to LOGICAL, ODBC, or DISPLAY) at execution time.
-
In an
INSERT or
UPDATE operation, the SELECTMODE RUNTIME option supports automatic conversion of input data values from a display format (DISPLAY or ODBC) to logical storage format. This compiled display-to-logical data conversion code is applied only if the select mode setting when the SQL code is executed is LOGICAL (which is the default for all InterSystems SQL execution interfaces).
A keyword clause specifying the language you are using for
code_body. Permitted clauses are LANGUAGE OBJECTSCRIPT (for 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 ObjectScript. The language used must match the LANGUAGE clause. However, code specified in ObjectScript can contain embedded SQL.
InterSystems IRIS uses the code you supply to generate the actual code of the method. If the code you specify is SQL, InterSystems IRIS provides additional lines of code when generating the method that embed the SQL in an ObjectScript “wrapper,” provide a procedure context handler (if necessary), and handle return values. The following is an example of this InterSystems IRIS-generated wrapper code:
NEW SQLCODE,%ROWID,%ROWCOUNT,title
&sql( SELECT col FROM tbl )
QUIT $GET(title)
If the code you specify is OBJECTSCRIPT, the 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 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.%ROWCOUNT=%ROWCOUNT
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.
You can execute a function in a
SELECT statement, such as the following:
SELECT StudentName,StudentAge,SQLUser.HalfAge() AS HalfTheAge
FROM SQLUser.MyStudents
An SQLCODE -359 error is generated if the function does not exist. If you do not specify a schema, InterSystems IRIS 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.
CREATE FUNCTION RandomLetter()
RETURNS INTEGER
PROCEDURE
LANGUAGE OBJECTSCRIPT
{
:Top
SET x=$RANDOM(90)
IF x<65 {GOTO Top}
ELSE {QUIT $CHAR(x)}
}
SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetter()
DROP FUNCTION RandomLetter
The following example creates a function that invokes ObjectScript code, which in turn contains embedded SQL:
&sql(CREATE FUNCTION TraineeName(
SSN VARCHAR(11),
OUT Name VARCHAR(50) )
PROCEDURE
RETURNS VARCHAR(30)
FOR SQLUser.MyStudents
LANGUAGE OBJECTSCRIPT
{
NEW SQLCODE,%ROWCOUNT
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 SQLUser.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 ObjectScript code following the function’s LANGUAGE OBJECTSCRIPT keyword. Within the ObjectScript code there is
Embedded SQL code, marked by
&sql and enclosed in parentheses.