in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 47 48 49 50 51 52 < 53 > 54 55 56 57 58 59 .. 218 >> Next

A view definition selects and returns data from one or more columns of one or more base tables or other views, or a combination of tables and views. You can enforce access privileges, and you can use a WHERE clause to qualify particular rows and to perform functions such as SUM or AVERAGE.

Note: You can create hierarchies of views in which views are created on views. This can be useful, but be aware that deleting any of the lower-level views destroys dependencies of the higher-level views in the hierarchy.

Benefits of Using Views

There are several good reasons to use views, such as:

A simplified user perception of very large and complex tables

Security, by omitting to select the sensitive columns of the underlying data table

Well-defined, well-tested, high-performance access to data

Logical data independence, which minimizes the need to modify your applications if you restructure base tables

A column defined in a view can be derived and does not need to exist in the underlying base tables. For example, it is possible to display summed or averaged data in a column you name in the CREATE VIEW definition.

For more detailed information on views, see:

"Limiting Data Access with Views" on page 6-39

Teradata RDBMS Database Design

"CREATE VIEW" in Teradata RDBMS SQL Reference, Volume 4

Teradata RDBMS SQL Reference, Volume 1 and Volume 6

2 - 74

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Stored Procedures

Stored Procedures

A stored procedure is a set of compiled procedural statements and SQL statements. The procedural statements make it possible to write significant portions of complex applications purely in SQL.

Stored procedures also enable you to develop and execute certain types of SQL statements that cannot be performed interactively, such as:

Cursor control and declaration statements

Flow control statements and condition handlers I

Nested compound statements |

Dynamic SQL statements

Special forms of SQL statements (for example, SELECT INTO)

The client application is relieved of having to perform many intermediate steps because users can create, compile, store, execute, replace, and recompile their procedures directly on the Teradata RDBMS server instead of on the client. (For statement syntax and complete usage details, see Teradata RDBMS SQL Reference, Volume 6.)


You can develop procedures with complex application logic using nested compound statements. During transaction processing, Teradata RDBMS can | execute nested CALL statements as well as self-referencing (recursive) procedures, runtime exception and completion condition handling, and dynamic SQL. |

A stored procedure can initiate and complete a transaction or multiple transactions in succession, as required, and can be part of an encompassing transaction.


Stored procedures on Teradata RDBMS offer the following features:

Unlimited size of stored procedure object code |

Execution from any client platform

Semantics compliant with ANSI SQL99

Support of SQL, including:

Most DML, DCL, and DDL statements, including special forms

Dynamic SQL statements

Transaction control statements, including ABORT and ROLLBACK

Atomic UPSERT processing (INSERT IF ... ELSE UPDATE ...)

SQL control statements

Teradata RDBMS Database Administration

2 - 75 Chapter 2: Building the Teradata RDBMS

Stored Procedures

Updatable or read-only CURSORs via the FOR control statement or DECLARE, OPEN, FETCH, and CLOSE statements


Variable declaration statements

SQLSTATE-based exception and completion handlers

Generic exception and completion handlers

LOCKING modifiers, which can be used with all supported statements except CALL

Code caching, to enhance the performance of stored procedures that are executed repeatedly

Formatting strings for locale-specific conversion of data types such as INTEGER (monetary) and DATE

Comments in the stored procedure body Supporting Client Utilities

Stored procedure execution and DDL operations are supported by many Teradata client facilities, including:





I PreProcessor 2 (PP2) supports stored procedure execution, but not creation

Teradata SQL Assistant (formerly known as Queryman)

DMTEQ (via BTEQWIN in Teradata Manager)

You define stored procedures with these client utilities:

IF you write for . THEN use the . For instructions, see .
BTEQ DMTEQ (via BTEQWIN) .COMPILE BTEQ command "Stored Procedures" in Teradata RDBMS SQL Reference, Volume 6 Teradata Preprocessor2 Programmer Guide Basic Teradata Query Reference Teradata Manager User Guide Teradata Call-Level Interface Version 2 - Reference for Network-Attached Systems - Reference for Channel-Attached Systems Teradata Driverfor the JDBC Interface User Guide ODBC Driver for Teradata User Guide Teradata SQL Assistant for Microsoft Windows User Guide
Previous << 1 .. 47 48 49 50 51 52 < 53 > 54 55 56 57 58 59 .. 218 >> Next