Teradata Macros

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 Macros

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:

It is the database in which it exists, not the user who created it.
Determines the macro access privileges, not the Macro.
Must have all the appropriate privileges for executing the Macro, including WITH GRANT OPTION

Syntax

Macros are created using the CREATE MACRO statement. Following is the generic syntax of the CREATE MACRO command.

CREATE MACRO <macroname> [(parameter1, parameter2,...)] 
( 
   <sql statements> 
);

macro_name: The name of the new Macro. If a fully qualified name is not specified, the default database or user is used. The name of a macro must be unique within its containing user or database.

Emp_Id	First_Name	Last_Name	BirthDate
202001	Mike	Richard	11/05/1990
202002	Robert	Williams	20/01/1988
202003	Peter	Collin	21/08/1985
202004	Alexa	Stuart	10/12/1992
202005	Robert	Peterson	19/03/1987

Salary Table:

Emp_Id	Gross	Deduction	NetPay
202001	40,000	4,000	36,000
202002	80,000	6,000	74,000
202003	90,000	7,000	83,000
202004	75,000	5,000	70,000
202005	80,000	00	80,000

The following example creates a Macro called Get_Emp. It contains a select statement to retrieve records from the employee table.

CREATE MACRO Get_Emp AS 
( 
   SELECT 
   Emp_Id, 
   First_Name, 
   Last_Name 
   FROM  
   employee 
   ORDER BY Emp_Id; 
);

Executing Macros

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.

Syntax

Macros are executed using the EXEC command. Following is the syntax of the EXECUTE MACRO command.

Example

The following example executes the Macro names Get_Emp; when the following command is executed, it retrieves all records from the employee table.

EXEC Get_Emp; 
      /* Query completed. 5 rows found. 3 columns returned */
      /* Total elapsed time was 1 second */

Emp_Id          First_Name           Last_Name 
202001           Mike                 Richard
202002           Robert               Williams
202003           Peter                Collin 
202004           Alexa                Stuart
202005           Robert               Peterson  

Parameterized Macros

Teradata Macros can accept parameters. Within a Macro, these parameters are referenced with a semicolon (;).

Example

Following is an example of a Macro that accepts parameters.

CREATE MACRO Get_Emp_Salary(Emp_Id INTEGER) AS 
( 
   SELECT 
   Emp_Id, 
   NetPay 
   FROM  
   Salary 
   WHERE Emp_Id = :Emp_Id; 
);

Executing Parameterized Macros

Macros are executed using the EXEC command. We need EXEC privilege to execute the Macros.

Syntax

Following is the syntax of the EXECUTE MACRO statement.

Example

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.

EXEC Get_Emp_Salary(202001); 

       /* Query completed. One row found. 2 columns returned */
       /* Total elapsed time was 1 second */
 
Emp_Id                  NetPay 
202001                  36,000

Replace a Macro

REPLACE privilege depends on whether the Macro being replaced already exists.

If the Macro already exists, the DROP MACRO privilege on the Macro or it contains a database or user.
If the Macro does not already exist, the CREATE MACRO privilege on the Macro or its containing database or user.

NOTE

Once a macro has been replaced, its immediate owner is the database in which it exists, not the user who replaced it.
The immediately owning database must have all the appropriate privileges for executing the Macro, including WITH GRANT OPTION.

Next TopicTeradata OLAP Functions

← prev next →