FOR | Teradata Vantage - FOR - Advanced SQL Engine - Teradata Database

Purpose

Executes a statement for each row fetched from a table.

Invocation

Executable.

Stored procedures only.

Syntax

[ label_name : ] FOR for_loop_variable
  AS [ cursor_name CURSOR FOR ] cursor_specification
  DO statement [...]
  END FOR [ label_name ] ;

cursor_specification

SELECT cursor_spec [,...]
  FROM {
    table_name [,...] |

    table_name {
      INNER |
      { LEFT | RIGHT | FULL } OUTER
    } JOIN table_name ON condition
  }

statement

{ SQL_statement |
  compound_statement |
  assignment_statement |
  condition_statement |
  [ label_name : ] iteration_statement [ label_name ] |
  ITERATE label_name |
  LEAVE label_name
}

compound_statement

[ label_name : ] BEGIN
  [ local_declaration ] [...]
  [ cursor_declaration ] [...]
  [ condition_handler ] [...]
  [ statement; ] [...]
END [ label_name ] ;

assignment_statement

SET assignment_target = assignment_source

condition_statement

{ CASE_statement | IF_statement }

iteration_statement

{ WHILE conditional_expression
    DO statement; [...] |

  LOOP
    statement; [...]
  END LOOP |

  FOR for_loop_variable AS [ cursor_name CURSOR FOR ] cursor_specification
    DO statement; [...]
  END FOR |

  REPEAT
    statement; [...]
    UNTIL conditional_expression
  END REPEAT
}

cursor_spec

{ column_name [ [AS] alias_name ] |

  expression [AS] alias_name |

  *
}

local_declaration

DECLARE {
  variable_name [,...] data_type [ DEFAULT { literal | NULL } ] |
  condition_name CONDITION [ FOR SQLSTATE [VALUE] sqlstate_cod ]
} ;

cursor_declaration

DECLARE cursor_name [ [NO] SCROLL ] CURSOR
  [ WITHOUT RETURN |
    WITH RETURN [ONLY] [ TO { CALLER | CLIENT } ]
  ]
  [ FOR { cursor_specification [ FOR { READ ONLY | UPDATE } ] |
          statement_name
        } 
  ] ;

condition_handler

DECLARE { CONTINUE | EXIT } HANDLER FOR
  { 
    { SQLSTATE [ VALUE ] sqlstate_code | condition_name } [,...] |

    { SQLEXCEPTION | SQLWARNING | NOT FOUND } [,...]

  } handler_action_statement ;

IF_statement

See IF.

CASE_statement

See CASE.

label_name

An optional label for the FOR statement.

If an ending-label is specified, you must specify a beginning-label that is equivalent to the ending-label. The beginning-label must be terminated by a colon character (:).

The label name of the BEGIN … END compound statement cannot be reused in an iteration statement. One label name cannot be reused within one group of nested FOR statements, but can be reused for different non-nesting iteration statements.

for_loop_variable

The name of the loop.

cursor_name

The name of the cursor.

Used for updatable cursors as the object of the WHERE CURRENT OF clause.

cursor_specification

A single SELECT statement used as the cursor.

In read-only cursors, the single SELECT statement can contain one SELECT or multiple SELECTs that use set operators like UNION, INTERSECT or MINUS.

Updatable cursors do not support the set operators.

statement

One or more DML, DDL, DCL statements, including dynamic SQL statements, or control statements, including BEGIN … END compound statements.

ANSI Compliance

FOR is ANSI/ISO SQL:2011-compliant.

Authorization

DECLARE CURSOR and FOR Statements

FOR statements contain DECLARE CURSOR statements.

LEAVE and ITERATE

You can execute LEAVE and ITERATE statements within a FOR block.

Using a Correlation Name for a Cursor Specification

You can define aliases for the columns and expressions in a cursor using the standard object AS correlation_name syntax. You must qualify any aliased object with the for_loop_variable name if you reference it within the loop.

You cannot reference a non-aliased cursor expression within the loop.

