Javatpoint Logo
Javatpoint Logo

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.

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

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.

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.

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.

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.





Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA