Purpose
Selects data from the row with the oldest insertion timestamp in the specified queue table, deletes the row from the queue table, and assigns the values in that row to host variables in an embedded SQL application or to local variables or parameters in stored procedures.
Invocation
Executable.
Stored procedures and embedded SQL.
Syntax: Stored Procedures Only
{ SELECT | SET } AND CONSUME TOP 1 select_list INTO into_spec [,...] FROM queue_table_name
- into_spec
-
[:] { local_variable_name | parameter_name }
Syntax: Embedded SQL Only
{ SELECT | SET } AND CONSUME TOP 1 select_list INTO into_spec [,...] FROM queue_table_name
- into_spec
-
[:] host_variable_name [ [INDICATOR] :host_indicator_name ]
- select_list
- An ASTERISK character (*) or a comma-separated list of valid SQL expressions.
- local_variable_name
- The name of the local variable declared in the stored procedure into which the SELECTed data is to be placed.
- queue_table_name
- The name of a queue table that was created with the QUEUE option in the CREATE TABLE statement.
- parameter_name
- The name of the stored procedure parameter into which the SELECTed data is to be placed.
- host_variable_name
- The name of the host variable into which the selected data is to be placed.
- host_indicator_name
- The name of the host indicator variable.
ANSI Compliance
SELECT AND CONSUME … INTO is a Teradata extension to the ANSI/ISO SQL:2011 standard.
Authorization
To execute a SELECT AND CONSUME … INTO from a queue table, you must have the SELECT and DELETE privileges on that table.
Attributes of a Queue Table
A queue table is similar to an ordinary base table, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.
The first column of a queue table contains Queue Insertion TimeStamp (QITS) values. The CREATE TABLE statement must define the first column with the following data type and attributes:
TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
The QITS value of a row indicates the time the row was inserted into the queue table, unless a different, user-supplied value is inserted.
Using a Colon Character in Embedded SQL
In embedded SQL, blanks before and after a colon character are optional; use of the colon character before host_variable_name is optional; a colon character must precede a host_indicator_name.
Rules for Embedded SQL
The same rules that apply to SELECT … INTO apply to SELECT AND CONSUME … INTO.
Related Topics
- queue_table_name, see “CREATE TABLE (Queue Table Form)” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- Usage notes, information on transaction processing, locks, and restrictions, see “SELECT AND CONSUME” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
- The rules that apply to SELECT … INTO and SELECT AND CONSUME … INTO, see Rules for Embedded SQL.