![]() Previous |
![]() Next |
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:
Parameters
The name of the SQL stored procedure.
The name of a variable in the RDBMS.
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