DML Statements Teradata

These DML statements are allowed in a stored procedure:

  • INSERT (using UPI or USI)
  • UPDATE (using UPI or USI)
  • DELETE (using UPI or USI)
  • SELECT-INTO (using UPI or USI)
  • DELETE using cursor (for multiple rows in FOR statement)
  • UPDATE using cursor (for multiple rows in FOR statement)
  • SELECT using cursor (for multiple rows in FOR statement)
  • Nested CALL statements

Most of the DML is written the same as when used for interactive processing. However, the cursor processing in a FOR was demonstrated using the FOR statement. The CALL statement is also demonstrated previously in this chapter.

Most of the SQL is pretty standard. However, the SELECT-INTO is a major departure from normal SQL. It is used to select a single row (UPI or USI) from a table and assign the value(s) in the specified columns to the respective target variables.

The syntax for the SELECT-INTO is:

The number of columns or expressions in the SELECT list must match the number of variables or parameters in the assignment target list. The assignment target list may only contain parameters that are defined as OUT or INOUT parameters, and local variables.

The following modifies the earlier procedure named Paws_Proc to eliminate the IN parameter and use the SELECT-INTO to retrieve a single value from the table called My_LoopCtl and stores it in Stopper that is used to determine the number of times for the loop to execute:

In order to use this technique, My_LoopCtl table needs to contain one row only. That row must have a column called Cnt_Col. Then, prior to calling the procedure, the row must be updated in the My_LoopCtl table to change Cnt_Col with a value to control the desired number of times the loop is to be performed. Now, when it is retrieved by the SELECT-INTO, the loop works that many times.

Potential Teradata error codes when using SELECT-INTO:

  • 7627 is reported if SELECT attempts to return more than one row
  • 7632 is reported if SELECT does not return any rows

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics