Teradata Stored Procedures Teradata

Compatibility: ANSI

In Version 2 Release 4, Teradata provides Stored Procedural Language (SPL) to create Stored Procedures. These procedures allow the combination of both SQL and SPL control statements to manage the delivery and execution of the SQL.

Teradata used always had a scripting language in its utilities, such as BTEQ, to manage loops, conditional tests and processing on the host computer. To differentiate SQL and the scripting language, most of the utilities use a dot (.) command and the Teradata Call Level Interface (CLI).

Later, more standard tools were introduced to access rows stored within Teradata. Instead of using the Call Level Interface directly, they call the Open Data Base Computing (ODBC) software for connectivity across a network.

The call structure for the ODBC is standard. Most database vendors provide an ODBC executable for their database to convert the ODBC calls into calls for the proprietary CLI. The advantage of this technique becomes apparent in that more tools are made available to all users to access any database that has an ODBC connection. However, because these tools are interactive and ODBC based, they do not normally have a scripting language.

One of the advantages to stored procedures is that they are stored and executed within the Teradata database and not as a separate utility on a different computer. Therefore, the SPL commands are available for execution from all tools and not just those with their own scripting language.

Stored procedures constitute a major departure from the way "normal" SQL works within Teradata, like a macro. The following chart lists the differences between macros and stored procedures.

differences between macros and stored procedures

Writing a stored procedure is more like writing a program than writing a macro. The macro contains only SQL and maybe dot commands that are only for use in BTEQ. Normally a SELECT results in rows being returned to the user. A stored procedure does not return rows to the user like a macro. Instead, the selected column or columns must be used within the procedure.

A stored procedure contains SQL to access data from within Teradata. However, it must contain SPL to control the execution of the SQL. A stored procedure is a bit like writing a script in the Support Environment front-end for MultiLoad, TPump or FastExport. The major difference with stored procedures is that the language is much more comprehensive, allows the SELECT and is ANSI standard.

Like a macro, stored procedures allow parameter values to be passed to it at execution time. Unlike a macro that allows only input values, a stored procedure also provides output capabilities. A stored procedure only returns output values to a user client as output parameters, not as rows.

We normally think of the client as being the requestor and Teradata being the server. When using stored procedures, another layer is added. When a procedure is called, it is a server to the user program. However, it normally interfaces with Teradata on the behalf of the client. The only way to do that is to become a client too. So, a procedure plays both roles. This is a 3-tiered environment where: Tier 1 is the user as a client, Tier 2 is the procedure as first a server and then as a client and Tier 3 is Teradata as a server. The procedure acts as a server/client in that it is first a server to the user and then a client to Teradata in order to retrieve one or more rows to service the user request.

The processing flow of a procedure is more like a program. It is a procedural set of commands, where SQL is a non-procedural language. As mentioned earlier, it does not return rows to the user. Therefore, the use of a SELECT, UPDATE or DELETE statement that processes multiple rows will need to be managed within the stored procedure. In other words, you cannot code a simple procedure with SELECT * FROM <table-name>. A procedure is not a macro and a macro is not a stored procedure.

The intent of this chapter is to show and explain the commands as well as the technique for a successful implementation of procedures. It provides a basis to understand and begin to use stored procedures. The syntax for SPL is very flexible and powerful. So, each command will be discussed individually with an example demonstrating it. The examples will increase in complexity and functionality as more SPL commands are introduced.

Although DDL is not allowed within a procedure, DDL must be used to implement a stored procedure. The DDL is used to CREATE a procedure. Additionally, you will need a recent release of Queryman and the ODBC in order for the CREATE PROCEDURE statement to be recognized and sent to Teradata.

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

Teradata Topics