Teradata Stored ProcedureA stored procedure contains a set of SQL statements and procedural statements. They consist of a set of control and condition handling comments that make SQL a computationally complete programming language. The definition of the stored procedure is stored in the database, and the parameters are stored in data dictionary tables. These features provide a server-based procedural interface to Teradata Database for application programmers. Stored procedures can be a great benefit for some tactical query applications. This section provides:
AdvantagesTeradata Stored Procedure provides the following essential advantages, such as:
Elements of Stored ProceduresThe set of statements constituting the main tasks of the stored procedure is called the stored procedure body, consisting 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. The following statements are not allowed, such as:
A compound statement stored procedure body consists of a BEGIN-END statement enclosing a set of declarations and statements, including:
Creating a Stored ProcedureTeradata stored procedure is created from the following commands:
The procedures are stored in the user database space as objects and are executed on the server. Syntax Following is the syntax of the CREATE PROCEDURE statement. Example Consider the following Salary Table of the employees.
The following example creates a stored procedure named InsertSalary to accept the values and insert them into the Salary Table. Executing Stored ProceduresIf we have sufficient privileges, we can execute a stored procedure from any supporting client utility or interface using the SQL CALL statement. We can also execute a stored procedure from an external stored procedure written in C, C++, or Java. We have to specify arguments for all the parameters contained in the stored procedure. Syntax Following is the generic syntax of the CALL statement. Example The below example calls the stored procedure InsertSalary and inserts records to Salary Table of the employees.
Next TopicTeradata User Management |