Stored Procedures in Teradata

Stored Procedures

Introduction

Stored procedures are called Persistent Stored Modules in the ANSI SQL:2008 standard. They are written in SQL and consist of a set of control and condition handling statements that make SQL a computationally complete programming language.

These features provide a server-based procedural interface to Teradata Database for application programmers.

Teradata stored procedure facilities are a subset of and conform to the ANSI SQL:2008 standards for semantics.

Elements of Stored Procedures

The set of statements constituting the main tasks of the stored procedure is called the stored procedure body, which can consist of a single statement or a compound statement, or block.

A single statement stored procedure body can contain one control statement, such as LOOP or WHILE, or one SQL DDL, DML, or DCL statement, including dynamic SQL. Some statements are not allowed, including:

• Any declaration (local variable, cursor, condition, or condition handler) statement • A cursor statement (OPEN, FETCH, or CLOSE)

A compound statement stored procedure body consists of a BEGIN-END statement enclosing a set of declarations and statements, including:

• Local variable declarations

• Cursor declarations

• Condition declarations 

• Condition handler declaration statements 

• Control statements 

• SQL DML, DDL, and DCL statements supported by stored procedures, including dynamic SQL 

• Multistatement requests (including dynamic multistatement requests) delimited by the keywords BEGIN REQUEST and END REQUEST

Compound statements can also be nested.

For information about control statements, parameters, local variables, and labels, see SQL Stored Procedures and Embedded SQL.

Creating Stored Procedures

A stored procedure can be created from:

• BTEQ utility using the COMPILE command • CLIv2 applications, ODBC, JDBC, and Teradata SQL Assistant (formerly called Queryman) using the SQL CREATE PROCEDURE or REPLACE PROCEDURE statement.

The procedures are stored in the user database space as objects and are executed on the server.

For the syntax of data definition statements related to stored procedures, including CREATE PROCEDURE and REPLACE PROCEDURE, see SQL Data Definition Language. Note: The stored procedure definitions in the next examples are designed only to demonstrate the usage of the feature. They are not recommended for use.

Example: CREATE PROCEDURE

Assume you want to define a stored procedure NewProc to add new employees to the Employee table and retrieve the name of the department to which the employee belongs.

You can also report an error, in case the row that you are trying to insert already exists, and handle that error condition.

The CREATE PROCEDURE statement looks like this:

CREATE PROCEDURE NewProc (IN name CHAR(12), IN number INTEGER, IN dept INTEGER, OUT dname CHAR(10)) BEGIN INSERT INTO Employee (EmpName, EmpNo, DeptNo ) VALUES (name, number, dept); SELECT DeptName INTO dname FROM Department WHERE DeptNo = dept; END;

This stored procedure defines parameters that must be filled in each time it is called.

Modifying Stored Procedures

To modify a stored procedure definition, use the REPLACE PROCEDURE statement.

Example: REPLACE PROCEDURE

Assume you want to change the previous example to insert salary information to the Employee table for new employees.

The REPLACE PROCEDURE statement looks like this:

REPLACE PROCEDURE NewProc (IN name CHAR(12), IN number INTEGER, IN dept INTEGER, IN salary DECIMAL(10,2), OUT dname CHAR(10))

BEGIN INSERT INTO Employee (EmpName, EmpNo, DeptNo, Salary_Amount) VALUES (name, number, dept, salary); SELECT DeptName INTO dname FROM Department WHERE DeptNo = dept; END;

Executing Stored Procedures

If you have sufficient privileges, you can execute a stored procedure from any supporting client utility or interface using the SQL CALL statement. You can also execute a stored procedure from an external stored procedure written in C, C++, or Java.

You have to specify arguments for all the parameters contained in the stored procedure.

Here is an example of a CALL statement to execute the procedure created in “Example: CREATE PROCEDURE”:

CALL NewProc ('Jonathan', 1066, 34, dname);

