Stored Procedural Language (SPL) Statements Teradata

Within the create request of a procedure, called the procedure body, there will normally be Data Manipulation Language (DML) and SPL. Using Data Definition Language (DDL) and Data Control Language (DCL) is not currently permitted within a procedure.

The DML has already been covered in this book. This chapter primarily concentrates on the SPL. The only exception will be a discussion on SELECT, UPDATE and DELETE regarding cursors and the new SELECT-INTO for setting initial values of variables in a procedure.

The <procedure-body> may contain any or all of the following SPL commands:

  • BEGIN / END — Defines scope and functionality of the procedure body
  • CALL – Executes a procedure from a procedure
  • DECLARE – Optional, establishes local variables or handler routines for use within the procedure
  • FOR / END FOR – Allows for a cursor to process commands for each row fetched from Teradata
  • IF / END IF - Provides for a conditional test of a variable
  • ITERATE - Skips rest of the statements after the iterate statement and continues with the iteration of the logic
  • LEAVE – Allows for an exit from a loop
  • LOOP / END LOOP – Defines the processing logic to repeat unconditionally
  • PRINT – Aids in testing and debugging of a new procedure
  • SET – Assigns a value to a variable or parameter
  • WHILE / END WHILE – Establishes a conditional test prior to doing a logic loop

These commands have been listed above in alphabetic sequence. They are normally written in a sequence to accomplish a specific task. Each of these is discussed below in more detail with examples to demonstrate their functionality.

The syntax of the <procedure-body> must contain a BEGIN and END combination statement formatted as:

BEGIN / END Statements

The BEGIN and END specifications are both required and they define the body of the procedure. All other SPL commands will be contained within the boundaries delineated by these two statements. Again, they are both required and because of the dependency on each other, they are referred to as a compound statement

The following is the syntax of the BEGIN and END commands:

[ <label-name>: ]BEGIN END [ <label-name> ] ;

Optionally, a BEGIN may specify a label name to identify the SPL section within the procedure. When a label name is desired, it connects to the BEGIN via a colon (:). The END will only use a label name if one is defined on the BEGIN. A label name is optional whenever there is a single section of SPL. When multiple sections (more than one BEGIN and END statements) are used, the interior sections must have a label name.

The next table is used in the next examples:
BEGIN / END Statements

BEGIN / END Statements

The following demonstrates a simple procedure with a single section used to INSERT a row with all null values into the Customer table:


After the execution of the above procedure, the next SELECT returns:

6 Rows Returnednext SELECT returns

Notice that a separate SELECT (outside the procedure) is needed to see the row of null values instead of returning them from the procedure. The only output to the client from the execution of this procedure is a resultant status code. It indicates either a successful completion or a failure in the procedure.
More discussion will appear in this chapter to explain why this is the case. For now, suffice it to say that a stored procedure can't return rows to the client as noted in Figure.

The following demonstrates a technique to nesting one section within another using a label name on the inner section:

In both of these examples, the indentation is optional and used by us to assist in understanding how the coding works. The first section does not use the optional label for the BEGIN and END. It contains an INSERT into the Customer table. Then, another BEGIN and an END called SecondSection is used. This SPL section contains a DELETE from the order table. The END for SecondSection comes before the END for the first BEGIN and specifies the label name SecondSection as defined in the BEGIN. It is nested within the first BEGIN and last END as compound statements.

The normal flow is from top to bottom. So, the customer INSERT is performed first, then the order DELETE is performed. Although this same processing occurs without a nested BEGIN / END for SecondSection, it introduces this technique for use with other commands covered below and the ability to allow repeated processing of one or more sections. It may not always be desirable to repeat all the commands, only specific ones. The procedure must be written to manage all desired processing.

Like all CREATE statements, the procedure name may be qualified to request its creation in a particular database. The parameter list is optional as in a macro. However, the parentheses are not optional and must be coded, even without parameters. When used, the parameters are normally included to make the procedure more flexible and provide communications with the outside world regarding each execution. The procedure body contains SPL to manage the execution of the SQL. The setup and use of parameters and SPL is covered progressively in this chapter to facilitatelearning how to use SPL.

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

Teradata Topics