InterSystems SQL Reference
CREATE FUNCTION
|
|
CREATE FUNCTIONname (parameter_list ) [characteristics ] [ LANGUAGE SQL ] BEGINcode_body ; END CREATE FUNCTIONname (parameter_list ) [characteristics ] LANGUAGE OBJECTSCRIPT {code_body }
name | The name of the function to be created as a method in a stored procedure class. The name must be a valid identifier. A procedure name can be qualified (schema.procname), or unqualified (procname). An unqualified procedure name takes the system-wide default schema name. The name must be followed by parentheses, even if no parameters are specified. |
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. |
LANGUAGE OBJECTSCRIPT LANGUAGE SQL |
Optional The programming language used for code_body. Specify LANGUAGE OBJECTSCRIPT (for ObjectScript) or LANGUAGE SQL. If the LANGUAGE clause is omitted, SQL is the default. |
code_body | 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 (;). ObjectScript program code is enclosed in curly braces. ObjectScript code lines must be indented. |
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, InterSystems IRIS 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, InterSystems IRIS 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. |
&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 }