For details on using CALL to execute stored procedures, see “CALL” in SQL Data Manipulation Language.

For details on executing stored procedures from an external stored procedure, see SQL External Routine Programming.

Output From Stored Procedures

Stored procedures can return output values in the INOUT or OUT arguments of the CALL statement.

Stored procedures can also return result sets, the results of SELECT statements that are executed when the stored procedure opens result set cursors. To return result sets, the CREATE PROCEDURE or REPLACE PROCEDURE statement for the stored procedure must specify the DYNAMIC RESULT SET clause.

For details on how to write a stored procedure that returns result sets, see SQL Stored Procedures and Embedded SQL.

Recompiling Stored Procedures

The ALTER PROCEDURE statement enables recompilation of stored procedures without having to execute SHOW PROCEDURE and REPLACE PROCEDURE statements.

This statement provides the following benefits:

• Stored procedures created in earlier releases of Teradata Database can be recompiled to derive the benefits of new features and performance improvements.

• Recompilation is also useful for cross-platform archive and restoration of stored procedures

• ALTER PROCEDURE allows changes in the following compile-time attributes of a stored procedure: • SPL option • Warnings option

Deleting, Renaming, and Retrieving Information About a Stored Procedure

For more information, see SQL Data Definition Language.

Archiving Procedures

Stored procedures are archived and restored as part of a database archive and restoration. Individual stored procedures can be archived or restored using the ARCHIVE or RESTORE statements of the ARC utility.

Related Topics

IF you want to … THEN use the following statement …

delete a stored procedure from a database DROP PROCEDURE

rename a stored procedure RENAME PROCEDURE

get information about the parameters specified in a stored procedure and their attributes

HELP PROCEDURE

get the data definition statement most recently used to create, replace, or modify a stored procedure

SHOW PROCEDURE

stored procedure control and condition handling statements

SQL Stored Procedures and Embedded SQL.

invoking stored procedures the CALL statement in SQL Data Manipulation Language.

creating, replacing, dropping, or renaming stored procedures

SQL Data Definition Language.

controlling and tracking privileges for stored procedures

• SQL Data Control Language. • Database Administration.

External Stored Procedures

Introduction

External stored procedures are written in the C, C++, or Java programming language, installed on the database, and then executed like stored procedures.

Usage

Here is a synopsis of the steps you take to develop, compile, install, and use external stored procedures: 1 Write, test, and debug the C, C++, or Java code for the procedure. 2 If you are using Java, place the class or classes for the external stored procedure in an archive file (JAR or ZIP) and call the SQLJ.INSTALL_JAR external stored procedure to register the archive file with the database. 3 Use CREATE PROCEDURE or REPLACE PROCEDURE for external stored procedures to create a database object for the external stored procedure. 4 Use GRANT to grant privileges to users who are authorized to use the external stored procedure. 5 Invoke the procedure using the CALL statement.

User-Defined Functions

Introduction

SQL provides a set of useful functions, but they might not satisfy all of the particular requirements you have to process your data.

User-defined functions (UDFs) allow you to extend SQL by writing your own functions in the C, C++, or Java programming language, installing them on the database, and then using them like standard SQL functions.

You can also install UDF objects or packages from third-party vendors.

UDF Types

Teradata Database supports three types of UDFs.

UDF Type Description

Scalar Scalar functions take input parameters and return a single value result. Examples of standard SQL scalar functions are CHARACTER_LENGTH, POSITION, and TRIM.

Usage

Here is a synopsis of the steps you take to develop, compile, install, and use a UDF: 1 Write, test, and debug the C, C++, or Java code for the UDF. 2 If you are using Java, place the class or classes for the UDF in an archive file (JAR or ZIP) and call the SQLJ.INSTALL_JAR external stored procedure to register the archive file with the database. 3 Use CREATE FUNCTION or REPLACE FUNCTION to create a database object for the UDF. 4 Use GRANT to grant privileges to users who are authorized to use the UDF. 5 Call the function.