|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Working with Relational Tables, 6 of 7
Support is provided for stored procedures and triggers. They cannot contain
SELECT statements. an analytic workspace stored procedure cannot contain output variables or transactions, nor can it call another procedure. You can create a stored procedure or trigger in an OLAP DML program. Example 10-29 shows the OLAP DML syntax for creating a procedure named
OLAP DML syntax differs slightly from the standard SQL syntax. A tilde (
~) is required instead of a semicolon as a terminator, and two colons (
::) are required instead of one in an assignment statement.
SQL CREATE PROCEDURE new_products - (aw_id CHAR, aw_name CHAR, aw_cost NUMBER) IS - price number~ - BEGIN - aw_price ::= aw_cost * 2.5~ - INSERT INTO products - VALUES(aw_id, aw_name, aw_price)~ - END~
You use a
PROCEDURE statement to run a stored procedure, using the following syntax.
The arguments can be literal text or input host variables. When you use input host variables, be sure to use a colon before the variable name. Also be sure to use the same number of arguments with appropriate data types for the parameters defined in the procedure. You can use literal arguments when executing a stored procedure as shown in Example 10-30 which uses the
new_products procedure to insert a single row in the
products table, or you can specify analytic workspace objects as arguments as shown in Example 10-31 which runs the same procedure but inserts data stored in analytic workspace dimensions and variables into the
products table. The
add-prods program in Example 10-31, "Using Workspace Objects as Parameters for a Stored Procedure" illustrates using a
FOR loop to loop over all of the values in status. To call
add_prods, you issue a command like the following to set the status of
prod to include only the values you wish to update.