Macros in Teradata

Macros

Introduction

A frequently used SQL statement or series of statements can be incorporated into a macro and defined using the SQL CREATE MACRO statement. See “CREATE MACRO” in SQL Data Definition Language.

The statements in the macro are performed using the EXECUTE statement. See “EXECUTE (Macro Form)” in SQL Data Manipulation Language.

A macro can include an EXECUTE statement that executes another macro.

• guidelines for creating triggers • conditions that cause triggers to fire • trigger action that occurs when a trigger fires • the trigger action time • when to use row triggers and when to use statement triggers

CREATE TRIGGER in SQL Data Definition Language.

• temporarily disabling triggers • enabling triggers • changing the creation timestamp of a trigger

ALTER TRIGGER in SQL Data Definition Language.

permanently removing triggers from the system DROP TRIGGER in SQL Data Definition Language.

Definition

A macro consists of one or more statements that can be executed by performing a single statement. Each time the macro is performed, one or more rows of data can be returned.

Performing a macro is similar to performing a multistatement request (see “Multistatement Requests” on page 135).

Single-User and Multiuser Macros

You can create a macro for your own use, or grant execution authorization to others.

For example, your macro might enable a user in another department to perform operations on the data in Teradata Database. When executing the macro, a user need not be aware of the database being accessed, the tables affected, or even the results.

With the exception of CREATE AUTHORIZATION and REPLACE AUTHORIZATION, a data definition statement is allowed in macro if it is the only SQL statement in that macro.

A data definition statement is not resolved until the macro is executed, at which time unqualified database object references are fully resolved using the default database of the user submitting the EXECUTE statement. If this is not the desired result, you must fully qualify all object references in a data definition statement in the macro body.

A macro can contain parameters that are substituted with data values each time the macro is executed. It also can include a USING modifier, which allows the parameters to be filled with data from an external source such as a disk file. A COLON character prefixes references to a parameter name in the macro. Parameters cannot be used for data object names.

Executing a Macro

Regardless of the number of statements in a macro, Teradata Database treats it as a single request.

When you execute a macro, either all its statements are processed successfully or none are processed. If a macro fails, it is aborted, any updates are backed out, and the database is returned to its original state.

Ways to Perform SQL Macros in Embedded SQL

Macros in an embedded SQL program are performed in one of the following ways.

IF the macro … THEN use …

is a single statement, and that statement returns no data

• the EXEC statement to specify static execution of the macro -or-

• the PREPARE and EXECUTE statements to specify dynamic execution. Use DESCRIBE to verify that the single statement of the macro is not a data returning statement.

Static SQL Macro Execution in Embedded SQL

Static SQL macro execution is associated with a macro cursor using the macro form of the DECLARE CURSOR statement.

When you perform a static macro, you must use the EXEC form to distinguish it from the dynamic SQL statement EXECUTE.

Dynamic SQL Macro Execution in Embedded SQL

Define dynamic macro execution using the PREPARE statement with the statement string containing an EXEC macro_name statement rather than a single-statement request.

The dynamic request is then associated with a dynamic cursor. See “DECLARE CURSOR (Macro Form)” in SQL Stored Procedures and Embedded SQL for further information on the use of macros.

Dropping, Replacing, Renaming, and Retrieving Information About a Macro

For more information, see SQL Data Definition Language.

Archiving Macros

Macros are archived and restored as part of a database archive and restoration. Individual macros can be archived or restored using the ARCHIVE or RESTORE statements of the ARC utility.

For details, see Teradata Archive/Recovery Utility Reference.

• consists of multiple statements • returns data

a cursor, either static or dynamic.

The type of cursor used depends on the specific macro and on the needs of the application.

IF the macro … THEN use …

IF you want to … THEN use the following statement …

drop a macro DROP MACRO

redefine an existing macro REPLACE MACRO

rename a macro RENAME MACRO

get the attributes for a macro HELP MACRO

get the data definition statement most recently used to create, replace, or modify a macro

SHOW MACRO