Macro is a set of SQL statements that are stored and executed by calling the Macro name. The definition of Macros is stored in the Data Dictionary.
Users only need EXEC privilege to execute the Macro. Users don't need separate rights on the database objects used inside the Macro.
A macro consists of one or more statements that can be executed by performing a single statement. Each time the Macro is completed, one or more rows of data can be returned.
Macro statements are executed as a single transaction. If one of the SQL statements in Macro fails, then all the statements are rolled back. Macros can accept parameters. Macros can contain DDL statements, but that should be the last statement in Macro.
A frequently used SQL statement or series of statements can be incorporated into a macro and defined using the SQL CREATE MACRO statement. The comments in the Macro are performed using the EXECUTE statement. A macro can include an EXECUTE statement that executes another macro.
CREATE MACRO privilege on the database or user in which the Macro is to be created. The creator automatically gets the DROP MACRO and EXECUTES privileges WITH GRANT OPTION. The immediate owner of the Macro:
Macros are created using the CREATE MACRO statement. Following is the generic syntax of the CREATE MACRO command.
The following example creates a Macro called Get_Emp. It contains a select statement to retrieve records from the employee table.
The immediate owner of the Macro (the database in which the Macro resides) must have the necessary privileges on objects named in the request set for statements that are contained in the Macro.
Macros are executed using the EXEC command. Following is the syntax of the EXECUTE MACRO command.
The following example executes the Macro names Get_Emp; when the following command is executed, it retrieves all records from the employee table.
Teradata Macros can accept parameters. Within a Macro, these parameters are referenced with a semicolon (;).
Following is an example of a Macro that accepts parameters.
Executing Parameterized Macros
Macros are executed using the EXEC command. We need EXEC privilege to execute the Macros.
Following is the syntax of the EXECUTE MACRO statement.
The following example executes the Macro names Get_Emp. It accepts employee Id as a parameter and extracts records from the employee table for that employee.
Replace a Macro
REPLACE privilege depends on whether the Macro being replaced already exists.