Teradata - Macros
Macro is a set of SQL statements which are stored and executed by calling the Macro name. The definition of Macros is stored in Data Dictionary. Users only need EXEC privilege to execute the Macro. Users don't need separate privileges on the database objects used inside the Macro. 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.
Macros are created using CREATE MACRO statement.
Following is the generic syntax of CREATE MACRO command.
CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( <sql statements> );
Consider the following Employee table.
The following example creates a Macro called Get_Emp. It contains a select statement to retrieve records from employee table.
CREATE MACRO Get_Emp AS ( SELECT EmployeeNo, FirstName, LastName FROM employee ORDER BY EmployeeNo; );
Macros are executed using EXEC command.
Following is the syntax of EXECUTE MACRO command.
The following example executes the Macro names Get_Emp; When the following command is executed, it retrieves all records from employee table.
EXEC Get_Emp; *** Query completed. 5 rows found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName LastName ----------- ------------------------------ --------------------------- 101 Mike James 102 Robert Williams 103 Peter Paul 104 Alex Stuart 105 Robert James
Teradata Macros can accept parameters. Within a Macro, these parameters are referenced with ; (semicolon).
Following is an example of a Macro that accepts parameters.
CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( SELECT EmployeeNo, NetPay FROM Salary WHERE EmployeeNo = :EmployeeNo; );
Executing Parameterized Macros
Macros are executed using EXEC command. You need EXEC privilege to execute the Macros.
Following is the syntax of EXECUTE MACRO statement.
The following example executes the Macro names Get_Emp; It accepts employee no as parameter and extracts records from employee table for that employee.
EXEC Get_Emp_Salary(101); *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. EmployeeNo NetPay ----------- ------------ 101 36000