Oracle9i OLAP Developer's Guide to the OLAP DML Release 2 (9.2) Part Number A95298-01 |
|
Developing Programs, 7 of 12
Ordinarily, the lines of a program are executed sequentially, that is, in linear fashion. However, a well-designed program controls the flow of execution by using commands that redirect the path of execution when appropriate.
You can use the following control structures to modify the sequence of command execution.
When you use control structures to branch to a particular location, you must provide a label for the location in order to identify it clearly. When creating a label, follow these guidelines:
.
), or underscore (_
).:
).While GOTO
makes it easy to branch within a program, frequent use of it can obscure the logic of your program, making it difficult to follow its flow. This is particularly true when you have a complex program with several labels and GOTO
commands that skip over large portions of code.
To keep the logic of your programs clear, minimize your use of GOTO
.
Sometimes a GOTO
command is the best programming technique, but often there are better alternatives. For example:
GOTO
commands in an IF
command, you can often place your alternative sets of commands between DO
and DOEND
commands within the IF
command itself.IF
command to choose between two different programs, or use the SWITCH
command to choose among many different programs.The FOR
command executes the commands in the loop for each value in the current status of the dimension. You must limit the dimension to the desired values before executing the FOR
command. For example, you can produce a series of output lines that show the price for each product.
LIMIT month TO FIRST 1 LIMIT product TO ALL FOR product SHOW JOINCHARS('Price for ' product ': $' price)
Each output line has the following format.
Price for TENTS: $165.50
When your data is multidimensional, you can specify more than one dimension in a FOR
command to control the order of processing. For example, you can use the following command to control the order in which dimension values of the units
data are processed.
FOR month district product units = ...
When this assignment statement is executed, the month
dimension varies the slowest, the district
dimension varies the next slowest, and the product
dimension varies the fastest. Thus, a loop is performed over all products for the first district before doing the next district, and over all districts for the first month before doing the next month.
Within the FOR
loop, each specified dimension is temporarily limited to a single value while it executes the commands in the loop. You can therefore work with specific combinations of dimension values within the loop.
If actual figures for unit sales are stored in a variable called units
and projected figures for unit sales are stored in a variable called units.plan
, then the code in your loop can compare these figures for the same combination of dimension values.
LIMIT month TO FIRST 1 LIMIT product TO ALL LIMIT district TO ALL FOR district product DO IF (units.plan - units)/units.plan GT .1 THEN SHOW JOINCHARS(- 'Unit sales for ' product ' in ' - district ' are not within 10% of plan.') DOEND
These lines of code are processed as described below.
FOR
loop is entered.FOR
loop, the actual figure is tested against the planned figure. If the unit sales figure for TENTS
in BOSTON
is more than 10 percent below the planned figure, then the following message is sent to the current outfile.
Unit sales for TENTS in BOSTON are not within 10% of plan.
FOR
loop is complete for the first district.Your program might try to set or refine the status of the product
dimension to include only the products for which unit sales are greater than 500. If no products have unit sales of more than 500, then you can use the IFNONE
keyword to specify that execution branch to the novals
label.
LIMIT product KEEP units GT 500 IFNONE novals
In the commands following the novals
label, you can handle the special situation in which no products have units sales greater than 500.
As an alternative to branching to an IFNONE
label, you can also handle null status for a dimension with the OKNULLSTATUS
option. If you set OKNULLSTATUS
to YES
, then you will be allowed to set the status of a dimension to null. You can then check for null status and execute appropriate commands with an IF
command, or you can handle null status as one of the cases in a SWITCH
command.
OKNULLSTATUS = YES LIMIT month TO sales GT salesnum IF STATLEN(month) LT 1 THEN GOTO showerr
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|