15.10 - compound statement - Teradata Database

BEGIN-END statement enclosing a set of declarations and statements.

You can specify local variable declarations, cursor declarations, condition handler declarations and SQL and control statements within a compound statement.

Nesting of compound statements is allowed.

Example: Converting a CLOB Containing XML Data into Rows for Insertion into a Table

The following example creates a procedure that converts a CLOB value containing XML sales data into a set of rows that are inserted into a sales table.

First the table definitions:

    CREATE TABLE sales (
      partnum   INTEGER, 
      qtysold   INTEGER, 
      storecode INTEGER, 
      salesdate DATE)
    PRIMARY INDEX (partnum, qtysold, storecode, salesdate);
    CREATE TABLE saleslog (
      storecode INTEGER, 
      salesdate DATE, 
      sales     CLOB, 
      logdate   DATE, 
      logtime   TIME)
    PRIMARY INDEX (storecode, salesdate, logdate, logtime);

The following SQL code defines the procedure:

    CREATE PROCEDURE stores_sales_procedure (storecode INTEGER, 
                                             salesdate DATE, 
                                             salesclob CLOB)
      BEGIN
        INSERT INTO saleslog (:storecode, :salesdate, :salesclob,
                              CURRENT_DATE, CURRENT_TIME);
        INSERT INTO sales 
          SELECT * FROM TABLE (xmlparser(:salesclob));
      END;

The stages in the process followed by this procedure are as follows:

1. The sales CLOB is inserted into a log table named saleslog.
2. The CLOB is passed by means of the variable salesclob to a user-defined table function named xmlparser (see CREATE FUNCTION (Table Form)).
3. The system invokes table function xmlparser on every AMP.
4. The table function invokes fnc_loblocal (the definitions for xmlparser and fnc_loblocal are not shown for this example) to detect whether the CLOB is located on the same AMP on which the function is executing.
5. The function that has local access to the CLOB strips out the XML sales data and returns standard relational table rows to be inserted into sales.