Using Stored Procedures and Triggers

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 new_products.

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.

Example 10-29 Creating a Stored Procedure Named new_products

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~

Executing a stored procedure

You use a PROCEDURE statement to run a stored procedure, using the following syntax.

SQL PROCEDURE procedure-name (arg1, arg2, arg3, . . .)

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.

CALL add_prods('last 5')

Example 10-30 Providing Literal Values to a Stored Procedure

SQL PROCEDURE new_products -
   ('P81', '8mm Camcorder')

Example 10-31 Using Workspace Objects as Parameters for a Stored Procedure

DEFINE add_prods PROGRAM
LD Add new products using stored procedure new_products
PROGRAM
ARG newprods TEXT
PUSH aw_prod
LIMIT aw_prod TO &newprods

" Loop over aw_prod to insert the data
FOR aw_prod
   DO
      SQL PROCEDURE new_products(:aw_prod_id, :paw_rod_name)
   DOEND
POP aw_prod
END