16.20 - Example: Creating an SQL Procedure with a Multistatement Request - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

The following procedure executes the statements in the multistatement request in parallel:

    CREATE PROCEDURE salesadjust(IN item    INTEGER, 
                                 IN numsold INTEGER)
      BEGIN
        DECLARE price DECIMAL(8,2);
        SELECT item_price INTO price 
        FROM pricetbl 
        WHERE item = pricetbl.item_no;
        BEGIN REQUEST
          UPDATE sales_summary
          SET total_sales = total_sales + price * numsold
          WHERE item = sales_summary.item_no
          ;UPDATE inventory
          SET item_no = item_no - numsold
          WHERE item = inventory.item_no;
        END REQUEST;
      END;

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

  1. The price of an item is retrieved from pricetbl.
  2. The total sales amount is computed by multiplying the total price by the number of items sold.
  3. Two update requests are dispatched to the AMPs in parallel:

    The first updates a total_sales summary table that keeps track of the cash made on all items sold.

    The second adjusts inventory to reflect the remaining quantity stocked for that particular item.