15.00 - Macros - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Macros

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.

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.

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.

Contents of a Macro

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

 

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.

  • 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.

    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 more information on the use of macros.

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

     

    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

    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.