Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Designing Programs, 7 of 13
When an OLAP DML program returns a value, it is called a user-defined function. This means you can use it in commands and expressions in the same way you use OLAP DML functions.
A user-defined function is a program that contains a RETURN command followed by an expression. The RETURN command returns a single value when the program terminates.
RETURN expression
When you create a user-defined function, define the program with a data type or dimension name, using the following syntax of the DEFINE command.
DEFINE programname PROGRAM [datatype|dimension]
The datatype argument specifies the data type of the value to be returned by the program when it is called as a function.
The dimension argument specifies the name of a dimension whose value the program returns when it is called as a function. The return value will be a single value of the dimension, not a position (integer). The dimension must be defined in the same analytic workspace as the program. The value that is returned by the program has the data type that is specified in the definition. If you specify a dimension name, then the program returns a value of that dimension.
The return expression in the program should match the data type that is specified in its definition. If the data type of the return value does not match the data type that is specified in its definition, then the value is converted to the data type in the definition.
If you do not specify a data type for the program, then the return value is converted to the data type that is required by the context from which the program was called.
For the complete syntax of the DEFINE PROGRAM command, see the entry for the command in OLAP DML Reference.
User-defined functions can accept arguments. A user-defined function returns only a single value. However, if you supply an argument to a user-defined function when you are using the function in a context that loops over a dimension (for example, in a REPORT command), then the function returns results with the same dimension as its argument.
You must declare the arguments using the ARGUMENT command within the program, and you must specify the arguments in parentheses following the name of the program. For more information about using arguments with programs, see "Passing Arguments" and the entry for the ARGUMENT command in OLAP DML Reference.
Suppose your analytic workspace contains a variable called UNITS.PLAN, which is dimensioned by the PRODUCT, DISTRICT, and MONTH dimensions. The variable holds integer data that indicates the number of product units that are expected to be sold.
Suppose also that you define a program named UNITS_GOALS_MET. This program is a user-defined function. It accepts three dimension-value arguments that specify a given cell of the UNITS.PLAN variable, and it accepts a fourth argument that specifies the number of units that were actually sold for that cell. The program returns a Boolean value to the calling program. It returns YES when the actual figure comes up to within 10 percent of the planned figure; it returns NO when the actual figure does not.
The definition of the UNITS_GOALS_MET program is listed below.
DEFINE UNITS_GOAL_MET PROGRAM BOOLEAN LD Tests whether actual units met the planned estimate "Program Initialization argument userprod text argument userdist text argument usermonth text argument userunits integer variable answer boolean trap on errorlabel push product district month "Program Body limit product to userprod limit district to userdist limit month to usermonth if (units.plan - userunits) / units.plan gt .10 then answer = no else answer = yes "Normal Exit pop product district month return answer "Abnormal Exit errorlabel: pop product district month signal errorname errortext END
To execute the UNITS_GOAL_MET program and store the return value in a variable called SUCCESS, you can use an assignment statement.
success = units_goal_met('TENTS' 'BOSTON' 'JUN96' 2000)
For more information, see the following table.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|