Updatable and Read-Only Cursors

An updatable, or positioned, cursor is a cursor defined by the application for a query that can also used to update the results rows.

A cursor is updatable if there is at least one positioned DELETE or positioned UPDATE that references it inside the FOR loop.

Rules for SQL Statements Within a FOR Loop

• You can specify all DML statements, including CALL, positioned UPDATE and positioned DELETE.
• You can specify all control statements.
• Transaction statements are allowed only in read-only cursors. They cannot be specified in updatable cursors.
• Each local variable, parameter, column, correlation name, or status variable referenced in the SQL statement must have been previously declared.

Rules for FOR Cursors

• ABORT, COMMIT, and ROLLBACK statements are not permitted in an updatable cursor.

  An attempt to execute any of these statements returns a runtime error.

• The cursor specification must not return the warning code 3999.
• The cursor specification cannot contain a WITH…BY clause.
• If the cursor specification contains a UNION operator, the referenced correlation or column name must be the correlation or column names used in the first SQL SELECT statement.

Rules for FOR-Loop Variables

• FOR loop variable names must be unique if they are used in nested FOR iteration loops.
• FOR loop variable names can be the same as the cursor name and correlation names within a FOR iteration statement.
• If you use a FOR loop variable in an SQL statement other than a control statement within the iteration statement, you must prefix it with a colon character (:).
• Unqualified symbols in a FOR loop are assumed to be variable or parameter names.

Rules for FOR-Loop Correlation Names

• A correlation name must be unique in a FOR iteration statement; however, the same correlation name can be used both for nested and non-nested FOR iteration statements.
• A correlation name can be the same as the FOR loop variable and the names of cursors within a FOR iteration statement.
• Columns and correlation names must be qualified with a FOR loop variable when referenced in SQL statements, including control statement, within the iteration statement.
• If a column or correlation name is not qualified, then column and correlation name references are treated as either parameters or local variables.
• The scope of a FOR iteration statement correlation name is the body of the statement.

Rules for FOR-Loop Cursor Names

• A cursor name must be unique if used in the nested FOR iteration statements.
• A cursor name can be the same as the for-loop variable or the correlation or column names in a FOR statement.
• The scope of a cursor name is confined to the FOR statement in which it is defined. If FOR statements are nested, the cursor name associated with an outer FOR statement can be referenced in statements within inner FOR statement(s).

Example: FOR-Loop Insert

L1:
FOR CustCursor AS c_customer CURSOR FOR
  SELECT CustomerNumber AS Number
        ,CustomerName AS Name
        ,(Amount + 10000) a
    FROM customer
DO
  SET hCustNbr = CustCursor.Number;
  SET hCustName = CustCursor.Name;
  SET hAmount = CustCursor.a + CustCursor.a * 0.20;
  INSERT INTO Cust_temp VALUES (hCustNbr, hCustName);
END FOR L1;

Example: FOR-Loop Delete

FOR CustCursor AS c_customer CURSOR FOR
  SELECT CustomerNumber
        ,CustomerName
    FROM Customer
DO
  SET hCustNbr = CustCursor.CustomerNumber;
  SET hCustName = CustCursor.CustomerName;
  DELETE FROM Customer WHERE CURRENT OF c_customer;
END FOR;

Example: FOR-Loop Update

L1:
FOR CustCursor AS c_customer CURSOR FOR
   SELECT CustomerNumber AS Number
         ,CustomerName AS Name
         ,(Amount + 10000) a
     FROM Customer
DO
   SET hCustNbr = CustCursor.Number;
   SET hCustName = CustCursor.Name;
   SET hAmount = CustCursor.a + CustCursor.a * 0.20;
   IF hAmount > 50000 THEN
   hAmount = 500000;
END IF;
UPDATE customer
   SET amount = hAmount WHERE CURRENT OF c_customer;
   INSERT INTO Cust_temp VALUES (hCustNbr,
               hCustName);
END FOR;

Related Topics