15.00 - Stored Procedures - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Stored Procedures

Stored procedures are called Persistent Stored Modules in the ANSI/ISO 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/ISO 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  

    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  

    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”:

       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. They 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

     

    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

    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

     

    For details on …

    See …

    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.