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.