Example: Creating a Procedure that Consumes a Queue Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The following procedure consumes a shopping cart queue table row that returns the three variables sp_ordernum, sp_product, and sp_quantity:

    CREATE PROCEDURE consumecart(OUT sp_ordernum CHARACTER(15), 
                        OUT sp_product  CHARACTER(30), 
                                 OUT sp_quantity INTEGER) 
    BEGIN 
    SELECT AND CONSUME TOP 1 ordernum, product, quantity INTO 
     :sp_ordernum, :sp_product, :sp_quantity FROM shoppingcart;
    END;

The system retrieves the queue table row at the top of the FIFO queue from a single AMP and deletes it from the shoppingcart table.

The row was consumed first because it had been in the queue for the longest duration as determined by its QITS value being the oldest of all rows in the queue. The system builds the response row as specified in the expression list and returns it to the requestor in variables.