Macro

A MACRO is a Teradata extension to ANSI SQL that contains prewritten SQL statements.The actual text of the macro is stored in a global repository called the Data Dictionary (DD).

Unlike a view, a macro can store one or multiple SQL statements. Additionally, the SQL is not restricted to only SELECT operations. INSERT, UPDATE, and DELETE commands are valid within a macro. When using BTEQ, conditional logic and BTEQ commands may also be incorporated into the macro.

A macro allows you to name a set of one or more statements. When you need to execute those statements, simply execute the named macro. Macros provide a convenient shortcut for executing groups of frequently-run SQL statements.

Below is complete list of commands to manipulate macros.

CREATE MACRO macroname AS ( . . . );

Rules for Macro

1. Macros can contain one or more SQL Statements​

2. Macros are a database Extension in Teradata and not ANSI-Standard​

3. Macros do NOT require PERM Space​

4. Macros are stored in the Data Dictionary in the USER DBC​

5. Every statement in the macro must end in a semi-colon ;​

6. Only one DDL Statement can exist in a Macro and it must be the last statement​

7. Input Parameters can be passed to the Macro​

8. Macros run when users use the EXEC Macro Command​

9. Macros can call views and even other Macros​

10. All SQL Statements inside a Macro are considered one Transaction​

11. Each Macro name must be a unique object within the database it resides​

12. USERS need only the EXEC privilege to run a macro​


13. No underlying privileges to tables inside the macro need be required

No comments:

Post a Comment