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

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 122 123 124 125 126 127 < 128 > 129 130 131 132 133 134 .. 241 >> Next


Access to data via a macro is controlled by the access rights of the macro owner.

A user creating the macro must have the rights of all statements needed to be executed by the macro. To allow other users to execute the macro, the macro creator must have the privileges necessary to execute each statement, and all privileges must have the WITH GRANT OPTION.

Once a macro has been created, its owner is the database in which it exists, not the user who created it. The owning database must have all the appropriate rights for executing the macro, including WITH GRANT OPTION.

Not all of the data attributes that may be specified for table columns are relevant to, or operate fully (as a type declaration, type attribute) for, macro parameters. For example, CHECK constraints and the COMPRESS phrase are not supported.

_ Executing a macro constitutes an implicit transaction. Therefore, in

Macro Execution BTET transaction mode (Teradata RDBMS mode) a macro need not

be enclosed by a BEGIN TRANSACTION statement and an END TRANSACTION statement.

If the session executing a macro is in ANSI mode, then unless the body of the macro ends with a COMMIT, the actions of the macro are uncommitted until a commit or rollback occurs in subsequent statements. If a user defines a macro with a COMMIT, it may be executed only by sessions running in ANSI mode.

Because macro execution implies a transaction, the user receives no response to individual statements contained in the macro body until all statements have been completed successfully. Any object that is referenced by a statement is locked until the macro transaction is completed, or until the macro is terminated because of a statement error.

If a macro contains a data definition statement (CREATE, DROP, MODIFY, etc.), it may contain no other statement. Note that a data definition statement in a macro is not fully resolved until the macro is executed. At that time, unqualified references to database objects are resolved using the default database for the user submitting the EXECUTE statement. It is therefore recommended that object references in a macro data definition statement be fully qualified (e.g., databasename.tablename) in the macro body.

Not All Data Attributes Supported in Macro Parameters

8-64

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE MACRO

Note: If the macro is used only in ANSI mode, add a COMMIT to the end of the macro. If the macro is used in both ANSI and Teradata mode, the user should provide the COMMIT, when in ANSI mode.

Resolution of Unqualified Names

Consider the following example, in which the default database for user XYZ is database ABC, and the database for user LMN is database is DEF. User XYZ defines Macrol thus:

CREATE MACRO Macrol AS

(CREATE TABLE mambo AS (cl INTEGER, c2 INTEGER););

The fully-qualified (databasename.tablename) name was not supplied in the CREATE TABLE DDL statement.

Because CREATE MACRO is a DDL statement, the database name is not resolved when the macro is created, but instead, when the macro is executed. The result is that if user XYZ executes Macro1, table ABC.mambo is created, and if user LMN executes Macrol, table DEF.mambo is created.

Now, if user XYZ creates another macro (Macro2) thus:

CREATE MACRO Macro2 AS

(UPDATE tango SET counter = counter + 1;);

The fully-qualified (databasename.tablename) table name was not supplied in the UPDATE statement.

Because UPDATE is a DML statement, it is fully resolved when the macro is created. Subsequently, executing Macro2 results in ABC.tango being updated, whether executed by user ABC, LMN, or any other user.

To summarize, the following are true for unqualified names in macros:

• They are not resolved in DDL statements when the macro is created—they are resolved when the macro is executed.

• They are fully resolved in DML statements when the macro is created.

When the CREATE MACRO statement is executed, the RDBMS parses the text of statements in the macro, and resolves names that are not fully qualified in those statements. Note, however, that unqualified names in DDL statements in macros are left unresolved.

A macro defined using the * symbol is forever bound by the Using the * Symbol in definition of the table as of the time the macro was created.

Macros

For example:

CREATE MACRO GetEmp AS (SELECT * FROM Employee;);

Teradata RDBMS for UNIX SQL Reference

8-65
Teradata SQL Syntax Guide

CREATE MACRO

If a column is later added to or removed from the table, the following statement still returns the number of columns that existed in Employee when GetEmp was defined.

EXECUTE GetEmp;

If columns have been dropped or data types have been changed, executing the Macro may result in an error message or unexpected behavior.

In creating a macro, the name must be unique in the database, Naming Macros because with one exception, duplicate-named objects are not
Previous << 1 .. 122 123 124 125 126 127 < 128 > 129 130 131 132 133 134 .. 241 >> Next