Previous
Previous
 
Next
Next


SQL PROCEDURE

The SQL PROCEDURE command executes procedures stored in the RDBMS.


Note:

You can also create SQL stored procedures using the OLAP DML. See:

Syntax

SQL PROCEDURE procedure-name (parameters)

where parameters is one or more of the following, separated by commas:

sql-parameter
:dml-parameter

Parameters

procedure-name

The name of the SQL stored procedure.

sql-parameter

The name of a variable in the RDBMS.

:dml-parameter

An OLAP DML expressions such as a OLAP DML variable. See "Using OLAP DML Expressions in OLAP DML SQL Statements" for more information on using OLAP DML expressions in OLAP DML SQL statements.

Usage Notes

Creating SQL Procedures using the OLAP DML

To create a stored procedure using the OLAP DML, issue an OLAP DML SQL statement with a SQL CREATE PROCEDURE statement as its argument. The syntax for coding CREATE PROCEDURE as an argument within an OLAP DML SQL statement is slightly different than the syntax for coding CREATE PROCEDURE in SQL proper. When coded as an arguments to an OLAP DML statements, use a tilde (~) instead of a semicolon as a terminator, and two colons instead of one in an assignment statement. See Example: Creating a Stored Procedure.

Restrictions When Calling SQL Procedures using the OLAP DML

A stored procedure called using an OLAP DML SQL PROCEDURE statement cannot contain output variables or transactions.

Examples

Creating a Stored Procedure

The following example shows the syntax for creating a procedure named new_products.

SQL CREATE OR REPLACE PROCEDURE new_products -
   (id CHAR, name CHAR, cost NUMBER) AS -
      price NUMBER~ -
   BEGIN -
      price ::= cost * 2.5~ -
      INSERT INTO products -
         VALUES(id, name, price)~ -
   END~

Executing a Stored Procedure

The following FOR loop executes a SQL stored procedure named new_products and inserts data stored in dimensions and variables into a relational table. In this example, prod is an Oracle OLAP dimension, and labels.p and cost.p are variables dimensioned by prod.

FOR prod
   DO
      SQL PROCEDURE new_products(:prod, :labels.p, :cost.p)
      IF SQLCODE NE 0
         THEN BREAK
   DOEND